Rafa, ----- Original Message ----- From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, November 04, 2002 6:06 PM Subject: DeadLock bug using mysql/Innodb
> Description: > Hello, > > I have a deadlock problem when I try to execute some update sentences > using 'Lock For Update'. > > I need to create a new document which is identified by a unique number, > which is stored in the field 'Ped' of the table 'Pedidos'. > To obtain a new document number I add 1 to the counter of the last document, > and the counter has to begin with a character set formed by 4 characters. > > For example, I want to obtain the last document number which begins > by 'CA02'. The steps I must follow are: > > - I obtain the last document which begins by 'CA02'. I do the selection of > the last document using 'Select ... For Update': > Select Ped From Pedidos Where Ped>'CA02' And Ped<'CA02Z' > Order by Ped Desc Limit 1 For Update > > Now, I have the last document which begins by 'CA02' and I donīt > allow anyone to access to this record because I set an exclusive lock using > 'For Update'. > > If other user tries to create a new document with begins with the same charater > set 'CA02', he must execute the same above sentence and this user remains locked. > > But when the first user tries to insert the new document with the new number, > it obtains the following error: > Error 1213: Deadlock found When trying to get lock; try restarting transaction > > InnoDB executes a rollback sentence for the first user and the second user > is unlocked. > > I will explain the sentences executed with the client of mysql 4.0.4: > > User 1 User 2 > ------ ------ > Begin; Begin; > Select Ped from Pedidos Select Ped from Pedidos > where Ped>'CA02' where Ped>'CA02' > and Ped<'CA02Z' and Ped<'CA02Z' > order by Ped desc limit 1 order by Ped desc limit 1 > for update; for update; > >+++++++CA02000155 >---locked > Insert into Pedidos(ped) > values('CA02000156'); > >Error 1213 Deadlock found. Unlocked... > > It seems that the user 2 locks user 1, but it should not be because user 2 is actually > locked by user 1. > > On the other hand, the following sentences work fine: > User 1 User 2 > ------ ------ > Begin; Begin; > Select Ped from Pedidos Select Ped from Pedidos > where where > left(Ped,4)='CA02' left(Ped,4)='CA02' > order by Ped desc order by Ped desc > limit 1 for update; limit 1 for update; > >+++++++CA02000155 >---locked > Insert into Pedidos(ped) > values('CA02000156'); > >Ok. > Commit; >---unlocked > > I have sent to ftp://support.mysql.com/pub/mysql/secret the table definition > and data (Pedidos.txt) in a compressed file named Pedidos.zip so you can > reproduce the bug. thank you for a very detailed study of the problem. This is not a bug but inoptimality. A waiting next-key lock represents a cursor which has already started its scan. An ascending cursor might have scanned the place where the other user is trying to insert. An example (users run with AUTOCOMMIT=0): 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. 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. In your case the cursor probably travels downwards. But the InnoDB lock table is not aware of the direction the cursor came from. A general rule with InnoDB index record 'gap' and next-key locks is that they block inserts by other users but do not give the lock holder the right to do an insert. Since purge can remove delete marked records, gaps may merge, and two users may even both hold an X-lock on some gap in the index. > Thanks in advance, > Rafa Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com 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 > > > > __________________________________________________________________ > 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 > --------------------------------------------------------------------- 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