Database Isolation Levels (MySQL)
This content below was part of my answer to a question on Quora about MySQL Isolation levels: http://b.qr.ae/xhWSbP
Answer from Quora
Here are some useful links:
Notes:
- In the second link, have a look at the “Read phenomena” section
To understand these isolation levels, imagine 2 transactions:
Imagine that the table FOO has one record {name:’sid’, age:35} to start with. The first transaction is doing 2 reads while the second transaction is updating the age from 35 to 36.
Transaction_1
Start transaction
Select * from FOO where name=’sid’
<imagine transaction_2 starts and commits here>
Select * from FOO where name=’sid’
commit
Transaction_2
Start transaction
Update FOO set age = 36 where name = ‘sid’
commit
I try to show that Transaction_1’s 2 selects envelop Transaction_2’s update.
Ok, so now let’s look at what happens under each of the 4 isolation levels:
- Read Uncommitted : Transaction_1’s first select will see ‘sid’ with an age of 35 but its second select will see ‘sid’ with an age of 36 . This is true even if Transaction_2’s commit failed and rolled back. The fact that Transaction_1’s second select read data that was never committed is called a “dirty read”. This is horrible.
- Read Committed : Transaction_1’s first select will see ‘sid’ with an age of 35 but its second select will ‘sid’ with an age of 36 if and only if Transaction_2’s commit succeeded. The fact that Transaction_1’s two selects are seeing the same row with different data (i.e. age changed) is called a non-repeatable read.
- Repeatable Read : The phenomena above cannot happen. However if Transaction_2 were altered to insert a record with {name:’sid’, age:100} after it executed the insert, then Transaction_1 would see two rows on its second select, one for “sid” with age=35 and another with age=100. In other words, between the 2 selects in Transaction_1, a new “phantom” row appeared. This is called a “phantom read”.
- Serializable : “Phantom reads” cannot occur.
-
buy-steroids-uk--co reblogged this from rooksfury
-
matthew liked this
-
rooksfury posted this
nosql
sharadag
boredandroid