List:General Discussion« Previous MessageNext Message »
From:Jesper Wisborg Krogh Date:February 1 2010 8:42pm
Subject:Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
View as plain text  
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  
> <dante@stripped> 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 <dante@stripped>  
>>> 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
>  - mdykman@stripped
>
>  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=jesper@stripped
>

Thread
Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transactionDante Lorenso1 Feb
  • Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transactionMichael Dykman1 Feb
    • Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transactionJohan De Meersman1 Feb
      • Re: Serialization failure: 1213 Deadlock found when trying to getlock; try restarting transactionD. Dante Lorenso1 Feb
    • Re: Serialization failure: 1213 Deadlock found when trying to getlock; try restarting transactionD. Dante Lorenso1 Feb
      • Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transactionMichael Dykman1 Feb
        • Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transactionJesper Wisborg Krogh1 Feb
          • Re: Serialization failure: 1213 Deadlock found when trying to get lock; tryrestarting transactionMadonna DeVaudreuil1 Feb