关于Oracle锁定和总结的问题
首先，下面是一些设置表和背景的脚本。
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)
);此表中的一行表示AMT正从SRC移动到DEST。ID列是一个代理键。第一行表示将从B1移到S1。SRC和DEST中的值是不同的--在两者中都不能出现相同的值。
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);还有一张这样的桌子。它对相同的信息有不同的看法。这里的每一行表示从"Who“中添加或删除的内容。世卫组织的价值观是B1、B2。还有S1，S2..。
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更新到TEST_B中。
到目前为止，这是我的解决方案。
步骤1：
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)如果一个行由另一个会话插入，并在步骤1.5中被逗号，那么更新将捕获比插入更多的行。如何在不逐行处理的情况下解决此问题？
进一步详细说明了真实的TEST_P表中有一个状态栏。只有当事物处于正确的状态时，它们才被包含到TEST_B中。
由于各种原因，实际上需要TEST_B。我不能就这样把它变成风景什么的。有后续的处理等。
回答 2
Stack Overflow用户
发布于 2009-07-31 08:50:33
在您的示例中，您将更新TEST_P的所有行。两个简单的解决方案将使您能够确保两个表上的信息是一致的。你可以：
LOCK TABLE test_p IN EXCLUSIVE MODE在您的事务期间(其他插入会话必须等待)，这将阻止第一个会话在事务开始后看到其他会话所做的更改。
方法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结果是一致的，在提交会话1之后，将看到由会话2插入的行：
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 selectedStack Overflow用户
发布于 2009-07-31 09:52:20
在这里，一个合并语句可以处理这个需求。
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子句中的目标表连接标识需要更新的行可能更有效，以避免更新不需要修改的行。
https://stackoverflow.com/questions/1210993
复制相似问题
腾讯云开发者
