Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- Dante
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- Dante -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
First things first: You *are* on InnoDB, which has row-level locking instead of table-level ? On Mon, Feb 1, 2010 at 4:08 PM, Michael Dykman mdyk...@gmail.com wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- Dante -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Johan De Meersman wrote: First things first: You *are* on InnoDB, which has row-level locking instead of table-level ? Yes, both cli_lock and queue tables are InnoDB. The server is running MySQL 5.1.36. I find it strange that I would have so many of these deadlocks throughout a day when these queries run from 3 processes every 20 seconds. What's the chance that 2 scripts should be executing these queries simultaneously, and even if the probability exists, why is it causing this deadlock error each time? If I break the query into 2 parts ... like SELECT FOR UPDATE followed by the INSERT/UPDATE, would that help fix the errors? What is this error exactly, anyhow? Where is the deadlock ... is it on the select or the insert? -- Dante On Mon, Feb 1, 2010 at 4:08 PM, Michael Dykman mdyk...@gmail.com mailto:mdyk...@gmail.com wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com mailto:da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- Dante -- -- D. Dante Lorenso da...@lorenso.com 972-333-4139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The SELECT FOR UPDATE is supposed to lock those rows selected.. an operation in another connection attempting to read or modify those rows gets an error on the lock if it is still in place. That is that SELECT FOR UPDATE is supposed to do. If that is not the behaviour you want, then why are you using the lock? - michael dykman On Mon, Feb 1, 2010 at 12:29 PM, D. Dante Lorenso da...@lorenso.com wrote: Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Try to run SHOW ENGINE INNODB STATUS; Near the top there will be some information on the latest deadlock. That might help you to understand what is deadlocking. Sometimes changing the query or changing the indexes can remove the condition that causes the deadlock. I don't know whether you have triggers on any of your tables? If so that's one place to watch for as well as the deadlock will show up as it is on the original query even if it is a trigger causing it. Jesper On 02/02/2010, at 6:06 AM, Michael Dykman wrote: The SELECT FOR UPDATE is supposed to lock those rows selected.. an operation in another connection attempting to read or modify those rows gets an error on the lock if it is still in place. That is that SELECT FOR UPDATE is supposed to do. If that is not the behaviour you want, then why are you using the lock? - michael dykman On Mon, Feb 1, 2010 at 12:29 PM, D. Dante Lorenso da...@lorenso.com wrote: Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? unsub=jes...@noggin.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
May I suggest this link? I found it useful. I haven't looked but there may be more recent posts with additional information. http://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/ Donna From: Jesper Wisborg Krogh jes...@noggin.com.au To: mysql@lists.mysql.com Date: 02/01/2010 03:43 PM Subject: Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Try to run SHOW ENGINE INNODB STATUS; Near the top there will be some information on the latest deadlock. That might help you to understand what is deadlocking. Sometimes changing the query or changing the indexes can remove the condition that causes the deadlock. I don't know whether you have triggers on any of your tables? If so that's one place to watch for as well as the deadlock will show up as it is on the original query even if it is a trigger causing it. Jesper On 02/02/2010, at 6:06 AM, Michael Dykman wrote: The SELECT FOR UPDATE is supposed to lock those rows selected.. an operation in another connection attempting to read or modify those rows gets an error on the lock if it is still in place. That is that SELECT FOR UPDATE is supposed to do. If that is not the behaviour you want, then why are you using the lock? - michael dykman On Mon, Feb 1, 2010 at 12:29 PM, D. Dante Lorenso da...@lorenso.com wrote: Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? unsub=jes...@noggin.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdevaudre...@sironahealth.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
