MySQL lock mechanism and transaction control

How to add Locks

Syntax for locking tables:
LOCK TABLES
Tbl_name [as alias] {READ [LOCAL] | [Low_priority] WRITE}
[, Tbl_name [as alias] {READ [LOCAL] | [Low_priority] WRITE}] ...
Unlock syntax:
UNLOCK TABLES

The InnoDB storage engine provides row-level locks, two locking modes for shared and exclusive locks, and four different isolation levels.

Dead lock

InnoDB automatically detects the deadlock of a transaction and rolls back one or several transactions to prevent deadlocks. InnoDB cannot detect deadlocks where the MySQL lock tables The settings table is locked or where the storage engine that is involved with INNODB is locked. You must resolve these situations by setting the value of the Innodb_lock_wait_timeout system variable. If you want to rely on lock waiting time-out to solve the deadlock problem, for updating a transaction-intensive application, it is possible to cause a large number of transaction lock waits, resulting in system exceptions, so it is not recommended to mix the tables of different storage types in one transaction, nor recommend the same type of table with different locking methods.

Transaction control

MySQL supports local transactions through set autocommit, START TRANSACTION, Commit, and rollback statements. Grammar:
START TRANSACTION | BEGIN [Work]
COMMIT [Work] [and [No] CHAIN] [[No] RELEASE]
ROLLBACK [Work] [and [No] CHAIN] [[No] RELEASE]
SET autocommit = {0 | 1}
By default, MySQL is autocommit, and if you need to commit and rollback a transaction through explicit commit and rollback, you need to start the transaction through explicit transaction control commands, which are significantly different from Oracle's transaction management. If the application is migrating from an Oracle database to a MySQL database, you need to ensure that the transaction is explicitly managed in your app.

1. The start transaction or BEGIN statement can start a new transaction.
2. Commit and rollback are used to commit or rollback a transaction.
3. The chain and release clauses are used to define the operation after a transaction commits or rolls back, chain starts a new thing immediately, and has the same isolation level as the transaction just now, and release disconnects the client.
4. The set autocommit can modify the way the current connection is submitted, and if set autocommit=0, all the transactions after Setup need to be committed or rolled back by explicit commands.

If we only need transaction control over certain statements, it is convenient to start a transaction using start transaction, so that the transaction can be automatically returned to autocommit after the end, if we want all of our transactions to be not autocommit, It is convenient to control transactions by modifying autocommit so that you do not need to execute start TRANSACTION at the beginning of each transaction.

Time	Session_1	Session_2
--------------------------------------------------------->	Mysql> select * from TT3; Empty Set (0.00 sec)	Mysql> select * from TT3; Empty Set (0.00 sec)
	mysql> start transaction; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into TT3 values (' 1 ', 1); Query OK, 1 row affected (0.03 sec)
		Mysql> select * from TT3; Empty Set (0.00 sec)
	Mysql> commit; Query OK, 0 rows affected (0.05 sec)
		Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 1 | 1.00 | +------+------+ 1 row in Set (0.00 sec)
	mysql> INSERT into TT3 values (' 2 ', 2); Query OK, 1 row affected (0.04 sec) This transaction is performed according to Autocommit
		Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 1 | 1.00 | | 2 | 2.00 | +------+------+ 2 rows in Set (0.00 sec)
	mysql> start transaction; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into TT3 values (' 3 ', 3); Query OK, 1 row Affected (0.00 sec) Mysql> Commit and chain; Query OK, 0 rows affected (0.05 sec) Automatic start of a new transaction mysql> INSERT into TT3 values (' 4 ', 4); Query OK, 1 row Affected (0.00 sec)
		Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 1 | 1.00 | | 2 | 2.00 | | 3 | 3.00 | +------+------+ 3 Rows in Set (0.00 sec)
	Mysql> commit; Query OK, 0 rows affected (0.06 sec)
		Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 1 | 1.00 | | 2 | 2.00 | | 3 | 3.00 | | 4 | 4.00 | +------+------+ 4 rows in Set (0.00 sec)

Starting a transaction will cause an implied unlock tables to be executed:

Time	Session_1	Session_2
--------------------------------------------------------->	Mysql> select * from TT3; Empty Set (16.65 sec)	Mysql> select * from TT3; Empty Set (16.65 sec)
	mysql> lock table Tt3 write; Query OK, 0 rows Affected (0.00 sec)
		Mysql> select * from TT3; Wait
	mysql> INSERT into TT3 values (' 1 ', 1); Query OK, 1 row affected (0.07 sec)	Wait
	mysql> rollback; Query OK, 0 rows Affected (0.00 sec)	Wait
	mysql> start transaction; Query OK, 0 rows Affected (0.00 sec)	Wait
		Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 1 | 1.00 | +------+------+ 1 row in Set (37.71 sec) When a transaction is started, the table lock is freed. A table lock added to lock mode cannot be rolled back by rollback.

Therefore, in the same transaction, it is best not to use a table of different storage engines, otherwise rollback requires special handling of non-transaction type tables because commit, rollback can only commit and roll back tables of the transaction type.
Typically, only committed transactions are recorded in the binary log, but if a transaction contains a non-transactional type of table, the rollback operation is also logged to the binary log to ensure that updates to the non-transaction type table can be replicated to the database from which it was made.

As with Oracle's transaction management, all DDL statements cannot be rolled back, and some DDL statements cause implicit commits.

In a transaction, you can specify a part of a rollback transaction by defining savepoint, but you cannot specify a part of the commit transaction. For complex applications, you can define multiple different savepoint, and roll back different savepoint when different conditions are met. It is important to note that if you define savepoint with the same name, the SavePoint defined later overrides the previous definition. For savepoint that you no longer need to use, you can remove savepoint from the release savepoint command, delete the savepoint, and no longer execute the rollback to savepoint command.

Our example is to simulate a part of a rollback transaction by defining the SavePoint to specify the location of the transaction that needs to be rolled back.

Time	Session_1	Session_2
--------------------------------------------------------->	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 2 | 2.00 | | 3 | 3.00 | | 4 | 4.00 | +------+------+ 3 Rows in Set (0.00 sec)	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 2 | 2.00 | | 3 | 3.00 | | 4 | 4.00 | +------+------+ 3 Rows in Set (0.00 sec)
	mysql> start transaction; Query OK, 0 rows Affected (0.00 sec) Mysql> Delete from tt3 where id = ' 2 '; Query OK, 1 row Affected (0.00 sec)
	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 3 | 3.00 | | 4 | 4.00 | +------+------+ 3 Rows in Set (0.00 sec)	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 2 | 2.00 | | 3 | 3.00 | | 4 | 4.00 | +------+------+ 3 Rows in Set (0.00 sec)
	mysql> savepoint test; Query OK, 0 rows Affected (0.00 sec) Mysql> Delete from tt3 where id = ' 3 '; Query OK, 1 row Affected (0.00 sec)
	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 4 | 4.00 | +------+------+ 3 Rows in Set (0.00 sec)	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 2 | 2.00 | | 3 | 3.00 | | 4 | 4.00 | +------+------+ 3 Rows in Set (0.00 sec)
	mysql> rollback to savepoint test; Query OK, 0 rows Affected (0.00 sec)
	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 3 | 3.00 | | 4 | 4.00 | +------+------+ 2 rows in Set (0.00 sec)	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 2 | 2.00 | | 3 | 3.00 | | 4 | 4.00 | +------+------+ 3 Rows in Set (0.00 sec)
	Mysql> commit; Query OK, 0 rows affected (0.05 sec)
	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 3 | 3.00 | | 4 | 4.00 | +------+------+ 2 rows in Set (0.00 sec)	Mysql> select * from TT3; +------+------+ | ID | name | +------+------+ | 3 | 3.00 | | 4 | 4.00 | +------+------+ 2 rows in Set (0.00 sec)

http://hudeyong926.iteye.com/blog/1489929

Http://see.xidian.edu.cn/cpp/html/1469.html

Http://www.cnblogs.com/ggjucheng/archive/2012/11/14/2770445.html