CODEWITHSUNDEEP
mysqlinner-joininnodbtable-locking
lbj99
lbj99

Reputation: 3

Does InnoDB block on a SELECT that joins with a locked table?

Suppose, I run this query, which locks the entire table in InnoDB until it finishes:

Update UserDetails set balance = 0

Then I run this query:

Select * from User inner join UserDetails on (User.id = UserDetails.userid)

We know that selecting from a locked table is allowed. But will InnoDB allow the select with inner join on a locked table to run concurrently, even when the UPDATE is running?

Upvotes: 0

Views: 437

Answers (1)

Rick James
Rick James

Reputation: 142540

Update UserDetails set balance = 0

Updates every row in the table, so it must lock every row in the table.

It is rarely wise to design a schema such that you need to do such a massive update. I strongly recommend you rethink the processing. If you wish to discuss this further, please provide more about the logic.

Back to your question...

There are (to simplify things) two types of row locks -- exclusive versus shared read.

'Exclusive' says "hands-off, I'm busy here". 'Shared read' is any number of spectators watching.

But there is more to the story when you get involved with transactions. There are cases where you want a SELECT to block an UPDATE. See FOR UPDATE.

But will InnoDB allow the select with inner join on a locked table to run concurrently, even when the UPDATE is running?

Is the SELECT a transaction by itself, not part of a larger transaction?

Yes, it is very possible for the SELECT and the UPDATE to overlay. Each is looking at a different snapshot of the data. (That gets into implementation and "transaction isolation modes".)

Upvotes: 0

Related Questions