锁:innodb_lock_wait_timeout和 innodb_rollback_on_timeout?

innodb_lock_wait_timeout 超时后只回滚一条sql，整个事务并不回滚，如需要事务回滚，则需要设置 innodb_rollback_on_timeout



http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

innodb_lock_wait_timeout

Command-Line Format	--innodb_lock_wait_timeout=#
System Variable	Name	innodb_lock_wait_timeout
	Variable Scope	Global, Session
	Dynamic Variable	Yes
Permitted Values	Type	numeric
	Default	50
	Min Value	1
	Max Value	1073741824

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

When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction). To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option. See also Section 14.21.4, “InnoDB Error Handling”.

You might decrease this value for highly interactive applications or OLTP systems, to display user feedback quickly or put the update into a queue for processing later. You might increase this value for long-running back-end operations, such as a transform step in a data warehouse that waits for other large insert or update operations to finish.

innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.

The lock wait timeout value does not apply to deadlocks, because InnoDB detects them immediately and rolls back one of the deadlocked transactions.

As of MySQL 5.5, innodb_lock_wait_timeout can be set at runtime with the SET GLOBAL or SET SESSION statement. Changing the GLOBAL setting requires the SUPER privilege and affects the operation of all clients that subsequently connect. Any client can change the SESSION setting for innodb_lock_wait_timeout, which affects only that client.         

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout

innodb_rollback_on_timeout

Command-Line Format	--innodb_rollback_on_timeout
System Variable	Name	innodb_rollback_on_timeout
	Variable Scope	Global
	Dynamic Variable	No
Permitted Values	Type	boolean
	Default	OFF

In MySQL 5.5, InnoDB rolls back only 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 transaction (the same behavior as in MySQL 4.1).         

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26250550/viewspace-1423334/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26250550/viewspace-1423334/