Avoid Shared Locks from Subqueries When Possible

Shared LocksIn this blog post, we’ll look at how to avoid shared locks from subqueries.

I’m pretty sure most of you have seen an UPDATE statement matching rows returned from a SELECT query:

This query, when executed with autocommit=1, is normally harmless. However, this can have bad effects when combined with other statements in the same transaction that result in holding the shared locks from the SELECT query. But first, let me explain why the SELECT query would hold locks in the first place.

Due to InnoDB’s ACID properties, to make sure that the outer UPDATE statement has a consistent view of the matching rows from the SELECT query the server has to acquire a shared lock on those rows. No other thread should modify those matching rows to maintain consistency within the transaction. To demonstrate, let’s take two transactions executed in specific order below:

By the time the second session executes, it will be in a LOCK WAIT state (as confirmed from INFORMATION_SCHEMA):

Information_Schema.INNODB_LOCKS confirms that our first transaction has held a shared lock on the rows that matched the SELECT queries from the first transaction. This can be bad for a number of reasons:

  1. As the number of rows that matches the SELECT grows, DEADLOCK and lock wait timeouts can become more frequent
  2. As a consequence of this, ROLLBACKs would also increase (and are expensive operations)
  3. Your users can become unhappy, especially if it is not handled gracefully from the application

If you really need the consistency of the view between the table being read from and the table getting updated, the lock is necessary and unavoidable. Avoiding the deadlocks and lock wait timeouts can be minimized, but not totally avoided.

On the other hand, if you’re not worried about view consistency, there are two ways you can avoid such problems: by using variables or making sure the SELECT becomes a transient read inside the transaction (i.e., by dumping the results into an OUTFILE).

The first method is bound by the group_concat_max_len variable. If you think you will only have a few resulting IDs that fit into group_concat_max_len, this is a good solution.

The second approach is only meant to overcome the limitation of the GROUP_CONCAT method.

Again, these two approaches only work if you do not care if the result of the SELECT queries changes on the other table between the BEGIN statement and UPDATE within the transaction.

Share this post

Comments (6)

  • Holger Thiel

    Why not:

    mysql1> create temporary table t as select id from ibcmp where id > 90000;

    September 26, 2017 at 9:13 am
  • Jervin Real

    Holger, it still leaves a shared lock on the matching rows from ibcmp, unless you use autocommit=1 or not use explicit transactions.

    September 26, 2017 at 9:34 am
  • David Bennett

    Could you have a concurrency issue with access to the hard defined /tmp/id.csv ? Maybe you could use uuid() to recreate a unique temporary filename?

    September 26, 2017 at 10:07 am
  • Jervin Real

    David, good point – UUID() is an option (ideally wth ROW binlog format).

    September 26, 2017 at 11:16 am
  • Tom Brown

    You cannot use the IN operator as you suggest. The appropriate function is FIND_IN_LIST. (The IN operator will treat the variable as a single string, even though it’s meant to be a concatenated list)

    March 4, 2019 at 2:10 pm
    • Tom Brown

      Oops… It’s FIND_IN_SET

      mysql1> begin;
      mysql1> select group_concat(id) into @ids from ibcmp where id > 90000;
      mysql1> update ibreg set k=1 where FIND_IN_SET(id,@ids) > 0;
      mysql2> begin;
      mysql2> delete from ibcmp where iid > 90000;

      March 4, 2019 at 2:11 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.