ASA SQL User's Guide
  Using Transactions and Isolation Levels
    How locking works

Types of locks

Adaptive Server Anywhere uses four distinct types of locks to implement its locking scheme and ensure appropriate levels of isolation between transactions:

• read lock      (shared)

• phantom lock or anti-insert lock      (shared)

• write lock      (exclusive)

• anti-phantom lock or insert lock      (shared)

Remember that the database server places these locks automatically and needs no explicit instruction.

Each of these locks has a separate purpose, and they all work together. Each prevents a particular set of inconsistencies that could occur in their absence. Depending on the isolation level you select, the database server will use some or all of them to maintain the degree of consistency you require.

The above types of locks have the following uses:

• A transaction acquires a write lock whenever it inserts, updates, or deletes a row. No other transaction can obtain either a read or a write lock on the same row when a write lock is set. A write lock is an exclusive lock.

• A transaction can acquire a read lock when it reads a row. Several transactions can acquire read locks on the same row (a read lock is a shared or nonexclusive lock). Once a row has been read locked, no other transaction can obtain a write lock on it. Thus, a transaction can ensure that no other transaction modifies or deletes a row by acquiring a read lock.

• An anti-insert lock, or phantom lock, is a shared lock placed on an indexed scan position to prevent phantom rows. It prevents other transactions from inserting a row into a table immediately before the row which is anti-insert locked. Anti-insert locks for lookups using indexes require a read lock on each row that is read, and one extra read lock to prevent insertions into the index at the end of the result set. Phantom rows for lookups that do not use indexes require a read lock on all rows in a table to prevent insertions from altering the result set, and so can have a bad effect on concurrency.

• An insert lock, or anti-phantom lock, is a shared lock placed on an indexed scan position to reserve the right to insert a row. Once one transaction acquires an insert lock on a row, no other transaction can acquire an anti-insert lock on the same row. A read lock on the corresponding row is always acquired at the same time as an insert lock to ensure that no other process can update or destroy the row, thereby bypassing the insert lock.

Adaptive Server Anywhere uses these four types of locks as necessary to ensure the level of consistency that you require. You do not need to explicitly request the use of a particular lock. Instead, you control the level of consistency, as is explained in the next section. Knowledge of the types of locks will guide you in choosing isolation levels and understanding the impact of each level on performance.

Exclusive versus shared locks 

These four types of locks each fall into one of two categories:

• Exclusive locks      Only one transaction can hold an exclusive lock on a row of a table at one time. No transaction can obtain an exclusive lock while any other transaction holds a lock of any type on the same row. Once a transaction acquires an exclusive lock, requests to lock the row by other transactions will be denied.

  Write locks are exclusive.

• Shared locks      Any number of transactions may acquire shared locks on any one row at the same time. Shared locks are sometimes referred to as non-exclusive locks.

  Read locks, insert locks, and anti-insert locks are shared.

Only one transaction should change any one row at one time. Otherwise, two simultaneous transactions might try to change one value to two different new ones. Hence, it is important that a write lock be exclusive.

By contrast, no difficulty arises if more than one transaction wants to read a row. Since neither is changing it, there is no conflict of interest. Hence, read locks may be shared.

You may apply similar reasoning to anti-insert and insert locks. Many transactions can prevent the insertion of a row in a particular scan position by each acquiring an anti-insert lock. Similar logic applies for insert locks. When a particular transaction requires exclusive access, it can easily achieve exclusive access by obtaining both an anti-insert and an insert lock on the same row. These locks do not conflict when they are held by the same transaction.

Which specific locks conflict? 

The following table identifies the combination of locks that conflict.

These conflicts arise only when the locks are held by different transactions. For example, one transaction can obtain both anti-insert and insert locks on a single scan position to obtain exclusive access to a location.