MS Sql Server嵌套事务 与 Oracle保存点/自治事务的比较
create table ttest1(col int);
create table ttest2(col int);
[一].MS Sql Server嵌套事务(这其是就是保存点)
------------------test...1
begin transaction a;
insert into ttest1 values(1);
begin transaction b;
insert into ttest2 values(2);
update ttest2 set col1 = (select count(1) from ttest2);
commit transaction b;
rollback transaction a;
--COMMIT B 没起作用
select * from ttest1;
--0 rows
select * from ttest2;
--0 rows
------------------test...2
begin transaction a;
insert into ttest1 values(1);
begin transaction b;
insert into ttest2 values(2);
update ttest2 set col1 = (select count(1) from ttest2);
commit transaction b;
commit transaction a;
select * from ttest1;
--1 rows
select * from ttest2;
--1 rows
------------------test...3
begin transaction a;
insert into ttest1 values(1);
begin transaction b;
insert into ttest2 values(2);
update ttest2 set col1 = (select count(1) from ttest2);
rollback transaction b;
commit transaction a;
--无法回滚b
------------------test...3 实际上就演变成保存点了
begin transaction a;
insert into ttest1 values(1);
save transaction b;
insert into ttest2 values(2);
update ttest2 set col1 = (select count(1) from ttest2);
rollback transaction b;
commit transaction a;
select * from ttest1;
--1 rows
select * from ttest2;
--0 rows
/*
如果用过程或者函数实现,我们会看到,结果是一样的
*/
[二].oracle 保存点
------------------test...1
BEGIN
SET TRANSACTION READ WRITE;
INSERT INTO TTEST1 VALUES (1);
SAVEPOINT A;
INSERT INTO TTEST2 VALUES (2);
UPDATE TTEST2 SET COL = (SELECT COUNT (1) FROM TTEST2);
COMMIT A;
Rollback;
END;
--语法错误,无法提交
--注释掉commit a;后
select * from ttest1;
--0 rows
select * from ttest2;
--0 rows
------------------test...2
BEGIN
SET TRANSACTION READ WRITE;
INSERT INTO TTEST1 VALUES (1);
SAVEPOINT A;
INSERT INTO TTEST2 VALUES (2);
UPDATE TTEST2 SET COL = (SELECT COUNT (1) FROM TTEST2);
COMMIT A;
COMMIT;
END;
--语法错误,无法提交
--注释掉commit a;后
select * from ttest1;
--1 rows
select * from ttest2;
--1 rows
------------------test...3
BEGIN
SET TRANSACTION READ WRITE;
INSERT INTO TTEST1 VALUES (1);
SAVEPOINT A;
INSERT INTO TTEST2 VALUES (2);
UPDATE TTEST2 SET COL = (SELECT COUNT (1) FROM TTEST2);
ROLLBACK TO A;
COMMIT;
END;
select * from ttest1;
--1 rows
select * from ttest2;
--0 rows
/*
通过以上两个实验我们可以看到,所谓的MS Sql Server其实就相当于Oracle中的保存点的概念
*/
[三].oracle 自治事务(并行会话)
CREATE OR REPLACE PACKAGE Test AS
PROCEDURE TEST1;
PROCEDURE TEST2;
END Test;
/
------------------test...1
CREATE OR REPLACE PACKAGE BODY Test AS
PROCEDURE TEST1 IS
BEGIN
COMMIT;
SET TRANSACTION READ WRITE;
INSERT INTO TTEST1 VALUES (1);
TEST2;
ROLLBACK;
END;
PROCEDURE TEST2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TTEST2 VALUES (10);
UPDATE TTEST2 SET COL = (SELECT COUNT (1) FROM TTEST1);
COMMIT WORK;
END;
END Test;
/
select * from ttest1;
--0 rows
select * from ttest2;
--1 rows(COL = 0)
------------------test...2
CREATE OR REPLACE PACKAGE BODY Test AS
PROCEDURE TEST1 IS
BEGIN
COMMIT;
SET TRANSACTION READ WRITE;
INSERT INTO TTEST1 VALUES (1);
TEST2;
COMMIT ;
END;
PROCEDURE TEST2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
--SET TRANSACTION READ WRITE; --错误的
INSERT INTO TTEST2 VALUES (10);
UPDATE TTEST2 SET COL = (SELECT COUNT (1) FROM TTEST1);
ROLLBACK WORK;
END;
END Test;
/
select * from ttest1;
--1 rows
select * from ttest2;
--0 rows
/*
通过上面的例子我们可以看出,这是两个完全独立的会话
*/
很长时间了,不保证上面说的是正确的,呵呵