List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:February 1 2010 3:08pm
Subject:Re: Serialization failure: 1213 Deadlock found when trying to get
lock; try restarting transaction
View as plain text  
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 <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?
>
> -- Dante
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
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