set transaction 命令有以下功能
1、给一个事物命名
set transaction name “tname”;
2、指定一个事物使用回滚段
set transaction use rollback segment “回滚段名";
3、建立事物隔离级
set transaction isolation level serializable;如果在设置隔离级前有更新表t1没有提交,在设置隔离级后,更新表会等待,先前的提交后,隔离级里的会报错);
set transaction level read commited(默认情况的设置,如果在设置隔离级前有更新表t1没有提交,在设置隔离级后,更新表会等待,先前的提交后,隔离级里的会执行);
4、指定事物是read-only or read/write
set transaction read only;(只能读,不能进行dml操作)(只能看到设置事物前数据的变化,看不到设置事物后数据的改变)
set transaction read write;(默认情况是read write)(只能看到设置事物后数据的变化)
Restriction on Read-only Transactions Only the following statements are permitted in a read-only transaction:
-
Subqueries—
SELECT
statements without thefor_update_clause
-
LOCK
TABLE
-
SET
ROLE
-
ALTER
SESSION
-
ALTER
SYSTEM
DECLARE
daily_order_total NUMBER(12,2);
weekly_order_total NUMBER(12,2);
monthly_order_total NUMBER(12,2);
BEGIN
COMMIT; -- end previous transaction
SET TRANSACTION READ ONLY NAME 'Calculate Order Totals';
SELECT SUM (order_total)
INTO daily_order_total
FROM orders
WHERE order_date = SYSDATE;
SELECT SUM (order_total)
INTO weekly_order_total
FROM orders
WHERE order_date = SYSDATE - 7;
SELECT SUM (order_total)
INTO monthly_order_total
FROM orders
WHERE order_date = SYSDATE - 30;
COMMIT; -- ends read-only transaction
END;
/
set transaction语法:
一个事物隐式开始是以获取tx锁。例如 1、修改数据
2、SELECT
...FOR
UPDATE
3、使用
SET
TRANSACTION
statement or theDBMS_TRANSACTION
package
以commited 或者rollback,或者执行了ddl语句隐式提交结束事物;
试验1:
1在一个session 1中执行set transaction read only,查看表t1中的数据
2在sesssion 2 中修改数据,并且提交
3在session 1中查看表t1中的数据
4 在session 1 中查看数据
SQL> set transaction read only;
事务处理集。
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- ------------------ ---------- -------------- ---------- ---------- ----------
1 a 777 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择13行。
在session 2中修改表中的数据:
SQL> update t1 set sal=0 ;
已更新13行。
SQL> commit;
提交完成。
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
1 a 0 20
7369 SMITH CLERK 7902 17-12月-80 0 20
7499 ALLEN SALESMAN 7698 20-2月 -81 0 300 30
7521 WARD SALESMAN 7698 22-2月 -81 0 500 30
7566 JONES MANAGER 7839 02-4月 -81 0 20
7654 MARTIN SALESMAN 7698 28-9月 -81 0 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 0 30
7782 CLARK MANAGER 7839 09-6月 -81 0 10
7839 KING PRESIDENT 17-11月-81 0 10
7844 TURNER SALESMAN 7698 08-9月 -81 0 0 30
7900 JAMES CLERK 7698 03-12月-81 0 30
7902 FORD ANALYST 7566 03-12月-81 0 20
7934 MILLER CLERK 7782 23-1月 -82 0 10
已选择13行。
查看session 1中的数据
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- ------------------ ---------- -------------- ---------- ---------- ----------
1 a 777 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- ------------------ ---------- -------------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择13行。
在session 1中更新数据:
SQL> update t1 set sal=2;
update t1 set sal=2
*
第 1 行出现错误:
ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作
结论:1、从上述试验可以发现 设置了set transaction read only后,其他session修改t1表工资,在当前事物中的所有查询,只能看到这个事物开始前的所有变化,看不到这个事物开始后的所有表的变化。
2、set transaction read only 的当前session中只能读取数据,不能进行dml操作;
set transaction read only 不适合用在sys用户。
试验2:
1在一个session 1中执行set transaction read write,查看表t1中的数据
2在sesssion 2 中修改数据,并且提交
3在session 1中查看表t1中的数据
SQL> set transaction read write name 'aa';
事务处理集。
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- ------------------ ---------- -------------- ---------- ---------- ----------
1 a 0 20
7369 SMITH CLERK 7902 17-12月-80 0 20
7499 ALLEN SALESMAN 7698 20-2月 -81 0 300 30
7521 WARD SALESMAN 7698 22-2月 -81 0 500 30
7566 JONES MANAGER 7839 02-4月 -81 0 20
7654 MARTIN SALESMAN 7698 28-9月 -81 0 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 0 30
7782 CLARK MANAGER 7839 09-6月 -81 0 10
7839 KING PRESIDENT 17-11月-81 0 10
7844 TURNER SALESMAN 7698 08-9月 -81 0 0 30
7900 JAMES CLERK 7698 03-12月-81 0 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- ------------------ ---------- -------------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81 0 20
7934 MILLER CLERK 7782 23-1月 -82 0 10
已选择13行。
在session 2中更新sal
SQL> update t1 set sal=1 ;
已更新13行。
SQL> commit;
提交完成。
在session 1 中查看结果
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- ------------------ ---------- -------------- ---------- ---------- ----------
1 a 1 20
7369 SMITH CLERK 7902 17-12月-80 1 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1 500 30
7566 JONES MANAGER 7839 02-4月 -81 1 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 1 30
7782 CLARK MANAGER 7839 09-6月 -81 1 10
7839 KING PRESIDENT 17-11月-81 1 10
7844 TURNER SALESMAN 7698 08-9月 -81 1 0 30
7900 JAMES CLERK 7698 03-12月-81 1 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- ------------------ ---------- -------------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81 1 20
7934 MILLER CLERK 7782 23-1月 -82 1 10
已选择13行。
结论:事物默认情况是read write的,从上述试验可以发现 设置了set transaction read write后,其他session修改工资,在当前事物中的所有查询,可以看到这个事物开始后的所有变化。
试验3:
SQL> set transaction name 'aa';
SQL> update t1 set sal=2;
已更新13行。
SQL> select xidusn,xidslot,xidsqn,used_ublk,used_urec,name from v$transaction;
XIDUSN XIDSLOT XIDSQN USED_UBLK USED_UREC NA
---------- ---------- ---------- ---------- ---------- --
25 4 241 1 1 aa
结论:可以看到事物的命名
实验:set transaction isolation level serializable name 'aa';
1在session 2中更新t1表工资,并且不提交
2在session 1 中开始串行隔离级,并且更新t1表工资,不提交
3 session 2 提交事物
4 查看session 1
session 2:
SQL> update t1 set sal=1;
已更新13行。
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
1 a 1 20
7369 SMITH CLERK 7902 17-12月-80 1 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1 500 30
7566 JONES MANAGER 7839 02-4月 -81 1 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 1 30
7782 CLARK MANAGER 7839 09-6月 -81 1 10
7839 KING PRESIDENT 17-11月-81 1 10
7844 TURNER SALESMAN 7698 08-9月 -81 1 0 30
7900 JAMES CLERK 7698 03-12月-81 1 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81 1 20
7934 MILLER CLERK 7782 23-1月 -82 1 10
已选择13行。
session 1:
SQL> set transaction isolation level serializable name 'aa';
事务处理集。
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- ------------------ ---------- -------------- ---------- ---------- ----------
1 a 777 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------- ------------------ ---------- -------------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择13行。
SQL> update t1 set sal=2;
等待中
session 2:
commit
查看session 1:
SQL> update t1 set sal=2;
update t1 set sal=2
*
第 1 行出现错误:
ORA-08177: 无法连续访问此事务处理
结论:如果在设置串行隔离级前如果修改了t1表,并且没有提交,再设置了串行隔离级后,修改t1表就会出现等待,并且如果设置隔离级之前的修改提交后,此表在隔离级里是不能被修改,会报错,这样保证了数据的一致性。
实验:set transaction isolation level READ COMMITTED
name 'aa';
1在session 2中更新t1表工资,并且不提交
2在session 1 中开始隔离级 READ COMMITTED
,并且更新t1表工资,不提交
3 session 2 提交事物
4 查看session 1
session 2:
SQL> update t1 set sal=1;
已更新13行。
session 1 :
SQL> set transaction isolation level READ COMMITTED name 'aa';
事务处理集。
SQL> select *from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ------- ------------------ ---------- -------------- ---------- ---------- -
1 a 777
7369 SMITH CLERK 7902 17-12月-80 800
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
7566 JONES MANAGER 7839 02-4月 -81 2975
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
7698 BLAKE MANAGER 7839 01-5月 -81 2850
7782 CLARK MANAGER 7839 09-6月 -81 2450
7839 KING PRESIDENT 17-11月-81 5000
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
7900 JAMES CLERK 7698 03-12月-81 950
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ------- ------------------ ---------- -------------- ---------- ---------- -
7902 FORD ANALYST 7566 03-12月-81 3000
7934 MILLER CLERK 7782 23-1月 -82 1300
已选择13行。
SQL> update t1 set sal=2;
出现等待
session 2:
SQL> commit;
提交完成。
查看session 1:
SQL> update t1 set sal=2;
已更新13行。
结论:如果在设置read commited隔离级前如果修改了t1表,并且没有提交,再设置了read commited隔离级后,修改t1表就会出现等待,并且如果设置隔离级之前的修改提交后,此表在隔离级里的等待消失,更新数据,这样保证了数据的一致性。
查看当前session的事物隔离级:
select decode(Bitand(Flag,268435456),268435456,'Serializable','Non-Serializable')
From V$Transaction, V$Session
Where Taddr=Addr and
Sid=(SELECT USERENV('SID') FROM DUAL);
DECODE(BITAND(FLAG,268435456),26
--------------------------------
Non-Serializable
SQL> set transaction isolation level serializable name 'aa';
事务处理集。
SQL> update t1 set sal=1;
已更新13行。
SQL> select decode(Bitand(Flag,268435456),268435456,'Serializable','Non-Serializable')
2 From V$Transaction, V$Session
3 Where Taddr=Addr and
4 Sid=(SELECT USERENV('SID') FROM DUAL);
DECODE(BITAND(FLAG,268435456),26
--------------------------------
Serializable
查看其他session是否隔离级
SQL> select decode(Bitand(Flag,268435456),268435456,'Serializable','Non-Serializable')
2 From V$Transaction, V$Session
3 Where Taddr=Addr and
4 Sid=162;
https://cn.forums.oracle.com/forums/thread.jspa?messageID=10713568