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.


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

Re: DeadLock bug using mysql/Innodb

Rafa,

----- Original Message -----
From:
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:
> 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
> To unsubscribe, e-mail

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

Re: DeadLock bug using mysql/Innodb

Rafa,

----- Original Message -----
From:
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:
> 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 bugs-thread12904@lists.mysql.com
To unsubscribe, e-mail

JDBC driver: Buggy for DatabaseMetaData.getImported/Exported keys ?

Hi:

I am using Connector/J 3.0.2 against MySQL
3.23.52-max.

My default table type is set to InnoDB (since I
always use innodb).

The following methods are acting strangely:

- DatabaseMetaData.getImportedKeys()
- DatabaseMetaData.getExportedKeys()

Often these methods return partial information and
sometimes no information. Here's a simple test harness
that illustrates the issue.

--- set up test tables [cut-n-paste this] ------------

CREATE TABLE UserStatus (
id TINYINT NOT NULL AUTO_INCREMENT,
type CHAR(50),
PRIMARY KEY (id)
);

CREATE TABLE UserType (
id TINYINT NOT NULL AUTO_INCREMENT,
type CHAR(20) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE Locations (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
department VARCHAR(50),
PRIMARY KEY (id)
);

CREATE TABLE Users (
id INTEGER AUTO_INCREMENT,
locationID INTEGER DEFAULT NULL,
userTypeID TINYINT NOT NULL,
statusID TINYINT NOT NULL,
username VARCHAR(50) NOT NULL UNIQUE,
PRIMARY KEY (id),

FOREIGN KEY (statusID)
REFERENCES UserStatus(id), INDEX(statusID) ,
FOREIGN KEY (locationID)
REFERENCES Locations(id), INDEX(locationID) ,
FOREIGN KEY (userTypeID)
REFERENCES UserType(id), INDEX(userTypeID)
);

CREATE TABLE Groups (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE UsersAndGroups(
userID INTEGER NOT NULL,
groupID INTEGER NOT NULL,
dateAdded TIMESTAMP,
PRIMARY KEY (userID, groupID)
);

---------------- end test tables -------------

Create the above tables in a test database. Make
sure you specify innodb as the default table type.
(otherwise hack the above to add tabletype=innodb
for each table).

Now, after connecting to this database, call
getImportedKeys and getExportedKeys thru your
java code. Specify the "Users" table.

Findings:

1. ImportedKeys
Expected-to-see: statusID, locationID, userTypeID
Actual: statusID

2. ExportedKeys
Expected-to-see: UsersAndGroups
Actual: empty

I am trying to write an automated tool that examines
a database and generated a java dbobjects layer. It's
quite possible that I am missing something here but
it's also possible that the driver is buggy because
the expected and actual values diverge significantly.

Best regards,

--javadesigner@yahoo.com


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: JDBC driver: Buggy for DatabaseMetaData.getImported/Exportedkeys ?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

j.random.programmer wrote:
> Hi:
>
> I am using Connector/J 3.0.2 against MySQL
> 3.23.52-max.
>
> My default table type is set to InnoDB (since I
> always use innodb).
>
> The following methods are acting strangely:
>
> - DatabaseMetaData.getImportedKeys()
> - DatabaseMetaData.getExportedKeys()
>
> Often these methods return partial information and
> sometimes no information. Here's a simple test harness
> that illustrates the issue.

This was fixed in the version that is going to be 3.0.3 a few weeks ago.
The parsing has changed, so that it now uses 'SHOW CREATE TABLE', but it
is more complex, so there have been a few kinks to work out.

>
> --- set up test tables [cut-n-paste this] ------------
>
> CREATE TABLE UserStatus (
> id TINYINT NOT NULL AUTO_INCREMENT,
> type CHAR(50),
> PRIMARY KEY (id)
> );
>
> CREATE TABLE UserType (
> id TINYINT NOT NULL AUTO_INCREMENT,
> type CHAR(20) NOT NULL,
> PRIMARY KEY (id)
> );
>
> CREATE TABLE Locations (
> id INTEGER NOT NULL AUTO_INCREMENT,
> name VARCHAR(30),
> department VARCHAR(50),
> PRIMARY KEY (id)
> );
>
> CREATE TABLE Users (
> id INTEGER AUTO_INCREMENT,
> locationID INTEGER DEFAULT NULL,
> userTypeID TINYINT NOT NULL,
> statusID TINYINT NOT NULL,
> username VARCHAR(50) NOT NULL UNIQUE,
> PRIMARY KEY (id),
>
> FOREIGN KEY (statusID)
> REFERENCES UserStatus(id), INDEX(statusID) ,
> FOREIGN KEY (locationID)
> REFERENCES Locations(id), INDEX(locationID) ,
> FOREIGN KEY (userTypeID)
> REFERENCES UserType(id), INDEX(userTypeID)
> );
>
> CREATE TABLE Groups (
> id INTEGER NOT NULL AUTO_INCREMENT,
> name VARCHAR(50) NOT NULL,
> PRIMARY KEY (id)
> );
>
> CREATE TABLE UsersAndGroups(
> userID INTEGER NOT NULL,
> groupID INTEGER NOT NULL,
> dateAdded TIMESTAMP,
> PRIMARY KEY (userID, groupID)
> );
>
> ---------------- end test tables -------------
>
> Create the above tables in a test database. Make
> sure you specify innodb as the default table type.
> (otherwise hack the above to add tabletype=innodb
> for each table).
>
> Now, after connecting to this database, call
> getImportedKeys and getExportedKeys thru your
> java code. Specify the "Users" table.
>
> Findings:
>
> 1. ImportedKeys
> Expected-to-see: statusID, locationID, userTypeID
> Actual: statusID

This works correctly with the new code.

>
> 2. ExportedKeys
> Expected-to-see: UsersAndGroups
> Actual: empty

Why would you expect to see this? With the SQL you give above, you don't
reference the Users table from the UsersAndGroups table. The only table
that has foreign keys is the Users table.

Also, as an aside, it is better to send JDBC bug reports to either
bugs@lists.mysql.com (preferred), or to java@lists.mysql.com, because
the chance that they will get seen quicker by someone 'in the known' is
guaranteed.

-Mark
- --
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE97LWZtvXNTca6JD8RAvI9AKClvF/YyhMZBFtegAbc0/PFBqUL0gCb B8SO
AM1iOZPlJcv05rYFFcFK7go=
=TZ66
-----END PGP SIGNATURE-----


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