I am trying to grasp the concept of MySQL locks and came across this section in the docs: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html:
A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.
That leads me to the conclusion that two transactions can never lock rows in the same table in parallel. Let me explain it on the following example:
Assume I have the following table:
CREATE TABLE t1 (id INT, PRIMARY KEY (id)) ENGINE = InnoDB;
Further, lets assume I have 7 rows inserted. This is the balanced tree of the primary key:
Now assume we lock row with id 3:
SELECT * FROM t1 WHERE id=3 FOR UPDATE
From the above paragrph
A locking read (..) set record locks on every index record that is scanned in the processing of the SQL statement.
I would guess that there is an exclusive lock on row 3,5,10. Is that right?
If that would be the case, then
SELECT * FROM t1 WHERE id=100 FOR UPDATE
would try to set an exclusive lock on row 10, because it scanned in the process to find 100. But that means, it has to wait for the other transaction to be finished.
This implies, any transaction that locks a row in table t1 will always first lock row with id 10. Thus, its not possible that two transactions lock two different rows in parallel.
My questions are:
- Is that actually true?
- How can I actually see the balanced index tree, so I can do some local testing?
