CODEWITHSUNDEEP
oracle11g
Victor
Victor

Reputation: 17097

does an update statement in oracle hold a lock even if no rows were updated

If I run an update statement in oracle that says '0 rows updated' because it does not match the where clause and i do not commit, does it still hold the lock on any protion of the table? My guess is no, but i cannot prove it.

Upvotes: 1

Views: 3072

Answers (3)

David Aldridge
David Aldridge

Reputation: 52376

As documented:

The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE statements are as follows:

  • The transaction that contains a DML statement acquires exclusive row locks on the rows modified by the statement. Other transactions cannot update or delete the locked rows until the locking transaction either commits or rolls back.

  • The transaction that contains a DML statement does not need to acquire row locks on any rows selected by a subquery or an implicit query, such as a query in a WHERE clause. A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of.

  • A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.

  • In addition to the necessary exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the affected rows. If the containing transaction already holds a share, share row exclusive, or exclusive table lock for that table, the row exclusive table lock is not acquired. If the containing transaction already holds a row share table lock, Oracle Database automatically converts this lock to a row exclusive table lock.

The table lock is necessary to protect the table from changes while the update is in progress, and if no rows are modified by the update then this is the only lock applied.

If the statement carries out an update on a row that results in no change to that row (eg SET DATE_OF_BIRTH = NULL for a row where date_of_birth is already nullthe row lock is still taken.

Upvotes: 0

Abhijith Nagarajan
Abhijith Nagarajan

Reputation: 4030

It does not hold any lock.

Simple test case

  1. Open two oracle sessions (sqlplus or sqldeveloper or by any other means)
  2. update table1 where clause (session 1)
  3. update table1 where clause (session 2)
  4. Commit from session1 (if there is a table lock then this should hang)
  5. commit from session2 (if there is a table lock then this statement will cause deadlock)

Condition is same with row locking (both sessions deleting same row if exists) as well.

Upvotes: 0

user330315
user330315

Reputation:

No row locks are held after an update that didn't update anything (after all, if there is no row, which one should be locked?)

Your transaction will still have some share locks (on the table) but those are only there to prevent other transactions from altering the table. It's basically the same kind of "lock" a select statement acquires on the table.

From the manual:

A row is locked only when modified by a writer.

And further down in the manual:

A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified

So if no row is changed, there can't be a lock.

Upvotes: 1

Related Questions