A silent but important change

While learning Oracle and working with it, sometimes I’m getting really surprised. Especially when I discover odd differences between reality and what documentation states.

Few months ago I noticed one silent change in Oracle locking strategy that was introduced since Oracle 9.2.0.6.0 (and 10.1.0.4.0, for 10g family). I called it “silent” because it’s not reflected anywhere in Oracle documentation yet despite the fact that it may break logic of some applications where manual locking statements (e.g. LOCK TABLE) are used. And what is more confusing, there are only two Metalink bug notes (4115353 and 4252394), from which one can conclude that locking behavior was changed intentionally, to fix some locking-related bug in RAC environments.

Here are details. One can repeat this small test in Oracle 9.2.0.5.0 and earlier:

SQL> show release
release 902000500
SQL>
SQL> create table t(x) as
  2  select * from dual;
 
Table created.
 
SQL> select * from t for update;
 
X
-
X
 
SQL> select type, lmode
  2    from v$lock
  3   where request = 0 and
  4   sid = (select sid
  5            from v$mystat
  6           where rownum = 1);
 
TY      LMODE
-- ----------
TX          6
TM          2

So everything is as expected: SELECT ... FOR UPDATE applied TX lock in mode 6 as well as TM lock in mode 2 (also called "row share" lock).

But look what will happen if we rerun our small test in 9.2.0.6.0:

SQL> show release
release 902000600
 
...
< rerun "create table" and 
  "select ... for update" here >
...
 
SQL> select type, lmode
  2    from v$lock
  3   where request = 0 and
  4   sid = (select sid
  5            from v$mystat
  6           where rownum = 1);
 
TY      LMODE
-- ----------
TX          6
TM          3

Well, that was a bit surprising. Now Oracle applied TM mode 3 lock (e.g. "row exclusive" table-level lock) as we issued SELECT ... FOR UPDATE statement.

The impact of this change on existing applications is not so critical unless they have implemented some manual locking strategy. For example, look what may happen in 9.2.0.6.0 and above:

Session #1> show release
release 902000600
Session #1>
Session #1> lock table t in share mode;
 
Table(s) Locked.
 
Session #2> show release
release 902000600
Session #2>
Session #2> select * from t for update nowait;
select * from t for update nowait
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SELECT ... FOR UPDATE NOWAIT statement in session 2 failed, but it succeeds in Oracle 9.2.0.5.0 and earlier (10.1.0.3.0 and earlier).

Small change, but it may cause a bit of headache to someone who doesn’t know about it.

Andrew.