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]
