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

Transaction isolation level setting

Set global transaction isolation level read committed; / / Global set session transaction isolation level read committed; / / current session

Modify the transaction commit method (whether to commit automatically, mysql defaults to auto commit)

SET AUTOCOMMIT = 1; / / automatic submission, manual submission for 0

Different database engines have different MVCC schemas, such as optimistic and pessimistic concurrency control.

Innodb

Description:

InnoDB's MVCC is achieved by saving two hidden columns after each row of records. These two columns, one saves the creation time of the row, and the other saves the expiration time (or deletion time) of the row. Of course, what is stored is not the actual time value, but the system version number (system version number). Each time you start a new transaction, the system version number is automatically incremented. The system version number at the beginning of the transaction will be used as the version number of the transaction.

SELECT

InnoDB checks each row of records against the following two criteria:

A. InnoDB only looks for rows whose version is earlier than the current transaction version (that is, the system version number of the row is less than or equal to the system version number of the transaction), which ensures that the rows read by the transaction either exist before the transaction starts, or are inserted or modified by the transaction itself.

b. The deleted version of the row is either undefined or greater than the current transaction version number. This ensures that the rows read by the transaction are not deleted before the transaction begins.

Only records that meet the above two conditions can be returned as query results.

INSERT

InnoDB saves the current system version number as the line version number for each line of the new insert.

DELETE

InnoDB saves the current system version number as the row deletion identity for each row deleted.

UPDATE

InnoDB is a new record for inserting a row, saving the current system version number as the row version number, and saving the current system version number to the original line as the line deletion ID.

Note:

MVCC only works under two isolation levels, REPEATABLE READ and READ COMMITED. The other two isolation levels are incompatible with MVCC because READ UNCOMITTED always reads the latest rows of data, not those that match the current transaction version. SERIALIZABLE locks all rows read.

View the status of the current table

Show table status like 'task'\ G

Dirty, unrepeatable, phantom reading

Dirty reading: the current transaction reads the uncommitted state of another transaction, and the transaction is not isolated.

Non-repeatable reading: transaction isolation is achieved, but the data is found to be inconsistent when the same piece of data is read twice.

Illusory reading: twice querying the same batch of data, it is found that new data has been inserted, mainly because other transactions have inserted the dataset. (added gap lock to solve the problem)

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.