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

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.. =A0that is just the lock doing it's job.
>> Take that advice literally.. =A0when you fail with that class of
>> exception, delay a milli-second or two and retry. =A0For a large PHP
>> site I designed, we had that behaviour built-in: up to three attempts
>> waits 5, then 10 ms between trys. =A0In 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". =A0I don't usua=
lly
> like those kinds of answers. =A0I've written similar queries in another D=
B and
> never got these types of errors. =A0Perhaps there is a better way to crea=
te a
> "queue" system that avoids this problem entirely? =A0I 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:
>
> =A0 =A0state1 ---> processing1 --> state2 ---> processing2 ---> state3
>
> I want to find a record that is in state1 and reserve the right to proces=
s
> it. =A0After 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. =
=A0I am
> actually using PHP/MySQL and this problem sounds like a job for a message
> queue. =A0So, 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 th=
e
> app run as if the message never occurred (since there's not a problem wit=
h
> seeing that message)?
>
> -- Dante
>
>
>>
>> =A0- 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=
 one
>>> of
>>> my processing threads. =A0I do this by inserting the unique ID of the
>>> record I
>>> want to reserve into my "cli_lock" table. =A0The following query is wha=
t 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 differe=
nt
>>> 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?
>



--=20
 - michael dykman
 - mdykman@stripped

 May the Source be with you.