ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

错误的处理：

1、innodb引擎

超时由innodb_lock_wait_timeout参数控制，该参数在文件my.cnf

Command-Line Format	--innodb_lock_wait_timeout=#
Option-File 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
	Range	1 .. 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 alsoSection 14.3.19.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 insideInnoDB 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 SESSIONstatement. 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.

2、ndbcluster引擎
超时由   TransactionDeadlockDetectionTimeout参数控制，注意该参数在管理节点的config.ini文件的[ndbd default] or [ndbd]定义

Effective Version	Type/Units	Default	Range/Values
NDB 7.3.0	milliseconds	1200	50 - 4G
Restart Type: N (See Section 17.3.3.1, “MySQL Cluster Data Node Configuration Parameters”, for information about restart types.)

When a node executes a query involving a transaction, the node waits for the other nodes in the cluster to respond before continuing. A failure to respond can occur for any of the following reasons:

• The node is “dead”

• The operation has entered a lock queue

• The node requested to perform the action could be heavily overloaded.

This timeout parameter states how long the transaction coordinator waits for query execution by another node before aborting the transaction, and is important for both node failure handling and deadlock detection.

The default timeout value is 1200 milliseconds (1.2 seconds).

The minimum for this parameter is 50 milliseconds.

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

转载于:http://blog.itpub.net/195110/viewspace-1080898/