038 Lock wait timeout exceeded;try restarting transaction

Scenario: There are two sessions where session 1 is in a transactional operation, session 2 waits for this transaction to complete, and then there will be an error generated.

By querying the information, please tidy up here.

One: Summarize the role of the timeout parameter

1. Operation

　　

2. Specific explanations

　　 1) connect_timeoutThe number of seconds the MYSQLD server waits for a connect packet before responding with bad handshake increasing t He connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at　　' XXX ', System Error:errno. Translation: The time (in seconds) that the MySQL service waits for a connection packet, which will throw a ' bad handshake ' response over this time. If the client often encounters a form such as: Lost connection to MySQL server at ' XXX ', System error:errno error, then adding Connect_timeout value can be helpful; Next, MySQL handles client requests Process: The basic principle of MySQL should be that there is a listener thread that loops through the request, and when a request comes in, the thread is created (or taken from the thread pool) to handle the request. Since the MySQL connection uses the TCP protocol, the TCP three handshake is required before. After the TCP three handshake succeeds, the client enters the block and waits for a message from the server. The server will create a thread at this time (or take a thread from the thread pool) to process the request, and the primary validation section includes host and username password validation. Host authentication is familiar to us because you have specified host when authorizing a user with the grant command. The user name password authentication is the server terminal to send to the client a random number, the client with the random number and password after multiple encryption sent to the server authentication. If passed, the entire connection handshake process is complete2) Delayed_insert_timeoutThe number of seconds the insert delay operation is delayed, not the insert operation, but insert delayed, which is deferred. This parameter will be abandoned again later, do not pay attention!3) Innodb_flush_log_at_timeoutThis is 5.6 in the appearance, is the InnoDB unique parameters, log refresh interval, the default is 1 seconds4) Innodb_lock_wait_timeoutInnoDB the maximum time (in seconds) that a transaction waits for a row-level lock, which is discarded over this time. The default value is 50 seconds5) Innodb_rollback_on_timeoutIn InnoDB, if this parameter is on, the transaction is rolled back when the last statement in the transaction times out, the default is off, and the state is off.6) interactive_timeout (interactive) and wait_timeout (non-interactive)The time-out settings for interactive and non-interactive links prevent clients from linking the database for long periods of time, and nothing is done in a sleep state, forcing the long sleep link to close. Or look at the official documents, from the document to see Wait_timeout and interactive_timeout refers to the inactive connection time-out, when the connection thread starts Wait_timeout is set to one of these two values according to whether it is interactive or non-interactive mode. If we run the mysql-uroot-p command, the login to Mysql,wait_timeout will be set to the value of interactive_timeout. If we do not do anything within the wait_timeout time, we will be prompted to time out again, which requires the MySQL client to reconnect. By default, both values are 28800 (8h), and the two value is normally set to 1000s.7) Lock_wait_timeoutGets the time-out period for metadata locks. This is suitable for all tables except the system tables (MySQL library).
Different fromInnodb_lock_wait_timeout is the wait time for row-level locks on DML operations, while Lock_wait_timeout is the lock waiting time for data structure DDL operations 　　 8)Net_read_timeoutNet_write_timeoutBoth represent the time-out for the database to send the network packets and accept the network packets.9) rpl_stop_slave_timeout (default value, no modification required)In 5.6.13 start and later versions, this parameter can be used to control the execution time of the stop slave, when the replay of a large transaction, the abrupt execution of the stop slave, the command stop slave will be executed long (time set too short will be out), This can cause deadlocks or blocking, severely impacting performance, default and maximum values of 31.536 million seconds (one year)) Slave_net_timeoutThe number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the R EAD, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the Master_connect_retry option for the change MASTER to statement, and the Number of reconnection attempts is limited by the--master-retry-count option.　　The default is 3600 seconds (one hour). Translation: The time from the library to wait for more data from the main library exceeds this time (the default is 3600), slave thinks the connection is problematic, and terminates the change to read the main library, attempting to reconnect to the main library. Wait for the first time after the timeout to attempt the new connection, after which the reconnection interval is controlled by specifying the value of master_connect_retry= at Change master, and the number of reconnection is from change master to Specifies the value of the master-retry-count= control.Two: Solve1. Interpretation of this questionWhen the InnoDB engine reports this error after the lock wait time-out, the statement that waits too long is rolled back (not the entire transaction). If you want the SQL statement to wait longer after other transactions are completed, you can increase the value of the parameter innodb_lock_wait_timeout configuration.　　If there are too many long-running locked transactions, you can reduce the value of this innodb_lock_wait_timeout, and in a particularly busy system, you can reduce concurrency. InnoDB the maximum time (in seconds) that a transaction waits for a row-level lock, which is discarded over this time. The default value is 50 seconds. A transaction a tries to access a row of data, but the row of data is locked by another InnoDB transaction B, and transaction a waits for transaction B to release the lock, waiting for a value that exceeds the Innodb_lock_wait_timeout setting to error 1205 (HY000)2. Theoretical valuesInnodb_lock_wait_timeout is a dynamic parameter, the default value is 50 seconds, the minimum value is 1 seconds, the maximum value is 1073741824;3.setinnodb_lock_wait_timeout=1500; and set global innodb_lock_wait_timeout=1500; the difference. The former is equivalent to the set session only affects the current session, the latter as a global modification, will only affect the session opened after the modification, note that the latter can not change the current session;4. Adjust the large parameters 　　

5. Re-experiment

Longer after the experiment, there is no problem now.

There is no problem with the lock table, because this is just a simple experiment and it will not happen.

038 Lock wait timeout exceeded;try restarting transaction