oracle SET TRANSACTION

原创

feier7501 2023-04-26 18:46:08 博主文章分类：oracle ©著作权

©著作权归作者所有：来自51CTO博客作者feier7501的原创作品，请联系作者获取转载授权，否则将追究法律责任

语法：

set_transaction::=

oracle SET TRANSACTION_事务处理

NAME

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 3月 26 21:53:36 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT XIDUSN, XIDSLOT, XIDSQN, USED_UBLK, USED_UREC, NAME FROM V$TRANSACTION;

未选定行

SQL> SELECT * FROM SCOTT.DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> SET TRANSACTION NAME 'TRAN UPDATE';

事务处理集。

SQL> SELECT XIDUSN, XIDSLOT, XIDSQN, USED_UBLK, USED_UREC, NAME FROM V$TRANSACTION;

未选定行

SQL> UPDATE SCOTT.DEPT SET DNAME='SALES1' WHERE DEPTNO=30;

已更新 1 行。
SQL> SELECT NAME FROM V$TRANSACTION;

NAME
--------------------------------------------------------------------------------
TRAN UPDATE

SQL> ROLLBACK;

回退已完成。

SQL> SELECT NAME FROM V$TRANSACTION;

未选定行

SQL>

READ ONLY和READ WRITE

非SYS用户

对于非SYS用户，只支持SELECT、LOCK TABLE、SET ROLE、ALTER SESSION、ALTER SYSTEM。例如：

SQL> SET TRANSACTION READ ONLY;

事务处理集。

SQL> SELECT * FROM TB_USER;

        ID USER_NAME              USER_AGE
---------- -------------------- ----------
       628 user1                        21
       630 user3                        23

SQL> UPDATE TB_USER SET USER_AGE=31 WHERE ID=628;
UPDATE TB_USER SET USER_AGE=31 WHERE ID=628
       *
第 1 行出现错误:
ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作


SQL>

设置成READ WRITE后就可以了：

SQL> SET TRANSACTION READ WRITE;
SET TRANSACTION READ WRITE
*
第 1 行出现错误:
ORA-01453: SET TRANSACTION 必须是事务处理的第一个语句


SQL> COMMIT;

提交完成。

SQL> SET TRANSACTION READ WRITE;

事务处理集。

SQL> SELECT * FROM TB_USER;

        ID USER_NAME              USER_AGE
---------- -------------------- ----------
       628 user1                        21
       630 user3                        23

SQL> UPDATE TB_USER SET USER_AGE=31 WHERE ID=628;

已更新 1 行。

SQL>

注意：SET TRANSACTION之前最好先COMMIT一下。

SYS用户

用SYS用户登录：

C:\>SQLPLUS "/AS SYSDBA"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 27 20:38:27 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT * FROM SCOTT.DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> SET TRANSACTION READ ONLY;

事务处理集。

SQL> UPDATE SCOTT.DEPT SET LOC='BOSTON1' WHERE DEPTNO=40;

已更新 1 行。

SQL> SELECT * FROM SCOTT.DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON1

SQL> ROLLBACK;

回退已完成。

SQL> SELECT * FROM SCOTT.DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

可见对于SYS用户，SET TRANSACTION READ ONLY是不起作用的。

ISOLATION LEVEL

READ COMMITTED

默认是READ COMMITTED。

起两个事务，对于事务A，执行：

SQL> UPDATE DEPT SET LOC='BOSTON1' WHERE DEPTNO=40;

已更新 1 行。

暂时不COMMIT。

对于事务B，执行：

SQL> UPDATE DEPT SET LOC='BOSTON2' WHERE DEPTNO=40;

会发现事务B一直在等待，没有出现“已更新”的提示。然后事务A执行COMMIT，事务B才显示已更新。

此时在事务A执行SELECT：

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON1

SQL>

在事务B也执行SELECT：

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON2

会发现结果不一样。

然后事务B执行COMMIT，于是两个结果都一样了，变成了：

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON2

SQL>

SERIALIZABLE

开启两个事务，对于事务A，执行：

SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

事务处理集。

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

然后在事务B执行：

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> UPDATE DEPT SET LOC='BOSTON2' WHERE DEPTNO=40;

已更新 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON2

SQL>

再在事务A执行：

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

发现事务B所做的UPDATE，尽管已经COMMIT了，对事务A还是不可见的。就像单用户数据库。

当一个事务需要更新数据并进行可重复读取时就需要使用序列化事务。