oracle SET TRANSACTIONNAMEREAD ONLY和READ WRITEISOLATION LEVELUSE ROLLBACK SEGMENT

80 阅读 0 评论 53 点赞

我是靠谱客的博主爱笑电脑，最近开发中收集的这篇文章主要介绍oracle SET TRANSACTIONNAMEREAD ONLY和READ WRITEISOLATION LEVELUSE ROLLBACK SEGMENT，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

详细文档：http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10005.htm#SQLRF01705

语法：

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还是不可见的。就像单用户数据库。

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