ASA SQL User's Guide
Using Transactions and Isolation Levels
How locking works
The DELETE operation follows almost the same steps as the INSERT operation, except in the opposite order.
Write lock the affected row.
Delete each index entry present for the any values in the row. Immediately prior to deleting each index entry, acquire one or more anti-insert locks as necessary to prevent another transaction inserting a similar entry before the delete is committed. In order to verify referential integrity, the database server also keeps track of any orphans created as a side effect of the deletion.
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.
The transaction can be committed provided referential integrity will not be violated by doing so: record the operation in the transaction log file including the values of all entries in the row, release all locks, and destroy the row.
Cascade the delete operation, if you have selected this option and have modified a primary or foreign key.
The database server must ensure that the DELETE operation can be rolled back. It does so in part by acquiring anti-insert locks. These locks are not exclusive; however, they deny other transactions the right to insert rows that make it impossible to roll back the DELETE operation. For example, the row deleted may have contained a primary key value, or another unique value. Were another transaction allowed to insert a row with the same value, the DELETE could not be undone without violating the uniqueness property.
Adaptive Server Anywhere enforces uniqueness constraints through indexes. In the case of a simple table with only a one-attribute primary key, a single phantom lock may suffice. Other arrangements can quickly escalate the number of locks required. For example, the table may have no primary key or other index associated with any of the attributes. Since the rows in a table have no fundamental ordering, the only way of preventing inserts may be to anti-insert lock the entire table.
Deleting a row can mean acquiring a great many locks. You can minimize the effect on concurrency in your database in a number of ways. As described earlier, indexes and primary keys reduce the number of locks required because they impose an ordering on the rows in the table. The database server automatically takes advantage of these orderings. Instead of acquiring locks on every row in the table, it can simply lock the next row. Without the index, the rows have no order and thus the concept of a next row is meaningless.
The database server acquires anti-insert locks on the row following the row deleted. Should you delete the last row of a table, the database server simply places the anti-insert lock on an invisible end row. In fact, if the table contains no index, the number of anti-insert locks required is one more than the number of rows in the table.
While one or more anti-insert locks exclude an insert lock and one or more read locks exclude a write lock, no interaction exists between anti-insert/insert locks and read/write locks. For example, although a write lock cannot be acquired on a row that contains a read lock, it can be acquired on a row that has only an anti-insert lock. More options are open to the database server because of this flexible arrangement, but it means that the server must generally take the extra precaution of acquiring a read lock when acquiring an anti-insert lock. Otherwise, another transaction could delete the row.