Rafa, ----- Original Message ----- From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, November 05, 2002 11:00 AM Subject: 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. yes, in 4.0.5 if you do SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT * FROM t FOR UPDATE; that will only lock the existing rows, not the gaps between the rows. Then any user is free to insert new rows to the table. This is how SELECT ... FOR UPDATE works in Oracle. My guess is that 4.0.5 will be released around Nov 15th. User 1: mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where a >= 100 for update; +-----+ | a | +-----+ | 110 | +-----+ 1 row in set (0.00 sec) mysql> insert into t values (100); Query OK, 1 row affected (0.00 sec) User 2: mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where a >= 100 for update; +-----+ | a | +-----+ | 110 | +-----+ 1 row in set (19.42 sec) mysql> select * from t where a >= 100 for update; +-----+ | a | +-----+ | 100 | | 110 | +-----+ 2 rows in set (0.00 sec) > Thanks very much for your attention, > > Rafa. Regards, Heikki sql query > 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 --------------------------------------------------------------------- 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
