Oracle 事务隔离

Question

我有一个方法 SaveApp() 将停用现有记录并插入一个新记录。

void SaveApp(int appID)
{
   begin transaction;
   update;
   insert;
   commit transaction;
}

假设在数据库表 SalesApp 中，我有 2 条 appID 等于 123 的记录；

记录 1，appID 123，非事件

记录 2，appID 123，事件

如果我调用这个方法 SaveApp()同时在两个线程中，第一个事务(我们称之为 T1 )将更新现有的两条记录，而第二个事务(我们称之为 T2 )等待。

后 T1 完成后，该表中现在将有 3 条记录。然而，不知何故 T2 不知道新插入的记录，更新查询 T2 只更新前两条记录，插入第四条。

在这两个方法调用之后，在数据库中，我们现在将有 4 条记录，第 3 条和第 4 条都处于事件状态，这是错误的。

记录 1，appID 123，非事件

记录 2，appID 123，非事件

记录 3，应用 ID 123，事件

记录 4，应用 ID 123，事件

你知道有什么解决方案可以解决这个问题吗？我试过使用隔离级别可序列化，但它不起作用。

谢谢!

Answer 1

您是否有另一个表，每个 AppId 包含一行，通过唯一键或主键约束强制执行？如果是这样，请使用 select for update在父表上按 AppId 序列化访问。

创建表:

session_1> create table parent (AppId number primary key);

Table created.

session_1> create table child (AppId number not null references Parent(AppId)
  2      , status varchar2(1) not null check (status in ('A', 'I'))
  3      , InsertedAt date not null)
  4  /

Table created.

插入起始值:

session_1> insert into Parent values (123);

1 row created.

session_1> insert into child values (123, 'I', sysdate);

1 row created.

session_1> insert into child values (123, 'A', sysdate);

1 row created.

session_1> commit;

Commit complete.

开始第一笔交易:

session_1> select AppId from Parent where AppId = 123 for update;

     APPID
----------
       123

session_1> update Child set Status = 'I' where AppId = 123 and Status = 'A';

1 row updated.

session_1> insert into child values (123, 'A', sysdate);

1 row created.

在提交之前，在第二个 session 中，确保我们只看到第一行:

session_2> select * from Child;

     APPID S INSERTEDAT
---------- - -------------------
       123 I 2010-08-16 18:07:17
       123 A 2010-08-16 18:07:23

开始第二笔交易:

session_2> select AppId from Parent where AppId = 123 for update;

session 2 现在被阻塞，等待 session 1。并且不会继续。
提交 session 1 将解锁 session

session_1> commit;

Commit complete.

我们现在看到的 session 2:

     APPID
----------
       123

完成第二笔交易:

session_2> update Child set Status = 'I' where AppId = 123 and Status = 'A';

1 row updated.

session_2> insert into child values (123, 'A', sysdate);

1 row created.

session_2> commit;

Commit complete.

session_2> select * from Child;

     APPID S INSERTEDAT
---------- - -------------------
       123 I 2010-08-16 18:07:17
       123 I 2010-08-16 18:07:23
       123 I 2010-08-16 18:08:08
       123 A 2010-08-16 18:13:51

编辑 技术来自于由 Thomas Kyte 撰写的专家 Oracle 数据库架构第二版，第 23-24 页。 http://www.amazon.com/Expert-Oracle-Database-Architecture-Programming/dp/1430229462/ref=sr_1_2?ie=UTF8&s=books&qid=1282061675&sr=8-2

编辑 2 我还建议实现 Patrick Merchand 对此问题的回答，以强制执行 AppId 只能有一个事件记录的规则。所以最终的解决方案将有两个部分，这个答案是关于如何以得到你想要的方式进行更新的答案，以及帕特里克确保该表符合保护数据完整性的要求。