为什么此选择更新示例有效？ [英] Why does this select for update example work?

问题描述

我正在运行一些简单的脚本来测试可能解决的完整性问题。假设我有一个表 my_table

I'm running some simple scripts to test possible solutions for an integrity problem I'm solving. Suppose I have a table my_table

|foo     |
|1       |

我有以下两个摘要：

// db_slow.php
<?php
$db = new PDO('mysql:host=localhost;dbname=my_playground;charset=utf8', 'root', '');
echo 'starting transaction<br />';
$db->beginTransaction();
$stmt = $db->query('select * from my_table for update');
$rows = $stmt->fetchAll();
echo 'count tables: ', count($rows), '<br />';
if (count($rows) == 1) {
    sleep(10);
    $db->query('insert into my_table(foo) VALUES(2)');
}
$db->commit();
echo 'done';

// db_fast.php
<?php
$db = new PDO('mysql:host=localhost;dbname=my_plyaground;charset=utf8', 'root', '');
echo 'starting transaction<br />';
$db->beginTransaction();
$stmt = $db->query('select * from my_table for update');
$rows = $stmt->fetchAll();
echo 'count tables: ', count($rows), '<br />';
if (count($rows) == 1) {
    $db->query('insert into my_table(foo) VALUES(3)');
}
$db->commit();
echo 'done';

db_slow.php 有10秒的延迟

据我了解， select ...进行更新锁定它选择的所有行。如果我运行 db_slow ，那么 db_fast ， db_fast 也有一个10秒的延迟，因为它正在等待 db_slow 如我所料。

As I understand, select ... for update locks all rows it selects. If I run db_slow then db_fast, db_fast also has a 10 second delay, as it's waiting for db_slow as I expect.

但是，我没有得到的是输出为：

However, what I don't get is this is the output:

// db_slow.php
starting transaction
count tables: 1
done

// db_fast.php
starting transaction
count tables: 2
done

和 my_table

|foo      |
|1        |
|2        |

---

据我了解， select ... for update 锁定为该事务选择的所有行。所以这就是我的期望：

---

As I understand, select ... for update locks all rows that are selected for that transaction. So this is what I expect:

1. db_slow：选择第1行并锁定它


2. db_slow：看到只有1行，然后等待


3. db_fast：尝试选择第1行，看到它已被锁定，等待


4. db_slow：在行中插入' 2'


5. db_fast：继续，因为未锁定第1行


6. db_fast：仅选中了1行，因此将插入'3'


7. 最后以 foo：1，2，3

1. db_slow: select row 1 and lock it
2. db_slow: see that it's only 1 row and wait
3. db_fast: try to select row 1, see that it's locked, wait
4. db_slow: insert row with '2'
5. db_fast: continues because row 1 is unlocked
6. db_fast: only selected 1 row, so it inserts '3'
7. End up with foo: 1, 2, 3

上面描述的输出和延迟似乎可以确认步骤1、2、3、4。似乎 db_fast 在尝试获取锁定后正在运行select？我以为它会选择一行，然后锁定或等待。

The output and delay described above seems to confirm steps 1, 2, 3, 4. It seems like db_fast is running select after trying to obtain a lock? I thought it would select the one row, then lock or wait.

一些相关的问题：

当我使用 select运行......以共享模式锁定我最终得到

// db_slow.php
starting transaction
count tables: 1
done

// db_fast.php
starting transaction
count tables: 1
done

code> my_table

And my_table

|foo      |
|1        |
|3        |

为什么 db_slow 不插入行' 2'即使它认为表中只有1行（插入行的条件）？

Why is db_slow not inserting a row '2' even when it thinks there's only 1 row in the table (the condition to insert a row)?

推荐答案

预期的行为有点偏离。在提交db_slow之前，表中的所有行均被锁定。提交后，有两行。 db_slow提交时，db_fast被解除阻止。因此，其行为是：

I think the expected behavior is a little off. Before db_slow commits, all rows in the table are locked. After it commits, there are two rows. db_fast is unblocked when db_slow commits. Hence, the behavior is:

1. db_slow：选择第1行并将其锁定


2. db_slow：请参见只有1行，然后等待


3. db_fast：尝试选择第1行，看看它是否已锁定，等待


4. db_slow：插入带有'2的行'


5. db_slow：提交


6. db_fast：解除阻止并读取2行


7. db_fast：不执行一切


8. 最后是foo：1、2

1. db_slow: select row 1 and lock it
2. db_slow: see that it's only 1 row and wait
3. db_fast: try to select row 1, see that it's locked, wait
4. db_slow: insert row with '2'
5. db_slow: commit
6. db_fast: unblocked and reads 2 rows
7. db_fast: doesn't do anything
8. End up with foo: 1, 2

这篇关于为什么此选择更新示例有效？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！