ORA-08177带锁的桌子 [英] ORA-08177 with locked table

问题描述

之后

create table l (id int primary key, val int);
insert into l values (0, 0);

并初始化两个oracle会话

and initializing two oracle sessions

alter session set isolation_level=serializable;

以下连续操作导致 ORA-08177: can't serialize access for this transaction .

the following succession leads to a ORA-08177: can't serialize access for this transaction.

  session 1                           session 2                          
 -----------------------------------+-----------------------------------
  lock table l in exclusive mode                                         
 -----------------------------------+-----------------------------------
                                      lock table l in exclusive mode     
 -----------------------------------+-----------------------------------
  update l set val = 1 where id = 0   [blocked]                          
 -----------------------------------+-----------------------------------
  commit                              [blocked]                          
 -----------------------------------+-----------------------------------
                                      update l set val = 2 where id = 0  
 -----------------------------------+-----------------------------------
                                      [ORA-08177]                        

那是为什么?对我来说，它看起来很完美.

Why is that? It looks perfectly serial to me.

推荐答案

当可序列化事务试图更改另一个事务(可序列化与否)已更改的数据(或者甚至位于与另一个事务已经存在的数据相邻的数据)时在同一数据块中更改)开始可序列化事务后，您会收到ORA-08177错误消息.将事务隔离级别设置为serializable时，需要牢记两个重要事项:

When serializable transaction is trying to change data that another transaction(serializable or not) has already changed (or even data that reside near to the data another transaction has already changed in the same data block) after that serializable transaction began, you'll receive the ORA-08177 error message. When you are setting transaction isolation level to serializable, there are two important things needed to be kept in mind:

1. 可序列化事务只有在可序列化事务(而不是语句)开始时已经提交了对另一个事务所做的记录更改的情况下，才可以更改记录.
2. 读取一致性扩展到事务级别，而不是语句级别，这是将事务隔离级别设置为read committed(默认事务隔离级别)时的情况.

1. Serialzable transaction can change a record only if changes to that record made by another transaction(s) were already committed by the time the serializable transaction (not the statement) began.
2. Read consistency is extended to transaction level, not statement level as it is when transaction isolation level is set to read committed(default transaction isolation level).

基本上，当可序列化事务开始时，它会获取自己的数据快照，已提交的数据，并且仅在该快照上操作，并且仅看到自己的已提交数据-在获取新的快照之前，无法看到其他事务数据的提交.数据.在结束(提交或回滚)一个可序列化事务并启动另一个可序列化事务之后，将获取新的数据快照.

Basically, when serializable transaction begins, it acquires its own snapshot of data, committed data, and operates on that snapshot only, and sees its own committed data only - cannot see committed by another transaction data until it acquires a new snapshot of data. And the new snapshot of data will be acquired after you end (commit or rollback) one serializable transaction and start another one.

导致ORA-08177错误的简单示例:

-- /* test table */
SQL> create table t1(col) as 
       select 3 from dual;
Table created.

-- sqlplus session #1                     sqlplus #session 2
----------------------------------------------------------------------------
SQL> alter session 
       set isolation_level=serializable;

                                          SQL> alter session set
                                               isolation_level=serializable;

/*
   You start serializable transaction by locking the t1 table in 
   exclusive mode. 

 */

-- serializable transaction #1

SQL> lock table t1 in exclusive mode;    -- serializable transaction # 2
                                         -- snapshot of t1 has been acquired
SQL> update t1 set col = 5               SQL> lock table t1 in exclusive mode;
      where col = 3; 
                                         -- this update does not see changes 
1 row updated.                           -- transaction #1 has already made 
                                         -- to t1's row where col = 3. 
                                         SQL> update t1 set col = 7 
SQL> commit;                                   where col = 3;
                                         ERROR at line 1:
Commit complete.                         ORA-08177: can't serialize access 
                                                    for this transaction 

在上述情况下，Oracle为了使数据库保持一致模式会向您抛出ORA-08177，因为它知道以下事实:可序列化事务#1更改了并且可序列化交易#2尚未获取新的数据快照，而是在旧的快照上进行操作.

In the above situation Oracle, in order to keep your database in a consistent mode throws the ORA-08177 at you, because it's aware of the fact that the serializable transaction #1 made changes to the row(s) where col = 3 and serializable transaction #2 has not acquired a new snapshot of data, yet - operates on old one.

同时使用显式排他表锁和可序列化的事务似乎非常非常过分: b)为了避免ORA-08177，您需要提交或回滚引起ORA-08177错误的事务并再次重试，并且一旦发出提交或回滚，将立即释放独占表锁.

It seems to be very, very excessive to use explicit exclusive table lock and serializable transaction together: a) tremendous hit upon concurrency; b) In order to avoid the ORA-08177 you need to commit or rollback transaction that raises ORA-08177 error and retry it again, and as soon as you issued commit or rollback, the exclusive table lock would be immediately released.

这篇关于ORA-08177带锁的桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！