isolation_level

最新推荐文章于 2025-03-04 15:10:17 发布

jgmydsai

最新推荐文章于 2025-03-04 15:10:17 发布

阅读量882

点赞数

本文介绍如何在Oracle数据库中设置和查询事务隔离级别，包括READ COMMITTED和SERIALIZABLE两种模式，并通过实例演示了如何使用特定的SQL语句来查看当前会话的隔离级别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天在一qq群里学到查isolation_level状态的语句

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as test@192.168.1.118:1521/orcl

SQL> 
SQL> alter session set isolation_level=read committed;
Session altered
SQL> update dept set  loc='SHANGHAI' where deptno=30;
1 row updated
SQL> select sid,
  2         serial#,
  3         flag,
  4         CASE
  5           WHEN BITAND(t.flag, POWER(2, 28)) = 0 THEN
  6            'READ COMMITTED'
  7           ELSE
  8            'SERIALIZABLE'
  9         END AS ISOLATIONLEVEL
 10    from V$transaction t, v$session s
 11   where t.addr = s.taddr
 12     AND audsid = USERENV('SESSIONID');
       SID    SERIAL#       FLAG ISOLATIONLEVEL
---------- ---------- ---------- --------------
       149         50       7683 READ COMMITTED

SQL> alter session set isolation_level=serializable;
Session altered
SQL> update dept set loc='CHENGDU' where deptno=30;
1 row updated
SQL> select sid,
  2         serial#,
  3         flag,
  4         CASE
  5           WHEN BITAND(t.flag, POWER(2, 28)) = 0 THEN
  6            'READ COMMITTED'
  7           ELSE
  8            'SERIALIZABLE'
  9         END AS ISOLATIONLEVEL
 10    from V$transaction t, v$session s
 11   where t.addr = s.taddr
 12     AND audsid = USERENV('SESSIONID');
       SID    SERIAL#       FLAG ISOLATIONLEVEL
---------- ---------- ---------- --------------
        20         66  268443139 SERIALIZABLE