"Go" database Isolation level detailed----learning notes

Http://blog.sina.com.cn/s/blog_616b428f010163bo.html

A transaction (transaction) is the execution unit of a database management system, either a database operation (such as a select operation) or a set of sequence of operations. Transaction acid properties, i.e. atomicity (atomicity), consistency (consistency), isolation (isolation), persistence ( Durability).

Atomicity: Guarantees that all operations in a transaction are performed or not performed entirely. For example, a transfer transaction is performed, either a successful transfer or a failure. Succeeds, the amount is transferred from the transfer account to the destination account, and the amount of the two account changes accordingly, and the amount of the two accounts is unchanged. The transfer out account does not appear to be deducted from the money, and the destination account does not receive the money in the case.

Consistency: Ensure that the database always maintains the consistency of the data-before the transaction operation is consistent, and the transaction is consistent after the operation, regardless of whether the transaction is successful or not. As in the above example, the database is consistent on the data before and after the transfer.

Isolation: When multiple transactions are executed concurrently, the result should be the same as the serial execution of multiple transactions. Obviously, the simplest isolation is the serial execution of all the transactions: first, First, and one to allow the next to execute. However, the database is inefficient, such as: Two different transactions just read the same batch of data, so it can be done concurrently. There are different isolation levels to control the effect of concurrent execution. This is detailed below.

Persistence: persistence means that after a thing operation is complete, the impact on the database is persistent, and the database should be recoverable even if the database is compromised by a failure. The usual implementation is to use the log.

transaction ISOLATION level ( transaction Isolation levels ): The isolation level is the level of concurrency control over transactions. ansi/ iso sql is divided into serialization (repeatable Read), Read Committed ( read commited), read UNCOMMITTED (read uncommited) four levels. In order to achieve the isolation level, the database typically employs a lock (lock). Generally in the programming time only need to set the isolation level, as to the specific use of what the lock is set by the database. Four levels are introduced first, followed by examples of concurrency problems that occur in the next three levels (repeatable read, Read committed, read UNCOMMITTED).

Serialization (SERIALIZABLE): All transactions are executed serially, one by one, to avoid Phantom reads (Phantom reads). For databases that implement concurrency control based on locks, serialization requires that a range lock (range lock) be acquired when executing a range query, such as selecting a user aged between 10 and 30. If a database that does not implement concurrency control based on locks is checked for transactions that violate serial operations, the transaction needs to be rolled back.

REPEATABLE READ (repeatable Read): All data retrieved by select cannot be modified, thus avoiding the inconsistency of read data before and after a transaction. However, there is no way to control phantom reading, because other transactions cannot change the selected data at this time, but can increase the data because the previous transaction does not have a range lock.

Readcommited: The data being read can be modified by other transactions. This can result in non-repeatable reads. That is, a read lock is obtained when the transaction reads the data, but is released immediately after the read (no need to wait until the end of the transaction), and the write lock is released after the transaction commits. After you release a read lock, you may be able to modify the data by other things. This level is also the default isolation level for SQL Server.

Readuncommited: This is the lowest isolation level, allowing other transactions to see data that is not committed. This level can cause dirty reads (Dirty Read).

Example: The following three kinds of isolation levels corresponding to the concurrency problem. Suppose there are two of transactions. Transaction 1 executes query 1, then transaction 2 executes query 2, then commits, and then query 1 in transaction 1 executes again. The query is based on the following table:

users
id	name	Span lang= "en-us" xml:lang= "en-US" >age
1	joe	20
2	< Span>jill	25

Repeatable reading (phantom Reading,Phantom reads)

The same query is executed once in a transaction, but the result set returned is not the same. This occurs because the scope lock (range lock) is not acquired when the select operation is performed, causing other transactions to still insert new data.

Transaction 1	Transaction 2
SELECT * from users WHERE age between and 30;
	INSERT into Users VALUES (3, ' Bob ', 27); COMMIT;
SELECT * from users WHERE age between and 30;

Note that transaction 1 executes two times for the same query statement (query1). If you adopt a higher level of isolation (that is, serialization), then the two-second query should return the same result set. However, in the repeatable read isolation level, the result set is not the same as the two times. But why is it called repeatable reading level? That is because the level resolves the following non-repeatable read problems.

Read Committed (non-repeatable read,non-repeatable reads)

In a database system that uses locks to implement concurrency control, it is not repeatable to read because there is no read lock when performing a select operation.

Transaction 1	Transaction 2
SELECT * FROM users WHERE id = 1;
	UPDATE users SET age = + WHERE id = 1; COMMIT;
SELECT * FROM users WHERE id = 1;

In this example,Transaction 2 commits successfully , so Transaction 1 will get a different age value for the second time . In serializable and Repeatable the Read isolation level , the database should return the same value. In the Read committed and read UNCOMMITTED levels, the database returns the updated value. In this way, non-repeatable reads occur.

READ UNCOMMITTED (dirty read,dirty reads)

If one transaction 2 reads the value modified by another transaction 1, but the last transaction 1 rolls back, then transaction 2 is enrolled with a dirty data, which is called dirty reading. This behavior occurs when the transaction is allowed to read UNCOMMITTED updates.

Transaction 1	Transaction 2
SELECT * FROM users WHERE id = 1;
	UPDATE users SET age = + WHERE id = 1;
SELECT * FROM users WHERE id = 1;
	RollBack

Above, you can wait until the following table:

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read not submitted	YES	YES	YES
Read Committed	NO	YES	YES
REPEATABLE READ	NO	NO	YES
Serialization	NO	NO	NO

Reference:

Isolation (Database systems):http://en.wikipedia.org/wiki/Isolation_ (computer_science)

shared lock : The lock that is added by the Read table operation, after locking, the other user can only get the shared lock of the table or row, can not get the exclusive lock, that is, can only read can not write

Exclusive Lock: The lock that is added by the Write table operation, the other user cannot get any lock of the table or row after lock, it is typical in MySQL transaction

Range of Locks:

row Lock: adds a lock to a row of records

table Lock: Lock the entire table

This is combined to include row-level shared locks, table-level shared locks, row-level exclusive locks, table-level exclusive locks

Start transaction;

SELECT * from user where userId = 1 for update;

After executing this sentence

1) When other transactions want to acquire shared locks, such as transactions with a transaction isolation level of Serializable, execute

select * from user;

will be suspended because the serializable SELECT statement needs to acquire a shared lock

2) When other transactions are executed

SELECT * from user where userId = 1 for update;

Update user Set userage = n where userId = 1;

will also be suspended because the for update acquires an exclusive lock on this row of data and needs to wait until the previous transaction releases the exclusive lock before it can proceed.

http://blog.csdn.net/kingofase/article/details/5715297 is described in the following

In the standard SQL specification, 4 transaction Isolation levels are defined, and different isolation levels deal differently with transactions:

Unauthorized read (READ UNCOMMITTED): Dirty reads are allowed, but updates are not allowed to be lost. If a transaction has already started writing data, the other data does not allow simultaneous writes, but allows other transactions to read the data on this line. This isolation level can be achieved through an "exclusive write lock".

Authorized read (read Committed): Allows non-repeatable reads, but dirty reads are not allowed. This can be achieved through "instantaneous shared read lock" and "exclusive write lock". Transactions that read data allow other transactions to continue to access the row's data, but uncommitted write transactions will prevent other transactions from accessing the row.

REPEATABLE READ (REPEATABLE READ): Disables non-repeatable reads and dirty reads, but sometimes phantom data can occur. This can be achieved through "shared read lock" and "exclusive write lock". Transactions that read data prohibit write transactions (but allow read transactions), and write transactions prohibit any other transactions.

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.

The higher the isolation level, the greater the integrity and consistency of the data, but also the greater the impact on concurrency performance. For most applications, it is preferable to set the isolation level of the database system to read Committed, which avoids dirty reads and has good concurrency performance. Although it causes concurrency problems such as non-repeatable reads, virtual reads, and second-class loss updates, the application can be controlled by pessimistic or optimistic locks on individual occasions where such problems may occur.

It is already known through the previous introduction that by choosing different isolation levels, you can avoid the various problems that are mentioned earlier in the transaction processing to varying degrees. Therefore, the selection of database isolation level is particularly important, when selecting the isolation level of the database, you should pay attention to the following principles of processing:

First, you must exclude "unauthorized reads," because it can be very risky to use it between multiple transactions. A rollback operation or failure of a transaction will affect other concurrent transactions. The rollback of the first transaction clears the operation of the other transaction completely, even leaving the database in an inconsistent state. It is possible that a transaction that has been rolled back to the end of the data has been modified to commit because "unauthorized read" allows other transactions to read the data, and finally the entire error state is propagated across other transactions.

Second, most applications do not need to use "serialization" Isolation (in general, it is not a problem to read Phantom data), and this isolation level is difficult to measure. Pessimistic locks are generally used in applications that are currently using serialization isolation, forcing all transactions to serialize execution.

The rest is the choice between "authorized read" and "repeatable read". Let's consider repeatable reads first. If all data access is in a unified atomic database transaction, this isolation level eliminates the possibility that one transaction will overwrite the data during another concurrent transaction (the second Transaction update loss issue). This is a very important issue, but using repeatable reads is not the only way to solve the problem.

If you use version data, Hibernate automatically uses the version data. Hibernate's first-level session cache and version data have provided you with most of the features of "repeatable read isolation." In particular, version data prevents the loss of two updates, and the first-level session cache ensures that the state of the persistent load data is isolated from other transaction modifications to the data, so it is possible to use authorization to read quarantine and version data for all database transactions.

Repeatable read provides better efficiency for database queries (only for those long database transactions), but because Phantom reads still exist, there is no need to use it (for Web applications, it is generally very rare to query the same table two times in a database transaction).

You can also consider choosing a level two cache that uses Hibernate, which provides the same transactional isolation as the underlying database transaction, but it can weaken the isolation. If the cache concurrency policy is heavily used in the level two cache, it does not provide duplicate read semantics (for example, read and write that will be discussed in later chapters, especially non-strict read and write), and it is easy to choose the default isolation level: because "repeatable reads" are not possible at all, there is no need to slow down the database. On the other hand, there may be no level two cache for critical classes, or a full transaction cache that provides "repeatable read isolation." Do you need to use "repeatable reads" in your business? If you like, of course you can do that, but more often there is no need to spend this price.

"Go" database Isolation level detailed----learning notes