Re: deadlock with innodb
Gleb Paharenko wrote: Hello. Among other things check that you correctly process lock timeouts. I've check this, but it seems fine. I'm testing it this way with 3 applications on the same computer (2 processors) : - 2 clients applications with an open connection to the DB (which is remote with single processor) are waiting for a event to be trigger by the network. When the event comes they fire the query (BEGIN; SELECT ... FOR UPDATE; do stuff; COMMIT/ROLLBACK ). - 1 supervisor application that send those events to the clients applications (in order to reproduce production conditions). If I send the two event without delay ( send client1; send client2 ) the SELECT .. FOR UPDATE goes through for the _two_ clients at the same time and then cause InnoDB to complain about a deadlock. If i introduce a delay of 20 ms (send client1; sleep(20 ms); send client 2); only one SELECT .. FOR UPDATE goes through, the other one does wait until it's commited/rollback as expected (and so reproduce what I can observe if I do it by hand). The problem is just that if the two SELECT ... FOR UPDATE does arrive at the same time, it throws a deadlock. Well, if that's the expected behaviour, it's fine with me, but I still don't undestand why it does happen. Thanks for your help, Well, I'm sure it's a bug hidden somewhere in my apps, i've check with another connexion and it worked ;) -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deadlock with innodb
Philippe Poelvoorde wrote:
Gleb Paharenko wrote:
Hello.
What transaction isolation level do you use? By the
way - there's a fresh bug related to SELECT ... FOR UPDATE:
http://bugs.mysql.com/bug.php?id=9512
tx_isolation is set to : REPEATABLE-READ (which is the default)
I've stripped everything uneeded from my code, and printf debug messages
before the query (the string sent to mysql_query ) and a printf after
the query returns. I do the query with two application in parallel,
sleep for 3 seconds after SELECT .. FOR UPDATE then carry on with the
rest of the code. So if I understand well the SELECT ... FOR UPDATE
statement, one of the application should hang on mysql_query() (from the
C api) until the other one either do a COMMIT or a ROLLBACK. But from
what I observe is that the 2 queries goes through _and_ returns. (thus i
get two empty sets and try to insert twice the various records in my two
tables)
Well, I'm sure it's a bug hidden somewhere in my apps, i've check with
another connexion and it worked ;)
If I try to do it on the command line, it works as expected.
Can this be related to the libmysql library ? I'm using on the client
side the version that comes with 4.0.20a for windows, and the server is
a 4.1.10a (Linux).
Thanks for your help,
Philippe Poelvoorde [EMAIL PROTECTED] wrote:
Hello,
here is a snippet of my code :
BEGIN
SELECT ... FROM table1, table2 ... FOR UPDATE
is_present = false
if ( we have results ) {
for ( all results ) {
SELECT COUNT(*) FROM table1 ... FOR UPDATE
if ( match all conditions )
is_present = true
}
}
if ( is_present == false ) {
INSERT INTO table1 VALUES ()
INSERT INTO table2 VALUES ()
}
COMMIT
in all errors I do a rollback.
This code is intended to insert a component into 2 tables and must
ensure that the component is unique before inserting. This code is
the same across several clients that try to do the same at the same
time.
If I do it by hand with two mysql client, it works (one mysqlclient
wait on the SELECT ... FOR UPDATE while i can insert with the other
one, then the SELECT .. FOR UPDATE returns with the first
mysqlclient), but with my applications, I sometimes get an error 1213
(DEADLOCK) from innodb. The documentation state that the transaction
should be rerun. If I do it, it works fine. What does cause this
deadlock ?
If I trace my queries I could see the inserts going _twice_ and one
does fails on this deadlock. I don't really understand why the two
applications try to insert data since I've specified the FOR UPDATE
in the SELECT to lock insertion of new record. Is there anything I'm
mistaking ?
Thanks for your help,
--
Philippe Poelvoorde
COS Trading Ltd.
+44.(0)20.7376.2401
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deadlock with innodb
Hello. Among other things check that you correctly process lock timeouts. Well, I'm sure it's a bug hidden somewhere in my apps, i've check with another connexion and it worked ;) -- 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]
Re: deadlock with innodb
Hello.
What transaction isolation level do you use? By the
way - there's a fresh bug related to SELECT ... FOR UPDATE:
http://bugs.mysql.com/bug.php?id=9512
Philippe Poelvoorde [EMAIL PROTECTED] wrote:
Hello,
here is a snippet of my code :
BEGIN
SELECT ... FROM table1, table2 ... FOR UPDATE
is_present = false
if ( we have results ) {
for ( all results ) {
SELECT COUNT(*) FROM table1 ... FOR UPDATE
if ( match all conditions )
is_present = true
}
}
if ( is_present == false ) {
INSERT INTO table1 VALUES ()
INSERT INTO table2 VALUES ()
}
COMMIT
in all errors I do a rollback.
This code is intended to insert a component into 2 tables and must
ensure that the component is unique before inserting. This code is the
same across several clients that try to do the same at the same time.
If I do it by hand with two mysql client, it works (one mysqlclient wait
on the SELECT ... FOR UPDATE while i can insert with the other one, then
the SELECT .. FOR UPDATE returns with the first mysqlclient), but with
my applications, I sometimes get an error 1213 (DEADLOCK) from innodb.
The documentation state that the transaction should be rerun. If I do
it, it works fine. What does cause this deadlock ?
If I trace my queries I could see the inserts going _twice_ and one does
fails on this deadlock. I don't really understand why the two
applications try to insert data since I've specified the FOR UPDATE in
the SELECT to lock insertion of new record. Is there anything I'm
mistaking ?
Thanks for your help,
--
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]
Re: deadlock with innodb
Gleb Paharenko wrote:
Hello.
What transaction isolation level do you use? By the
way - there's a fresh bug related to SELECT ... FOR UPDATE:
http://bugs.mysql.com/bug.php?id=9512
tx_isolation is set to : REPEATABLE-READ (which is the default)
I've stripped everything uneeded from my code, and printf debug messages
before the query (the string sent to mysql_query ) and a printf after
the query returns. I do the query with two application in parallel,
sleep for 3 seconds after SELECT .. FOR UPDATE then carry on with the
rest of the code. So if I understand well the SELECT ... FOR UPDATE
statement, one of the application should hang on mysql_query() (from the
C api) until the other one either do a COMMIT or a ROLLBACK. But from
what I observe is that the 2 queries goes through _and_ returns. (thus i
get two empty sets and try to insert twice the various records in my two
tables)
If I try to do it on the command line, it works as expected.
Can this be related to the libmysql library ? I'm using on the client
side the version that comes with 4.0.20a for windows, and the server is
a 4.1.10a (Linux).
Thanks for your help,
Philippe Poelvoorde [EMAIL PROTECTED] wrote:
Hello,
here is a snippet of my code :
BEGIN
SELECT ... FROM table1, table2 ... FOR UPDATE
is_present = false
if ( we have results ) {
for ( all results ) {
SELECT COUNT(*) FROM table1 ... FOR UPDATE
if ( match all conditions )
is_present = true
}
}
if ( is_present == false ) {
INSERT INTO table1 VALUES ()
INSERT INTO table2 VALUES ()
}
COMMIT
in all errors I do a rollback.
This code is intended to insert a component into 2 tables and must
ensure that the component is unique before inserting. This code is the
same across several clients that try to do the same at the same time.
If I do it by hand with two mysql client, it works (one mysqlclient wait
on the SELECT ... FOR UPDATE while i can insert with the other one, then
the SELECT .. FOR UPDATE returns with the first mysqlclient), but with
my applications, I sometimes get an error 1213 (DEADLOCK) from innodb.
The documentation state that the transaction should be rerun. If I do
it, it works fine. What does cause this deadlock ?
If I trace my queries I could see the inserts going _twice_ and one does
fails on this deadlock. I don't really understand why the two
applications try to insert data since I've specified the FOR UPDATE in
the SELECT to lock insertion of new record. Is there anything I'm
mistaking ?
Thanks for your help,
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deadlock with innodb
Hello,
here is a snippet of my code :
BEGIN
SELECT ... FROM table1, table2 ... FOR UPDATE
is_present = false
if ( we have results ) {
for ( all results ) {
SELECT COUNT(*) FROM table1 ... FOR UPDATE
if ( match all conditions )
is_present = true
}
}
if ( is_present == false ) {
INSERT INTO table1 VALUES ()
INSERT INTO table2 VALUES ()
}
COMMIT
in all errors I do a rollback.
This code is intended to insert a component into 2 tables and must
ensure that the component is unique before inserting. This code is the
same across several clients that try to do the same at the same time.
If I do it by hand with two mysql client, it works (one mysqlclient wait
on the SELECT ... FOR UPDATE while i can insert with the other one, then
the SELECT .. FOR UPDATE returns with the first mysqlclient), but with
my applications, I sometimes get an error 1213 (DEADLOCK) from innodb.
The documentation state that the transaction should be rerun. If I do
it, it works fine. What does cause this deadlock ?
If I trace my queries I could see the inserts going _twice_ and one does
fails on this deadlock. I don't really understand why the two
applications try to insert data since I've specified the FOR UPDATE in
the SELECT to lock insertion of new record. Is there anything I'm
mistaking ?
Thanks for your help,
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
