2.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout
还是先祭出官方文档，从文档中看，这个值是针对innodb引擎的，是innodb中行锁的等待超时时间，默认为50秒。如果超时，则当前语句会回滚。如果设置了innodb_rollback_on_timeout，则会回滚整个事务，否则，只回滚事务等待行锁的这个语句。
The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
同样来测试下(先创建一个innodb引擎的表test，只有一列，列名为a)：
mysql> CREATE TABLE `test` ( `a` int primary key) engine=innodb;
首先插入三条测试数据
mysql> select * from test; +---+ | a | +---+ | 1 | | 2 | | 3 |
当前innodb_rollback_on_timeout=OFF，设置innodb_lock_wait_timeout=1，我们开启两个事务
##事务1 加行锁 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where a=2 for update; +---+ | a | +---+ | 2 | +---+ 1 row in set (0.01 sec)
##事务2，请求行锁 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from test where a=1; Query OK, 1 row affected (0.00 sec) mysql> delete from test where a=2; ##请求行锁超时 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from test; +---+ | a | +---+ | 2 | | 3 | +---+ 2 rows in set (0.00 sec) mysql> begin; ##这里我们直接开启另外的事务，则原来的事务只会回滚第二条语句，最终结果就是test表中只剩下2和3.如果这里我们显示的rollback，则会回滚整个事务，保持1，2，3不变。
那么如果innodb_rollback_on_timeout=ON,同样事务2会超时，但是这个时候如果我们begin开启新的事务，那么会回滚请求锁超时的整个事务，而不是像前面那样只回滚了超时的那条语句。
2.4 lock_wait_timeout
文档中描述如下，简单说来lock_wait_timeout是元数据锁等待超时，任意锁元数据的语句都会用到这个超时参数，默认为一年。元数据锁可以参加mysql metadata lock，为了保证事务可串行化，不管是myisam还是innodb引擎的表，只要是开始一个事务，就会获取操作表的元数据锁，这时候如果另一个事务要对表的元数据进行修改，则会阻塞直到超时。
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000. This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements
测试例子：我们用一个myisam引擎的表myisam_test来测试。其中有一条记录(1,1)，现在我们先开启一个事务，然后执行一个select语句。另外打开一个session，然后执行表的元数据操作，如删除表，会发现操作阻塞直到lock_wait_timeout秒后提示超时。
##第一个session，获取metadata lock mysql> show create table myisam_test; -----------------------------------------------------------+ | Table | Create Table | +----------------------------------------------------------- | myisam_test | CREATE TABLE `myisam_test` ( `i` int(11) NOT NULL, `j` int(11) DEFAULT NULL, PRIMARY KEY (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> start transactio