User's Guide

Part III. Using SQL Anywhere

Chapter 19. Using Transactions and Locks

When a transaction is reading or writing a row in a database table, the database engine automatically locks the individual row (row level locking) to prevent other transactions from interfering with the data, or from obtaining unreliable data. The transaction that has access to the row is said to hold the lock. Depending on the type of lock, other transactions may have limited access to the locked row, or none at all.

All locks for a transaction are held until the transaction is complete (COMMIT or ROLLBACK), with a single exception noted below.

SQL Anywhere allows users to determine the extent to which transactions can operate concurrently by setting isolation levels. Isolation levels are discussed in the next section.

Types of locks

There are three distinct types of locks:

• read lock
• write lock
• phantom lock

Uses for locks

They have the following uses:

• Whenever a transaction inserts, updates, or deletes a row, a write lock is set. No other transaction can obtain a lock on the same row when a write lock is set. A write lock is an exclusive lock.
• A read lock can be set when a transaction reads a row. Several transactions can acquire read locks on the same row (a read lock is a nonexclusive lock). However, once a row has been read locked no other transaction may obtain a write lock on it.
• A phantom lock is a read lock that prevents phantom rows. Phantom 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.