CODEWITHSUNDEEP
mysqltransactionsisolation-leveldatabase-deadlocks
Viacheslav Kondratiuk
Viacheslav Kondratiuk

Reputation: 8879

MySQL: Transaction isolation levels, deadlocks

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?

Upvotes: 3

Views: 878

Answers (1)

Argeman
Argeman

Reputation: 1353

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.

Upvotes: 3

Related Questions