1. Overview
In this tutorial, we’ll talk about the “Lock wait timeout exceeded” error in MySQL. We’ll discuss what causes this error and some nuances regarding MySQL locks.
For the sake of simplicity, we’ll focus on MySQL’s InnoDB engine, as it’s one of the most popular ones. However, we can use the same tests used here to check the behavior of other engines.
2. Locking in MySQL
A lock is a special object that controls access to a resource. In the case of MySQL, these resources can be tables, rows, or internal data structures.
Another concept to get used to is the lock mode. The lock mode “S” (shared) allows a transaction to read a row. Multiple transactions can acquire the lock of a particular row at the same time.
An “X” (exclusive) lock allows a single transaction to acquire it. The transaction can update or delete the row, while the other has to wait until the lock is released so they can acquire it.
MySQL also has intention locks. These are related to tables and indicate the kind of locks a transaction intends to acquire on rows in the table.
Locking is crucial to guarantee consistency and reliability in highly-concurrent environments. However, when optimizing for performance, some trade-off has to be made, and in those cases, it’s essential to choose the correct isolation level.
3. Isolation Level
MySQL InnoDB offers four transaction isolation levels. They provide different levels of balance between performance, consistency, reliability, and reproducibility. They are, respectively, from the least strict to the most:
- READ UNCOMMITTED**:** in short, all transactions can read all the changes made by others even if they were not committed
- READ COMMITTED: only committed changes are visible to other transactions
- REPEATABLE READ: the first query defines a snapshot, and it becomes the baseline for that row. Even if another transaction changes the row right after the read, the baseline will always be returned if there are no changes after the first query
- SERIALIZABLE: behaves exactly like the previous one except that if autocommit is disabled, it locks the row during any update or delete, and reads are only allowed after commit
Now that we understand how the different isolation levels work, let’s run some tests to examine locking scenarios. First, in order to keep it short, we’ll run all testing in the default isolation level REPEATABLE READ. However, later we can run the tests for all the other levels.
4. Monitoring
The tools we’ll see here don’t apply for production use necessarily. Instead, they’ll allow us to understand what’s happening under the hood.
The commands will describe how MySQL deals with the transaction and which locks relate to which transactions or how to acquire more data from such transactions. So again, these tools will help us during our tests but may not be applicable in a production environment, or at least not when the error has already occurred.
4.1. InnoDB Status
The command SHOW ENGINE INNODB STATUS shows us lots of information about internal structures, objects, and metrics. The output may be truncated depending on the number of available and active connections. However, we’ll only need to look at the transactions section for our use case.
In the transactions section, we’ll find things like:
- number of active transactions
- the status of each transaction
- number of tables involved in each transaction
- number of locks acquired by the transaction
- possibly the statement executed that may be holding the transaction
- information about lock wait
There is much more to see there, but this will be enough for us now.
4.2. Process List
The command SHOW PROCESSLIST presents a table with the session currently opened, and the table displays the following information:
- session id
- user name
- host connected
- database
- command/current active statement type
- running time
- state of the connection
- session description
This command lets us get an overview of the different active sessions, their state, and their activity.
4.3. Select Statement
MySQL exposes some useful information through some tables, and we can use them to understand the kinds of locks strategies applied in a given scenario. They also hold things like the id of the current transaction.
For the purpose of this article we’ll use tables information_schema.innodb_trx and performance_schema.data_locks.
5. Testing Setup
To run our tests, we’ll use a docker image of MySQL to create our database and populate our test schema so that we can exercise some transaction scenarios:
# Create MySQL container
docker run --network host --name example_db -e MYSQL_ROOT_PASSWORD=root -d mysql
Once we have our database server, we can create the schema by connecting to it and executing the scripts:
# Logging in MySQL
docker exec -it example_db mysql -uroot -p
Then, after typing the password, let’s create the database and insert some data:
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE zipcode (
code varchar(100) not null,
city varchar(100) not null,
country varchar(3) not null,
PRIMARY KEY (code)
);
INSERT INTO zipcode(code, city, country)
VALUES ('08025', 'Barcelona', 'ESP'),
('10583', 'New York', 'USA'),
('11075-430', 'Santos', 'BRA'),
('SW6', 'London', 'GBR');
6. Testing Scenarios
The most important thing to remember is that the “Lock wait timeout exceeded” error happens when a transaction is waiting for a lock acquired by another.
The amount of time the transaction will wait depends on the value in the property innodb_lock_wait_timeout defined at the global or session level.
The possibility of facing this error depends on the complexity and the number of transactions per second. However, we’ll try to reproduce some common scenarios.
Another point that may be worth mentioning is that a simple retry strategy can solve the problem caused by this error.
To help us during our tests, we’ll run the following command for all sessions we open:
USE example_db;
-- Set our timeout to 10 seconds
SET @@SESSION.innodb_lock_wait_timeout = 10;
This defines the lock wait timeout to 10 seconds, preventing us from waiting too long to see the error.
6.1. Row Lock
As row locks are acquired in different situations, let’s try to reproduce a sample.
First, we’ll connect to the server from two different sessions using the logging-in MySQL script we saw earlier. After that, let’s run the statement below in both sessions:
SET autocommit=0;
UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
After 10 seconds, the second session will fail:
mysql> UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
The error happens because the first session starts a transaction due to the disabling of the autocommit. Next, once the UPDATE statement runs within the transaction, the exclusive lock of that row is acquired. However, no commit is executed, leaving the transaction open and causing the other transaction to keep waiting. As the commit never happens, the timeout of the lock wait reaches the limit. This also applies to DELETE statements.
6.2. Checking Row Lock in Data Locks Table
Now, let’s rollback in both sessions and run the script as before in the first session, but this time, in the second session, let’s run the following statements:
SET autocommit=0;
UPDATE zipcode SET code = 'Test' WHERE code = '08025';
As we can observe, both statements execute successfully because they no longer require the lock of the same row.
To confirm that, we’ll run the following statement in any of the sessions or in a new one:
SELECT * FROM performance_schema.data_locks;
The statement above returns four rows, two of them being table intent locks that specify that a transaction may intend to lock a row in the table and the other two being record locks. Looking at the columns LOCK_TYPE, LOCK_MODE and LOCK_DATA, we can confirm the locks we just described:
Running rollback in both sessions and the query again, the result is an empty dataset.
6.3. Row Lock and Indexes
This time let’s use a different column in our WHERE clause. For the first session, we’ll run:
SET autocommit=0;
UPDATE zipcode SET city = 'SW6 1AA' WHERE country = 'USA';
While in the second one, let’s run these statements:
SET autocommit=0;
UPDATE zipcode SET city = '11025-030' WHERE country = 'BRA';
Something unexpected just happened. Even though the statements target two different rows, we have got the lock timeout error. Ok, if we repeat this same test right after running the SELECT statement on the table performance_schema.data_locks, we’ll see that actually, the first session locked all the rows, and the second session is waiting.
The problem is related to how MySQL executes the query to find the candidates for the update because the column used in the WHERE clause has no index. MySQL has to scan all the rows to find the ones that match the WHERE condition, which also causes these rows to be locked.
It’s important to be sure that our statements are optimal.
6.4. Row Lock and Updates/Deletes With Multiple Tables
Other common cases for the lock timeout error are DELETE and UPDATE statements involving multiple tables. The number of locked rows depends on the statement execution plan, but we should keep in mind that all the tables involved may have some rows locked.
As an example, let’s rollback all the other transactions and execute these statements:
CREATE TABLE zipcode_backup SELECT * FROM zipcode;
SET autocommit=0;
DELETE FROM zipcode_backup WHERE code IN (SELECT code FROM zipcode);
Here, we created a table and started a transaction that reads from the zipcode table and writes to the zipcode_backup table in a single statement.
The next step is to run the following statement in the second session:
SET autocommit=0;
UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
Once again, transaction two timed out as the first one had acquired the lock of the rows in the table. Let’s just run the SELECT statement in the data_lock table to demonstrate what happened. Then, let’s rollback both sessions.
6.5. Row Lock When Filling Temp Tables
In this example, let’s mix DDL and DMLs executing in the first session of the new script:
CREATE TEMPORARY TABLE temp_zipcode SELECT * FROM zipcode;
Then if we repeat the statement we used before in the second session, we’ll be able to see the lock error once again.
6.6. Shared and Exclusive Lock
Let’s not forget to rollback both session transactions at the end of each test.
We already discussed shared and exclusive locks. However, we didn’t see how to define them explicitly using the LOCK IN SHARE MODE and FOR UPDATE options. First, let’s use the shared mode:
SET autocommit=0;
SELECT * FROM zipcode WHERE code = 'SW6' LOCK IN SHARE MODE;
Now, we’ll run the same update as we did previously, and the result is again the timeout. Besides that, we should remember that reads are allowed here.
As opposed to the SHARE MODE, the FOR UPDATE doesn’t allow read locks, as shown next when we run a statement in the first session:
SET autocommit=0;
SELECT * FROM zipcode WHERE code = 'SW6' FOR UPDATE;
And then, we run the same SELECT statement with the SHARE MODE option used before in the first session, but now in the second one, and we’ll observe once more the timeout error. To recap, the SHARE MODE lock can be acquired for more than one session, and it locks writes. The exclusive lock or FOR UPDATE option allows reads but not lock reads or writes.
6.7. Table Locks
Table lock doesn’t have a timeout and is not recommended for InnoDB:
LOCK TABLE zipcode WRITE;
Once we run this, we can open another session, try a select or an update, and check that it will be locked, but this time, no timeout happens. Going a bit further, we can open a third session and run:
SHOW PROCESSLIST;
It displays the active sessions with their state, and we’ll see the first session sleeping and the second one waiting for the metadata lock of the table. The solution, in this case, would be running the next command:
UNLOCK TABLES;
Other scenarios where we may find sessions waiting to acquire some metadata lock are during the execution of DDL, like ALTER TABLEs.
6.8. Gap Locks
Gap locks happen when a particular interval of indexed records is locked, and another session tries to perform some operation within this interval. In this case, even inserts can be impacted.
Let’s consider the following statement executed in the first session:
CREATE TABLE address_type ( id bigint(20) not null, name varchar(255) not null, PRIMARY KEY (id) );
SET autocommit=0;
INSERT INTO address_type(id, name) VALUES (1, 'Street'), (2, 'Avenue'), (5, 'Square');
COMMIT;
SET autocommit=0;
SELECT * FROM address_type WHERE id BETWEEN 1 and 5 LOCK IN SHARE MODE;
In the second session, we’ll run the following statement:
SET autocommit=0;
INSERT INTO address_type(id, name) VALUES (3, 'Road'), (4, 'Park');
After we run the data lock, we select the statement in a third session so we can check the new LOCK MODE value, GAP. This can also be applied for UPDATE and DELETE statements.
6.9. Deadlocks
By default, MySQL tries to identify deadlocks, and in case it manages to solve the graph of dependencies between the transactions, it automatically kills one of the tasks in order to allow the others to go through. Otherwise, we get a lock timeout error, as we saw before.
Let’s simulate a simple deadlock scenario. For the first session, we execute:
SET autocommit=0;
SELECT * FROM address_type WHERE id = 1 FOR UPDATE;
SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id();
The last SELECT statement will give us the current transaction ID. We’ll need it to check the logs later. Then, for the second session, let’s run:
SET autocommit=0;
SELECT * FROM address_type WHERE id = 2 FOR UPDATE;
SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id();
SELECT * FROM address_type WHERE id = 1 FOR UPDATE;
In the sequence, we go back to session one and run:
SELECT * FROM address_type WHERE id = 2 FOR UPDATE;
Immediately, we’ll get an error:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
And last, we go to a third session, and we run:
SHOW ENGINE INNODB STATUS;
The output of the command should be similar to this:
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 4036, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 9, OS thread handle 139794615064320, query id 252...
SELECT * FROM address_type WHERE id = 1 FOR UPDATE
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4036 lock_mode X locks rec but not gap
Record lock
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4036 lock_mode X locks rec but not gap waiting
Record lock
...
*** (2) TRANSACTION:
TRANSACTION 4035, ACTIVE 59 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), ... , 2 row lock(s)
MySQL thread id 11, .. query id 253 ...
SELECT * FROM address_type WHERE id = 2 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4035 lock_mode X locks rec but not gap
Record lock
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4035 lock_mode X locks rec but not gap waiting
Record lock
...
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 4037
...
LIST OF TRANSACTIONS FOR EACH SESSION:
...
---TRANSACTION 4036, ACTIVE 18 sec
3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 9, ... , query id 252 ...
Using the transaction ids we got before, we can find a lot of useful information, such as the state of the connection at the moment of the error, the number of row locks, the last command executed, the description of holding locks, and the description of the locks the transaction was waiting for. After that, it repeats the same for the other transactions involved in the deadlock. Also, in the end, we find the information about which transactions were rolled back.
7. Conclusion
In this article, we looked at locks in MySQL, how they work, and when they cause the “Lock wait timeout exceeded” error.
We defined test scenarios that allowed us to reproduce this error and check the internal nuances of the database server when handling transactions.