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: <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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
