MySQL InnoDB plus lock timeout rollback mechanism

Add by Zhj: It seems that my understanding of MySQL has yet to be deepened, the water is still quite deep ah, MySQL to record lock, you can set the time-out through the innodb_lock_wait_timeout parameter,

If the lock waits longer than this time, it will be rolled back, but there are two ways to roll back: the first: Rollback the current lock statement, and the second: Roll back the entire transaction. Both of these methods are passed by parameter

Innodb_rollback_on_timeout to control. If off, indicates that only the SQL statement that locks the timeout is rolled back when the lock time-out rollback is on, or if it is on, the entire transaction is rolled back. Default

is off. In MySQL Admin Cookbook, the author strongly recommends that this parameter be set to ON, but it doesn't matter if it's off, and you can capture that Gartha in your application.

After the application executes the rollback, this guarantees atomicity, so long as you do not commit, you will not destroy the atom. That's what Django should do. My personal humble opinion is: feeling

Set to ON, relying on the database itself to automatically complete the rollback better.

Original: http://www.cnblogs.com/hustcat/archive/2012/11/18/2775487.html

1.innodb_rollback_on_timeout Variable

The following is a description of the MySQL official manual that closes the innodb_rollback_on_timeout variable:

In MySQL 5.0.13 and up, InnoDB rolls back is the last statement on a transaction timeout by default. If--innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire Transa Ction (the same behavior as before MySQL 5.0.13). This variable is added in MySQL 5.0.32.

The default value for this variable is OFF, and if the transaction is Gartha, the action performed on the previous statement is rolled back. If set on, the entire transaction is rolled back.

The following is an example to verify the above paragraph.

2. Example

(1) innodb_rollback_on_timeout is OFF

Session 1	Session 2
Mysql> CREATE TABLE TT (c1 int primary key, C2 int) Engine=innodb; Query OK, 0 rows affected (0.01 sec) Mysql> INSERT INTO TT values (1, 1); Query OK, 1 row Affected (0.00 sec) Mysql> begin; Query OK, 0 rows Affected (0.00 sec) Mysql> SELECT * from the TT where C1=1 lock in share mode; +----+------+ | C1 | C2 | +----+------+ |    1 | 1 | +----+------+ 1 row in Set (0.00 sec)
	Mysql> begin; Query OK, 0 rows Affected (0.00 sec) Mysql> INSERT INTO TT values (10,10); Query OK, 1 row Affected (0.00 sec) Mysql> Delete from TT where c1=1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction Mysql> select * from TT; +----+------+ | C1 | C2 | +----+------+ |    1 | 1 | |   10 | 10 | +----+------+ 2 rows in Set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) Mysql> select * from TT; +----+------+ | C1 | C2 | +----+------+ |    1 | 1 | +----+------+ 1 row in Set (0.00 sec)
Mysql> select * from TT; +----+------+ | C1 | C2 | +----+------+ |    1 | 1 | +----+------+ 1 row in Set (0.00 sec)
	Mysql> begin; Query OK, 0 rows Affected (0.00 sec) Mysql> INSERT INTO TT values (10,10); Query OK, 1 row Affected (0.00 sec) Mysql> Delete from TT where c1=1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction Mysql> commit; Query OK, 0 rows affected (0.02 sec) Mysql> select * from TT; +----+------+ | C1 | C2 | +----+------+ |    1 | 1 | |   10 | 10 | +----+------+ 2 rows in Set (0.00 sec)

Session2 the transaction is rolled back to the previous statement because of Gartha.

(2) innodb_rollback_on_timeout is on

Session 1	Session 2
Mysql> begin; Query OK, 0 rows Affected (0.00 sec) Mysql> SELECT * from the TT where C1=1 lock in share mode; +----+------+ | C1 | C2 | +----+------+ |    1 | 1 | +----+------+ 1 row in Set (0.00 sec)
	Mysql> begin; Query OK, 0 rows Affected (0.00 sec) Mysql> INSERT INTO TT values (11,11); Query OK, 1 row Affected (0.00 sec) Mysql> Delete from TT where c1=1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction Mysql> select * from TT; +----+------+ | C1 | C2 | +----+------+ |    1 | 1 | |   10 | 10 | +----+------+ 2 rows in Set (0.00 sec) Mysql> commit; Query OK, 0 rows Affected (0.00 sec) Mysql> select * from TT; +----+------+ | C1 | C2 | +----+------+ |    1 | 1 | |   10 | 10 | +----+------+ 2 rows in Set (0.00 sec)

Session2 Gartha, the entire transaction is rolled back.

3. Summary

innodb_rollback_on_timeout is OFF, the transaction is rolled back to the previous savepoint, andInnoDB creates a savepoint before executing each SQL statement. See Code:

Int Row_insert_for_mysql ( /* Out:error code or Db_success */ byte* Mysql_rec,/* in:row in the MySQL format */ row_prebuilt_t* prebuilt)/* in:prebuilt struct in MySQL HANDLE * * { 。。。 Savept = Trx_savept_take (TRX); 。。。

If a transaction is Gartha, it is equivalent to rolling back to the previous statement. However, after the error, the transaction has not been completed, the user can choose to continue to commit, or rollback the previous operation, the user chooses whether to further commit or rollback the transaction.

innodb_rollback_on_timeout is on, the entire transaction is rolled back. This can be verified from the Row_mysql_handle_errors function.

Ibool
Row_mysql_handle_errors (
/*====================*/
/* Out:true If it was a lock wait and
We should continue running the query thread */
ulint* new_err,/* out:possible new error encountered in
Lock wait, or if no new error, the value
of trx->error_state at the entry of this
function */
trx_t* TRX,/* in:transaction */
que_thr_t* THR,/* in:query thread */
trx_savept_t* savept)/* In:savepoint or NULL */
{
...
else if (err = = Db_deadlock//deadlock occurs
|| Err = = Db_lock_table_full
|| (Err = = Db_lock_wait_timeout
&& row_rollback_on_timeout)) {
/* Roll back the whole transaction; This resolution was added
To version 3.23.43 */

Trx_general_rollback_for_mysql (TRX, FALSE, NULL); All transactions rolled back

} else if (err = = Db_out_of_file_space
|| Err = = Db_lock_wait_timeout) {

Ut_ad (! ( Err = = Db_lock_wait_timeout
&& row_rollback_on_timeout));

if (savept) {//Roll back to previous save point
/* Roll back the latest, possibly incomplete
Insertion or UPDATE */

Trx_general_rollback_for_mysql (TRX, TRUE, savept);
}
/* MySQL would roll back the latest SQL statement */
...

Question:innodb_rollback_on_timeout is off, is the atomicity of the transaction destroyed?

A: No, as you can see from the example, the transaction simply rolls back the state of the previous statement, and the entire transaction is not actually complete (commit or rollback), and as the application detects the error, it should either commit or roll back the transaction. If the atomicity of a transaction is strictly required, of course, the rollback is executed and the transaction is rolled back.

MySQL InnoDB Lock timeout rollback mechanism (RPM)