40 Why Are There So Many Locks for Insert Statements

40 Why Are There so Many Locks for insert Statements #

In the previous article, I mentioned that MySQL has optimized the locking of auto-increment primary keys, releasing the lock as soon as the auto-increment ID is obtained.

Therefore, the INSERT statement is a lightweight operation. However, this conclusion only applies to “ordinary INSERT statements”. There are some INSERT statements that are considered “special cases” and require locking other resources during execution, or cannot release the auto-increment lock immediately after obtaining the auto-increment ID.

So, in this article, let’s discuss this topic.

INSERT … SELECT statement #

Let’s start with the question from yesterday. The table structure and initialization data for tables t and t2 are as follows, and today’s examples will still focus on these two tables.

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
 
insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
 
create table t2 like t

Now, let’s see why, under the REPEATABLE READ isolation level and binlog_format=statement, executing the following statement:

insert into t2(c,d) select c,d from t;

requires locking all rows and gaps in table t?

In fact, we still need to consider the consistency between the log and the data. Let’s take a look at the execution sequence:

img

Figure 1 Concurrent insert scenario

The actual effect of this is that if session B executes first, since this statement adds a (-∞,1] next-key lock on the primary key index of table t, the insert statement of session A is not allowed to execute until after the execution of the statement.

But if there is no lock, session B’s insert statement may be executed first, but the write to the binlog may be delayed. Therefore, in the case of binlog_format=statement, the binlog will record the following statement sequence:

insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;

When this statement is executed on the standby server, it will also write the row with id=-1 to table t2, resulting in inconsistency between the master and standby copies.

INSERT loop writing #

Of course, when executing insert … select, the target table is also not fully locked, but only the resources that need to be accessed are locked.

If there is a requirement to insert a row into table t2, where the value of c in this row is the maximum value of c in table t plus 1.

In this case, we can write the SQL statement like this:

insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

The locking scope of this statement is the (3,4] and (4,supremum] next-key locks on the index c of table t, as well as the row with id=4 on the primary key index.

The execution process of this statement is also relatively simple, scanning the first row from table t in descending order according to the c index, and inserting the result into table t2.

Therefore, the number of scanned rows for the entire statement is 1.

The slow query log for this statement is shown in the following figure:

img

Figure 2 Slow query log - Inserting data into table t2

From this slow query log, we can see that Rows_examined=1, which perfectly verifies that the number of scanned rows for executing this statement is 1.

So, if we want to insert such a row of data into table t:

insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

What is the execution process of this statement? And how many rows are scanned?

At this point, if we look at the slow query log, we will find that something is wrong.

img

Figure 3 Slow query log - Inserting data into table t

As you can see, the value of Rows_examined is 5 this time.

I mentioned in a previous article that I hope you can learn to “mentally simulate” the execution process of the entire statement based on the results of the explain command. Today, let’s try it together.

Figure 4 shows the explain result for this statement.

img

Figure 4 Explain result

From the Extra field, we can see the “Using temporary” message, indicating that this statement uses a temporary table. This means that during the execution process, the contents of table t need to be read and written into a temporary table.

The rows displayed in the figure is 1. Let’s make a guess about the execution process of this statement: if we assume that the result of the subquery is read out (scanning 1 row), written into the temporary table, and then read from the temporary table (scanning 1 row), and written back to table t. Then the number of scanned rows for this statement should be 2, not 5.

Therefore, this guess is incorrect. In fact, the rows=1 in the Explain result is due to the influence of the limit 1.

From another perspective, let’s see how many rows InnoDB has actually scanned. Figure 5 shows the result of checking Innodb_rows_read before and after executing this statement.

img

Figure 5 Checking the change in Innodb_rows_read You can see that before and after the execution of this statement, the value of Innodb_rows_read has increased by 4. Since the default temporary table is using the Memory engine, the 4 rows queried are all from the table t, which means a full table scan is performed on table t.

So, let’s clarify the entire execution process:

  1. Create a temporary table with two columns, c and d.
  2. Scan the table t according to the index c, retrieving c=4, 3, 2, 1 in sequence, and then performing a lookup operation on the table to read the values of c and d and write them into the temporary table. At this point, Rows_examined=4.
  3. Since the semantics include a limit 1, only the first row of the temporary table is selected and then inserted into table t. At this point, the value of Rows_examined is incremented by 1, becoming 5.

Therefore, this statement will cause a full table scan on table t and apply shared next-key locks on all gaps in the index c. Consequently, during the execution of this statement, other transactions cannot insert data into this table.

As for why this statement needs a temporary table for execution, the reason is that in cases where data is traversed and updated simultaneously, if the read data is directly written back to the original table, it may read the just-inserted records during the traversal process, which would conflict with the semantics if the newly inserted records are involved in the calculation logic.

Due to the implementation of this statement not directly using limit 1 within a subquery, it requires scanning the entire table t. Its optimization method is also quite simple: use the method described earlier, first insert into the temporary table temp_t, so only one row needs to be scanned; then retrieve this row of data from temp_t and insert it into table t1.

Of course, since this statement involves a very small amount of data, you can consider using an in-memory temporary table for this optimization. The syntax for optimizing using an in-memory temporary table is as follows:

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t  (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

INSERT with unique key conflict #

The previous two examples involve the use of insert ... select, and now I’m going to talk about the most common case where an insert statement encounters a unique key conflict.

For a table with a unique key, encountering a unique key conflict when inserting data is also a common situation. Let me give you a simple example of a unique key conflict.

img

Figure 6: Locking during unique key conflict

This example is also performed under the REPEATABLE READ isolation level. As you can see, the insert statement that session B is trying to execute is in a lock wait state.

In other words, when the insert statement executed by session A encounters a unique key conflict, it not only returns an error, but also locks the conflicting index. As mentioned earlier, a next-key lock is defined by its right boundary value. At this point, session A holds a shared next-key lock (read lock) on the range (5,10] of index c.

As for why this read lock is added, I haven’t found a reasonable explanation myself. In terms of its function, this will prevent this row from being deleted by other transactions.

There is an incorrect description of this issue in the official documentation, which wrongly states that if the conflict occurs on the primary key index, a record lock is added, while a next-key lock is added only for unique indexes. However, in practice, both types of indexes are locked with next-key locks.

Note: I discovered this bug while writing this article and consulting the documentation. I have already reported it to the official channel here and it has been verified.

In the comments section of the previous articles, someone asked about deadlocks that occur when concurrently inserting data into a table with multiple unique indexes. However, since they didn’t provide a reproducible scenario or context, I cannot analyze it. Therefore, when you leave a question in the comments section, I suggest providing a reproducible scenario or contextual information so that I can better analyze the issue with you.

Here, I will share a classic deadlock scenario with you. If you have encountered other deadlocks caused by unique key conflicts, please feel free to leave a comment.

img

Figure 7: Unique key conflict - Deadlock

When session A executes the rollback statement to roll back, session C notices the deadlock almost simultaneously and returns.

The logic behind this deadlock is as follows:

  1. At time T1, session A is started and executes an insert statement, which adds a record lock on value c=5 in the index c. Note that this index is a unique index, so it degenerates to a record lock (if you have a vague memory, you can review the locking rules introduced in the [21st article]).

  2. At time T2, session B executed the same insert statement and encountered a unique key conflict, so it added a read lock. Similarly, session C also added a read lock on the record with c=5 on index c.

  3. At time T3, session A rolled back. At this point, both session B and session C attempted to continue with the insert operation and both needed to add write locks. Both sessions had to wait for the row locks held by each other, resulting in a deadlock.

The state transition diagram for this process is shown below.

img

Figure 8: State Transition Diagram - Deadlock

insert into … on duplicate key update #

The previous example resulted in an error when a primary key conflict occurred. If it was written as:

insert into t values(11,10,10) on duplicate key update d=100;

it would add an exclusive next-key lock (write lock) on the index c for the record (5,10].

The semantics of the insert into ... on duplicate key update statement is to insert a row of data, and if a unique key constraint is violated, execute the update statement that follows.

Note that if multiple columns violate the unique constraint, the rows will be modified in the order of the indexes, starting with the first index that conflicts.

Now, the table t already has the rows (1,1,1) and (2,2,2). Let’s take a look at the effect of the following statement:

img

Figure 9: Simultaneous Conflict on Two Unique Keys

As can be seen, the primary key id is checked first, and MySQL determines that this statement conflicts with the row where id=2, so it modifies the row with id=2.

It is important to note that the affected rows returned by executing this statement is 2, which can easily lead to misunderstandings. In reality, only one row is actually updated. It’s just that in the code implementation, both the insert and update operations consider themselves successful, so the update count is increased by 1, and the insert count is also increased by 1.

Summary #

In today’s article, I introduced several special cases of the insert statement.

insert...select is a common method for copying data between two tables. It is important to note that under the REPEATABLE READ isolation level, this statement will add read locks to the records and gaps scanned in the select table.

If the insert and select targets are the same table, it may result in circular writes. In this case, we need to introduce temporary user tables for optimization.

If a unique key conflict occurs in an insert statement, a shared next-key lock (S lock) will be added to the conflicting unique value. Therefore, after encountering an error caused by a unique key constraint, it is important to commit or rollback the transaction as soon as possible to avoid long periods of locking.

Finally, I have a question for you.

What method do you usually use to copy data between two tables and what precautions do you take? In your application scenario, what are the advantages of this method compared to other methods?

Feel free to share your experience and analysis in the comments, and I will select interesting comments to analyze with you at the end of the next article. Thank you for listening, and feel free to share this article with more friends to read together.

Previous Question #

We have already answered the previous question in the article.

Some students mentioned that if there are other threads operating on the original table during the execution of insert...select, it will result in logical errors. However, this is not the case. If no locks are added, it is a snapshot read.

During the execution of a statement, its consistency view does not change, so even if other transactions modify data in the original table, it will not affect the data seen by this statement.