MySQL Common errors

Lock wait timeout exceeded; Try restarting transaction some information

1.

Lock wait timed out. is caused by the current transaction waiting for other transactions to release the lock resource. You can find out the tables and statements of the lock resource competition, optimize your SQL, create indexes, etc., and if not, you can reduce the number of concurrent threads appropriately.

2.

Your transaction timed out waiting for a lock on a table, and it is estimated that the table is being locked by another process and has not been released.

can use SHOW INNODB status/g; Look at the lock situation.

3. Search and solve the problem

On the Management node [NDBD default]

Area Plus:

transactiondeadlockdetectiontimeout=10000 (set to 10 seconds) is 1200 (1.2 seconds) by default

4.

The InnoDB automatically detects deadlocks for rollback, or terminates deadlock situations.

Reference

InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock.

InnoDB tries to pick small transactions to roll back, where the size of a transaction are determined by the number of rows Inserted, updated, or deleted.

If the parameter is Innodb_table_locks=1 and autocommit=0, InnoDB will be aware of the deadlock on the table and the row-level lock at the MySQL level. In addition, InnoDB does not detect the MySQL lock tables command and other storage engine deadlocks.

You should set Innodb_lock_wait_timeout to resolve this situation.

Innodb_lock_wait_timeout is the time-out for InnoDB to discard row-level locks.

MySQL Common errors