This is the fourth day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021
The transaction isolation levels of THE SQL standard include read Uncommitted, Read Committed, Repeatable Read, and serializable.
Given that there is only one column in table T, and one row has a value of 1, the following is the behavior of each isolation level performing two transactions in chronological order.
| A transaction | Transaction B |
|---|---|
| Start transaction A query and get the value 1 | Starting the transaction |
| — | The query returns the value 1 |
| — | Change the 1 to the 2 |
| The query results in the value V1 | — |
| Commit transaction B | |
| Query the value V2 | — |
| Commit transaction A | — |
| Query the value V2 | — |
-
Read uncommitted
Before a transaction commits, its changes can be seen by other transactions.
If the isolation level is read Commit, V1 is 1 and V2 has a value of 2. Transaction B’s updates are not seen by A until they are committed. So V3 is also going to be 2.
| A transaction | Transaction B |
|---|---|
| Start transaction A query and get the value 1 | Starting the transaction |
| — | The query returns the value 1 |
| — | Change the 1 to the 2 |
| The query returns the value 1 | — |
| Commit transaction B | |
| The query returns a value of 2 | — |
| Commit transaction A | — |
| The query returns a value of 2 | — |
-
Read the submission
After a transaction commits, its changes are seen by other transactions.
If the isolation level is read Commit, V1 is 1 and V2 has a value of 2. Transaction B’s updates are not seen by A until they are committed. So V3 is also going to be 2.
| A transaction | Transaction B |
|---|---|
| Start transaction A query and get the value 1 | Starting the transaction |
| — | The query returns the value 1 |
| — | Change the 1 to the 2 |
| The query returns the value 1 | — |
| Commit transaction B | |
| The query returns a value of 2 | — |
| Commit transaction A | — |
| The query returns a value of 2 | — |
-
Repeatable read
The data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Of course, at the repeatable read isolation level, uncommitted changes are also invisible to other transactions.
If the isolation level is Repeatable, V1, V2 are 1, and V3 are 2. The reason why V2 is still 1 is that the transaction must see consistent data during execution.
| A transaction | Transaction B |
|---|---|
| Start transaction A query and get the value 1 | Starting the transaction |
| — | The query returns the value 1 |
| — | Change the 1 to the 2 |
| The query returns the value 1 | — |
| Commit transaction B | |
| The query returns the value 1 | — |
| Commit transaction A | — |
| The query returns a value of 2 | — |
-
serialization
As the name implies, for the same row, “write” will add “write lock”, “read” will add “read lock”. When a read-write lock conflict occurs, the last accessed transaction must wait for the previous transaction to complete before execution can continue.
If the isolation level is serialized, it will be locked while transaction B performs “change 1 to 2”. Transaction B cannot continue execution until transaction A commits. So from A’s point of view, V1 and V2 are equal to 1, and V3 is equal to 2.
| A transaction | Transaction B |
|---|---|
| Start transaction A query and get the value 1 | Starting the transaction |
| — | The query returns the value 1 |
| — | Change the 1 to the 2 |
| The query returns the value 1 | — |
| Commit transaction B | |
| The query returns the value 1 | — |
| Commit transaction A | — |
| The query returns a value of 2 | — |
The default isolation level for An Oracle database is “READ Commit”, whereas for MySQL it is “repeatable READ”. MySQL is configured to set the startup parameter transaction-ISOLATION to read-COMMITTED. You can use Show variables to see the current values.