代码之家 › 专栏 › 技术社区 › WW.

关于Oracle锁定和摘要的问题

locking oracle

WW. · 技术社区 · 16 年前

首先,下面是一些用于设置表和背景的脚本。

CREATE TABLE TEST_P
(
  ID    NUMBER(3) NOT NULL PRIMARY KEY,
  SRC   VARCHAR2(2) NOT NULL,
  DEST  VARCHAR2(2) NOT NULL,
  AMT   NUMBER(4) NOT NULL,
  B_ID_SRC  NUMBER(3),
  B_ID_DEST NUMBER(3)
);

此表中的一行表示金额正在从 SRC DEST . 这个 ID 列是代理键。第一行表示10件物品正在从B1移动到S1。中的值 SRC 目的地 不同-不能在两者中显示相同的值。

INSERT INTO TEST_P VALUES (1, 'B1', 'S1', 10, NULL, NULL);
INSERT INTO TEST_P VALUES (2, 'B2', 'S1', 20, NULL, NULL);
INSERT INTO TEST_P VALUES (3, 'B3', 'S2', 40, NULL, NULL);
INSERT INTO TEST_P VALUES (4, 'B1', 'S2', 80, NULL, NULL);
INSERT INTO TEST_P VALUES (5, 'B4', 'S2', 160,NULL, NULL);

CREATE TABLE TEST_B
(
  ID       NUMBER(3)   NOT NULL  PRIMARY KEY,
  BATCH    NUMBER(3)   NOT NULL,
  WHO      VARCHAR2(2) NOT NULL,
  AMT      NUMBER(4)   NOT NULL
);

CREATE SEQUENCE TEST_B_SEQ START WITH 100;

需要编写一个进程,该进程将定期从 TEST_P 并填充 TEST_B . 它还必须更新 B_ID_SRC B_ID_DEST 哪些是外键 测试 .

这是我目前的解决方案。

INSERT INTO TEST_B
(ID, BATCH, WHO, AMT)
SELECT TEST_B_SEQ.NEXTVAL, 42, WHO, AMT FROM
(
  SELECT SRC AS WHO, SUM(AMT) AMT FROM TEST_P
  WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
  GROUP BY SRC
  UNION ALL
  SELECT DEST, -SUM(AMT) FROM TEST_P
  WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
  GROUP BY DEST)
;

步骤2:

UPDATE TEST_P
  SET B_ID_SRC = (SELECT ID FROM TEST_B WHERE BATCH = 42 AND TEST_P.SRC = WHO),
      B_ID_DEST = (SELECT ID FROM TEST_B WHERE BATCH = 42 AND TEST_P.DEST = WHO);

这有两个问题:

1) 应锁定SELECT中的行。我如何使用选择按钮来执行此操作 FOR UPDATE ?

进一步详情 测试 表上有一个状态列。只有当事物处于正确的状态时,它们才会被包含到 .

由于种种原因 测试 实际上是必需的。我不能只是把它当作一个风景或什么的。还有后续处理等。

2 回复 | 直到 16 年前

Vincent Malgrat 16 年前

在您的示例中,您将更新 TEST_P . 两个简单的解决方案可以确保两个表上的信息是一致的。你可以:

LOCK TABLE test_p IN EXCLUSIVE MODE
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE

方法1很简单,我将演示方法2:

session 1> ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;

Session altered

session 1> INSERT INTO TEST_B
        2  (ID, BATCH, WHO, AMT)
        3  SELECT TEST_B_SEQ.NEXTVAL, 42, WHO, AMT FROM
        4  (
        5    SELECT SRC AS WHO, SUM(AMT) AMT FROM TEST_P
        6    WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
        7    GROUP BY SRC
        8    UNION ALL
        9    SELECT DEST, -SUM(AMT) FROM TEST_P
       10    WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
       11    GROUP BY DEST)
       12  ;

6 rows inserted

在这里,我插入一行和另一个会话并提交:

session 2> INSERT INTO TEST_P VALUES (6, 'B4', 'S2', 2000,NULL, NULL);

1 row inserted

session 2> commit;

Commit complete

会话1尚未看到与会话2一起插入的行:

session 1> select * from TEST_P;

  ID SRC DEST   AMT B_ID_SRC B_ID_DEST
---- --- ---- ----- -------- ---------
   1 B1  S1      10          
   2 B2  S1      20          
   3 B3  S2      40          
   4 B1  S2      80          
   5 B4  S2      16

session 1> UPDATE TEST_P
        2    SET B_ID_SRC = (SELECT ID FROM TEST_B WHERE BATCH = 42 AND TEST_P.SRC = WHO),
        3        B_ID_DEST = (SELECT ID FROM TEST_B WHERE BATCH = 42 AND TEST_P.DEST = WHO);

5 rows updated

session 1> commit;

Commit complete

session 1> select * from TEST_P;

  ID SRC DEST   AMT B_ID_SRC B_ID_DEST
---- --- ---- ----- -------- ---------
   6 B4  S2    2000          
   1 B1  S1      10      100       104
   2 B2  S1      20      101       104
   3 B3  S2      40      102       105
   4 B1  S2      80      100       105
   5 B4  S2     160      103       105

6 rows selected

David Aldridge 16 年前

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606

你的陈述大致如下:

MERGE INTO TEST_B
USING 
(
  SELECT SRC AS WHO, SUM(AMT) AMT FROM TEST_P
  WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
  GROUP BY SRC
  UNION ALL
  SELECT DEST, -SUM(AMT) FROM TEST_P
  WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
  GROUP BY DEST)
ON (
WHEN MATCHED THEN UPDATE SET ...;

通过USING子句中的目标表连接来标识需要更新的行可能更有效,以避免更新不需要修改的行。