在并发事务更新数据后选择更新数据的示例

The following example illustrates how the different isolation levels attempt 选择…​FOR UPDATE after a concurrent transaction has performed updates.

在该示例中,诺福德支持的所有三个隔离级别被示出。主事务的隔离级别与第二个,第三和第四列中的事务的隔离级别相同。换句话说:

  • The isolation_level for the main transaction is CONSISTENT READ when compared with the behavior of the statements listed in the CONSISTENT READ column.

  • The isolation_level for the main transaction is READ COMMITTED when compared with the behavior of the statements listed in the READ COMMITTED column.

死锁检测:如果存在阻止,则可能有可能的僵局。因此,Nuodb具有分布式死锁检测系统,可检测这些死锁,然后选择牺牲的交易之一(Force中止)。这与使用两阶段锁定的数据库都非常相似。

CONSISTENT READ

The main transaction will block the second CONSISTENT READ transaction on a 选择…​FOR UPDATE. The second CONSISTENT READ transaction blocks because it has detected a concurrent update and doesn’t know whether to fail or update until the main transaction has finished. Only if the main transaction performs a 回滚 can the second CONSISTENT READ transaction proceed.

READ COMMITTED

The main transaction will block the second READ COMMITTED transaction on a 选择…​FOR UPDATE. The second READ COMMITTED transaction blocks because it has detected a concurrent update and doesn’t know whether to fail or update until the main transaction has finished. Once the main transaction has successfully committed the transaction, the second READ COMMITTED transaction completes its update after it has obtained the most recent version of the row.

主要交易 CONSISTENT READ Transaction READ COMMITTED Transaction
CREATE TABLE t1 (f1 INTEGER);
 INTO t1 VALUES (1),(3),(5);
犯罪;
START TRANSACTION
ISOLATION LEVEL [.var]__isoloation_level__;
选择 * FROM t1;
F1
---
1
3
5
START TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
选择 * FROM t1;
F1
---
1
3
5
START TRANSACTION
ISOLATION LEVEL READ COMMITTED;
选择 * FROM t1;
F1
---
1
3
5
更新 t1  f1 = 42 WHERE f1 = 5;
选择 * FROM t1;
F1
---
1
3
42
选择 * FROM t1;
F1
---
1
3
5
选择 * FROM t1;
F1
---
1
3
5
选择 f1 FROM t1
WHERE f1 = 5 FOR UPDATE;
/* Blocks */
选择 f1 FROM t1
WHERE f1 = 5 FOR UPDATE;
/* Blocks */
犯罪;

ERROR: update conflict in table T1. 任何读取都会继续快照视图。

Update completes, no error. No update as there is no row with f1=5.

选择 * FROM t1;
F1
---
1
3
42

任何读取都会继续快照视图。

选择 * FROM t1;
F1
---
1
3
5
选择 * FROM t1;
F1
---
1
3
42
犯罪;
犯罪;
选择 * FROM t1;
F1
---
1
3
42
选择 * FROM t1;
F1
---
1
3
42