Lock wait timeout exceeded; try restarting transaction

Little scum · Posted on 3/13/2019 1:08:36 PM

Lock wait timeout exceeded; The try restarting transaction is the lock waiting timeout. This is caused by the current transaction waiting for other transactions to release the lock resource.

Workaround:

Execute the following command in a mysql query

Login is visible.

In fact, we can use the following three tables to find out the reasons:

innodb_trx ## All transactions that are currently running
innodb_locks ## The currently occurring lock
innodb_lock_waits ## Correspondence of lock waiting

The results are as follows:

Find out the thread number mysql_thread_id with a few other threads with different parameters

Execute the command kill + thread number to solve it.

desc information_schema.innodb_locks;

Field	Type	Null	Key	Default	Remark
lock_id	varchar(81)	NO			Lock ID
lock_trx_id	varchar(18)	NO			The transaction ID that owns the lock
lock_mode	varchar(32)	NO			Lock mode
lock_type	varchar(32)	NO			Lock type
lock_table	varchar(1024)	NO			Locked table
lock_index	varchar(1024)	YES		NULL	Locked indexes
lock_space	bigint(21) unsigned	YES		NULL	The locked tablespace number
lock_page	bigint(21) unsigned	YES		NULL	Locked page number
lock_rec	bigint(21) unsigned	YES		NULL	The record number of the locked
lock_data	varchar(8192)	YES		NULL	Locked data

desc information_schema.innodb_lock_waits

Field	Type	Null	Key	Default	Remark
requesting_trx_id	varchar(18)	NO			The transaction ID of the request lock
requested_lock_id	varchar(81)	NO			The lock ID of the request lock
blocking_trx_id	varchar(18)	NO			The transaction ID that currently owns the lock
blocking_lock_id	varchar(81)	NO			The lock ID of the lock that currently owns the lock

desc information_schema.innodb_trx ;

Field	Type	Null	Key	Default	Extra	Remark
trx_id	varchar(18)	NO				Transaction ID
trx_state	varchar(13)	NO				Transaction status:
trx_started	datetime	NO		0000-00-00 00:00:00		transaction start time;
trx_requested_lock_id	varchar(81)	YES		NULL		innodb_locks.lock_id
trx_wait_started	datetime	YES		NULL		The time at which the transaction starts waiting
trx_weight	bigint(21) unsigned	NO		0		#
trx_mysql_thread_id	bigint(21) unsigned	NO		0		Transaction thread ID
trx_query	varchar(1024)	YES		NULL		Specific SQL statements
trx_operation_state	varchar(64)	YES		NULL		The current operational status of the transaction
trx_tables_in_use	bigint(21) unsigned	NO		0		How many tables are used in a transaction
trx_tables_locked	bigint(21) unsigned	NO		0		How many locks a transaction has
trx_lock_structs	bigint(21) unsigned	NO		0		#
trx_lock_memory_bytes	bigint(21) unsigned	NO		0		Transaction locked memory size (B)
trx_rows_locked	bigint(21) unsigned	NO		0		The number of rows locked by the transaction
trx_rows_modified	bigint(21) unsigned	NO		0		The number of lines for which the transaction was changed
trx_concurrency_tickets	bigint(21) unsigned	NO		0		The number of transactions and invoices
trx_isolation_level	varchar(16)	NO				Transaction isolation level
trx_unique_checks	int(1)	NO		0		Whether it is unique check
trx_foreign_key_checks	int(1)	NO		0		Whether foreign key checks
trx_last_foreign_key_error	varchar(256)	YES		NULL		Foreign key error at the end
trx_adaptive_hash_latched	int(1)	NO		0		#
trx_adaptive_hash_timeout	bigint(21) unsigned	NO		0		#

[Source] Lock wait timeout exceeded; try restarting transaction

Related Posts

Sections viewed