Database: The database transaction isolation level that you understand

Reprint Please specify source: Jiq Technical Blog-Jiyichin

Introduction: In the online search a lot of articles on business, feel alone to see it is difficult to understand, so synthesize their own understanding of writing an article I can understand about relational database transactions.

I. Characteristics of the transaction

we all know that database transactions have ACID Features:

Atomic (atomicity): a transaction either succeeds or fails

Consistency (consistency): consistency represents the integrity of the underlying data store. The database must be in a legitimate state before and after the transaction is executed. What is a legitimate state?

For example, to satisfy the uniqueness of the database constraints, data type validation, referential integrity, and more complex also includes the results of transaction execution will not be different from the actual business expectations.

For example, for example, two tables and professional watches, because students want to have a professional, so students need to refer to the professional table, but if you add a professional and a student, and this student happens to belong to this new profession, then the two insert operations must be placed in a transaction if there is a failure to rollback, Otherwise, the student may cite a non-existent professional, the database reference is incomplete after the transaction execution.

In addition, for example, the banking system has multiple accounts, the money will only be transferred between the accounts, and will not disappear in a vacuum, if you want to transfer account A's money to account B, then account A's debit operations such as and Account B's add money operation is either successful together, or failed together, to ensure that business data are in a consistent state before and

Isolation (Isolation): isolation means that transactions must be executed independently without interfering with other processes or transactions

Durability (persistent): After the transaction ends, the result of the transaction must be cured.

Second, the question

Without any concurrency control mechanism, there are several types of problems that occur when different threads execute transactions:

1. Update lost

(1) Update lost (lostupdate)

Two transactions are updated at the same time, and a second transaction rollback overwrites the data for the first transaction update, causing the update to be lost

(2) Two update issues (Secondlost updates problem)

Two transactions read the data and update at the same time, the first transaction update fails because it is overwritten by a second transaction.

2. Dirty Reading (Dirty Reads)

Transaction T1 reads the data that the transaction T2 modified but not yet committed, and then the transaction T2 rolls back its update operation, causing the transaction T1 to read dirty data.

3. Non-repeatable reading (non-repeatablereads)

After the transaction T1 reads a row of data , the transaction T2 modifies it, and when the transaction T1 reads the data again, it gets a different value than the previous one.

4. Phantom Reading (Phantom Reads)

Transaction T1 reads when the scope data is read, the transaction T2 inserts a piece of data, when the transaction T1 again data this range of data found different, there are some "phantom line."

Third, concurrency control

For This we need to provide different levels of concurrency access control for database transactions by providing different types of "lock" mechanisms, resulting in different levels of transaction isolation: Isolation Level (low-to-high)

Unread (Read UNCOMMITTED)

Meaning explanation: only restrict the same data write transaction to prohibit other write transactions. Resolve " update lost "

Name interpretation: Non-submitted data can be read

Required Locks: Exclusive write lock

Read Submit (Committed)

Meaning: only restrict the same data write transaction to prohibit other read and write transactions. Resolve Dirty reads , and update lost

Name interpretation: must be submitted for future data to be read

Required Locks: Exclusive write lock, instantaneous shared read lock

REPEATABLE READ (Repeatable Read)

Meaning explanation: Restricting the same data write transaction prevents other read and write transactions, read transactions prohibit other write transactions ( allow read ). Resolve " non-repeatable reads " and " update lost " and " Dirty Read ".

Note that there is no solution to phantom reading, and the way to solve phantom reads is to increase the range lock or the table lock.

Name Explanation: Ability to read repeatedly

Required Locks: Exclusive write lock, shared read lock

Serialization (Serializable)

Provides strict transaction isolation. It requires the transaction to serialize execution, and the transaction can be executed one after the other, but not concurrently. If transaction serialization is not possible only through row-level locks, other mechanisms must be ensured that the newly inserted data is not accessed by the transaction that just performed the query operation.

Restricting all read and write transactions must be serialized.

The following table is the ability of each isolation level to control various exceptions.

	Update lost	Dirty Read	Non-REPEATABLE READ	Phantom reading
RU (read not submitted)	Avoid
RC (read submit)	Avoid	Avoid
RR (Repeatable Read)	Avoid	Avoid	Avoid
S (serialized)	Avoid	Avoid	Avoid	Avoid

Iv. Common Database default Isolation level

Types of database default transaction isolation Levels

Database	Default Level
Mysql	Repeatable Read ( Repeatable Read )
Oracle	Read Submit (Committed)
Sql server	Read Submit (Committed)
DB2	Read Submit (Committed)
PostgreSQL	Read Submit (Committed)

Database: The database transaction isolation level that you understand