MySQL InnoDB transactions and locks

Question: Why is transaction introduced?

1>.Data Integrity

2>.Data security

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

1.Transaction Features

Transactions have four features: Atomiocity, Consistency, Isolation, and Durability.

1.1Atomicity

A transaction is the logical unit of work of a database. All operations in a transaction are either done or not done.

1.2Consistency

The result of transaction execution must be that the database changes from a consistent state to another consistent state.

1.3Isolation

The execution of a transaction cannot be disturbed by other transactions. That is, the internal operations of a transaction and the data used for other

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

1.4Durability

Once a transaction is successfully committed, modifications to data in the database are persistent. Other

Operations or faults should not affect the execution results.

2. MySQLInnoDBTransactions and locks in the engine

2.1 SELECT ...... LOCK IN SHARE MODE

Add a shared lock to the data searched in the session transaction. If the data searched in the session transaction has been exclusive locked by other session transactions, the shared lock will wait for the end of the transaction to be added. If the wait time is too long, the lock wait timeout required by the transaction will be displayed.

2.2 SELECT ..... FOR UPDATE

When a read update is added to the data searched in a session transaction, other session transactions cannot be added with other locks and must wait until the end of the transaction.

2.3 INSERT, UPDATE, DELETE

The session transaction adds 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 locks(GAP lock)

The InnoDB engine automatically adds a gap lock (or range lock) to the shared lock, update lock, and exclusive lock in session transactions when they need to be added to a range value domain ), lock non-existing data to prevent phantom writing.

Note:

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

3.Four transaction isolation Modes

3.1 READ UNCOMMITED

Dirty reads are allowed during the SELECT statement, that is, the SELECT statement reads data modified by other transactions but not committed.

3.2 READ COMMITED

The SELECT statement cannot be read repeatedly, that is, the same query statement is executed twice in the same transaction, when the data queried by other transactions is modified and committed, the data read twice is inconsistent.

3.3 REPEATABLE READ

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

3.4 SERIALIZABLE

The only difference from repeatable reads is that the normal SELECT statement is changed to SELECT… by default .... Lock in share mode. That is, it adds sharing to the data involved in the query statement, blocking other transactions from modifying real data.

4.Example of verifying transactions and locking

Next, we will use the InnoDB Engine in MySQL to explain how to implement the ACID feature and the impact between 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, we can write 10 million accounts with reasonable creation date distribution 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 accounts where ID = 1001;	Caimao101510
			Start transaction;
3			UPDATE account set VACCOUNT_ID = 'uncitted' where ID = 1001;
4			SELECT VACCOUNT_ID from accounts where ID = 1001;	Uncommitted
5	SELECT VACCOUNT_ID from accounts where ID = 1001;	Uncommitted
6			ROLLBACK;
7	SELECT VACCOUNT_ID from accounts 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 accounts where ID = 1001;	Caimao101510
3			Start transaction;
4			UPDATE account set VACCOUNT_ID = 'uncitted' where ID = 1001;
5			SELECT VACCOUNT_ID from accounts where ID = 1001;	Uncommitted
6	SELECT VACCOUNT_ID from accounts where ID = 1001;	Caimao101510
7			COMMIT;
8	SELECT VACCOUNT_ID from accounts 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 accounts where ID = 1001;	Caimao101510
3			Start transaction;
4			UPDATE account set VACCOUNT_ID = 'uncitted' where ID = 1001;
5			SELECT VACCOUNT_ID from accounts where ID = 1001;	Uncommitted
6	SELECT VACCOUNT_ID from accounts where ID = 1001;	Caimao101510
7			COMMIT;
8	SELECT VACCOUNT_ID from accounts 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 accounts where ID = 1001;	Caimao101510
3			Start transaction;
4			UPDATE account set VACCOUNT_ID = 'uncitted' where ID = 1001;	STATE: Updating
5	SELECT VACCOUNT_ID from accounts where ID = 1001;	Caimao101510
			Transaction 2 timeout
6	COMMIT;
7	Start transaction;
8	UPDATE account set VACCOUNT_ID = 'uncitted' where ID = 1001;
9			Start transaction;
10			SELECT VACCOUNT_ID from accounts 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 ('ugene ', now ());	STATE: update
6			Transaction 2 timeout
7			Start transaction;
8			SELECT * FROM account where id = 124998;	2007-10-
9			UPDATE account set gmt_create = date_add (gmt_create, interval + 1 day) where id = 124998;	Execution successful
10			SELECT * FROM account where id = 124998;
11	COMMIT;
12			COMMIT;
1	Start transaction;
2	UPDATE account set gmt_create = date_add (gmt_create, interval-1 day) WHERE gmt_create> '2017-07-01 ′;
3			Start transaction;
4			SELECT * FROM account WHERE gmt_create> '2017-07-10 'LIMIT 1;
5	SELECT * FROM account WHERE gmt_create> '2017-07-10 'LIMIT 1;			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> '2017-07-10 'LIMIT 1;
Update transactions without indexing Conditions
1	Start transaction;
	UPDATE account set gmt_create = date_add (gmt_create, interval-1 day) WHERE gmt_create> '2017-07-01 'AND gmt_create <'2017-07-10 ′;
			Start transaction;
			Insert into account (VACCOUNT_ID, gmt_create) values ('gmt _ create_interval ', now ());
			Transaction 2 timeout
	COMMIT;