Deadlock found when trying to get lock; try restarting transaction

[Pooly]

Hi,

I had that error for quiet a long time, and I usually restart the
transaction, but sometimes I have to do it 2 or 3 times, and I don't
really understand how it can happen.
I've strip down an example, that does basically :

BEGIN;
SELECT id FROM ttt WHERE id=7 FOR UPDATE;
INSERT INTO ttt(id) VALUES (7);
DELETE FROM ttt WHERE id=7;
COMMIT;

I run 10 instances of the program in parallel and I get the error :
Deadlock found when trying to get lock; try restarting transaction.
The isolation level is the default one.
My understanding of the SELECT ... FOR UPDATE is that I should not get
that deadlock, all transaction should be waiting on this select. From
the manual :
A SELECT ... FOR UPDATE reads the latest available data, setting
exclusive locks on each row it reads.
All instances should select the latest data, or wait until the lock is released.
Does anyone have pointer for a better explanations ?

Here is my program which I run in parallel with :
for i in 1 2 3 4 5 6 7 8 9; do ./test_mysql $i & done


#include "mysql/mysql.h"
#include <stdio.h>

int main(int argc, char **argv)
{
MYSQL *mysql;
int insert =0;

my_init();

mysql = mysql_init((MYSQL*)NULL);
if(! mysql_real_connect( mysql,
"127.0.0.1",
"root",
"",
"test",
3306,
NULL,
CLIENT_COMPRESS) ) {
printf("Connexion failed.\n");
mysql_close(mysql);
} else {
int ret;
printf("%s : create table\n", argv[1]);
ret = mysql_query(mysql,
"CREATE TABLE IF NOT EXISTS ttt "
"( id integer unsigned NOT NULL AUTO_INCREMENT,"
"PRIMARY KEY(id) "
") Engine=InnoDB;");
if ( ret ) {
printf("%s : Creation failed %s\n", argv[1],
mysql_error(mysql));
return 1;
}
printf("%s : Begin\n", argv[1]);
ret = mysql_query(mysql, "BEGIN");
if (ret) {
printf("%s : Begin failed %s\n", argv[1],
mysql_error(mysql));
return 1;
}
printf("%s : Begin ok\n", argv[1]);
printf("%s : Select for update\n", argv[1]);
ret = mysql_query(mysql,
"SELECT id FROM ttt WHERE id=7 FOR UPDATE");
if ( ret ) {
printf("%s : select failed : %s\n", argv[1],
mysql_error(mysql));
return 1;
} else {
MYSQL_RES *res;
res = mysql_store_result(mysql);
if ( res && mysql_num_rows(res) ) {
printf("%s : found a row\n", argv[1]);
insert = 0;
} else {
printf("%s : found no row\n", argv[1]);
insert = 1;
}
if ( res )
mysql_free_result(res);
}
printf("%s : Select for udate OK\n", argv[1]);
printf("%s : sleep\n");
sleep(1);
/* should be ok to check and not fire a timeout */
if (insert ) {
printf("%s : insertion \n", argv[1]);
ret = mysql_query(mysql,
"INSERT INTO ttt(id) VALUES (7)");
if ( ret ) {
printf("%s : insert failed : %s\n",
argv[1], mysql_error(mysql));
return 1;
}
printf("%s : delete it \n", argv[1]);
ret = mysql_query(mysql,
"DELETE FROM ttt WHERE id=7");
if ( ret ) {
printf("%s : delete failed : %s\n",
argv[1], mysql_error(mysql));
return 1;
}
}
printf("%s : commit\n", argv[1]);
ret = mysql_query(mysql, "COMMIT");
if ( ret ) {
printf("%s : commit failed : %s\n", argv[1],
mysql_error(mysql));
return 1;
}
printf("%s : Commit ok\n", argv[1]);

}
return 0;
}



--
Pooly
Webzine Rock : http://www.w-fenec.org/

[Gleb Paharenko]

Hello.

Post by Pooly
BEGIN;
SELECT id FROM ttt WHERE id=3D7 FOR UPDATE;
INSERT INTO ttt(id) VALUES (7);
DELETE FROM ttt WHERE id=3D7;
COMMIT;

Maybe we have this scenario:



A, B - transactions.



A -> SELECT ... FOR UPDATE - is setting an X lock on index for id=8

(next key locking, it is preventing the insertion of record with id=8)



B -> INSERT ... - is trying to get a lock and waits for A to release a

lock

A-> INSERT ... - A already has a lock (which is wasn't released by an

UPDATE statement). A is trying to get another one,

but B is already in the queue





In my opinion, UPDATE statement should be run immediately after

SELECT ... FOR UPDATE. BTW, your situation is a beat easier to

reproduce with this perl script (I put it here in case somebody has a

better scenario):



[***@blend pl]$ cat deadlock.pl

#!/usr/bin/perl

use strict;

use DBI;

my ($dbh,$sql,$dsn);

$dsn =

"DBI:mysql:database=test;host=localhost;mysql_socket=/home/gleb/mysqls/tmp/mysql.sock.gleb.d";

$dbh = DBI->connect

($dsn,

"root","",

{RaiseError => 1})

or die "connecting : $DBI::errstr\n";



$sql = "create table if not exists ttt(

id int unsigned not null

auto_increment primary key)";



$dbh->do($sql);

$sql = "begin";

$dbh->do($sql);

$sql = "select id from ttt where id=7 for update";

$dbh->do($sql);

sleep(1);

$sql = "insert into ttt set id=7";

$dbh->do($sql);

sleep(1);

$sql = "delete from ttt where id=7";

$dbh->do($sql);

$sql = "commit";

$dbh->do($sql);

$dbh->disconnect;



Run it as

for i in 1 2 3 4 5 6 7 8 9; do ./deadlock.pl & done



The snip from the output of 'SHOW INNODB STATUS':



------------------------

051007 23:09:51

*** (1) TRANSACTION:

TRANSACTION 0 1976, ACTIVE 1 sec, process no 2119, OS thread id 2768907

insertin

g

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 320

MySQL thread id 160, query id 848 localhost root update

insert into ttt set id=7

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 3 n bits 72 index `PRIMARY` of table

`test/ttt`

trx id 0 1976 lock_mode X insert intention waiting

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info

bits 0

0: len 8; hex 73757072656d756d; asc supremum;;



*** (2) TRANSACTION:

TRANSACTION 0 1984, ACTIVE 1 sec, process no 2132, OS thread id 2899987

insertin

g

mysql tables in use 1, locked 1

3 lock struct(s), heap size 320

MySQL thread id 168, query id 863 localhost root update

insert into ttt set id=7

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 9 page no 3 n bits 72 index `PRIMARY` of table

`test/ttt`

trx id 0 1984 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info

bits 0

0: len 8; hex 73757072656d756d; asc supremum;;



*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 3 n bits 72 index `PRIMARY` of table

`test/ttt`

trx id 0 1984 lock_mode X insert intention waiting

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info

bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

Post by Pooly
Hi,
I had that error for quiet a long time, and I usually restart the
transaction, but sometimes I have to do it 2 or 3 times, and I don't
really understand how it can happen.
BEGIN;
SELECT id FROM ttt WHERE id=3D7 FOR UPDATE;
INSERT INTO ttt(id) VALUES (7);
DELETE FROM ttt WHERE id=3D7;
COMMIT;
Deadlock found when trying to get lock; try restarting transaction.
The isolation level is the default one.
My understanding of the SELECT ... FOR UPDATE is that I should not get
that deadlock, all transaction should be waiting on this select. From
A SELECT ... FOR UPDATE reads the latest available data, setting
exclusive locks on each row it reads.
All instances should select the latest data, or wait until the lock is rele=
ased.
Does anyone have pointer for a better explanations ?
for i in 1 2 3 4 5 6 7 8 9; do ./test_mysql $i & done
#include "mysql/mysql.h"
#include <stdio.h>
int main(int argc, char **argv)
{
MYSQL *mysql;
int insert =3D0;
my_init();
mysql =3D mysql_init((MYSQL*)NULL);
if(! mysql_real_connect( mysql,
"127.0.0.1",
"root",
"",
"test",
3306,
NULL,
CLIENT_COMPRESS) ) {
printf("Connexion failed.\n");
mysql_close(mysql);
} else {
int ret;
printf("%s : create table\n", argv[1]);
ret =3D mysql_query(mysql,
"CREATE TABLE IF NOT EXISTS ttt "
"( id integer unsigned NOT NULL AUTO_INCREM=
ENT,"
"PRIMARY KEY(id) "
") Engine=3DInnoDB;");
if ( ret ) {
printf("%s : Creation failed %s\n", argv[1],
mysql_error(mysql));
return 1;
}
printf("%s : Begin\n", argv[1]);
ret =3D mysql_query(mysql, "BEGIN");
if (ret) {
printf("%s : Begin failed %s\n", argv[1],
mysql_error(mysql));
return 1;
}
printf("%s : Begin ok\n", argv[1]);
printf("%s : Select for update\n", argv[1]);
ret =3D mysql_query(mysql,
"SELECT id FROM ttt WHERE id=3D7 FOR UPDATE=
");
if ( ret ) {
printf("%s : select failed : %s\n", argv[1],
mysql_error(mysql));
return 1;
} else {
MYSQL_RES *res;
res =3D mysql_store_result(mysql);
if ( res && mysql_num_rows(res) ) {
printf("%s : found a row\n", argv[1]);
insert =3D 0;
} else {
printf("%s : found no row\n", argv[1]);
insert =3D 1;
}
if ( res )
mysql_free_result(res);
}
printf("%s : Select for udate OK\n", argv[1]);
printf("%s : sleep\n");
sleep(1);
/* should be ok to check and not fire a timeout */
if (insert ) {
printf("%s : insertion \n", argv[1]);
ret =3D mysql_query(mysql,
"INSERT INTO ttt(id) VALUES (7)");
if ( ret ) {
printf("%s : insert failed : %s\n",
argv[1], mysql_error(mysql));
return 1;
}
printf("%s : delete it \n", argv[1]);
ret =3D mysql_query(mysql,
"DELETE FROM ttt WHERE id=3D7");
if ( ret ) {
printf("%s : delete failed : %s\n",
argv[1], mysql_error(mysql));
return 1;
}
}
printf("%s : commit\n", argv[1]);
ret =3D mysql_query(mysql, "COMMIT");
if ( ret ) {
printf("%s : commit failed : %s\n", argv[1],
mysql_error(mysql));
return 1;
}
printf("%s : Commit ok\n", argv[1]);
}
return 0;
}
--
Pooly
Webzine Rock : http://www.w-fenec.org/

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ ***@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com