Michael Dykman 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.
Sounds like the answer is "that's just the way MySQL is". I don't
usually like those kinds of answers. I've written similar queries in
another DB and never got these types of errors. Perhaps there is a
better way to create a "queue" system that avoids this problem entirely?
I 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:
state1 ---> processing1 --> state2 ---> processing2 ---> state3
I want to find a record that is in state1 and reserve the right to
process it. After 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. I am actually using PHP/MySQL and this problem sounds like a job
for a message queue. So, 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
the app run as if the message never occurred (since there's not a
problem with seeing that message)?
-- Dante
- michael dykman
On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso <da...@lorenso.com> 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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
