CODEWITHSUNDEEP
databaseoracle-databaselockingcommit
tarun_tenniso
tarun_tenniso

Reputation: 214

0 rows Deleted/Updated/Inserted require COMMIT in ORACLE?

In an Oracle database, I just ran a query and I get

0 rows deleted.

Does a "0 row" DML operation lock a table in ORACLE and do I need to COMMIT if yes?

Upvotes: 1

Views: 2170

Answers (1)

user5683823
user5683823

Reputation:

The answer is yes, and yes. Oracle locks rows that are modified etc. - so that won't happen if no rows are affected; but Oracle also creates a subexclusive table lock regardless of any rows that may or may not be modified, and it releases such locks only on COMMIT or ROLLBACK or equivalent. (Of course, in this case you can also ROLLBACK - same effect since no rows are affected).

Demonstrated easily in the following session:

SQL> create table xx as
  2  select level lvl from dual connect by level <= 5;

Table created.

SQL> select * from xx;

       LVL
----------
         1
         2
         3
         4
         5

SQL> select a.object_name, b.locked_mode
  2  from   user_objects a join v$locked_object b
  3  on a.object_id = b.object_id;

no rows selected

SQL> delete from xx where lvl = 10;

0 rows deleted.

SQL> select a.object_name, b.locked_mode
  2  from   user_objects a join v$locked_object b
  3  on a.object_id = b.object_id;


OBJECT_NAME     LOCKED_MODE
--------------- -----------
XX                        3

SQL> commit;

Commit complete.

SQL> select a.object_name, b.locked_mode
  2  from   user_objects a join v$locked_object b
  3  on a.object_id = b.object_id;

no rows selected

Upvotes: 5

Related Questions