Рассуждения о блокировке пропусков в MySQL

Это похоже на то, что мне самому недавно было любопытно, поэтому позвольте мне попытаться объяснить ...

Каким бы ни было значение в Tx2, оно зависает. Таким образом, это в основном означает, что Tx1 удерживает разрыв во всем диапазоне (-∞, + ∞), когда удаление из testdl, где c = 1000, не может найти совпадение, верно ?.

Итак, мой вопрос: это намеренно? Какой в ​​этом смысл?

Это сделано намеренно, основная цель блокировок промежутков - предотвратить вставку любых записей в эти промежутки, чтобы избежать _ 1_.

Итак, представьте, что у вас есть пустая таблица и внутри транзакции вы выполняете delete from testdl where c = 1000;. Теперь, независимо от того, сколько таких строк существовало до того, как вы ожидали, что после этого запроса у вас не будет таких строк в вашей таблице, верно? Итак, если после этого вы выполните select * from testdl where c = 1000 for update; в той же транзакции, вы ожидаете, что это будет пустой результат.

Но чтобы убедиться, что в таблице нет новых строк с c = 1000, нам нужно заблокировать пробелы, в которые можно вставить такие записи. А в пустой таблице есть только один пробел: пробел между псевдозаписями инфимума и супремума (как указал Майкл).

В этом случае можно вставить любое значение> = 1000, поэтому Tx1 блокирует пробел (-∞, 1000)

Опять же, необходима ли блокировка (-∞, 1000)? В чем причина этого?

Я считаю, что приведенное выше объяснение должно также объяснить вопросы, которые вы задаете о втором случае, когда в таблице уже есть одна запись. Но я все равно постараюсь это объяснить.

В своей первой транзакции вы выполняете select * from testdl where c = 500 for update;, и теперь нам нужно убедиться, что нет новых записей с c = 500, если мы решим снова сделать такой запрос внутри этой транзакции. Поэтому нам нужно заблокировать для него все необходимые зазоры. Какие у нас есть пробелы? (-∞, 1000) и (1000, +∞), очевидно, новые записи, где c = 500 не будет вставлен во второй промежуток, но они будут вставлены в первый промежуток, поэтому мы должны заблокировать его.

Надеюсь, это ответит на него.

После выполнения

create table testdl (id int auto_increment, c int, primary key (id), key idx_c (c));

-- transaction 1
begin;
delete from testdl where c = 1000;

-- transaction 2
begin;
insert into testdl (c) values (?); -- ? can be any int

Результат select * from performance_schema.data_locks:

+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+
| INNODB | 140043377180024:1073:140043381454544   |                  2322 |        48 |      218 | test          | testdl      | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IX                 | GRANTED     | NULL                   |
| INNODB | 140043377180024:12:5:1:140043381451552 |                  2322 |        48 |      218 | test          | testdl      | NULL           | NULL              | idx_c      |       140043381451552 | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
| INNODB | 140043377180872:1073:140043381460688   |                  2321 |        49 |      154 | test          | testdl      | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX                 | GRANTED     | NULL                   |
| INNODB | 140043377180872:12:5:1:140043381457776 |                  2321 |        49 |      154 | test          | testdl      | NULL           | NULL              | idx_c      |       140043381457776 | RECORD    | X                  | GRANTED     | supremum pseudo-record |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+

транзакция 2 ожидает блокировки намерения вставки (отрицательная бесконечность, положительная бесконечность), потому что транзакция 1 удерживает блокировку следующего ключа (отрицательная бесконечность, положительная бесконечность), поэтому она не может продолжаться.

Но после выполнения

create table testdl (id int auto_increment, c int, primary key (id), key idx_c (c));
insert into testdl values(1, 1000);

-- transaction 1
begin;
select * from testdl where c = 500 for update;

Результат select * from performance_schema.data_locks:

+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 140043377180872:1074:140043381460688   |                  2341 |        49 |      167 | test          | testdl      | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX        | GRANTED     | NULL      |
| INNODB | 140043377180872:13:5:2:140043381457776 |                  2341 |        49 |      167 | test          | testdl      | NULL           | NULL              | idx_c      |       140043381457776 | RECORD    | X,GAP     | GRANTED     | 1000, 1   |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

транзакция 1 удерживает блокировку промежутка (отрицательная бесконечность, (1000, 1)), поэтому другие транзакции не могут вставлять данные в этот промежуток.