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.
Thanks in advance,
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
