MySQL InnoDB Transaction and lock detailed _mysql

Introduction: Why the introduction of transactions?

1>. Data Integrity

2>. Data Security

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

1. characteristics of the transaction

Transactions have four characteristics: atomicity (atomiocity), consistency (consistency), isolation (isolation), and persistence (durability), which are referred to as ACID properties.

1.1 of Atomic

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

1.2 Consistency

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

1.3 Isolation of

Execution of a transaction cannot be interfered by other transactions. That is, the operation within a transaction and the data used for other

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

1.4 Persistence of

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

An operation or failure should not have any effect on its execution results.

2. Things and locks in MySQL 's InnoDB engine

2.1 SELECT ... LOCK in SHARE MODE

The data that is found in the session transaction, plus a shared lock. If the data looked for in a session transaction has been locked by another session transaction, the shared lock waits for its end to be added, and if the wait time is too long, the lock wait timeout for the transaction is displayed.

2.2 SELECT ..... For UPDATE

Data found in a session transaction, plus a read update, other session transactions will no longer be able to add other locks and must wait for their end.

2.3 INSERT , UPDATE , DELETE

A session transaction adds an exclusive lock to the data that the DML statement operates on, and the transactions of the other sessions wait for it to release the exclusive lock.

2.4 Gap and next key lock (Clearance Lock)

The InnoDB engine automatically locks up shared locks, updates, and exclusive locks in session transactions, when added to an interval range, plus a gap lock (or range lock), locking the nonexistent data to prevent phantom writing.

Note:

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

3. Four transaction isolation modes

3.1 READ uncommited

The select allows dirty reads, which means that the select reads data that has not yet been committed by other transaction modifications.

3.2 READ commited

Select can not be read repeatedly, that is, the same transaction two times to execute the same query, if the first and second query between the time period, other transactions just modify its query data and submitted, the two read data inconsistent.

3.3 Repeatable READ

Select can be read repeatedly, that is, two times in the same transaction to execute the same query, the resulting data is always consistent.

3.4 SERIALIZABLE

The only difference with repeatable reads is that the normal SELECT statement is changed to select by default .... LOCK in SHARE MODE. That is, the data involved in the query statement plus the shared lock, blocking other transactions to modify the real data.

4. Verifying transactions and locking examples

Next, we'll use the InnoDB engine in MySQL to explain how it implements the Acid feature, and the impact between transactions at different isolation levels. Sample Table structure:

CREATE TABLE ' account ' (

' ID ' int (one) not NULL auto_increment,

' vaccount_id ' varchar 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 the account data of the 10W creation date to the table accounts to facilitate testing.

tx_isolation : SET GLOBAL tx_isolation= ' read-uncommitted '
Id	Business 1	Transaction 1 output	Business 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	Business 1	Transaction 1 output	Business 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	Business 1	Transaction 1 output	Business 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	Business 1	Transaction 1 output	Business 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	Business 1	Transaction 1 output	Business 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;	Successful execution
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 indexing condition 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;