I am trying to understand how transactions work, and I have ran into a scenario which does not make a lot of sense to me. I was hoping somebody could help me understand it.
I have two transactions
Transaction 1
BEGIN; update data set val = val + 1 where id = 1
Transaction 2
BEGIN; select * from data
I have two terminals open, I begin the first transaction and run the update query. This supposedly gives the exclusive lock to transaction 1 on tuple with id 1.
Following that, I run the second query in another terminal before committing the first transaction. I was expecting it to stall, since the first transaction has the exclusive lock which will prevent this transaction from acquiring the read lock on tuple with id 1.
However, mysql runs the select query and returns the "un-dirty" data.
Can somebody give me the explanation behind this behavior of mysql?
SELECT does not require a shared row lock by default. It can read the most recent committed version of the row without locking, by using the multi-version concurrency control (MVCC) architecture.
You can write a SELECT query that explicitly requests a lock, but without these locking clauses, SELECT requires no row locks.
To have a complete view of how transactions work, I think it makes sense to be aware that shared locks are aquired differently, depending on the isolation level.
Exclusive locks are released when transaction ends, regardless of the isolation level.
The difference between the isolation levels refers to the way in which Shared (Read) Locks are acquired/released.
Under Read Uncommitted isolation level, no Shared locks are acquired. Under this isolation level the concurrency issue known as "Dirty Reads" can occur.
Under Read Committed isolation level, Shared Locks are acquired for the concerned records. The Shared Locks are released when the current instruction ends. This isolation level prevents "Dirty Reads" but, since the record can be updated by other concurrent transactions, "Non-Repeatable Reads" or "Phantom Reads" can occur.
Under Repeatable Reads isolation level, Shared Locks are acquired for the transaction duration. "Dirty Reads" and "Non-Repeatable Reads" are prevented but "Phantom Reads" can still occur.
Under Serializable isolation level, ranged Shared Locks are acquired for the transaction duration. None of the above mentioned concurrency issues occur but performance is drastically reduced and there is the risk of Deadlocks occurrence.