Hello.
> 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):
[EMAIL PROTECTED] 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;;
Pooly wrote:
> 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=3D7 FOR UPDATE;
> INSERT INTO ttt(id) VALUES (7);
> DELETE FROM ttt WHERE id=3D7;
> 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 rele=
> ased.
> 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 =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
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
