David, next-key locks are purely inhibitive. Even though transaction (2) has an X-lock on the 'supremum' of the index, it cannot insert because also transaction (1) has an X-lock on the 'supremum'.
Why is it allowed that two transactions can both have an X-lock on a 'gap' in the index (the supremum is a special case of a gap)? We have to allow it because purge may remove records from the index, and two gaps can merge. In this specific case, the cursor of transaction (1) has already passed the place where transaction (2) is trying to insert. If we would allow (2) to do the insert, then the cursor of (1) should be more intelligent than it is now. It should look back at the inserted record, and check if it is in the result set the cursor is trying to read. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html ........... From: David Edwards ([EMAIL PROTECTED]) Subject: Re: InnoDB deadlock problem View: Complete Thread (3 articles) Original Format Newsgroups: mailing.database.myodbc Date: 2004-10-07 04:11:35 PST --0-1154760343-1097147469=:85242 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Tobias, Thanks for your reply. Unfortunately I couldn't see from the manual why I was getting the deadlock - transaction 2 already has a lock on the index it is waiting for. The difference seems to be 'insert intention' - I'm not sure what different types of exclusive lock there are and how they relate to each other. Is there any way I can get both types of lock in one go, in the first statement I execute? Thanks, David Tobias Asplund <[EMAIL PROTECTED]> wrote: On Wed, 6 Oct 2004, David Edwards wrote: > I've got a deadlock problem using InnoDB tables (...) > Transaction 1: > START TRANSACTION; > DELETE FROM results WHERE id_job = 25920; > INSERT INTO results(result,id_job) VALUES (31.461937,25920); > COMMIT; > > Transaction 2: > START TRANSACTION; > DELETE FROM results WHERE id_job = 25919; > INSERT INTO results(result,id_job) VALUES (25.388607,25919),(22.650234,25919); > COMMIT; I think this manual page might explain what's happening: http://dev.mysql.com/doc/mysql/en/InnoDB_Next-key_locking.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
