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
