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
