How to cope with deadlocks?

[eluser]kezman[/eluser]
Hey everyone!
I've written an application (codeigniter 2) using MySQL InnoDB engine.
Recently I've started obtaining 1205 error

Code:

Lock wait timeout exceeded; try restarting transaction

I've optimized the length of my transactions, but it didn't help.

I've read
http://dev.mysql.com/doc/refman/5.0/en/i...locks.html

Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

How can I do this using CodeIgniter 2?

Thanks in advance!

PS I know that I can just increase "lock_wait_timeout" but this is not the best solution, because it doesn't solve the reason.

[eluser]rana[/eluser]
What about putting the whole transaction block inside a loop of maximum 5 times?

[eluser]Otemu[/eluser]
Hi,

"Use SHOW ENGINE INNODB STATUS to determine the cause of the latest deadlock" Have you tried this? Maybe you can see the cause and modify your queries.

"Always be prepared to re-issue a transaction if it fails due to deadlock" - A loop would help here as Rana said above, maybe set a delay for each loop to give a chance that maybe another lock will become free and the query may now execute.

"try using a lower isolation level such as READ COMMITTED" - Have you tried this and seen the effect run SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

if nothing else helps, serialize your transactions with table-level locks. - have you tried this?

[eluser]kezman[/eluser]
Thanks for answers!

I tried to re-issue a transaction 100 times (with a loop) with sleep(10) but it didn't help.

I know that READ COMMITTED level will help to prevent gap locks, but I don't want to switch, because in one transaction I won't see the results of the previous insert and I need it.

Code:

START TRANSACTION;
INSERT INTO A (..., ....) VALUES (1, 2);
INSERT INTO B (...., ...) VALUES (3, last_inserted_id_in_a_table);
COMMIT;

Using READ COMMITTED level this stuff won't work.