Transaction and lock resolution for MySQL InnoDB

Introduction question: why introduce transactions?

1 > . Data integrity

2 > . Data security

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

1. Characteristics of transactions

Transactions have four properties: atomicity (Atomiocity), 1 tropism (Consistency), isolation (Isolation), and persistence (Durability), which are referred to as ACID properties.

1.1 atomic

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

The consistency of 1.2

The result of the transaction execution must be to move the database from one 1 deterministic state to another 1 deterministic state.

1.3 isolation,

The execution of a transaction cannot be interrupted by other transactions. That is, the operation within a transaction and the use of data on others

Transactions are isolated, and concurrent transactions do not interfere with each other.

1.4 persistence

Once a transaction is successfully committed, changes to the data in the database are persistent. And then other other things

Operations or failures should not have any impact on the results of their execution.

2. MySQL InnoDB engine for things with locks

2.1 SELECT... LOCK IN SHARE MODE

The data found in the session transaction, plus a Shared lock. If the data found in a session transaction has been locked exclusively by another session transaction, the Shared lock will wait until it ends, and if the wait time is too long, the lock wait timeout required by the transaction will be displayed.

2.2 SELECT... . FOR UPDATE

Data found in a session transaction, plus a read update, cannot be added to other session transactions and must wait until it ends.

2.3 INSERT, UPDATE, DELETE

The session transaction will attach an exclusive lock to the data operated on by the DML statement, and the transactions of the other sessions will wait for the exclusive lock to be released.

2.4 gap and next key lock (gap lock)

The InnoDB engine will automatically add Shared locks, update locks, and exclusive locks to session transactions, and when they need to be added to an interval range, a gap lock (or range lock) will be added to lock data that does not exist to prevent magic writes.

Remark:

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.4 transaction isolation modes

3.1 READ UNCOMMITED

Dirty reads are allowed on SELECT, meaning that SELECT reads data that has not yet been committed due to other transaction modifications.

3.2 READ COMMITED

When SELECT cannot be read repeatedly, that is, the same query statement is executed twice in the same transaction. If during the time period between the first and the second query, other transactions just modify the query data and commit, the data read twice will not match.

3.3 REPEATABLE READ

SELECT can be read repeatedly, that is, the same query statement is executed twice in a transaction, and the data is always 1 to 1.

3.4 SERIALIZABLE

The only difference from repeatable reads is that by default the normal SELECT statement is changed to SELECT... . LOCK IN SHARE MODE That is, the query statement involved in the data plus the sharing of the closure, block other transactions to modify the real data.

4. Verify the transaction and lock example

Next, we'll explain how the InnoDB engine in MySQL implements the ACID feature, and the effects of transactions on transactions at different isolation levels. Example 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 to the table account with account data with a reasonably distributed creation date 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; 3 UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; 4 SELECT VACCOUNT_ID from account where ID =1001; uncommitted 5 SELECT VACCOUNT_ID from account where ID =1001; uncommitted 6 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 3 START TRANSACTION; 4 UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; 5 SELECT VACCOUNT_ID from account where ID =1001; uncommitted 6 SELECT VACCOUNT_ID from account where ID =1001; caimao101510 7 COMMIT; 8 SELECT VACCOUNT_ID from account where ID =1001; uncommitted 9 COMMIT; tx_isolation: SET GLOBAL tx_isolation=' REPEATABLE-READ ' ID Transaction 1 Transaction 1 output Transaction 2 Transaction 2 output 1 START TRANSACTION; 2 SELECT VACCOUNT_ID from account where ID =1001; caimao101510 3 START TRANSACTION; 4 UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; 5 SELECT VACCOUNT_ID from account where ID =1001; uncommitted 6 SELECT VACCOUNT_ID from account where ID =1001; caimao101510 7 COMMIT; 8 SELECT VACCOUNT_ID from account where ID =1001; caimao101510 9 COMMIT; tx_isolation: SET GLOBAL tx_isolation='SERIALIZABLE' ID Transaction 1 Transaction 1 output Transaction 2 Transaction 2 output 1 START TRANSACTION; 2 SELECT VACCOUNT_ID from account where ID =1001; caimao101510 3 START TRANSACTION; 4 UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; STATE: Updating 5 SELECT VACCOUNT_ID from account where ID =1001; caimao101510 Transaction 2 timeout 6 COMMIT; 7 START TRANSACTION; 8 UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001; 9 START TRANSACTION; 10 SELECT VACCOUNT_ID from account where ID =1001; STATE: statistics 11 Transaction 2 timeout 12 commit; tx_isolation: SET GLOBAL tx_isolation=' REPEATABLE-READ ' ID Transaction 1 Transaction 1 output Transaction 2 Transaction 2 output 1 START TRANSACTION; 2 select max(ID) FROM account; 124999 3 START TRANSACTION; 4 UPDATE account set gmt_create=date_add(gmt_create,interval +1 day) WHERE ID > =124999; 5 insert into account VACCOUNT_ID, gmt_create values (' eugene ', now ()); STATE:update 6 Transaction 2 timeout 7 START TRANSACTION; 8 SELECT * FROM account WHERE ID =124998; 2007-10-20 13:47 9 UPDATE account set gmt_create=date_add(gmt_create,interval +1 day) WHERE ID =124998; Execute successfully 10 SELECT * FROM account WHERE ID =124998; 2007-10-21 13:47 11 COMMIT; 12 COMMIT; 1 START TRANSACTION; 2 UPDATE account set gmt_create=date_add(gmt_create,interval -1 day) WHERE gmt_create > '2009-07-01'); 3 START TRANSACTION; 4 SELECT * FROM account WHERE gmt_create > '2009-07-10' LIMIT 1; 2009-10-2 13:47 5 SELECT * FROM account WHERE gmt_create > '2009-07-10' LIMIT 1; 2009-10-1 13:47 STATE:update 6 insert into account VACCOUNT_ID, gmt_create values (' gmt_create_test ', now ()); 7 Transaction 2 timeout 8 COMMIT; 9 SELECT * FROM account WHERE gmt_create > '2009-07-10' LIMIT 1; 2009-10-1 13:47 No index conditional update transaction 1 START TRANSACTION; UPDATE account set gmt_create=date_add(gmt_create,interval -1 day) WHERE gmt_create > '2009-07-01' AND gmt_create < '2009-07-10'); START TRANSACTION; insert into account VACCOUNT_ID, gmt_create values (' gmt_create_interval ', now ()); Transaction 2 timeout COMMIT;