List:General Discussion« Previous MessageNext Message »
From:D. Dante Lorenso Date:February 1 2010 5:17pm
Subject:Re: Serialization failure: 1213 Deadlock found when trying to get
lock; try restarting transaction
View as plain text  
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 <mdykman@stripped 
> <mailto:mdykman@stripped>> 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 <dante@stripped
>     <mailto: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
>      >


-- 
----------
D. Dante Lorenso
dante@stripped
972-333-4139
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