Yes, the query reported by mysql and the query in the source match. And
this query is executed once for each tag. However, from the message, it
is inconclusive that we're dealing with deadlocks here. While it is
unlikely different calls to AddEventResult.php will have exactly the
same tags, they all have multiple tags and a good amount of them may
also be the same for the multiple concurrent calls.
In any case, the sorting ensures there wont be any out-of-sequence
locking and should avoid deadlocks. I'm not sure this is an application
problem as deadlocks will be logged, separate from lock timeouts. And
this is not what you have seen. You my need to tune your database server
a bit more or find a way to increase the timeout. Thanks.
-Akara
Xuekun Hu wrote:
Hi, Guys
I am doing Olio-php-0.1 load testing. Currently with 10000 users, the
apache logs showed "Lock wait timeout" error as below. PS. 9500 users
was passed.
[Mon Aug 24 15:31:00 2009] [error] [client 192.168.224.1] exception
'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock
wait timeout exceeded; try restarting transaction' in
/var/www/oliophp/classes/PDOConnection.php:77\nStack trace:\n#0
/var/www/oliophp/classes/PDOConnection.php(77): PDO->exec('update
SOCIALEV...')\n#1
/var/www/oliophp/public_html/addEventResult.php(101):
PDOConnection->exec('update SOCIALEV...')\n#2 {main}
... ...
From mysql "show innodb status" and "show processlist", the query
statement of "update SOCIALEVENTTAG set refcount = refcount + 1 where
tag='$tag'" were executing long time and the executing time were
continuted to increase.
In the AddEventResult.php code, I found the comment of "// We need to
sort the tags before insert/update. Different tag sequences can lead
to deadlocks." I checked the change log that coming from OLIO-45
issue. However I still can't fully understand why there are deadlocks
happnened? Will different transactions calling to AddEventResult.php
get the same tag record log sequence? Can anyone explicate a bit more?
I just suspect the "lock wait timeout" maybe also be caused by the
same possible dead lock.
Thx, Xuekun
