Oracle的事务隔离级别大概搞清楚了，说明还是要多翻翻Concepts

Isolation	Description	Description(Chinese)
Read committed	This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data. Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms.	这是默认的隔离级别。每个查询在提交之前查询到的数据是在查询开始前的数据，注意并不是事务开始之前的数据。是不读脏数据的但是并不阻止其他事务修改相同的数据。有可能在两次查询见数据已经被修改。所以该隔离级别可能出现不可重复读取和不存在读取的现象
Serializable	Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms	每个查询可以看到的数据是事务开始之前的数据，并包括本事务中的INSERT,UPDATE和DELETE的数据不会出现不可重复读取和不存在读取的现象
Read-only	Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements	同Serializable相同，但事务中不允许执行INSERT,UPDATE和DELETE

改变事务的隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

改变会话的隔离级别

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

实验：
READ COMMITTED

SQL> conn scott/tiger

已连接。

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> set transaction isolation level read committed;

事务处理集。

Time	Session A	Session B
Time1	insert deptno= 50
Time2	select * from dept 可以看到deptno= 50的数据
Time3		select * from dept 看不到deptno= 50的数据
Time4		update dept set deptno = deptno-10 commit
Time5	select * from dept 看到的数据为deptno=0，10，20，30，50的数据遇见了不可重复读取的现象
Time6		insert deptno=60
Time7	select * from dept 看到的数据为deptno=0，10，20，30，50,60的数据遇见了不存在读取的现象

SQL> set transaction isolation level serializable;

事务处理集。

Time	Session A	Session B
Time1	insert deptno= 50
Time2	select * from dept 可以看到deptno= 50的数据
Time3		select * from dept 看不到deptno= 50的数据
Time4		update dept set deptno = deptno-10 报错：ERROR 位于第 1 行: ORA-08177: 无法连续访问此事务处理 You will get this message whenever you attempt to update a row that has changed since your transaction began---当你试图修改其他事务已经更改的行
Time5	select * from dept 看到的数据为deptno=10，20，30，40，50的数据与Time2看到的数据一致
Time6		insert deptno=60 commit;
Time7	看到的数据为deptno=10，20，30，40，50的数据与Time2看到的数据一致

关于serializable的误解

serializable不意味着事务是以串行的方式一个接一个的执行的

expert one-on-one中文版关于这个实验不知道在翻译什么，看了英文版的豁然开朗

session A：

SQL> set transaction isolation level serializable;

事务处理集。

SQL> insert into a select count(*) from b;

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from a;

X

----------

0

session B：

SQL> set transaction isolation level serializable;

事务处理集。

SQL> insert into b select count(*) from a;

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from b;

Y

----------

0

两个表中数据都是0，如果事务是串行的，后一个执行insert的语句的值应该为1

关于如何查询隔离级别

SQL> alter session set isolation_level= serializable;

Session altered.

SQL> delete from t1 where rownum = 1;

1 row deleted.

SQL> select flag from v$transaction;

                  FLAG

                   ----------

268439043

SQL> commit;

Commit complete.

SQL> alter session set isolation_level = READ COMMITTED;

Session altered.

SQL> delete from t1 where rownum = 1;

1 row deleted.

SQL>  select flag from v$transaction;

FLAG

----------

1.JPG

ITPub博客

Oracle的事务隔离级别

最新文章