Database transaction ISOLATION LEVEL __ Database

Source: Internet
Author: User
Tags comparison table

If there is no concurrency control for the transaction, let's see if the database concurrency is the exception
1, lost Update (Lost update)
All two transactions update one row of data at the same time, but the second transaction fails to exit halfway, resulting in the failure of two changes to the data.
2, Dirty Read (Dirty reads)
A transaction started reading a row of data, but another transaction has updated the data but has not been able to submit it in time. This is quite dangerous because it is possible that all operations are rolled back.
3. Non-repetitive reading (non-repeatable reads)
A transaction repeatedly reads the same row of data two times, but it gets different results. The same query takes place more than once in the same transaction, and because of changes or deletions made by other submitting transactions, a different result set is returned each time, and a non duplicate read occurs.
4, two class loss update (Second lost updates problem)
Exceptions that cannot be read repeatedly. There are two concurrent transactions that read the same row of data, and then one of them commits the modification, while the other commits the modification. This will cause the first write operation to fail.
5, Phantom Reading (Phantom reads)
Transactions are queried two times during the operation, and the results of the second query include the first check

Four isolation levels:

ReadCommitted: Assuming that a transaction places a shared lock on the data being read, then data cannot be overwritten by other transactions, so when the B transaction reads to data, the sum of data read by A is consistent, so that dirty reads are avoided. Because the data can be overwritten before a is committed, then a value read by B may be changed by a after it is read, causing the value not to be repeated, or the result set of the previous data, which is the Phantom data, when B uses the same WHERE clause again.

ReadUncommitted:
Assuming that a transaction does not publish a shared lock and does not accept exclusive locks, concurrent B or other transactions can overwrite the data read by a transaction, and then the state of the data read by the concurrent C transaction may not be consistent with the data of a or B, then. Dirty reads, non-repeatable reads, and phantom data can all be present.

RepeatableRead:
(Note the first sentence in the MSDN text: Lock is placed on all data used in the query , so there is no dirty read).
Suppose a transaction places a lock on all data that is read, to prevent changes to data by other transactions, the state of the data is consistent with the data in a transaction before a is submitted and the data that is read by the new concurrent transaction, if it exists, avoids the need for repeatable reads. However, before a transaction is closed, b transactions can insert new records into the table where data resides, and other transactions, when queried again with the same WHERE clause, may have the last inconsistency of the resulting number, which is the Phantom data.

Serializable:
An exclusive lock is placed on the datasheet to prevent other users from updating rows or inserting rows into the dataset before the transaction completes, which is the strictest lock. It prevents dirty reads, non repeatable reads, and phantom data.

Here is the comparison table:

Isolation Level Dirty Read (Dirty Read) non-repeatable read (nonrepeatable Read) Phantom Reading (Phantom read)
Read not submitted (READ UNCOMMITTED) possible possible possible
Read submitted (committed read) No way possible possible
REPEATABLE READ (Repeatable Read) No way No way possible
Serializable (Serializable) No way No way No way

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.