Problems that may occur when database access
1. Dirty Read (Dirty Read)
T1 is updating a piece of data but has not yet committed it, at which point T2 read the uncommitted data. Later T1 may undo the update, T2 is reading dirty data.
2. Update lost (lost update)
T1, T2 at the same time modify a piece of data, suppose they read the a=16,t1 to a minus 1, and submit, a=15. T2 also to a minus 1, because T2 read A is 16, so after the submission of a is still equal to 15, so that a two reduction, the result is only reduced 1,T1 update was lost.
3. Cannot read repeatedly (Non-repeatable read)
T1 read a piece of data and did not end it. At this point the T2 is updated on this piece of data. Then, T1 again according to the same condition query to this data, got different content, produced cannot repeat read.
4. Phantom Read (Phantom Read)
T1 a table for multiple queries, while T2 inserts to the table, T1 returns a different result set each time, as if an illusion occurred.
ANSI SQL92 defines 4 transaction isolation levels to address these issues
LEVEL0 UNCOMMITTED read (READ UNCOMMITTED)
Do not solve any problems.
Level1 Submit Read (Read Committed)
Make sure that the data that the transaction reads is updated with the submitted data. Method is an updated transaction that locks the row and is not released until it is committed.
Level2 Repeat read (repeatable read)
Make sure that the transaction gets the same data each time it repeats the same condition query. Method is a transaction that makes a query lock the record and is not released after the query until the transaction ends.
LEVEL3 Serialization (Serializable)
Highest level of isolation. Transaction concurrency is not allowed and transactions must be executed sequentially to avoid all problems. But it requires the most resources.
SQL Transaction ISOLATION LEVEL
| Isolation level |
Dirty Read |
Do not read repeatedly |
Phantom reading |
| Uncommitted read (READ UNCOMMITTED) |
can occur |
can occur |
can occur |
| Submit read (Read Committed) |
To avoid |
can occur |
can occur |
| Repeat read (Repeatable read) |
To avoid |
To avoid |
can occur |
| Serialization (Serializable) |
To avoid |
To avoid |
To avoid |
