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/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
