In 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:
update ibreg set k=1 where id in (select id from ibcmp where id > 90000);
This query, when executed with
, 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 …[Read more]