sql - sql 何时在更新语句中独占锁定一行？

Question

在这些条件下，sql 中会出现竞争条件吗？

如果我在一个线程中运行此 SQL 更新，请调用它语句 1:

Update Items
Set Flag = B
where Flag = A;

这个 SQL 更新在另一个调用它的语句 2 中运行:

Update Items
Set Flag = C
where Flag = A;

每个线程是否可以读取 Flag 等于 A 的相同记录并使用自己的值写入记录？这样语句1可以先写，然后语句2写，反之亦然？

这个问题的答案取决于数据库何时以独占方式锁定更新。它是在找到记录之前还是在找到记录并评估 where 子句之后发生？

Answer 1

首先，有三个锁上下文:

数据库级锁

表级锁

行级锁

那么你有四种锁定模式:

九

IS

X

小号

IX 和 IS 锁是“意向”锁。在获取其他类型的锁之前持有这些锁。 X 锁是排他(写)锁，S 锁是共享(读)锁。
可以在任何上下文级别获取锁(IX、IS、X 或 S)锁。例如，数据库级别的 X 锁将阻止数据库中的所有其他操作。这是 SQLlite 采用的锁类型。读取时对整个数据库采取S锁，写入时对整个数据库采取X锁。写将等待任何 S 锁完成，并会阻塞新的 S 和 X 锁，直到释放写锁。这提供了可序列化的隔离事务级别。
对于 MySQL，锁定取决于存储引擎。 MyISAM 将在整个(一组)表上使用 X 和 S 锁。 X 锁将等待现有的 S 或 X 锁并阻止新锁。新的 X 锁将在队列中获得更高的优先级，在新的 S 锁之前移动。这种行为可以通过设置 LOW_PRIORITY_UPDATES 来改变，这可能会导致写饥饿，因为写将被降低优先级以支持读。
在 MySQL 中，可以使用“FLUSH TABLES WITH READ LOCK”获得对整个数据库的 X 锁。
InnoDB 锁定通过索引读取遇到的行。 InnoDB 锁定索引记录，并在遍历索引记录时锁定记录。 InnoDB 使用称为“间隙”锁的特殊锁来确保 REPEATABLE-READ 事务隔离级别。锁定在索引条目上，因此如果表没有为 UPDATE 查询建立良好的索引，那么许多行将被锁定。注意 InnoDB 不会为普通的 SELECT 查询创建 S 锁。它使用行版本控制，而不是行级锁定来获得一致的快照。
获取X锁时，数据库需要检测死锁。考虑以下:

>connection 1
start transaction;
update T set c = c + 1 order by id asc;

>connection 2
start transaction;
update T set c = c - 1 order by id desc;

在行锁定模型中，这两条语句不可能都成功完成。第一个将永远等待获取第二个持有的锁，反之亦然。数据库将选择要回滚的连接之一。 InnoDB 将选择更改次数最少的连接。 MyISAM 将锁定整个表，无论哪个连接首先获得锁，然后在第一个完成后运行第二个。
您给出的简单示例将在任何上下文(数据库、表或行)中通过 X 锁解决。如果两个连接以完全相同的类型开始，并且都运行两个尝试更新同一行的更新，则两者都将尝试获取 X 锁。只有一个连接可以获取 X 锁。无法准确确定哪一个将获得锁。另一个连接必须等到锁被释放，才能获得 X 锁。请记住，如果该行被 DELETE 或 UPDATE 锁定，那么等待器可能最终在等待后不会获取锁定，因为数据库中没有任何东西可以锁定。
在您的示例中，第一个 UPDATE 获取 X 锁，然后第二个 UPDATE 将等待 X 锁并最终执行但不匹配任何行。