DDL/DML操作在Read committed和Serializable 事务隔离等级中的示例

来源：互联网发布：h3c mac acl 编辑：程序博客网时间：2024/06/08 06:39

Read committed和Serializable 事务隔离等级概念介绍：

Read committed读已提交事务隔离等级：Oracle 默认隔离等级，支持不可重复读和幻读。

Serializable 事务隔离等级：只能看到事务开始时所有提交的改变和自身的改变，不支持不可重复读和幻读，其他用户的影响不了这个事务
适合用于修改操作比较短的事务。事务中存在多条SQL语句，需要数据从事务开始时就保持一致性。
Read committed与Serializable比较
Read committed                                        Serializable Transactions
支持SQL92标准                                           支持SQL92标准
读取物化视图维护语句级一致性                   维护事务级一致性
事务一致性支持语句级                               支持事务级
行级锁支持                                                 支持
读锁定写不支持                                           不支持
写锁定读不支持                                           不支持
非行级锁不支持                                           不支持
行级锁    支持                                              支持
事务锁等待支持                                           支持

先来个我遇到的错误：
事务隔离等级-作用域是一个事务，超出这个事务就无效。事务未完成（COMMIT或ROLLBACK）时，再指定事务隔离等级会出错，如下：
SQL> set transaction read only;
set transaction read only

ORA-01453: SET TRANSACTION must be first statement of transaction
SQL> rollback;
Rollback complete
SQL> set transaction read only;
Transaction set
因为之前数据没提交。可以COMMIT或ROLLBACK来解决。
说明：比如13:13:18 SQL> 提示符中的时间，因为运行命令前未按回车，所以时间是早些时候的，可能会两个会话中出现时间对不上的情况。

Serializable Transactions演示：----SYS用户不能设置此隔离等级-报ORA-08178

1.1 Serializable Transactions只能看到事务开始时所有提交的改变和自身的改变，并且作用域是一个事务，超出这个事务就无效。

会话2 在13:14:21时修改a=755这条数据为a=333，会话1在13:14:26执行的查询，结果依然是755.使用COMMIT，来结束事务也结束了serializable作用域，所以就能看到最新数据了。

会话1：

13:13:18 SQL> set transaction isolation level serializable;
Transaction set
13:13:56 SQL> select * from test;
      A
----------
   755
13:14:00 SQL>
13:14:24 SQL> select * from test;
      A
----------
   755
13:14:26 SQL> commit;
Commit complete
13:14:43 SQL> select * from test;
      A
----------
   333
13:30:02 SQL> set transaction isolation level serializable;
Transaction set
13:30:13 SQL> update test set a=388 where a=333;
1 row updated
13:30:18 SQL> commit;
Commit complete
13:30:21 SQL> select * from test;
      A
----------
   388

会话2：
13:14:03 SQL> update test set a=333 where a=755;
1 row updated
13:14:18 SQL> commit;
Commit complete
13:14:21 SQL> select * from tab;
TNAME                         TABTYPE  CLUSTERID
------------------------------ ------- ----------
SYS_TEMP_FBT                TABLE
TEST                         TABLE
13:14:34 SQL> select * from test;
      A
----------
   333

1.2 各种DDL操作对Serializable事务隔离等级的影响

truncate
会话2：
13:35:24 SQL> select * from test;
      A
----------
   388
13:35:29 SQL> truncate table test;
Table truncated
13:35:39 SQL> select * from test;
      A
----------

13:35:41 SQL>
会话1：
13:35:02 SQL> set transaction isolation level serializable;
Transaction set
13:35:16 SQL> select * from test;
      A
----------
   388
13:35:17 SQL>
13:35:45 SQL> select * from test;
      A
----------
13:35:47 SQL>

alter table:
会话1：
12:49:02 SQL> set transaction isolation level serializable;
Transaction set
12:49:42 SQL> select * from test;
      A
----------
   777
12:49:47 SQL> select * from test;
      A B
---------- --------------------
   777
12:52:05 SQL> select * from test;
      A B
---------- --------------------
   777
会话2：
12:47:49 SQL> select * from test;
      A
----------
   777

12:49:09 SQL> alter table test add b varchar2(20);
Table altered
12:50:23 SQL> select * from test;

      A B
---------- --------------------
   777

DROP TABLE：

会话1：

20:42:24 BYS@bys1>drop table test1;
Table dropped.
20:42:29 BYS@bys1>
会话2：

20:41:13 BYS@bys1>set transaction isolation level serializable;
Transaction set.
20:42:02 BYS@bys1>select * from test1;
OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
ICOL$                                  20
I_USER1                                46
CON$                                   28
UNDO$                                  15
C_COBJ#                                29
I_OBJ#                                  3
PROXY_ROLE_DATA$                       25
I_IND1                                 41
I_CDEF2                                54
20:42:12 BYS@bys1>select * from test1;
select * from test1
              *
ERROR at line 1:
ORA-00942: table or view does not exist
20:42:41 BYS@bys1>commit;
Commit complete.
20:42:45 BYS@bys1>

Read-only事务隔离等级：只能看到事务开始时所有提交的改变，自身不允许DML操作。

2.1 Read-only事务隔离等级只能看到事务开始时所有提交的改变，自身不允许DML操作。

会话1：
13:41:56 SQL> set transaction read only;
Transaction set
13:42:05 SQL> update test set a=755 where a=9;
update test set a=755 where a=9

ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
13:42:16 SQL>
13:42:54 SQL> select * from test;
      A
----------
      9
会话2：
13:42:29 SQL> select * from test;
      A
----------
      9
13:42:30 SQL> update test set a=755 where a=9;
1 row updated
13:42:42 SQL> commit;
Commit complete
13:42:45 SQL> select * from test;
      A
----------
   755

2.2 DDL操作对Read-only事务隔离等级的影响

truncate：设置为READ ONLY的事务会受此操作影响，查询报错。

会话1：
13:56:10 SQL>  set transaction read only;
Transaction set
13:56:13 SQL> select * from test;
      A B
---------- --------------------
   755
13:56:36 SQL> select * from test;
select * from test

ORA-01466: unable to read data - table definition has changed
13:56:49 SQL> commit;
Commit complete
13:58:41 SQL> select * from test;
      A B
---------- --------------------

会话2：
13:56:19 SQL> select * from test;
      A B
---------- --------------------
   755
13:56:21 SQL> truncate table test;
Table truncated
13:56:40 SQL> select * from test;
      A B
---------- --------------------

13:56:43 SQL>

alter table add列：这个操作不受read only事务隔离等级影响，可以查看到。

会话1：
13:46:38 SQL> set transaction read only;
Transaction set
13:46:47 SQL> select * from test;
      A
----------
   755
13:46:54 SQL>
13:47:51 SQL> select * from test;
       A B
---------- --------------------
   755
会话2：
13:46:58 SQL> select * from test;
      A
----------
   755
13:47:00 SQL> alter table test add b varchar2(20);
Table altered
13:47:43 SQL> select * from test;
      A B
---------- --------------------
   755

alter table drop列：这个操作受read only事务隔离等级影响，不可以查看到，会报错。

会话1：
13:09:50 SQL> select * from test;
select * from test

ORA-01466: unable to read data - table definition has changed
13:13:09 SQL> commit;
Commit complete
13:13:17 SQL> select * from test;
      A
----------
   755
会话2：
13:09:39 SQL> alter table test drop column b;
Table altered
13:10:18 SQL> select * from test;
      A
----------
   755