Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Michael Dykman (mdykmangmail.com)
Date: Mon Feb 01 2010 - 09:08:48 CST
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 <dantelorenso.com> wrote:
> 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: 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
> -- Dante
- michael dykman
May the Source be with you.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql