Avoid Shared Locks from Subqueries When Possible

Avoid Shared Locks from Subqueries When Possible

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Share this post

Comments (4)

  • Holger Thiel Reply

    Why not:

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

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

    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 Reply

    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 Reply

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

    September 26, 2017 at 11:16 am

Leave a Reply