PHP script hangs when trying to run sql on a locked table

Question

So I am using mysqli and when I try to perform the following query:

UPDATE Security.Users SET firstName = 'RYANTEST' WHERE id = '1'

while the Security.Users table has a lock on it, my php script just hangs forever (so far the longest I have measured is about 11 mins).

Shouldn't this return an error or at least timeout?

I have tried setting the interactive_timeout and waiting_timeout on mysql with no luck. I have also tried setting the php script to timeout after 3 seconds and again no luck (using set_time_limit() inside the script directly).

I am not sure what else I can try.

score 0 · Answer 1 · answered Jun 07 '12 at 18:33

0

No, this is the normal behaviour. If a table is locked, all other request that also require a lock on the table will wait their turn, until the previous lock is released.

This is to ensure data integrity in competitive environments.

answered Jun 07 '12 at 18:33

Sebas

21,192
9
55
109

well if there is a queyr that takes an hour, that means my script is going to hang for that long. I want to be able to at lease error out to the user after a certain point. Is there really nothing I can do about this? – ryanzec Jun 07 '12 at 18:38
however, if your user has enough rights, you might want to detect locks on the table. I don't know if this is possible to get a locked/unlocked flag, but I know how to get more or less this information... give me a minute – Sebas Jun 07 '12 at 18:41
see this post: http://stackoverflow.com/questions/2499976/detecting-locked-tables-mysql-locked-by-lock-table – Sebas Jun 07 '12 at 18:43

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

Take a look at this SO post which describes on how to set timeout for mysql long running queries

How can I stop a MySQL query if it takes too long?

edited May 23 '17 at 10:34

Community

1
1

answered Jun 07 '12 at 18:33

Rahul

76,197
13
71
125

PHP script hangs when trying to run sql on a locked table

2 Answers2