Skip to Main Content
  • Questions

  • Why does table get locked when no rows are updated?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Christoph.

Asked: January 07, 2005 - 9:55 am UTC

Last updated: January 08, 2005 - 12:48 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom,
Can you please explain what exactly gets locked when an update statement updates zero rows.
In the example below i issue an update statement that affects zero rows in two different sessions. Where i expected session 1 to block session 2, session 2 actually completed the transaction. There are exclusive row locks on non-existent rows.

Session 1:

sess1>create table t1 (f1 varchar2(10));

Table created.

sess1>update t1 set f1 = 'x' where 1=2;

0 rows updated.

sess1>SELECT b.object_name, a.locked_mode
2 FROM v$locked_object a, dba_objects b
3 WHERE a.object_id = b.object_id;

OBJECT_NAME LOCKED_MODE
------------------------------ -----------
T1 3

sess1>


Session 2:

sess2>update t1 set f1 = 'x' where 1=2;

0 rows updated.

sess2>SELECT b.object_name, a.locked_mode
2 FROM v$locked_object a, dba_objects b
3 WHERE a.object_id = b.object_id
4 ;

OBJECT_NAME LOCKED_MODE
------------------------------ -----------
T1 3
T1 3

sess2>

Can you please explain the locking and why Oracle holds these locks when they appear unnecessary?

Thanks,
Christoph

and Tom said...

they are not unnecessary -- they are there in order to prevent someone from altering the structure of the table your transaction is now dependent on!

You might want to look at dba_locks which decodes the lock mode

ops$tkyte@ORA9IR2> create table t ( x int );

Table created.

ops$tkyte@ORA9IR2> update t set x = 0;

0 rows updated.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec print_table( 'select * from dba_locks where session_id = (select sid from v$mystat where rownum=1)' );
.SESSION_ID : 10
.LOCK_TYPE : DML
.MODE_HELD : Row-X (SX)
.MODE_REQUESTED : None
.LOCK_ID1 : 36558
.LOCK_ID2 : 0
.LAST_CONVERT : 0
.BLOCKING_OTHERS : Not Blocking
-----------------

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> exec print_table( 'select * from v$lock where sid = (select sid from v$mystat where rownum=1)' );
.ADDR : 5B8A00C0
.KADDR : 5B8A00D4
.SID : 10
.TYPE : TM
.ID1 : 36558
.ID2 : 0
.LMODE : 3
.REQUEST : 0
.CTIME : 0
.BLOCK : 0
-----------------

PL/SQL procedure successfully completed.


It prevents other sessions from getting an exclusive lock on the table, nothing else really. Until you commit, you do in fact have an outstanding transaction against the table. We get that lock BEFORE the update happens -- in order to be certain that the update can in FACT happen (that there are no other locks preventing it from happening). This lock then prevents INCOMPATIBLE operations from taking place until we commit.

You might want to check out:

</code> http://docs.oracle.com/cd/B10501_01/server.920/a96524/c21cnsis.htm#3138 <code>




Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library