Shulou(Shulou.com)05/31 Report--

This article mainly introduces "detailed explanation of MySQL InnoDB transaction and lock". In daily operation, I believe many people have doubts about the detailed explanation of MySQL InnoDB transaction and lock. The editor has consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful to answer the doubts of "detailed explanation of MySQL InnoDB transaction and lock"! Next, please follow the editor to study!

Introduction: why introduce transactions?

1 >. Data integrity

2 >. Data security

3 >. Make full use of system resources to improve the concurrent processing ability of the system

1. Characteristics of the transaction

Transaction has four characteristics: Atomiocity, Consistency, Isolation and Durability, which are referred to as ACID characteristics.

1.1 atomicity

A transaction is the logical working unit of a database, and all operations included in the transaction are either done or not done.

1.2 consistency

The result of transaction execution must be to change the database from one consistent state to another.

1.3 isolation

The execution of one transaction cannot be interfered with by other transactions. That is, the operations within a transaction and the data used to other

Transactions are isolated and transactions executed concurrently do not interfere with each other.

1.4 persistence

Once a transaction is successfully committed, the modification of the data in the database is persistent. And then the rest.

The operation or failure should not have any effect on the result of its execution.

2. Things and locks in MySQL's InnoDB engine

2.1 SELECT. LOCK IN SHARE MODE

The data found in the session transaction, plus a shared lock. If the data found in the session transaction has been exclusively locked by other session transactions, the shared lock will wait for it to end, and if the waiting time is too long, it will show the lock waiting timeout required by the transaction.

2.2 SELECT... .. FOR UPDATE

With the data found in the session transaction and a read update trivial, other session transactions will not be able to add other locks and must wait for it to finish.

2.3 INSERT 、 UPDATE 、 DELETE

The session transaction will add an exclusive lock to the data operated by the DML statement, and the transactions of other sessions will wait for it to release the exclusive lock.

2.4 gap and next key lock (clearance lock)

The InnoDB engine automatically gives shared locks, update trivial locks and exclusive locks in session transactions, and adds a gap lock (or range lock) when it needs to be added to an interval range to lock non-existent data to prevent phantom writing.

Remarks:

The situation described in 2.1, 2.2, 2.3 and 2.4 above is also related to the transaction isolation level set by MySQL.

3. Four transaction isolation modes

3.1 READ UNCOMMITED

Dirty reading is allowed in SELECT, that is, SELECT reads data modified by other transactions that has not yet been committed.

3.2 READ COMMITED

SELECT cannot be read repeatedly, that is, the same query statement is executed twice in the same transaction. If the query data is modified and submitted by other transactions during the period between the first query and the second query, the data read twice is inconsistent.

3.3 REPEATABLE READ

SELECT can be read repeatedly, that is, when the same query statement is executed twice in the same transaction, the data obtained is always consistent.

3.4 SERIALIZABLE

The only difference between repeatable and readable is that ordinary SELECT statements are changed to SELECT by default. . LOCK IN SHARE MODE. That is, adding sharing trivial to the data involved in the query statement, blocking other transactions to modify the real data.

4. Validate transaction and locking example

Next, we will use the InnoDB engine in MySQL to explain how it implements the ACID feature and the impact between transactions under different isolation levels. Sample table structure:

CREATE TABLE `account `(

`ID`int (11) NOT NULL AUTO_INCREMENT

`VACCOUNT_ ID`varchar (32) NOT NULL

`GMT_ create` datetime NOT NULL

PRIMARY KEY (`ID`)

KEY `idx_VACCOUNT_PARAMETER_VACCOUNTID `(`VACCOUNT_ ID`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

Then write 10W items of account data with reasonable distribution of creation date to the table account to facilitate testing.

Tx_isolation:SET GLOBAL tx_isolation='read-uncommitted'

ID transaction 1 transaction 1 output transaction 2 transaction 2 output

1 START TRANSACTION

2 SELECT VACCOUNT_ID from account where ID = 1001; caimao101510

START TRANSACTION

three

UPDATE account set VACCOUNT_ID='uncommitted' where ID= 1001

four

SELECT VACCOUNT_ID from account where ID = 1001; uncommitted

5 SELECT VACCOUNT_ID from account where ID = 1001; uncommitted

six

ROLLBACK

7 SELECT VACCOUNT_ID from account where ID = 1001; caimao101510

8 COMMIT

Tx_isolation:SET GLOBAL tx_isolation='read-committed'

ID transaction 1 transaction 1 output transaction 2 transaction 2 output

1 START TRANSACTION

2 SELECT VACCOUNT_ID from account where ID = 1001; caimao101510

three

START TRANSACTION

four

UPDATE account set VACCOUNT_ID='uncommitted' where ID= 1001

five

SELECT VACCOUNT_ID from account where ID = 1001; uncommitted

6 SELECT VACCOUNT_ID from account where ID = 1001; caimao101510

seven

COMMIT

8 SELECT VACCOUNT_ID from account where ID = 1001; uncommitted

9 COMMIT

Tx_isolation:SET GLOBAL tx_isolation='REPEATABLE-READ'

At this point, the study of "detailed explanation of MySQL InnoDB transactions and locks" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.