First things first: You *are* on InnoDB, which has row-level locking instead of table-level ?
On Mon, Feb 1, 2010 at 4:08 PM, Michael Dykman <mdyk...@gmail.com> 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 <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? > > > > -- Dante > > > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
