List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:October 19 2004 12:12pm
Subject:Re: InnoDB deadlock problem
View as plain text  
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 (de1726@stripped)
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 <tobias@stripped> 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

Thread
InnoDB deadlock problemDavid Edwards7 Oct
  • Re: InnoDB deadlock problemTobias Asplund7 Oct
    • Re: InnoDB deadlock problemDavid Edwards7 Oct
Re: InnoDB deadlock problemHeikki Tuuri19 Oct