嵌套事务，自治事务

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

/*
通过上面的例子我们可以看出，这是两个完全独立的会话
*/ 

很长时间了，不保证上面说的是正确的，呵呵