There are two tables: site (id, name, url), page (id, code, content, path, site_id). The site table has a record with id = 1. The page table is empty.
I'm trying to insert a new record into the Page table if it doesn't already exist, using transactions (concurrently). For this purpose, I use a SELECT ... FOR UPDATE query to lock during validation so that I don't get two identical records in the table.
Isolation level for all transactions is REPEATABLE_READ. The transactions start as follows :
TRX 1:
START TRANSACTION;
select * from `page` where path = 'path' and site_id = 1 limit 1 for update;
TRX 2:
START TRANSACTION;
select * from `page` where path = 'path' and site_id = 1 limit 1 for update;
These queries use LOCK_TYPE = Record, LOCK_MODE = X locking. This article has a matrix reflecting, if a request for access in mode A has to wait for a transaction accessing the resource in mode B to finish. In matrix in the intersection of a row (LOCK_MODE = X) with a column (LOCK_MODE = X) stated WAITING. According to this, the second transaction must wait for the first transaction to complete because it uses this lock (LOCK_TYPE = RECORD, LOCK_MODE = X). However, in reality two identical locks are taken at the same time:
| ENGINE | ENGINE_TRANSACTION_ID | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|---|---|
| INNODB | 6180 | page | TABLE | IX | GRANTED | NULL |
| INNODB | 6180 | page | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 6178 | page | TABLE | IX | GRANTED | NULL |
| INNODB | 6178 | page | RECORD | X | GRANTED | supremum pseudo-record |
Can someone explain why this happens and how to fix it?