MySQL: Transaction isolation levels, deadlocks

Question

I have a long running process which import products and from time to time I get deadlock errors. According to my knowledge I thought that if during script execution I'll switch isolation level to Serializable I will solve an issue with deadlocks. But, what can I do, I can open 2 terminals and reproduce deadlock with Serializable.

conn1: SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALAZIBLE; conn1: START TRANSACTION; conn2: START TRANSACTION; conn1: UPDATE core_config_data set value = 1 WHERE config_id = 1; conn2: UPDATE core_config_data set value = 1 WHERE config_id = 2; conn1: UPDATE core_config_data set value = 1 WHERE config_id = 2; waiting... conn2: UPDATE core_config_data set value = 1 WHERE config_id = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Ok, after it I tried dirty reads with READ UNCOMMITED:

conn1: SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; conn1: START TRANSACTION; conn1: SELECT junk FROM employees WHERE employee_id = 1; junk=test; conn2: START TRANSACTION; conn2: update employees set junk='test1' where employee_id = 1; conn1: SELECT junk FROM employees WHERE employee_id = 1; junk=test;

Do you know where I'm mistaken?

Argeman · Accepted Answer · 2014-08-19 11:06:19Z

Everything is as expected. You simply can't avoid deadlocks just by isolation levels; the isolation level is for integrity of your data only.

You may be able to solve many of your deadlock issues by ensuring the same ordering of updated rows. That way the first connection will be able to process to the end without deadlock and then connection 2 will either fail or suceed, too.

Collectives™ on Stack Overflow

MySQL: Transaction isolation levels, deadlocks

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related