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

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 …

This is another article in a series of articles titled "A few notes ..." in which I will be posting some important information about locking concepts, different types of locks and what locks table engines support. Just like the previous article, the purpose of this article is to highlight important aspects that you should have in the back of your mind when developing applications.

Isolation is an important part of ACID properties that guarantee that transactions are processed in a reliable manner. But there are four different levels of isolation available and you have to understand each one of them to be able to select the correct one for your needs. This post intends on explaining the four levels together with their effects on locking and performance.