Locks placed with INSERT, UPDATE, and DELETE statements

When you execute an INSERT, UPDATE, or DELETE statement, the database server uses exclusive locks. An exclusive lock means that no other users can update or delete the item until the database server removes the lock.

In addition, no other users can view the row unless they are using the Dirty Read isolation level.

When the database server removes the exclusive lock depends on whether the database supports transaction logging:

• If the database supports logging, the database server removes all exclusive locks when the transaction completes (commits or rolls back).
• If the database does not support logging, the database server removes all exclusive locks immediately after the INSERT, MERGE, UPDATE, or DELETE statement completes, except when the lock is on the row that is currently being fetched into an update cursor.

  In this situation, the lock is retained during the fetch operation on the row, but only until the server fetches the next row, or until the server updates the current row by promoting the lock to an exclusive lock.

In a nonlogging database, the promotable update lock on a row fetched for update can be released by a DDL operation on the database while the INSERT, MERGE, UPDATE, or DELETE statement that originally created the lock is still running. To reduce the risk of data corruption if a concurrent session modifies the unlocked row, restrict operations that use promotable update locks to databases that support transaction logging.