CREATE TABLE DEPT(DEPTNO INT PRIMARY KEY,
SUM_OF_SALARY NUMBER)
/
CREATE TABLE EMP(EMPTO INT PRIMARY KEY,
DEPTNO REFERENCES DEPT,
SALARY NUMBER)
/
--有外键的表,不管依赖的表是否有数据,都不能够TRUNCATE,可以先禁用外键,再TRUNCATE,或者DELETE
/
ALTER TABLE EMP MODIFY CONSTRAINT SYS_C0030089 DISABLE;
/
TRUNCATE TABLE DEPT
/
ALTER TABLE EMP MODIFY CONSTRAINT SYS_C0030089 ENABLE
/
DELETE FROM DEPT
/
TRUNCATE TABLE EMP
/
--并发控制测试
INSERT INTO DEPT (DEPTNO) VALUES(1);
/
INSERT INTO DEPT (DEPTNO) VALUES(2);
/
INSERT INTO EMP(EMPTO,DEPTNO,SALARY)
VALUES(100,1,55);
/
INSERT INTO EMP(EMPTO,DEPTNO,SALARY)
VALUES(101,1,50);
/
SELECT * FROM DEPT
/
SELECT * FROM EMP
/
--SESSINO 1
INSERT INTO EMP(EMPTO,DEPTNO,SALARY)
VALUES(102,2,60);
--SESSINO 2
UPDATE EMP SET DEPTNO=2 WHERE EMPTO=100;
--SESSINO 2
UPDATE DEPT SET SUM_OF_SALARY=(SELECT SUM(SALARY) FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO)
WHERE DEPT.DEPTNO IN(1,2)
--SESSINO 1
UPDATE DEPT SET SUM_OF_SALARY=(SELECT SUM(SALARY) FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO)
WHERE DEPT.DEPTNO=2
此时会话1被阻塞但是会话1的读取部分(查询)已经处理好了,一致性读的机制冻结了该结果。
--SESSINO 2
commit
SELECT * FROM DEPT
session 1看到的结果?
session 2看到的结果?