Isolation level of the database

Introduction to the database transaction isolation level

A transaction (Transaction) is the basic unit of concurrency control. The so-called transaction, which is a sequence of operations that are either executed or not executed, is an inseparable unit of work. For example, a bank transfer job: Debit from one account and add another account, both of which are either executed or not executed. Therefore, they should be regarded as a business. A transaction is a unit of data consistency maintained by a database that maintains data consistency at the end of each transaction.

As can be seen from the above description, the proposed transaction is mainly to solve the problem of maintaining data consistency in the concurrency situation.

A transaction has the following 4 basic characteristics.

Atomic (atomicity): The operations contained in a transaction are considered to be a logical unit in which the operations of the logical unit either succeed or fail altogether.

Consistency (consistency): only legitimate data can be written to the database, or the transaction should roll it back to its original state.

Isolation (Isolation): Transactions allow multiple users to concurrently access the same data without destroying the correctness and integrity of the data. At the same time, the modification of parallel transactions must be independent of the modifications of other parallel transactions.

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

Database is definitely to be shared by the vast number of customers to access, then in the database operation process is likely to occur in the following kinds of uncertainty.

Update lost (Lost update): Two transactions update one row of data at the same time, but the second transaction fails to exit, resulting in two modifications to the data that are invalidated. This is because the system does not perform any lock operations, so concurrent transactions are not isolated.

Dirty Read (Dirty Reads): One transaction starts reading a row of data, but another transaction has updated this data but is not able to commit in a timely manner. This is quite dangerous because it is possible that all operations are rolled back.

Non-repeatable read (non-repeatable Reads): One transaction repeats two reads of the same row of data, but it gets different results. For example, in the middle of two reads, there is another transaction that modifies the row's data and submits it.

Two update issues (Second lost updates problem): A special case that cannot be read repeatedly. There are two concurrent transactions that read the same row of data at the same time, and one of them modifies the commit, and the other commits the modification. This will cause the first write operation to fail.

Virtual Read (Phantom Reads): The transaction is queried two times during the operation, and the result of the second query contains data that does not appear in the first query (the same SQL statement that does not require two queries). This is due to the fact that another transaction was inserted into the data during the two queries.

Isolation level of the database

To avoid the above scenarios, in the standard SQL specification, 4 transaction Isolation levels are defined, with different isolation levels dealing 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.

I. Database transactions
1. A transaction is a series of operations performed as a single logical unit of work. It can be an SQL statement or multiple SQL statements.
2. Transaction has four characteristics
Atomicity: The non-separation, the formation of the formation, the defeat is defeated.
Isolation: Independent execution of non-interference. Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transaction (another description: multiple transactions at the same time, they should not interfere with each other.) should prevent a transaction from being modified by other transactions, unreasonable access and incomplete reading data
3. Start transaction: Using API functions and Transact-SQL statements, you can start a transaction in an explicit, autocommit, or implicit manner.
4. End transaction: You can use a COMMIT (success) or ROLLBACK (failed) statement, or end the transaction through an API function.
5, the principle of creating a transaction:
It is important to keep the transaction as short as possible, and when the transaction starts, the database management system (DBMS) must retain a lot of resources before the transaction ends to ensure the correct and secure execution of the transaction.
Especially in a large number of concurrent systems, it is more important to keep transactions short to reduce the contention for concurrent resource locking.
1, transaction processing, prohibit interaction with the user, before the transaction begins to complete user input.
2, when browsing the data, try not to open the transaction
3. Keep the transaction as short as possible.
4. Consider using snapshot isolation for read-only queries to reduce blocking.
5. Flexibility to use a lower transaction isolation level.
6. Flexibility to use lower cursor concurrency options, such as optimistic concurrency options.
7. Minimize the amount of data that is accessed in the transaction.

Second, the isolation level of the transaction

1, the isolation level of database transaction: four kinds

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

2, the database general default isolation level is "Read Committed", the default transaction isolation level: Insert,update, delete is the x lock, will wait for the transaction to complete. In general, the isolation level can be set to read commited, which avoids dirty reads and has better concurrency performance. Although it can cause problems such as non-repeatable reads, virtual reads, and second-class update loss, it is possible for an application to control a pessimistic or optimistic lock on an individual occasion where such problems may occur.

3. SQL statements can use SET TRANSACTION isolation level to set the isolation levels for transactions. such as: SET TRANSACTION Isolation Level Read Committed. To use a stricter or looser isolation level in your application, you can customize the lock for the entire session by setting the isolation level of the session by using the SET TRANSACTION isolation levels statement.
When the isolation level is specified, the locking behavior of all SELECT statements in a SQL Server session runs at that isolation level and remains valid until the session terminates or the isolation level is set to another level.

4, another point to mention: The SQL standard to the transaction isolation level, is determined by the level of what is impossible to do, it does not necessarily occur, so different databases on transaction isolation of the level of the Convention is not the same, for example, some databases to the repeatable read level of serializable to treat. (lkdlhw_2000 Personal Understanding: Each database should follow the definition of four standard transaction isolation level, but some database implementation may not exist four, because serialization can avoid non-repeatable read, so some database syntax support SET TRANSACTION ISOLATION level is not repeatable read, But actually serialization is in effect. This means that as long as the level can avoid the problem of non-repeatable reading, it can be called non-repeatable read level. )

5. The isolation level defines the degree to which a transaction must be isolated from changes in resources or data made by other transactions. Transaction ISOLATION LEVEL Control:
Whether the lock is occupied while reading the data and the type of lock requested.
The time that the read lock was occupied.
Whether read operations that reference rows modified by other transactions are:
Blocks other transactions before the exclusive lock on the row is freed.
Retrieves the committed version of the row that existed when the statement or transaction was started.
READ UNCOMMITTED data modifications

Three, lock

1, classification: From the point of view of the database system: divided into exclusive lock (that is, lock it), share locks and update locks

2. The transaction uses locks to prevent other users from modifying data in another transaction that has not yet been completed. For multi-user systems, the lock mechanism is required. SQL Server has a number of locks that allow transactions to lock different resources. A lock is the protection of a specified resource and is not manipulated by another transaction. SQL Server has a number of locks that allow transactions to lock different resources. A lock is the protection of a specified resource and is not manipulated by another transaction. To minimize the cost of the lock, SQL Server automatically locks the resource object with the corresponding level of lock on the task. Locking smaller objects, such as locking rows, can improve concurrency, but it is expensive because if many rows are locked, more locks are required. Locking large objects, such as locking tables, can greatly reduce concurrency because locking the entire table restricts other transactions from accessing other parts of the table, but costs are lower because only a smaller number of locks are maintained.

3, the characteristics of the lock:
1. Lock is a means of ensuring concurrency control
2. Resources that can be locked include rows, pages, clusters, tables, and databases
3. Types of locks mainly include shared and exclusive locks
4. Special type of lock including intent lock, modify lock and Mode lock
5. Shared locks allow other transactions to continue using locked resources
6. Exclusive lock allows only one transaction to access the data
7. The system itself can handle deadlocks
8. The user can customize some characteristics of the lock according to the actual situation

4, the lock is defined to the SQL statement, the operation of the data SQL is: Select,insert,update, delete. Different things are isolated, which sends different locks to the table when the SQL is executed.

Four, multiple users concurrent operation of the database will bring the following data inconsistency problem:

Dirty Read dirty reads:
This event occurs when a transaction reads data that has not yet been committed. For example: Transaction1 modifies a row of data, and then Transaction2 reads the modified row before Transaction1 has committed the modification operation. If Transaction1 rolls back the modification operation, the data read by Transaction2 can be seen as never existed.
Non-repeatable read non-repeatable reads:
This event occurs when a transaction reads the same row of data two times, but each time the data gets different. For example: Transaction1 reads a row of data, and then Transaction2 modifies or deletes the row and commits the modify operation. When Transaction1 attempts to reread the row, it gets a different data value (if the row is updated) or discovers that the row no longer exists (if the row is deleted).
Virtual Read Phantom read:
This event occurs if a row of data that meets the search criteria appears in a subsequent read operation, but the row data is not part of the original data. For example, Transactio1 reads some rows that satisfy a certain search condition, and then Transaction2 inserts a new line that matches the Transaction1 's search criteria. If Transaction1 re-executes the query that produced the original rows, it will get a different row.

In order to solve these problems, the database introduces the mechanism of "lock" (from the point of view of database system: it is divided into exclusive lock (that is, it locks), share lock and update lock, detail content is no longer described).

V. lkdlhw_2000 Personal Understanding (The following questions are speculative, not confirmed):

Isolation levels are implemented by locks, and the isolation level of transactions is equivalent to the rules used by the database developer to implement a defined set of locks based on general business requirements, so that when we define the transaction isolation level to a certain level, if we do not meet the requirements, Can we also define our own SQL locks to override the default locking mechanism of the transaction isolation level?

There are two problems with locks: one is the granularity of the lock, one is the time of the lock, and the time of the lock should consist of two kinds of the release lock when the SQL is executed, and the one that releases the lock after the end of the transaction.

Vi. examples of transaction isolation levels

1. READ UNCOMMITTED: The lowest level of transaction isolation ensures that no illegal data is read during the read process. It is possible to appeal 4 types of uncertainties.
2. Read Committed: The default transaction level for most major databases ensures that a transaction does not read to another parallel transaction that has been modified but uncommitted, avoiding "dirty reads". This level applies to most systems.
First query transaction
SET TRANSACTION Isolation Level Read Committed
BEGIN Tran
Update Cate SET sname=sname+ ' B ' where id=1
SELECT * from Cate where id=1
WAITFOR DELAY ' 00:00:6 '
Rollback TRAN--ROLLBACK TRANSACTION
Select Getdate ()
SELECT * from Cate where id=1
A second query transaction
Set TRANSACTION Isolation LEVEL Read COMMITTED--replace committed with read uncommitted to see an example of dirty reads.
SELECT * from Cate where id=1
Select Getdate ()
You can see that using read Committed successfully avoids "dirty reads".
3. Repeatable READ: Guarantees that a transaction does not modify data that has been read by another transaction but not committed (rolled back). "Dirty reads" and "non-repeatable reads" are avoided, but more performance losses are incurred.
First query transaction
SET TRANSACTION Isolation Level REPEATABLE READ--replace REPEATABLE read with Read Committed to see an example of "non-repeatable reads"
BEGIN Tran
SELECT * from Cate where id=33--read data for the first time
WAITFOR DELAY ' 00:00:6 '
SELECT * from Cate where id=33--read data for the second time, non-repeatable read
Commit
A second query transaction
SET TRANSACTION Isolation Level Read committed
Update Cate set sname=sname+ ' JD ' where id=33
SELECT * from Cate where id>30
4. Serializable: The highest level of transaction isolation, the above 3 types of uncertainty will be circumvented. This level simulates the serial execution of the transaction.
Executes in the first query window
Set TRANSACTION Isolation Level Serializable--convert Serializable to repeatable read to see an example of phantom reading
BEGIN Tran
SELECT * from Cate where id>30--the first time the data is read, "phantom read" example
WAITFOR DELAY ' 00:00:6 '--delayed 6 second read
SELECT * from Cate where id>30--read data for the first time
Commit
A second query transaction
SET TRANSACTION Isolation Level Read committed
Delete from Cate where id>33
SELECT * from Cate where id>30
Create transaction

Set transaction levels: Set TRANSACTION isolation Level
Start transaction: BEGIN Tran
Commit TRANSACTION: Commit
ROLLBACK TRANSACTION: ROLLBACK
Create transaction savepoint: Save TRANSACTION Savepoint_name
Rollback to Transaction point: ROLLBACK TRANSACTION savepoint_name

Isolation level of the database