Data Area Timed Out Waiting for a Repository Read Lock Please Try Again

One of the nigh popular InnoDB'south errors is InnoDB lock wait timeout exceeded, for example:

          SQLSTATE[HY000]: Full general error: 1205 Lock await timeout exceeded; try restarting transaction        

The to a higher place simply means the transaction has reached the innodb_lock_wait_timeout while waiting to obtain an exclusive lock which defaults to l seconds. The common causes are:

1. The offensive transaction is not fast plenty to commit or rollback the transaction inside innodb_lock_wait_timeout duration.
2. The offensive transaction is waiting for row lock to be released by some other transaction.

The Effects of a InnoDB Lock Await Timeout

InnoDB lock wait timeout tin cause ii major implications:

• The failed statement is not being rolled back past default.
• Even if innodb_rollback_on_timeout is enabled, when a argument fails in a transaction, ROLLBACK is nonetheless a more than expensive operation than COMMIT.

Let'southward play around with a elementary instance to better understand the effect. Consider the following 2 tables in database mydb:

          mysql> CREATE SCHEMA mydb; mysql> USE mydb;        

The kickoff table (table1):

          mysql> CREATE Tabular array table1 ( id INT PRIMARY Central AUTO_INCREMENT, data VARCHAR(50)); mysql> INSERT INTO table1 Set data = 'information #i';        

The second tabular array (table2):

          mysql> CREATE TABLE table2 LIKE table1; mysql> INSERT INTO table2 SET data = 'information #2';        

Nosotros executed our transactions in 2 different sessions in the following order:

Ordering	Transaction #1 (T1)	Transaction #2 (T2)
1	SELECT * FROM table1; (OK)	SELECT * FROM table1; (OK)
ii	UPDATE table1 SET data = 'T1 is updating the row' WHERE id = one; (OK)
three		UPDATE table2 SET data = 'T2 is updating the row' WHERE id = 1; (OK)
iv		UPDATE table1 Prepare data = 'T2 is updating the row' WHERE id = 1; (Hangs for a while and eventually returns an error "Lock look timeout exceeded; try restarting transaction")
five	COMMIT; (OK)
vi		COMMIT; (OK)

Yet, the cease upshot after step #vi might exist surprising if we did not retry the timed out argument at stride #4:

          mysql> SELECT * FROM table1 WHERE id = ane; +----+-----------------------------------+ | id | data                              | +----+-----------------------------------+ | 1  | T1 is updating the row            | +----+-----------------------------------+    mysql> SELECT * FROM table2 WHERE id = 1; +----+-----------------------------------+ | id | data                              | +----+-----------------------------------+ | one  | T2 is updating the row            | +----+-----------------------------------+        

After T2 was successfully committed, ane would expect to get the same output " T2 is updating the row" for both table1 and table2 but the results testify that only table2 was updated. One might remember that if whatsoever mistake encounters within a transaction, all statements in the transaction would automatically get rolled back, or if a transaction is successfully committed, the whole statements were executed atomically. This is true for deadlock, only not for InnoDB lock await timeout.

Unless you lot set up innodb_rollback_on_timeout=i (default is 0 - disabled), automatic rollback is not going to happen for InnoDB lock wait timeout error. This means, past following the default setting, MySQL is not going to fail and rollback the whole transaction, nor retrying again the timed out statement and just process the next statements until information technology reaches COMMIT or ROLLBACK. This explains why transaction T2 was partially committed!

The InnoDB documentation conspicuously says "InnoDB rolls back only the last statement on a transaction timeout by default". In this case, we do non become the transaction atomicity offered by InnoDB. The atomicity in Acid compliant is either we get all or naught of the transaction, which ways partial transaction is merely unacceptable.

Dealing With a InnoDB Lock Wait Timeout

And so, if you are expecting a transaction to machine-rollback when encounters an InnoDB lock expect error, similarly as what would happen in deadlock, set the post-obit pick in MySQL configuration file:

          innodb_rollback_on_timeout=1        

A MySQL restart is required. When deploying a MySQL-based cluster, ClusterControl will always set innodb_rollback_on_timeout=1 on every node. Without this option, your application has to retry the failed argument, or perform ROLLBACK explicitly to maintain the transaction atomicity.

To verify if the configuration is loaded correctly:

          mysql> Prove GLOBAL VARIABLES Similar 'innodb_rollback_on_timeout'; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | innodb_rollback_on_timeout | ON    | +----------------------------+-------+        

To cheque whether the new configuration works, we tin can runway the com_rollback counter when this mistake happens:

          mysql> SHOW GLOBAL Condition LIKE 'com_rollback'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_rollback  | one     | +---------------+-------+        

Tracking the Blocking Transaction

At that place are several places that we can look to track the blocking transaction or statements. Let's kickoff past looking into InnoDB engine status under TRANSACTIONS section:

          mysql> Show ENGINE INNODB STATUS\G ------------ TRANSACTIONS ------------  ...  ---TRANSACTION 3100, ACTIVE two sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(southward), heap size 1136, 1 row lock(s) MySQL thread id 50, Bone thread handle 139887555282688, query id 360 localhost ::1 root updating update table1 gear up information = 'T2 is updating the row' where id = 1  ------- TRX HAS BEEN WAITING ii SEC FOR THIS LOCK TO BE GRANTED: Tape LOCKS space id 6 page no 4 due north bits 72 index PRIMARY of table `mydb`.`table1` trx id 3100 lock_mode X locks rec only not gap waiting Tape lock, heap no 2 Physical Record: n_fields 4; compact format; info $.25 0  0: len 4; hex 80000001; asc     ;;  1: len vi; hex 000000000c19; asc       ;;  two: len vii; hex 020000011b0151; asc       Q;;  iii: len 22; hex 5431206973207570646174696e672074686520726f77; asc T1 is updating the row;; ------------------  ---TRANSACTION 3097, Active 46 sec 2 lock struct(southward), heap size 1136, ane row lock(s), disengage log entries 1 MySQL thread id 48, Os thread handle 139887556167424, query id 358 localhost ::ane root Trx read view will not see trx with id >= 3097, sees < 3097        

From the in a higher place information, nosotros tin can get an overview of the transactions that are currently active in the server. Transaction 3097 is currently locking a row that needs to be accessed by transaction 3100. However, the in a higher place output does not tell us the bodily query text that could help us figuring out which part of the query/statement/transaction that nosotros demand to investigate further. Past using the blocker MySQL thread ID 48, let'due south meet what we can gather from MySQL processlist:

          mysql> SHOW FULL PROCESSLIST; +----+-----------------+-----------------+--------------------+---------+------+------------------------+-----------------------+ | Id | User            | Host            | db                 | Control | Time | Land                  | Info                  | +----+-----------------+-----------------+--------------------+---------+------+------------------------+-----------------------+ | four  | event_scheduler | localhost       | <naught>             | Daemon  | 5146 | Waiting on empty queue | <null>                | | 10 | root            | localhost:56042 | performance_schema | Query   | 0    | starting               | show full processlist | | 48 | root            | localhost:56118 | mydb               | Sleep   | 145  |                        | <null>                | | 50 | root            | localhost:56122 | mydb               | Slumber   | 113  |                        | <null>                | +----+-----------------+-----------------+--------------------+---------+------+------------------------+-----------------------+        

Thread ID 48 shows the command every bit 'Sleep'. Still, this does not assistance u.s.a. much to know which statements that block the other transaction. This is considering the argument in this transaction has been executed and this open transaction is basically doing nothing at the moment. Nosotros need to dive farther down to see what is going on with this thread.

For MySQL 8.0, the InnoDB lock expect instrumentation is available under data_lock_waits tabular array inside performance_schema database (or innodb_lock_waits table within sys database). If a lock wait event is happening, we should run into something like this:

          mysql> SELECT * FROM performance_schema.data_lock_waits\G ***************************[ 1. row ]*************************** ENGINE                           | INNODB REQUESTING_ENGINE_LOCK_ID        | 139887595270456:6:4:2:139887487554680 REQUESTING_ENGINE_TRANSACTION_ID | 3100 REQUESTING_THREAD_ID             | 89 REQUESTING_EVENT_ID              | eight REQUESTING_OBJECT_INSTANCE_BEGIN | 139887487554680 BLOCKING_ENGINE_LOCK_ID          | 139887595269584:six:4:two:139887487548648 BLOCKING_ENGINE_TRANSACTION_ID   | 3097 BLOCKING_THREAD_ID               | 87 BLOCKING_EVENT_ID                | nine BLOCKING_OBJECT_INSTANCE_BEGIN   | 139887487548648        

Note that in MySQL 5.six and 5.vii, the similar information is stored inside innodb_lock_waits table under information_schema database. Pay attention to the BLOCKING_THREAD_ID value. We can use the this information to look for all statements being executed by this thread in events_statements_history table:

          mysql> SELECT * FROM performance_schema.events_statements_history WHERE `THREAD_ID` = 87; 0 rows in set        

It looks like the thread information is no longer in that location. Nosotros tin verify by checking the minimum and maximum value of the thread_id column in events_statements_history tabular array with the following query:

          mysql> SELECT min(`THREAD_ID`), max(`THREAD_ID`) FROM performance_schema.events_statements_history; +------------------+------------------+ | min(`THREAD_ID`) | max(`THREAD_ID`) | +------------------+------------------+ | 98               | 129              | +------------------+------------------+        

The thread that we were looking for (87) has been truncated from the table. We can confirm this past looking at the size of event_statements_history tabular array:

          mysql> SELECT @@performance_schema_events_statements_history_size; +-----------------------------------------------------+ | @@performance_schema_events_statements_history_size | +-----------------------------------------------------+ | 10                                                  | +-----------------------------------------------------+        

The in a higher place means the events_statements_history tin can just store the last 10 threads. Fortunately, performance_schema has another table to store more rows called events_statements_history_long, which stores similar information but for all threads and information technology can contain style more rows:

          mysql> SELECT @@performance_schema_events_statements_history_long_size; +----------------------------------------------------------+ | @@performance_schema_events_statements_history_long_size | +----------------------------------------------------------+ | 10000                                                    | +----------------------------------------------------------+        

However, y'all volition get an empty result if you lot try to query the events_statements_history_long tabular array for the first time. This is expected because by default, this instrumentation is disabled in MySQL as we can see in the following setup_consumers table:

          mysql> SELECT * FROM performance_schema.setup_consumers; +----------------------------------+---------+ | NAME                             | ENABLED | +----------------------------------+---------+ | events_stages_current            | NO      | | events_stages_history            | NO      | | events_stages_history_long       | NO      | | events_statements_current        | Aye     | | events_statements_history        | YES     | | events_statements_history_long   | NO      | | events_transactions_current      | Yeah     | | events_transactions_history      | YES     | | events_transactions_history_long | NO      | | events_waits_current             | NO      | | events_waits_history             | NO      | | events_waits_history_long        | NO      | | global_instrumentation           | Aye     | | thread_instrumentation           | YES     | | statements_digest                | Aye     | +----------------------------------+---------+        

To activate table events_statements_history_long, we need to update the setup_consumers table as below:

          mysql> UPDATE performance_schema.setup_consumers SET enabled = 'Yep' WHERE name = 'events_statements_history_long';        

Verify if there are rows in the events_statements_history_long tabular array now:

          mysql> SELECT count(`THREAD_ID`) FROM performance_schema.events_statements_history_long; +--------------------+ | count(`THREAD_ID`) | +--------------------+ | 4                  | +--------------------+        

Cool. Now nosotros tin look until the InnoDB lock expect event raises again and when information technology is happening, y'all should see the following row in the data_lock_waits tabular array:

          mysql> SELECT * FROM performance_schema.data_lock_waits\Chiliad ***************************[ i. row ]*************************** ENGINE                           | INNODB REQUESTING_ENGINE_LOCK_ID        | 139887595270456:6:iv:2:139887487555024 REQUESTING_ENGINE_TRANSACTION_ID | 3083 REQUESTING_THREAD_ID             | 60 REQUESTING_EVENT_ID              | 9 REQUESTING_OBJECT_INSTANCE_BEGIN | 139887487555024 BLOCKING_ENGINE_LOCK_ID          | 139887595269584:6:4:2:139887487548648 BLOCKING_ENGINE_TRANSACTION_ID   | 3081 BLOCKING_THREAD_ID               | 57 BLOCKING_EVENT_ID                | 8 BLOCKING_OBJECT_INSTANCE_BEGIN   | 139887487548648        

Once again, we utilize the BLOCKING_THREAD_ID value to filter all statements that have been executed by this thread confronting events_statements_history_long tabular array:

          mysql> SELECT `THREAD_ID`,`EVENT_ID`,`EVENT_NAME`, `CURRENT_SCHEMA`,`SQL_TEXT` FROM events_statements_history_long  WHERE `THREAD_ID` = 57 ORDER Past `EVENT_ID`; +-----------+----------+-----------------------+----------------+----------------------------------------------------------------+ | THREAD_ID | EVENT_ID | EVENT_NAME            | CURRENT_SCHEMA | SQL_TEXT                                                       | +-----------+----------+-----------------------+----------------+----------------------------------------------------------------+ | 57        | one        | statement/sql/select  | <null>         | select connection_id()                                         | | 57        | 2        | statement/sql/select  | <null>         | SELECT @@VERSION                                               | | 57        | 3        | statement/sql/select  | <null>         | SELECT @@VERSION_COMMENT                                       | | 57        | 4        | statement/com/Init DB | <zero>         | <zippo>                                                         | | 57        | five        | argument/sql/brainstorm   | mydb           | begin                                                          | | 57        | seven        | statement/sql/select  | mydb           | select 'T1 is in the firm'                                    | | 57        | 8        | statement/sql/select  | mydb           | select * from table1                                           | | 57        | ix        | statement/sql/select  | mydb           | select 'some more select'                                      | | 57        | 10       | argument/sql/update  | mydb           | update table1 set data = 'T1 is updating the row' where id = i | +-----------+----------+-----------------------+----------------+----------------------------------------------------------------+        

Finally, we found the culprit. We can tell past looking at the sequence of events of thread 57 where the to a higher place transaction (T1) still has not finished notwithstanding (no COMMIT or ROLLBACK), and we can see the very last statement has obtained an exclusive lock to the row for update operation which needed by the other transaction (T2) and just hanging there. That explains why nosotros run into 'Sleep' in the MySQL processlist output.

Every bit we tin can come across, the above SELECT statement requires you to get the thread_id value beforehand. To simplify this query, we can use IN clause and a subquery to join both tables. The following query produces an identical consequence like the above:

          mysql> SELECT `THREAD_ID`,`EVENT_ID`,`EVENT_NAME`, `CURRENT_SCHEMA`,`SQL_TEXT` from events_statements_history_long WHERE `THREAD_ID` IN (SELECT `BLOCKING_THREAD_ID` FROM data_lock_waits) Society Past `EVENT_ID`; +-----------+----------+-----------------------+----------------+----------------------------------------------------------------+ | THREAD_ID | EVENT_ID | EVENT_NAME            | CURRENT_SCHEMA | SQL_TEXT                                                       | +-----------+----------+-----------------------+----------------+----------------------------------------------------------------+ | 57        | 1        | statement/sql/select  | <null>         | select connection_id()                                         | | 57        | 2        | statement/sql/select  | <nil>         | SELECT @@VERSION                                               | | 57        | 3        | statement/sql/select  | <aught>         | SELECT @@VERSION_COMMENT                                       | | 57        | iv        | statement/com/Init DB | <null>         | <goose egg>                                                         | | 57        | 5        | argument/sql/brainstorm   | mydb           | begin                                                          | | 57        | 7        | argument/sql/select  | mydb           | select 'T1 is in the firm'                                    | | 57        | 8        | argument/sql/select  | mydb           | select * from table1                                           | | 57        | 9        | statement/sql/select  | mydb           | select 'some more select'                                      | | 57        | 10       | statement/sql/update  | mydb           | update table1 set up data = 'T1 is updating the row' where id = 1 | +-----------+----------+-----------------------+----------------+----------------------------------------------------------------+        

Nonetheless, it is not practical for us to execute the above query whenever InnoDB lock expect upshot occurs. Apart from the error from the application, how would you know that the lock wait event is happening? We can automate this query execution with the following simple Bash script, called track_lockwait.sh:

          $ cat track_lockwait.sh #!/bin/fustigate ## track_lockwait.sh ## Print out the blocking statements that causing InnoDB lock wait  INTERVAL=5 DIR=/root/lockwait/  [ -d $dir ] || mkdir -p $dir  while true; do   check_query=$(mysql -A -Bse 'SELECT THREAD_ID,EVENT_ID,EVENT_NAME,CURRENT_SCHEMA,SQL_TEXT FROM events_statements_history_long WHERE THREAD_ID IN (SELECT BLOCKING_THREAD_ID FROM data_lock_waits) Lodge BY EVENT_ID')    # if $check_query is not empty   if [[ ! -z $check_query ]]; then     timestamp=$(date +%due south)     echo $check_query > $DIR/innodb_lockwait_report_${timestamp}   fi    sleep $INTERVAL done        

Utilize executable permission and daemonize the script in the background:

          $ chmod 755 track_lockwait.sh $ nohup ./track_lockwait.sh &        

Now, nosotros just need to await for the reports to be generated under the /root/lockwait directory. Depending on the database workload and row access patterns, you lot might probably see a lot of files nether this directory. Monitor the directory closely otherwise it would be flooded with too many written report files.

If you are using ClusterControl, you tin can enable the Transaction Log feature under Functioning -> Transaction Log where ClusterControl will provide a report on deadlocks and long-running transactions which will ease up your life in finding the culprit.

Conclusion

In summary, if we face a "Lock Wait Timeout Exceeded" error in MySQL, we demand to offset empathise the effects that such an error tin have to our infrastructure, so track the offensive transaction and deed on it either with shell scripts like track_lockwait.sh, or database management software like ClusterControl.

If yous decide to get with shell scripts, only behave in mind that they may save yous money but will cost you time, as you'd need to know a thing or 2 about how they work, apply permissions, and possibly brand them run in the background, and if y'all practice get lost in the crush jungle, we tin can assist.

Any you lot decide to implement, make sure to follow united states on Twitter or subscribe to our RSS feed to become more tips on improving the performance of both your software and the databases backing information technology, such as this post covering six common failure scenarios in MySQL.

polandmustence.blogspot.com

Source: https://severalnines.com/database-blog/how-fix-lock-wait-timeout-exceeded-error-mysql

Share this post