CODEWITHSUNDEEP
mysqltransactionsisolation-level
Vivek Vardhan
Vivek Vardhan

Reputation: 1178

Possible ways to avoid BAD WRITES in MySql

Suppose we have one Transaction -

Transaction T1

S1=>   id1 = select id, colA, colB, colC from table A where colA = 'A1';

S2=>   update table A set colB = 'B1' where id = 'id1'

Above, I want to change colB to B1, only if colA value is A1

Transaction T2

 S3=>  update  table A set colA = 'A2' where colA = 'A1';

Above Transaction sets colA value to A2

So, in the above scenario, if S1 from T1 happens, and then S3 from T2 happens, then S2 from T1, then it will be wrong, as after execution of S3, value of colA is A2, so i don't want S2 to be a success.

How can I avoid this?

Possible solutions -

  1. Use Optimistic / Pessimistic locking : I will go for this, only if no other option works
  2. Use SELECT for UPDATE: Like update table A set colB = 'B1' where id = 'id1' and colA = 'A1'
  3. Use Transaction Isolation level SERIALIZABLE

Can I achieve what I want using 3? Which one will be better out of these 2?

Upvotes: 0

Views: 29

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94662

I want to change colB to B1, only if colA value is A1

Then make that part of the WHERE clause

update table A 
    set colB = 'B1' 
where id = 'id1'
AND colA = 'A1'

Upvotes: 1

Related Questions