Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Transactions and Isolation Levels » How locking works

Locking during updates Next Page

Locking during deletes

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.

  1. Acquire a shared schema lock on the table, if one is not already held.

  2. Acquire an intent-to-write table lock on the table, if one is not already held.

  3. Write lock the row to be deleted.

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

  5. 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 will not be 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 will record the operation in the transaction log file and release all locks.