为什么MySQL显式谓词锁定不允许谓词锁定之外的INSERT语句 [英] Why does MySQL explicit predicate locking disallow INSERT statements outside of the predicate lock

问题描述

假设我们有以下数据库表:

Assuming we have the following database tables:

create table department (
    id bigint not null, 
    budget bigint not null, 
    name varchar(255), 
    primary key (id)
) ENGINE=InnoDB

create table employee (
    id bigint not null, 
    name varchar(255), 
    salary bigint not null, 
    department_id bigint, primary key (id)
) ENGINE=InnoDB

alter table employee 
add constraint FK_department_id 
foreign key (department_id) 
references department (id)

我们有2个departments:

insert into department (name, budget, id) 
values ('Hypersistence', 100000, 1)

insert into department (name, budget, id) 
values ('Bitsystem', 10000, 2)

第一个部门的3个employees:

insert into employee (department_id, name, salary, id) 
values (1, 'John Doe 0', 30000, 0)

insert into employee (department_id, name, salary, id) 
values (1, 'John Doe 1', 30000, 1)

insert into employee (department_id, name, salary, id) 
values (1, 'John Doe 2', 30000, 2)

假设我们有两个并发用户:Alice和Bob.

Assuming we have two concurrent users: Alice and Bob.

首先，爱丽丝锁定了属于第一个department的所有员工，并获得了该特定department的薪金总和:

First, Alice locks all the employee belonging to the 1st department and also gets the sum of salaries for that particular department:

SELECT * 
FROM employee 
WHERE department_id = 1 
FOR UPDATE

SELECT SUM(salary) 
FROM employee 
where department_id = 1 

现在，与此同时，预计Bob不能使用相同的department_id插入新的employee:

Now, in the meanwhile, it's expected that Bob cannot insert a new employee using the same department_id:

insert into employee (department_id, name, salary, id) 
values (1, `Carol`, 9000, 4)

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: 
    Lock wait timeout exceeded; try restarting transaction

因此，该锁阻止Bob针对同一谓词发出插入.

So, the lock prevented Bob from issuing an insert against the same predicate.

但是，即使Bob试图将employee插入不同的department中，也会引发相同的异常:

However, the same exception is thrown even if Bob tries to insert an employee in a different department:

insert into employee (department_id, name, salary, id) 
values (2, `Dave`, 9000, 5)

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: 
    Lock wait timeout exceeded; try restarting transaction

最后一条insert语句使用第二个department_id，因此该行不应与我们为其获取谓词锁定的select语句重叠.

This last insert statement is using the 2nd department_id, so this row should not overlap with the select statement for which we acquired a predicate lock.

为什么MySQL会阻止第二个与第一笔交易获得的谓词锁不重叠的插入?

Why does MySQL prevent the second insert which is not overlapping with the predicate lock acquired by the first transaction?

在SQL Server上也可以观察到相同的行为.

The same behavior can be observed on SQL Server as well.

将隔离级别更改为READ_COMMITTED时，谓词锁定不会阻止Bob发出的两个插入语句中的任何一个.

When changing the isolation level to READ_COMMITTED, the predicate lock does not prevent any of the two insert statements issued by Bob.

如果考虑以下

在REPEATABLE READ中，持有事务期间获取的每个锁 在交易期间.

In REPEATABLE READ every lock acquired during a transaction is held for the duration of the transaction.

在READ COMMITTED中，与扫描不匹配的锁被释放 声明完成后.

In READ COMMITTED the locks that did not match the scan are released after the STATEMENT completes.

但是，仍然难以确定为什么谓词锁定像在REPEATABLE READ上一样起作用.

However, it's still inetersting to find out why the predicate lockking works as it does on REPEATABLE READ.

推荐答案

SELECT FOR UPDATE锁定在employee表中的1和下一个值之间.由于没有下一个值，因此它将一直锁定直到supremum pseudo-record.可以在information_schema.innodb_locks中看到:

The SELECT FOR UPDATE is locking between 1 and the next value in the employee table. Since there is no next-value, it is locking until the supremum pseudo-record. This can be seen in information_schema.innodb_locks:

mysql> select * from innodb_locks;
+----------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+------------------------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index | lock_space | lock_page | lock_rec | lock_data              |
+----------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+------------------------+
| 28275:1448:3:1 | 28275       | X         | RECORD    | `test`.`employee` | PRIMARY    |       1448 |         3 |        1 | supremum pseudo-record |
| 28273:1448:3:1 | 28273       | X         | RECORD    | `test`.`employee` | PRIMARY    |       1448 |         3 |        1 | supremum pseudo-record |
+----------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)

如果您稍微更改测试用例，以便雇员中有一行dept-id = 2，然后尝试为dept-id = 3添加一个雇员，那么它将起作用.示例:

If you change the test-case slightly so there is a row in employees for dept-id=2, and then try and add an employee for dept-id=3, it will work. Example:

create table department (
    id bigint not null, 
    budget bigint not null, 
    name varchar(255), 
    primary key (id)
) ENGINE=InnoDB;

create table employee (
    id bigint not null, 
    name varchar(255), 
    salary bigint not null, 
    department_id bigint, primary key (id)
) ENGINE=InnoDB;


alter table employee 
add constraint FK_department_id 
foreign key (department_id) 
references department (id);


insert into department (name, budget, id) 
values ('Hypersistence', 100000, 1);

insert into department (name, budget, id) 
values ('Bitsystem', 10000, 2);

insert into department (name, budget, id) 
values ('XX', 10000, 3);


insert into employee (department_id, name, salary, id) 
values (1, 'John Doe 0', 30000, 0);

insert into employee (department_id, name, salary, id) 
values (1, 'John Doe 1', 30000, 1);

insert into employee (department_id, name, salary, id) 
values (2, 'John Doe 2', 30000, 2);


start transaction;

SELECT * 
FROM employee 
WHERE department_id = 1 
FOR UPDATE;


# new session

insert into employee (department_id, name, salary, id) 
values (3, 'Dave', 9000, 5)

这篇关于为什么MySQL显式谓词锁定不允许谓词锁定之外的INSERT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！