DeadLock bug using mysql/Innodb

Description:
Hi Heikki,

In your case:

>CREATE TABLE t (a INT NOT NULL, PRIMARY KEY (a)) TYPE = INNODB;
>INSERT INTO t VALUES (90); INSERT INTO t VALUES (110);
>User 1:
>SELECT * FROM t WHERE a >= 100 FOR UPDATE;
>User 2:
>SELECT * FROM t WHERE a >= 100 FOR UPDATE;
>
>Now if user 1 tries to do INSERT INTO t VALUES (100), he will receive a deadlock.
>The algorithm is that a cursor in InnoDB always travels in one direction.
>User 2 has his cursor waiting on the row (110) for an exclusive row next-key lock.
>If we allowed user 1 to insert (100), it would be a 'phantom row' in User 2's SELECT:
>if User 1 committed his transaction, and User 2 would repeat his own SELECT,
>a new row (100) would have appeared in the result set.

In my case, it's a bit different because User 1 would insert row (116), a new last document.

I think that User 2 should not lock User 1. User 1 should be able to insert row (100),
and it would not be a 'phantom row' in User 2's SELECT because he has not get the
rows yet because they are locked by User 1. So, when User 1 does a COMMIT, User 2
would see rows 100 and 110. Also, the manual says:
"A select ... for update will read the latest available data setting exclusive locks
on each row it reads."

So User 2 must wait for User 1 Commit/Rollback to get the latest data.

And as you said:

>A way to fix the inoptimality would be to let User 2 reverse his cursor if
>it ends up waiting for a row lock and there is an insert immediately before
>that row. But that is a bit complicated.

It should be a good solution!!!

On the other hand, if I change the isolation level (available from version 4.0.5),
to READ COMMITED, all the selects for update will only lock index records not the gaps
before them, so I think this can be the solution for User 1 to insert row (100), isn´t it?.
So READ COMMITED is the solution for me to insert a new
last document without having to check for the dead-lock problem
and for locks work fine.

Thanks very much for your attention,

Rafa.

How-To-Repeat:
Select Ped from Pedidos where Ped>'CA02' and Ped<'CA02Z' order by
Ped DESC limit 1 for update

Fix:
-

Synopsis:Subject:DeadLock bug using mysql/Innodb

Submitter-Id:
Originator: Rafa
Organization: Pecomark
MySQL support: none
Severity: non-critical
Priority: medium
Category: mysqld-max-nt
Class: sw-bug
Release: mysqld 4.0.4 beta(InnoDB)

Exectutable: mysqld-max-nt
Environment: Pentium III-MMX, 500 MHZ, 540 MB
System: Windows 2000
Compiler: -
Architecture: i


____________________________________________________________ ______
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php