In addition to row locks, SQL Anywhere also implements a form of key-range locking designed to prevent anomalies because of the presence of phantoms, or phantom rows. Phantoms are rows that appear, or disappear, with respect to an operation as a result of the dynamic nature of a database. Position locks are only relevant only when the database server is processing transactions operating at isolation level 3.
Transactions that operate at isolation level 3 are said to be serializable. This means that a transaction's behavior at isolation level 3 should not be impacted by concurrent update activity by other transactions. In particular, at isolation level 3, transactions cannot be affected by INSERTs or UPDATEs—phantoms—that introduce rows that can affect the result of a computation. SQL Anywhere uses position locks to prevent such updates from occurring. It is this additional locking that differentiates isolation level 2 (repeatable read) from isolation level 3.
To prevent the creation of phantoms rows, SQL Anywhere acquires locks on positions within a physical scan of a table. In the case of a sequential scan, the scan position is based on the row identifier of the current row. In the case of an index scan, the scan's position is based on the current row's index key value (which can be unique or non-unique). Through locking a scan position, a transaction prevents insertions by other transactions relating to a particular range of values in that ordering of the rows. For the purposes of this discussion with respect to locking, insertions means not only INSERT statements, but also UPDATE statements that change the value of an indexed attribute. In such cases, the UPDATE can be considered a DELETE of the index entry followed immediately by an INSERT.
There are two types of position locks supported by SQL Anywhere: phantom locks and anti-phantom locks. Both types of locks are shared, in that any number of transactions can acquire the same type of lock on the same row. However, phantom and anti-phantom locks conflict.