Four characteristics of database transactions and transaction isolation levels

Reference:

[1] http://www.cnblogs.com/fjdingsd/p/5273008.html

[2] http://blog.csdn.net/fg2006/article/details/6937413

Four characteristics of database transactions

If a database claims to support transactional operations, then the database must have the following four features :

⑴ atomicity (atomicity)

Atomicity means that all the operations contained in a transaction are either all successful or fail back, which is the same concept as the previous two blogs about transactions, so the operation of a transaction must be fully applied to the database if it succeeds, and it cannot have any effect on the database if the operation fails.

⑵ Consistency (consistency)

Consistency means that a transaction must transform a database from one consistent state to another, meaning that a transaction must be in a consistent state before and after execution.

Take the transfer, assume that user A and User B both the sum of money together is 5000, then no matter how a and B transfer, transfer several times, after the end of the transaction two users of the sum should be 5000, this is the consistency of the transaction.

⑶ Isolation (Isolation)

Isolation is when multiple users concurrently access the database, such as when the same table operation, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

To achieve this effect: for any two concurrent transactions T1 and T2, in the case of transaction T1, the T2 either ends before the T1 starts, or starts after the T1 ends, so that every transaction does not feel that another transaction is executing concurrently.

The isolated database on transactions provides a variety of isolation levels, which are described later.

⑷ Persistence (Durability)

Persistence refers to the fact that once a transaction is committed, changes to the data in the database are permanent, even if the database system encounters a failure, and the commit transaction is not lost.

Isolation of database transactions

Transaction isolation, when multiple threads are opening data in a transactional operations database, the database system is able to isolate operations to ensure the accuracy of each thread's access to the data. If you do not consider the isolation of transactions, several issues can occur:

Dirty Reads (drity read): A transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback operation, the latter will read the data is not correct.

Non-repeatable read (non-repeatable Read): Data inconsistency in two queries for a transaction, which may be the original data that was inserted in the middle of a transaction update during the two query process.

Phantom Read (Phantom Read): In a transaction two times the number of data pens inconsistent, for example, one transaction queried several columns (row) of data, while another transaction at this time inserted a new column of data, the previous transaction in the next query, you will find that there are a few columns of data that it did not previously.

The SQL standard defines a Class 4 isolation level, which includes specific rules to define which changes within and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrency processing and have lower system overhead.

READ UNCOMMITTED (Read UNCOMMITTED content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).
Read Committed (read submit content)

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.
Repeatable Read (can be reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.

Serializable (Serializable)
This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

The MySQL InnoDB storage Engine implements a multi-version concurrency Control Protocol--MVCC (Multi-version Concurrency control) that does not exist at the repeatable read (RR) isolation level.

Four characteristics of database transactions and transaction isolation levels