List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:February 1 2010 3:31pm
Subject:Re: Serialization failure: 1213 Deadlock found when trying to get
lock; try restarting transaction
View as plain text  
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 <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> 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.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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