From: Michael Dykman
Date: February 1 2010 3:08pm
Subject: Re: Serialization failure: 1213 Deadlock found when trying to get 
	lock; try restarting transaction
List-Archive: http://lists.mysql.com/mysql/220530
Message-Id: <814b9a821002010708r2b35287ck2fb1767b03be4e2@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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". =A0I want to lock an item from a table for exclusive access by o=
ne of
> my processing threads. =A0I do this by inserting the unique ID of the rec=
ord I
> want to reserve into my "cli_lock" table. =A0The 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
> =A0LEFT JOIN cli_lock l ON l.object_id =3D q.queue_id AND l.lock_type =3D=
 'parse'
> WHERE l.object_id IS NULL
> =A0AND q.status =3D '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 whe=
n
> trying to get lock; try restarting transaction
>
> Am I writing my query wrong or expecting behavior that MySQL doesn't
> support?
>
> -- Dante
>



--=20
 - michael dykman
 - mdykman@stripped

 May the Source be with you.