Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Designing and Creating Databases » Using transactions and isolation levels » How locking works


Locking conflicts

SQL Anywhere uses schema, row, table, and position 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 that is maintained by choosing the isolation level that best fits your requirements. Knowledge of the types of locks will guide you in choosing isolation levels and understanding the impact of each level on performance. Keep in mind that any one transaction cannot block itself by acquiring locks; a locking conflict can only occur between two (or more) transactions.

Which locks conflict?

While each of the four types of locks have specific purposes, all of the types interact and therefore may cause a locking conflict between transactions. To ensure database consistency, only one transaction should change any one row at any one time. Otherwise, two simultaneous transactions might try to change one value to two different new ones. Hence, it is important that a row write lock be exclusive. In contrast, no difficulty arises if more than one transaction wants to read a row. Since neither is changing it, there is no conflict. Hence, row read locks may be shared across many connections.

The following table identifies the combination of locks that conflict. Schema locks are not included because they do not apply to rows.

read (R) intent (R) write (R) shared (T) intent (T) exclusive (T) phantom (P) insert (P)
read (R) conflict conflict
intent (R) conflict conflict conflict
write (R) conflict conflict conflict conflict conflict
shared (T) conflict conflict conflict
intent (T) conflict conflict
exclusive (T) conflict conflict conflict conflict conflict conflict conflict conflict
phantom (P) conflict conflict
insert (P) conflict conflict