0

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?

lbj99
  • 3
  • 1
  • 2
  • Might be only locked to the filtered rows only. inoodb doesn't lock like myisam – Kamran Shahid Nov 15 '20 at 12:08
  • 1
    Yes, you can query a locked table in a join. InnoDB's [multiversioning architecture](https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html) allows another transaction to read rows without requiring locks. By the way, it would have taken you less time to try it yourself than to post this question to Stack Overflow. – Bill Karwin Nov 15 '20 at 18:01
  • You are probably correct. But it WILL save time for others with the same question in the future. – lbj99 Nov 16 '20 at 13:38

1 Answers1

0
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".)

Rick James
  • 135,179
  • 13
  • 127
  • 222