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 ()
}
COMMITin 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]
