Original The effect of MySQL innodb_rollback_on_timeout parameters on locks

Environment: Server Version:5.6.21-log MySQL Community Server (GPL)

Prerequisite Summary:

What is the role of innodb_rollback_on_timeout? A: Transaction B is a rollback of all statement or the last statement within a transaction after a lock wait time-out;      0 means rollback the last statement, the default value; a bit pit      1 indicates that all the statements in transaction B are rolled back;
This parameter is read-only and needs to be configured in MY.CNF, and the restart takes effect; Note: No automatic commit or ROLLBACK TRANSACTION after rollback of statements; pit

Table structure:

Mysql>ShowCreate Tablet12\g;*************************** 1. Row***************************       Table: T12Create Table:CREATE TABLE' T12 ' (' A 'int(Ten) unsigned not NULLauto_increment, ' B 'varchar(766)DEFAULT NULL, ' C 'int( One)DEFAULT NULL,  PRIMARY KEY(' A '),KEY' B ' (' B ')) ENGINE=InnoDB auto_increment= - DEFAULTCHARSET=latin11Rowinch Set(0.00Sec

Experiment One:

parameter configuration: MySQL>Show variables like 'Innodb_rollback_on_timeout';+----------------------------+-------+|Variable_name|Value|+----------------------------+-------+|Innodb_rollback_on_timeout|  on    |+----------------------------+-------+1Rowinch Set(0.00sec) MySQL>Show variables like 'tx_iso%';+---------------+-----------------+|Variable_name|Value|+---------------+-----------------+|Tx_isolation| Repeatable-READ |+---------------+-----------------+1Rowinch Set(0.00sec) MySQL>Show variables like 'autocommit';+---------------+-------+|Variable_name|Value|+---------------+-------+|Autocommit|  on    |+---------------+-------+1Rowinch Set(0.00Sec

Process:

Experiment Two:

parameter configuration: MySQL>Show variables like 'Innodb_rollback_on_timeout';+----------------------------+-------+|Variable_name|Value|+----------------------------+-------+|Innodb_rollback_on_timeout| OFF   |+----------------------------+-------+1Rowinch Set(0.00sec) MySQL>Show variables like 'tx_iso%';+---------------+-----------------+|Variable_name|Value|+---------------+-----------------+|Tx_isolation| Repeatable-READ |+---------------+-----------------+1Rowinch Set(0.00sec) MySQL>Show variables like 'autocommit';+---------------+-------+|Variable_name|Value|+---------------+-------+|Autocommit|  on    |+---------------+-------+1Rowinch Set(0.00Sec

Process:

Summarize:

1, after closing the innodb_rollback_on_timeout, once the Begin;start transaction, and so on, when a transaction is opened, when the lock wait time-out, the transaction requests the lock will not be freed until the transaction commits or rolls back or the session times out;

Therefore, the autocommit parameter is recommended to be set to on, as long as the program does not display the open transaction, you can avoid the above lock does not release the problem.

2. After the innodb_rollback_on_timeout is turned on, once the lock waits timeout, the SQL in the transaction will roll back all and release the lock requested before.

3, when Autocommit=on, as long as not show open transactions, there will be no above 2 problems, that is, the problem of lock and rollback.

Reprint please remark: http://www.cnblogs.com/su-han/p/6204016.html

[Original]mysql innodb_rollback_on_timeout parameter effect on lock