MSSQL DATABASE TRANSACTION ISOLATION LEVEL

There are 4 isolation levels for database transactions, from low to High, READ UNCOMMITTED ,Read Committed ,Repeatable Read ,Serializable , these four levels can be solved individually

Problems such as dirty reading, non-repetition reading, and phantom reading.

√: May appear x: does not appear

	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

Note: We discuss the isolation level scenario, mainly in the case of multiple transactions concurrency, so the next explanation is around the transaction concurrency.

READ UNCOMMITTED not submitted

The company paid, the leader of the 5000 yuan to the Singo account, but the transaction did not submit, and Singo just to check the account, found that the salary has been to the account, is 5000 yuan whole, very happy. But unfortunately,

The leader found that the amount of wages issued to Singo is not correct, is 2000 yuan, and then quickly rolled back the transaction, modified the amount, the transaction submitted, finally singo the actual wages only 2000 yuan, Singo empty joy a game.

The above situation, that is what we call dirty Read, two concurrent transactions, "transaction A: lead to Singo payroll", "Transaction B:singo query Payroll account", transaction B read the transaction A has not yet committed data.

When the isolation level is set to read UNCOMMITTED, dirty reads can occur and how to avoid dirty reads, see the next isolation level.

Read Committed reading Commit

Singo take the payroll card to spend, the system read to the Cary really have 2000 yuan, and at this time her wife also just on-line transfer, the Singo Pay card of 2000 yuan to another account, and before Singo submitted a business,

When the Singo deduction, the system checks to Singo's payroll card has no money, the deduction failed, Singo very puzzled, clearly card money, why ...

The above situation, that is what we say is not repeatable read, two concurrent transactions, "transaction A:singo consumption", "Transaction B:singo wife online Transfer",

Transaction a reads the data in advance, transaction B immediately updates the data, commits the transaction, and when transaction a reads the data again, the data has changed.

When the isolation level is set to read Committed, dirty reads are avoided, but may cause non-repeatable reads.

The default level for most databases is read committed, such as SQL Server, Oracle. To resolve the issue of non-repeatable reads, see the next isolation level.

REPEATABLE READ repeat

You can avoid non-repeatable reads when the isolation level is set to repeatable read. When Singo takes the payroll card to spend, once the system starts to read the Payroll card information (that is, the start of the transaction),

Singo's wife would not be able to modify the record, that is, Singo's wife can not be transferred at this time.

Although repeatable read avoids non-repeatable reads, it is possible to have phantom reads.

Singo's wife works in the banking department, and she often views Singo's credit card consumption records through the internal banking system. One day, she was inquiring into the total amount of credit card Singo month

(select sum (amount) from transaction where month = this month) is 80 yuan, and Singo at this time is in the outside Hu Eat Sea plug after the cashier pay, spend 1000 yuan,

That is, a new 1000-dollar consumption record (insert transaction ... ），

and submitted the transaction, then Singo wife will singo the month credit card consumption details printed to A4 paper, but found that the total consumption of 1080 yuan, Singo wife was surprised, thought there was an illusion, the illusion of the emergence of this.

Note:The default isolation level for MySQL is repeatable read.

Serialization of Serializable

Serializable is the highest transaction isolation level, with the highest cost and low performance, which is rarely used at this level, where the transaction sequence executes not only to avoid dirty reads, non-repeatable reads, but also to avoid Phantom reads.  

Ps: Basic concept of database lock

Lock waits : When a transaction is in a particular data (for example, rows, tables ...). When a lock is held on, other transactions can access the locked data resource only if the transaction terminates and releases the lock (depending on the type of lock, access rights vary),

The process by which other transactions wait for a lock is called a lock wait.

Lock Timeout : When the lock waits, the execution of other transactions is blocked, the transaction can be rolled back to the current request, which is the lock timeout, by configuring the lock timeout value and, within a specified time interval, if the pending transaction has not acquired a lock.

deadlock : Two or more transaction-to-lock cycle contention, called deadlocks, for example,

Such as

The first statement of transaction a modifies the first row of the table T1, the system adds an exclusive lock to the current row, and the other transaction does not access the current row.

Another transaction, B, performs a modification on the first row of the table T2, adding an exclusive lock to the T2 row.

Immediately after, transaction a accesses the table T2, enters the lock wait,

Transaction B also accesses the T1 and enters the lock wait,

This causes two transactions to cycle through the lock, resulting in deadlocks.

Reference: http://singo107.iteye.com/blog/1175084

MSSQL DATABASE TRANSACTION ISOLATION LEVEL