[Mysql] about the transaction isolation level, mysql isolation level

[Mysql] about the transaction isolation level, mysql isolation level
I. Lock types

There are many types of locks in MySQL, including common table locks and row locks, and new Metadata locks. Table locks apply locks to a whole table, although they can be divided into read locks and write locks, after all, locking the entire table will lead to a reduction in concurrency, which is generally used for ddl processing.

The row lock is used to lock data rows. This locking method is complicated, but because only limited data is locked and no restrictions are imposed on other data, it is highly concurrent, mySQL generally uses row locks to process concurrent transactions.

Ii. Lock Granularity

In order to improve the database concurrency as much as possible, the smaller the data range to be locked each time, the better. Theoretically, the solution that only locks the data of the current operation will get the maximum concurrency, however, the management lock is a resource-consuming task (involving operations such as obtaining, checking, and Releasing locks). Therefore, the database system must balance the high concurrency response and system performance, in this way, the concept of "Lock granularity" is introduced.

One way to improve the sharing and sending of resources is to make the locking object more selective. Try to lock only part of the data to be modified, rather than all resources. The more ideal way is to precisely lock the modified data slices. At any time, the less data is locked for a given resource, the higher the system concurrency, as long as there is no conflict between them.

However, locking also consumes resources. Various operations of the lock, including obtaining the lock, checking the lock, checking whether it has been removed, and releasing the lock, will increase the overhead of the system. The so-called lock policy seeks to balance the lock overhead and data security.

Table lock: The minimum management lock overhead and the minimum allowed concurrency lock mechanism. The lock mechanism used by the MyIsam storage engine. When you want to write data, lock the entire table and wait for other read and write actions. In addition to the lock policy used by the MyIsam storage engine, MySql also uses table locks to execute certain actions, such as alter table. In addition, the write lock has a higher priority than the read lock, so a write lock may be inserted before the read lock queue.

Row lock: Supports the maximum concurrency lock Policy (also brings the maximum lock overhead ). Both InnoDB and Falcon use this policy. Row-level locks are implemented only at the storage engine layer, but not at the MySQL server layer. The server layer is completely unaware of the lock implementation in the storage engine. MySql is an open architecture. You can implement your own storage engine and implement your own lock granularity policies. Unlike Oracle, you have no chance to change the lock policy, oracle uses row locks.

3. deadlock

Deadlock refers to the illusion that two or more transactions occupy each other on the same resource and request to lock the resources occupied by the other party, resulting in a vicious circle. A deadlock occurs when multiple transactions lock the same resource at the same time. The database system implements various Deadlock Detection and deadlock timeout mechanisms. InnoDB currently processes deadlocks by rolling back transactions that hold at least row-level exclusive locks.

Iv. ACID principles of transactions

From the business point of view, a set of database operations requires four features:

• Atomicity (Atomicity): A transaction must be considered an inseparable minimum unit of work. All operations in the entire transaction must be committed successfully or rolled back. For a transaction, it is impossible to perform only part of the operations, which is the atomicity of the transaction.
• Consistency: the database always changes from one consistent state to another. The following bank column will talk about
• Isolation (Isolation): Generally, modifications made by a firm are invisible to other transactions before they are finally committed.
• Durability (Durability): Once a transaction is committed, its modifications are permanently saved to the database. At this time, even if the system crashes, the modified data will not be lost. (The Persistence security is also related to the log refresh level. Different levels correspond to different data security levels .)

To better understand ACID, take bank account transfer as an example:

BEGIN;SELECT balance FROM checking WHERE customer_id = 10233276;UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;COMMIT;

Atomicity: either full submission (10233276 of the checking balance is reduced by 200, and the savings balance is increased by 200) or full rollback (the balance of both tables does not change)

Consistency: the consistency in this example is reflected in the fact that 200 yuan won't crash because the database system ran to 3rd rows and before 4th rows, because the transaction has not been submitted.

Isolation: Operation statements in A transaction are allowed to be isolated from statements in other transactions. For example, when transaction A runs after 3rd and before 4th, transaction B queries the checking balance, it can still see the RMB 200 deducted from transaction A (the account money remains unchanged) Because transaction A and transaction B are isolated from each other. Before transaction A is committed, transaction B cannot observe the data changes

5. Concurrency problems can be summarized into the following categories: 1. Update loss

When you cancel a transaction, overwrite the updated data committed by other transactions.

Example: Transaction A and transaction B are concurrently executed, and transaction A is committed after the update is executed. Transaction B is updated before transaction A and transaction B ends, then, the two update operations are lost.

2. Dirty reading

One Transaction reads the uncommitted update data of another transaction

Example: Transaction A and transaction B are concurrently executed. After transaction B is updated, transaction A queries the data that transaction B has not committed, and transaction B rolls back, the data obtained by transaction A is not the real data in the database. That is, dirty data, which is inconsistent with the data in the database.

3. Repeatable reading

One Transaction reads the updated data committed by another transaction

Example: Transaction A and transaction B run concurrently. Transaction A queries the data, and transaction B updates the data. When transaction A queries the data again, the data changes.

4. Overwrite updates

This is a special case in non-repeated reads. One Transaction overwrites the updated data committed by another transaction.

Example: transaction A updates the data, and transaction B updates the data. Transaction A queries and finds that the updated data has changed.

5. Virtual read (phantom read)

One Transaction reads the newly inserted data committed by another transaction

Example: Transaction A and transaction B run concurrently, transaction A queries data, and transaction B inserts or deletes data. Transaction A re-queries and finds that there is no data in the result set or the previous data disappears.

Vi. isolation level1. SERIALIZABLE)

During the execution of a transaction, no updates are made to the database by other transactions. during the execution of the transaction, no concurrent execution of other transactions is allowed. Fully serialized execution can only be performed one by one. Table-level shared locks are required for each read, and read/write operations are blocked.

2. repeatable read (repeatable read)

During the execution of a transaction, you can see the newly inserted records submitted by other transactions, but cannot see the updates of existing records by other transactions.

For read records, add A shared lock until transaction A ends. Other transaction B's attempt to modify this record will wait until transaction A ends.

The queries in the same transaction are consistent at the start time of the transaction, and the InnoDB default level. In the SQL standard, this isolation level eliminates non-repeated reads, but there are still Phantom reads.

3. READ COMMITTED)

During the execution of a transaction, you can see the newly inserted records that have been committed by other transactions, and the updates to existing records that have been committed by other transactions.

Add A shared lock to the record when reading data in transaction A, but release immediately after the read ends. The attempt to modify this record by other transaction B will wait until the reading process in A ends without the end of transaction. Therefore, the reading results for the same record may be different at different stages of transaction.

Possible problems: Repeated read is not allowed.

4. read uncommitted (uncommitted read)

During the execution of a transaction, you can see the newly inserted records not committed by other transactions, and the updates to existing records not committed by other transactions.

No shared lock is added. Therefore, other transaction B can modify the same record during the reading process of transaction A, which may cause the data read by A to be damaged or incomplete or incorrect.

In addition, transaction A can read the data modified in transaction B (not submitted. For example, transaction B modifies the R record but does not submit it. In this case, read the R record in transaction A and read the data modified by B.

Isolation level	Dirty Read)	NonRepeatable Read)	Phantom Read)
Read uncommitted)	Possible	Possible	Possible
Read committed)	Impossible	Possible	Possible
Repeatable read)	Impossible	Impossible	Possible
Serializable)	Impossible	Impossible	Impossible

 

Because InnoDB of MySQL uses the RR level by default, We need to enable this session to the RC level and set the binlog mode.

mysql> select @@session.tx_isolation;+-----------------------+| @@global.tx_isolation |+-----------------------+| REPEATABLE-READ       |+-----------------------+SET sessionbinlog_format = 'ROW'; //MIXED

Table Structure

CREATE TABLE `users` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,  `age` tinyint(11) NOT NULL,  PRIMARY KEY (`id`),  KEY `id_age` (`age`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;INSERT INTO users VALUES \( 1 , 'Bob' , 27 ), \( 2 , 'Mike' , 7 ),\( 3 , 'Tony' , 40 ),\( 4 , 'Bill' , 21 ),\( 5 , 'Mark' , 18 );

Phantom read

SET session transaction isolation level  Repeatable read;

Phantom read occurs when two identical queries are executed, and the result set returned by the second query is different from that returned by the first query. Occurrence: no range lock

Transaction 1	Transaction 2
SELECT * FROM users WHERE age BETWEEN 10 AND 30
	INSERT INTO users VALUES ( 3 , 'Bob' , 27 );
SELECT * FROM users WHERE age BETWEEN 10 AND 30;

 

How to Avoid: the serialization isolation mode may occur in any low-level isolation.

Non-repeated read

SET session transaction isolation level read committed;

In the lock-based parallel control method, if no read lock is added when select is executed, the non-repeated read will occur. In the multi-version parallel control mechanism, when a transaction with a commit conflict needs to be rolled back but released, the non-repeated read issue will occur.

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

 

In the above example, transaction 2 is committed successfully and the modifications it has made are visible. However, transaction 1 has read another value. At the isolation level of serialization and Repeatable read, the database management system returns the old value, that is, the value before being modified by transaction 2. At the isolation level between committed read and uncommitted read, the updated value may be returned, which is "non-repeated read ".

There are two policies to prevent this problem:

1. postpone the execution of transaction 2 until transaction 1 is committed or rolled back. This policy is applied when the lock is used. (Pessimistic lock mechanism, such as adding an exclusive lock to data rows using select for update)

2. In multi-version parallel control, transaction 2 can be committed first. Transaction 1 continues to run on the old version of data. When transaction 1 finally tries to commit, the database will check whether its results are the same as the sequential execution of transaction 1 and transaction 2. If yes, transaction 1 is committed successfully. If not, transaction 1 will be rolled back. (Optimistic Locking Mechanism)

Dirty read

SET session transaction isolation level read uncommitted;

Dirty read occurs when transaction A reads data that has been modified by another transaction B but has not yet been committed. If B rolls back, transaction A reads invalid data. This is similar to non-repeated reads, but the second transaction does not need to be committed.

Transaction 1	Transaction 2
SELECT * FROM users WHERE id = 1；
	UPDATE users SET age = 21 WHERE id = 1
SELECT FROM users WHERE id = 1；