Transaction ISOLATION LEVEL

" Concurrent Read Problem "

Overwrite update data when "First Class update Lost" is revoked.

Dirty read reads uncommitted updates . Pending status.

"Virtual read" the inserted data that the other transaction has committed . After the transaction T1 reads a data, the transaction T2 modifies it, and when the transaction T1 reads the data again, it gets a different value than the previous one.

"Non-repeatable read" does not avoid "keep up to date ". A transaction repeatedly reads the same row of data two times, but gets different results. Two reads the data is modified by other firms.

The second category of missing updates overwrites the updates that have been submitted .

3.1. First category missing update: When a transaction is undone, the updated data submitted by the other transaction is overwritten. 3.2. Dirty reads: One transaction is read to another transaction uncommitted update data. 3.3. Phantom read: A transaction executes two queries, but the second query has more data rows than the first query. 3.4. Non-REPEATABLE READ: One transaction reads the same row two times, but these two read the same data. 3.5. Category Two missing updates: This is a special case in non-repeatable reads, where one transaction overwrites the updated data submitted by another transaction.

Virtual ReadAfter the transaction T1 reads a data, the transaction T2 modifies it, and when the transaction T1 reads the data again, it gets a different value than the previous one. Phantom Reading(Phantom Reads) transactions are queried two times during the operation, the results of the second query contain data that is not present in the first query or the data that appears 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"

1) uncommitted read (READ UNCOMMITTED): The SELECT statement is executed in a non-locking manner, so it is possible to read dirty data with the lowest isolation level.

Dirty reads are allowed, but updates are not allowed to be lost. If one transaction has already started writing data, the other transaction does not allow simultaneous writes, but allows other transactions to read the row data. The isolation level can be "Exclusive write lock"Realize.

2) Submit Read Committed: "See INSERT, see Update" resolves dirty read, but does not resolve non-repeatable read.

Allow non-repeatable reads, but dirty reads are not allowed。 This can be achieved through "instantaneous shared read lock" and "exclusive write lock". --a transaction that reads data allows other transactions to continue to access the row's data, but uncommitted write transactions will prevent other transactions from accessing the row.

A shared lock is added to the record when the data is read in Trasaction A, but the read end is released immediately. Other transaction B attempts to modify this record will wait until the end of the read process in a, rather than the end of the entire trasaction a. Therefore, the read results for the same record may be different at different stages of trasaction a.

Problems that may occur: non-repeatable reading.

3) Repeatable read (repeated read): "See INSERT, don't see update"

A query within the same transaction is the default level of InnoDB when the transaction begins. In the SQL standard, this isolation level eliminates non-repeatable reads, but there are also phantom reads. This can be achieved through "shared read lock" and "exclusive write lock".

For a read-out record, add a shared lock until transaction a ends. Other transaction B attempts to modify this record will wait until Trasaction a ends.

Possible problems: Phantom reads (insertions) may occur when a scope query is executed.

--Transactions that read data will prohibit write transactions (but allow read transactions), and write transactions prohibit any other transactions.

4) serial Read (Serializable): Fully serialized read, all SELECT statements are implicitly converted to select ... Lock in SHARE MODE, that is, reads using table-level shared locks , both read and write blocking each other. The isolation level is highest.

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. It avoids dirty reads and has better concurrency performance. Although it causes concurrency problems such as non-repeatable reads, Phantom reads, and second-class loss updates, the application can be controlled by pessimistic or optimistic locks on individual occasions where such problems may occur.

Add range locks (such as table locks, page locks, and so on, I have no deep research on range lock) until transaction a ends. This prevents other Trasaction B operations such as insert,update in this range.

Magic Reading, dirty reading, non-repeatable reading and other problems will not occur.

Isolation Level Settings

SELECT @ @tx_isolation

Set TRANSACTION ISOLATION level Read Committed;

Set global transaction Isolation level Read Committed;

1) The Service startup option --transaction-isolation or set in the configuration file :

[mysqld]transaction-isolation = {read-uncommitted | read-committed | Repeatable-read | SERIALIZABLE}

2) Settings after service startup:

SET [GLOBAL | SESSION] TRANSACTION isolation level{READ Uncommitted | READ COMMITTED | Repeatable READ | SERIALIZABLE}

Transaction Properties Acid1) atomicity (atomicity)

The so-called atomicity is the operation of a set of operations as an operating unit, Atomic operations , that is, either all execute, or all do not execute.

2) Consistency (consistency)

Transactional consistency means that the database must be in a consistent state before and after a transaction is executed . If the transaction completes successfully, all changes in the system are applied correctly and the system is in a valid state. If an error occurs in the transaction, all changes in the system are automatically rolled back and the system returns to its original state.

Only legitimate data can be written to the database, or the transaction should roll back to its original state.

3) Isolation (isolation)

Isolation refers to the concurrency of transactions that are isolated from each other. That is, the operations within a transaction and the data being manipulated must be blocked from being seen by other transactions attempting to modify it.

4) Durability (durability)

Persistence refers to the fact that once a transaction is committed, its changes to the data in the database are permanent , and the subsequent operations and database failures should not have any effect on it. That is, once a transaction commits, the DBMS (database Management System) guarantees that its changes to the data in the database should be permanent and that persistence is guaranteed through database backup and recovery.

Transaction ISOLATION Level