Locking SELECT with CREATE TABLE, INSERT INTO, and User Variables

Locking is an important concept in databases. They help regulate access to the data, so your SELECT queries return consistent results and DML and DDL statements leave the data and schema in a consistent state. For the data, there are four different transaction isolation levels that influence which locks are taken. The most two commonly used isolation levels are REPEATABLE READ (the default in InnoDB) and READ COMMITTED (the default in some other databases). Both of those are said to provide non-locking reads, but there is a little more to it than that.

Selecting into a user variable causing a lock wait timeout.

One case where reads are always locking is when you explicitly requests locks by adding the FOR SHARE or FOR UPDATE modifiers. However there are also cases where SELECT statements becomes locking due to the way the result of the statement is used. Two such cases are CREATE TABLE ... SELECT (INSERT INTO ... SELECT behaves the same way) and assigning the result of the statement to a user variable. Those two cases are the topic of this blog.

CREATE TABLE .. SELECT / INSERT INTO … SELECT

First, you can investigate the locks taken by a CREATE TABLE ... SELECT and INSERT INTO ... SELECT statements. Since these two behaves the same for this example, only the CREATE TABLE version will be shown. The default transaction isolation level of REPEATABLE READ will be used:

Because of the SLEEP(0.01) in the WHERE clause, the statement will take around one minute to execute. The easiest way in MySQL 8.0 to determine which data locks are held by a statement is to use the performance_schema.data_locks table. In this case, the locks on the city table is those of interest, and you get get those like (notice that the thread id found above is used to only get the locks for that one connection):

The number of locks depend on how far into the execution the CREATE TABLE ... SELECT statement is. The outpuf rom the data_locks table shows there is an shared intention lock on the table and 525 shared record locks on the primary key (in InnoDB that is the row itself). At the end of the statement – just before the statement completes, all rows will be locked. What does that mean? If you try to update or delete one of the rows that are locked in a different connection, that statement will block and possibly encounter a lock wait timeout:

In this case, the timeout is set to happen after two seconds to avoid having to wait for the error to occur.

So, this was with the REPEATABLE READ transaction isolation level. Is there a difference in READ COMMITTED? Yes, there is. In that case the CREATE TABLE ... SELECT and INSERT INTO ... SELECT statements do not take any locks on the city table.

User Variables

The other use case, I want to discuss is storing the result of a SELECT statement in a user variable. This has an unexpected twist. As a start, consider finding the number of rows in the table and store the result in the @my_var user variable using the default REPEATABLE READ transaction isolation level:

Selecting from the performance_schema.data_locks while the query is executing, gives a result similar to before:

The optimiser in this case chooses to use the CountryCode index for counting the number of rows, but otherwise the locking situation is the same as before. It does however mean that you can update the row provided you do not use the CountryCode index to access the row, so in that sense it is an improvement. (The optimiser will choose the execution plan it thinks is most efficient – has the lowest cost – so you mileage may vary.)

However, the big surprise is when you change to the READ COMMITTED transaction isolation level:

Unlike before, assigning the value to a user variable still takes locks in the READ COMMITTED transaction isolation level:

The locking is slightly reduced compared to REPEATABLE READ (no gap locks) but this is still enough to cause serious locking problems in a production system.

Why does it takes locks even in READ COMMITTED when you assign the result to a user variable? I have not been able to find any documentation of it, but by assigning the value to a variable, you can re-use the value including in statements that modify the data, so for data consistency it does make sense that the locks last till the end of the transaction.