The effect of transaction isolation in write operations in mysql

Question

I have mysql proc that does one read and one update (in two different tables). To make sure that reading is fast, I changed the isolation level to Read Uncommitted. A dirty read is in order, and data consistency is not a big deal. But it looks at the Read Read Uncommitted Isolation level, updates are very slow - in fact, it affects other entries in my table.

My code is as follows

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT ParentId INTO @ParentId FROM TableA where Id = var_ID;



UPDATE TableB SET Counter = Counter + 1 where Id = @ParentId;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

I figured that the isolation level only affects read operations, not write. After turning on the Set Session .... report, my database performance dropped dramatically. All records on TableB take 1-2 seconds, whereas it was <100ms before (the table has records 5M-8M).

- ?

,

/

Answer 1

Try

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT ParentId INTO @ParentId FROM TableA where Id = var_ID;



UPDATE TableB SET Counter = Counter + 2 where Id = @ParentId;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;