Granularity of locks and locking schemes

The granularity of locks in a database refers to how much of the data is locked at one time. In theory, a database server can lock as much as an entire database or as little as one column of data. Such extremes affect the concurrency (number of users that can access the data) and locking overhead (amount of work to process lock requests) in the server. Adaptive Server supports locking at the table, page, and row level.

By locking at higher levels of granularity, the amount of work required to obtain and manage locks is reduced. If a query needs to read or update many rows in a table it can acquire:

Less overall work is required when a table-level lock is used, but large-scale locks can degrade performance by making other users wait until locks are released. Decreasing lock granularity makes more data accessible to other users. Finer granularity locks can degrade performance, since more work is necessary to maintain and coordinate the increased number of locks. To achieve optimum performance, a locking scheme must balance the needs of concurrency and overhead.

Adaptive Server provides these locking schemes:

For each locking scheme, Adaptive Server can lock an entire table, for queries that acquire many page or row locks, or can lock only the affected pages or rows.

NoteThe terms “data-only-locking” and “data-only-locked table” refer to both the datapages and datarows locking schemes, and are typically refered to as “DOL” tables. Allpages-locked tables are known as “APL” tables.