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]
