oracle11g

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.


Solution

  • 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.