Shulou(Shulou.com)06/02 Report--

Editor to share with you what are the characteristics of mysql transactions, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Brief introduction

A transaction is a logical processing unit consisting of a set of sql statements

Four characteristics of transaction atomicity (Atomicity): either succeed or fail undo log achieve consistency (Consistent): for example, the sum of the two amounts before and after the transfer remains the same (lsolation): the database provides a certain isolation mechanism to ensure that transactions run locks in an "independent" environment that is not affected by external concurrent operations. Mvcc multi-version concurrency control persistence (Durable): transaction commit persistence disk redo log transaction isolation level

There are four kinds of transaction isolation levels in the database, namely, read uncommitted, read committed, repeatable read, serialization, different isolation levels will produce dirty reading, phantom reading, non-repeatable reading and other related problems, therefore, when choosing the isolation level, it should be decided according to the application scenario, using different isolation levels.

READ-UNCOMMITTED √√√ READ-COMMITED × √√ REPEATABLE-READ × × √ SERIALIZABLE × × ×

Problems caused by transaction isolation level

Dirty read (Dirty Reads one transaction accesses data uncommitted by another transaction): when a transaction is accessing data and making modifications to the data that have not yet been committed to the database, another transaction also accesses the data and then uses the data. Non-repeatability (Non-Repeatable Reads a transaction twice the same query, query different data): a transaction reads the previously read data again at a certain time after reading some data, and finds inconsistencies with the previously read data. Update and delete phantom reading (Phantom Reads a transaction twice the same query) Query to different data): a transaction re-reads previously queried data according to the same query conditions, only to find that other transactions insert new data insertion validation that meets its query criteria.

View the isolation level of the transaction show variables like 'tx_isolation'

Check to see if the transaction automatically commits show variables like 'autocommit'

Turn off autocommit transaction = 0 | OFF

Set autocommit = 0

Dirty reading:

Set transaction isolation level A, B set session transaction isolation level read uncommitted;sessionA enable transaction start transaction; to insert a data INSERT INTO `db_ test`.`t _ user` (`id`, `name`) VALUES (5, 'DuQi'); sessionB another connection to query select * from t_user +-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | | 5 | DuQi | +-+-- + At this point, connection B queried the record of uncommitted transactions in connection A with an id of 5. Here it is verified that one session reads uncommitted data from another transaction.

Cannot be read repeatedly:

Modify transaction isolation level set session transaction isolation level read committed;A to enable transaction start transaction; verification update B execute query statement MySQL [db_test] > select * from t_user +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | | | 5 | DuQi | +-+ An execute update statement update t_user set name = 'duqi' where id = 5 | B execute query statement start transaction; MySQL [db_test] > select * from t_user +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | | | 5 | DuQi | +-+ A commit transaction commit | B execute query statement (the results of two queries in the same transaction are inconsistent) MySQL [db_test] > select * from t_user +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | | | 5 | duqi | +-+-+ continue to verify delete A, start transaction B, start transaction start transaction | A deletes a record delete from t_user where id = 5; B transaction query is normal, and the deleted record is still in MySQL [db_test] > select * from t_user +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | | | 5 | DuQi | +-+ A commit | B continue to query and find that the results of multiple queries in the same thing are inconsistent MySQL [db_test] > select * from t_user +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | +-+-+ verify insert A, B start transaction start transaction An inserts the record INSERT INTO `db_ test`.`t _ user` (`id`, `name`) VALUES (5, 'DuQi'); B queries MySQL [db_test] > select * user +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | +-+-+ A commit transaction commit B query can also query transactions committed by A MySQL [db_test] > select * from t_user. +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | | | 5 | DuQi | +-+ |

Illusory reading:

Modify transaction isolation level set session transaction isolation level repeatable read;A, B enable transaction start transaction;A, insert a data INSERT INTO `db_ test`.`t _ user` (`id`, `name`) VALUES (5, 'DuQi'); B query MySQL [db_test] > select * from t_user +-+-- +-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | +-+-+ A commit transaction commit B transaction query MySQL [db_test] > select * from t_user +-ZhangSan | 2 | LiSi | 3 | WangWu | 4 | LaoWang | 5 | DuQi | +-+-+ may be found between different transactions Insert can be queried, and then we continue to verify the update and delete A, B open transaction A update update t_user set name = 'duqi' where id = 5 B query MySQL [db_test] > select * from t_user +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | | | 5 | DuQi | +-+ A commit transaction commit B continue to query MySQL [db_test] > select * from t_user | +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | LaoWang | | | 5 | DuQi | +-+-+ We will continue to verify the deletion of A, B start transaction A transaction execute delete operation delete from t_user where id = 5 B transaction execution query MySQL [db_test] > select * from t_user +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | | 3 | WangWu | | 4 | LaoWang | | 5 | duqi | +-+ A commit transaction | B continue to query MySQL [db_test] > select * from t_user +-+-+ | id | name | +-+-+ | 1 | ZhangSan | | 2 | LiSi | | | 3 | WangWu | | 4 | LaoWang | | 5 | duqi | +-+ may be found by everyone. | The REPEATABLE-READ transaction isolation level solves the problem of deletions and updates, but the problem of insertion persists. MVCC

Multi-version concurrency control. MVCC is a method of concurrency control, which generally implements concurrent access to the database in the database management system and transaction memory in the programming language.

The main purpose of the implementation of mvcc in Mysql INNODB is to improve the concurrency performance of the database and to deal with read-write conflicts in a better way, so that even when there are read-write conflicts, it can be unlocked and non-blocking.

Before you understand mvcc, you must first understand two concepts, what is current reading and what is snapshot reading.

Current read

Read the latest version of the data

Operations like select lock in share mode (shared lock), select for update;update, insert, and delete (exclusive lock) are all current reads, why are they called current reads? That is, it reads the latest version of the record, ensures that other concurrent transactions cannot modify the current record, and makes a locked snapshot read of the read record.

Read historical version of the data

For example, an unlocked select operation is a snapshot read, which is neither a locked non-blocking read; the premise of a snapshot read is that the isolation level is not a serial level, and the snapshot read at the serial level will degenerate to the current read The reason why snapshot reading occurs is based on the consideration of improving concurrency performance. The implementation of snapshot reading is based on multi-version control, concurrency control, that is, MVCC, which can be considered as a variant of row lock, but it avoids locking operation in many cases and reduces overhead. Current read, snapshot read, MVCC relationship MVCC multi-version concurrency control refers to maintaining multiple versions of a data, so that there is no conflict between read and write operations. Snapshot reading is a non-blocking read function of mysql to achieve mvcc. The concrete implementation of mvcc module in Mysql is realized by three implicit fields, undo log and readview.

One additional point here: one of the three implicit fields is the only flag for the column. Some students must add a primary key when designing a table (the column depends on the primary key), even if it is almost useless. In fact, for a configuration table, there is no need to add a primary key to a table that rarely adds or deletes. Mysql will determine whether the table has a primary key when inserting data. If there is a primary key, it will use the primary key as the only mark. If there is no primary key, it will automatically generate a primary key of 7byte size, so the rationality of the table should be designed according to scenarios that do not need to be used.

Problems solved by mvcc

Concurrent scenario

1. Read: there is no problem and no concurrency control is needed. 2. Read and write: there is a thread safety problem, which may cause transaction isolation level problems, and may encounter dirty reading, non-repeatable reading, phantom reading 3, writing: there is a thread safety problem, and there may be update loss.

Problem solved

1. When reading and writing the database concurrently, there is no need to block the write operation during the read operation, and the write operation does not have to block the read operation, which improves the performance of concurrent read and write of the database. 2. It solves the transaction isolation problems such as dirty read, phantom read and non-repeatable read, but it can not solve the problem of update loss. MVCC implementation principle

The implementation principle of mvcc mainly depends on three hidden fields in the record and undolog,read view.

Hidden field

In addition to our custom fields, row records also have fields such as DB_TRX_ID,BD_ROLL_PTR,DB_ROW_ID implicitly defined by the database.

DB_TRX_ID recently modified transaction id: 6 bytes, record create this record or last modify this record transaction idDB_ROLL_PTR rollback pointer: 7 bytes, point to the previous version of this record, used to cooperate with undolog, point to the previous version of DB_ROW_ID hide primary key: 6 bytes, if the database table does not have a primary key Then innodb will automatically generate a 6-byte row_id

Undo log

Undo log is called rollback log, which represents the convenient rollback log generated during insert,delete,update operation.

When performing an insert operation, the resulting undo log is only needed when the transaction is rolled back, and can be discarded immediately after the transaction is committed. When performing update and delete operations, the resulting undo log is needed not only when the transaction is rolled back, but also when the snapshot is read, so it cannot be deleted casually, only when the snapshot read or transaction rollback does not involve the log. The corresponding log will be cleared uniformly by the purge thread (when the data is updated and deleted, only the old record is set. If the deleted_id of a record is true and the DB_TRX_ID is visible to the read view of the purge thread, then the record must be cleared.)

Principle

When the insert operation is performed, the corresponding delete statement is generated. When the delete operation is performed, the insert statement of the original data is backed up. When update is performed, the update statement of the original data is recorded. This makes it convenient to record the rollback.

Read View

READ View is the read view generated when the transaction performs snapshot read operations. At the moment when the transaction executes the snapshot, it generates a snapshot of the current data system, records and maintains the id of the current active transaction of the system, and the id value of the transaction is incremented.

DB_ROW_IDDB_TRX_IDDB_ROLL_PTRc_namei_age... eleven

Zhangsan118... 221zhangsan219... The greatest function of READ VIEW is to judge visibility, that is, when a transaction is performing a snapshot read, create a Read View view of the record as a condition to determine which version of the data the current transaction can see. It is possible to read the latest data, or it is possible to read the latest data. It is also possible to read a certain version of the data in the current record undolog. The visibility algorithm followed by read view is mainly to extract the DB_TRX_ID from the latest record of the data to be modified, and to compare it with the id of other active transactions of the system. If the properties of DB_TRX_ID and READ VIEW are not consistent with the visibility, then use the DB_ROLL_PTR rollback pointer to extract the DB_TRX_ID in the undolog for comparison. Traversing the DB_TRX_ID in the linked list until a qualified DB_TRX_ID is found, and the old record in which the DB_TRX_ID is located is the latest old version of the data visibility rule that can be seen by the current transaction

Before you understand the visibility rules, you need to understand the three global properties in Read View.

Trx_list: constant value list, which is used to maintain the active transaction of the system when Read View is generated. IDup_limit_id: record the minimum IDlow_limit_id of transaction ID in the trx_list list: Read View generation time the system has not assigned the next transaction ID

Comparison rule

1. Judge DB_TRX_ID first.

= low_limit_id. If it is greater than or equal to, the record in which the DB_TRX_ID resides will not appear until after the Read View is generated. Then it must not be visible for the current transaction. If it is less than, proceed to the next step to determine whether the DB_TRX_ID is in an active transaction. If so, it means that at the time of Read View generation, the transaction is still active, there is no commit, and the modified data cannot be seen in the current transaction. If not, the transaction started commit before the Read View was generated, and the result of the modification can be seen. These are all the contents of the article "what are the features of mysql transactions?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.