Problem with MySQL and lock conflict management
Posted: Mon Apr 25, 2011 4:52 pm
-newly installed standard windows MySQL 5.1
-i lock a record with my SQL editor (Heidi) on a InnoDB table : "begin;update XX set YY=1 where ZZ=1" (without commit)
-on LiveCode : i connect to the same DB, and i do a simple :
revExecuteSQL dbIDM, "update XX set YY=1 where ZZ=1"
LiveCode freeze for 20 seconds and then I receive an error message "Lost connection to MySQL server during query".
I tried the other way around (the begin + update without commit on LiveCode) and then a simple update within HeidiSQL : then HeidiSQL freezes, and a few seconds after, I got an error message : "SQL Error 1205 : lock wait timeout exceeded; try restarting transaction"
What's the point to freeze like that ? If a record is locked, why I can't have a proper negative feedback, so I can issue a rollback or warn the user ?
Or do i miss the whole thing ?
Furthermore : I can't issue the rollback on livecode, if the connection is lost ?
-i lock a record with my SQL editor (Heidi) on a InnoDB table : "begin;update XX set YY=1 where ZZ=1" (without commit)
-on LiveCode : i connect to the same DB, and i do a simple :
revExecuteSQL dbIDM, "update XX set YY=1 where ZZ=1"
LiveCode freeze for 20 seconds and then I receive an error message "Lost connection to MySQL server during query".
I tried the other way around (the begin + update without commit on LiveCode) and then a simple update within HeidiSQL : then HeidiSQL freezes, and a few seconds after, I got an error message : "SQL Error 1205 : lock wait timeout exceeded; try restarting transaction"
What's the point to freeze like that ? If a record is locked, why I can't have a proper negative feedback, so I can issue a rollback or warn the user ?
Or do i miss the whole thing ?
Furthermore : I can't issue the rollback on livecode, if the connection is lost ?