Database transaction isolation, English is called Transaction isolation level.
Recently in the maintenance of the project to the customer, a table in two processes at the same time update and query encountered deadlock (DeadLock), the data table has millions of thousands records, the above processing was updated thousands of records,
Query the entire table.
This is the premise, in order to understand this deadlock, probably related to the deadlock, isolation level, lock escalation and trace trace content, is sorted out, today talk about isolation level.
Profile
There are 3 types of non-readable phenomena, first of all about the acid characteristics of the RDBMS I (isolation-isolation)
· Dirty Read
· Non-REPEATABLE READ
· Phantom read
Then talk about the 4 levels of the transaction isolation level.
· Read UNCOMMITTED
· Read committed
· REPEATABLE READ
· Serializable
The higher the lower the isolation level, the lower the isolation level becomes.
For the highest isolation level, the read-out in the top 3 does not occur. However, there is a significant loss in system performance.
Presumably can be summed up as [want to improve the performance of the system to accept a certain degree of reading does not fit, if even if the performance is not related to the lack of it will not occur on the read the non-fit]
Reading does not fit
Explain that the 3 reads do not fit the phenomenon
Dirty Read
A phenomenon that reads data that has not yet been committed by another transaction:
1. Transaction A to update record A to record B (not yet submitted)
2. Transaction B queries the record
3. Transaction A has been rolled back
4. Transaction B reads record b
This is to set the isolation level to the lowest level, ah, have not seen such a system.
Non-REPEATABLE READ
Read the updated data of other transactions, resulting in a non-coherent read operation.
1. Transaction a queries the record, which is called a
2. Transaction b updates the contents of record A to record B and submits it.
3. Transaction a queries the record a again, and the content obtained has become record B.
Phantom read
saw a non-coherent phenomenon caused by records inserted by other transactions.
1. Transaction A is not logged when querying records.
2. Transaction B inserts the record and submits it.
3. Transaction A records that were inserted by transaction B when querying records again.
is not the feeling and non-repetition read a bit like Ah, focus on the insert operation or update operation.
In addition, the following is also the Phantom read OH
1. Transaction a statistical records are X-pieces.
2. Transaction B inserts the record and submits it.
3. Transaction A is again recorded and the number of pieces obtained is x+1.
This is the 3 kinds of non-binding phenomena about read operations.
Transaction isolation level and read non-binding relationship
|
Dirty Read |
Non-REPEATABLE READ |
Phantom read |
Read UNCOMMITTED |
Happen |
Happen |
Happen |
Read committed |
does not occur |
Happen |
Happen |
REPEATABLE READ |
does not occur |
does not occur |
Happen |
Serializable |
does not occur |
does not occur |
does not occur |
This is not to think that every time you choose to set the isolation level of serializable (serializable) once and for all Ah,
In fact, the isolation of high-quality is also a problem ah. This article does not explain, or another big paragraph, interested in the investigation.
About a few highlights
· Setting the isolation level does not necessarily guarantee that a corresponding error will not occur. and specific database-installed RDBMS has a large
Relationships, such as MySQL's repeatable Read's isolation level, also do not occur when Phantom reads.
· The so-called "guarantee does not occur", the processing method that is taken in order to make no mistakes is also different from the database RDBMS. When an exception occurs
Some databases report exceptions, and others wait for the transaction to end.
· The default isolation levels for database RDBMS vary, and the corresponding isolation level is different.
MySQL (InnoDB) |
Repeatable READ |
|
|
PostgreSQL |
READ COMMITTED |
|
|
Oracle |
READ COMMITTED |
|
|
SQL Server |
READ COMMITTED |
|
|
In this way, it takes time to write this, and then update the deadlock, lock escalation, and trace trace for SQL Server.
About the isolation level of the database