ASA SQL User's Guide
Using Transactions and Isolation Levels
How locking works
Adaptive Server Anywhere places locks on the following objects.
Rows in tables A transaction can lock a particular row to prevent another transaction from changing it. A transaction must place a write lock on a row if it intends to modify the row.
Insertion points between rows Transactions typically scan rows using the ordering imposed by an index, or scan rows sequentially. In either case, a lock can be placed on the scan position. For example, placing a lock in an index can prevent another transaction from inserting a row with a specific value or range of values.
Table schemas A transaction can lock the schema of a table, preventing other transactions from modifying the table's structure.
Of these objects, rows are likely the most intuitive. It is understandable that a transaction reading, updating, deleting, or inserting a row should limit the simultaneous access to other transactions. Similarly, a transaction changing the structure of a table, perhaps inserting a new column, could greatly impact other transactions. In such a case, it is essential to limit the access of other transactions to prevent errors.
You can use an index to order rows based on a particular criterion established when the index was constructed.
When there is no index, Adaptive Server Anywhere orders rows by their physical placement on disk. In the case of a sequential scan, the specific ordering is defined by the internal workings of the database server. You should not rely on the order of rows in a sequential scan. From the point of view of scanning the rows, however, Adaptive Server Anywhere treats the request similarly to an indexed scan, albeit using an ordering of its own choosing. It can place locks on positions in the scan as it would were it using an index.
Through locking a scan position, a transaction prevents some actions by other transactions relating to a particular range of values in that ordering of the rows. Insert and anti-insert locks are always placed on scan positions.
For example, a transaction might delete a row, hence deleting a particular primary key value. Until this transaction either commits the change or rolls it back, it must protect its right to do either. In the case of a deleted row, it must ensure that no other transaction can insert a row using the same primary key value, hence making a rollback operation impossible. A lock on the scan position this row occupied reserves this right while having the least impact on other transactions.