The DELETE operation follows almost the same steps as the INSERT operation, except in the opposite order. As with insertions
and updates, this sequence of operations is followed for all transactions regardless of their isolation level.
- Acquire a shared schema lock on the table, if one is not already held.
- Acquire an intent-to-write table lock on the table, if one is not already held.
- Identify candidate rows to be updated. As rows are scanned, they are locked. The default locking behavior is described in
Isolation levels and consistency.
At isolation levels 2 and 3 the following differences occur that are different from the default locking behavior: intent-to-write
row-level locks are acquired instead of read locks, and intent-to-write locks may in some cases be acquired on rows that are
ultimately rejected as candidates for update.
- For each candidate row identified in step 2.a, follow the rest of the sequence.
- Write lock the row to be deleted.
- Remove the row from the table so that it is no longer visible to other transactions. The row cannot be destroyed until the
transaction is committed because doing so would remove the option of rolling back the transaction. Index entries for the deleted
row are preserved, though marked as deleted, until transaction completion. This prevents other transactions from re-inserting
the same row.
- If the table is a primary table in a referential integrity relationship, and the relationship's DELETE action is not RESTRICT,
determine the affected row(s) in the foreign table(s) by first acquiring a shared schema lock on the table(s), an intent-to-write
table lock on each, and acquire write locks on all of the affected rows, modifying each as appropriate. Note that this process
may cascade through a nested hierarchy of referential integrity constraints.
The transaction can be committed provided referential integrity is not violated by doing so. In order to verify referential
integrity, the database server also keeps track of any orphans created as a side effect of the deletion. Upon COMMIT, the
server records the operation in the transaction log file and release all locks.
