- Remote DBA
- Disaster Recovery
- Migration Services
- Request Info
- DBMS Courses
- Request Info
- Tech Articles
- Version Info
- Ask SCI's Experts
In DBMS Locking, Part I, we began a discussion of managing locking issues in DBMS...and promised a follow-up...and now, without further interruption, the sequel to DBMS Locking Part I.
Causes of Lock Conflicts
Lock conflicts are normal and should be expected in any concurrent environment. Excessive lock conflicts are not expected, nor should they be tolerated. This section examines many of the common causes of excessive lock conflicts, and identifies ways to resolve them. There is no "magic" database parameter that will solve (or significantly reduce) lock contention. The responsibility for resolving lock conflict problems are shared between the application designer, the database administrator and the application programmer. To understand the cause of lock conflicts, one must understand when DBMS requests a record lock, promotes it to a higher level (exclusive), and when DBMS releases record locks. Armed with this information, you are able to minimize the number of records that DBMS locks and reduce the time that it keeps record locked.
Read-only record locks
DBMS places a read-only lock on a record whenever a record is accessed. Assuming the database is not set for "hold retrieval locks," DBMS maintains this lock until no currency indicators point to the record, and the record is not pointed to by an entry in a keeplist. If the database is set for "hold retrieval locks," then the read-only lock is held until the next COMMIT or ROLLBACK. Read-only record locks do not block other run-units from reading this record. However, if another run-unit attempts to update this record, either directly or indirectly, this results in a lock conflict.
Exclusive record locks
DBMS promotes a lock to exclusive mode whenever a record is updated. This update can occur directly, as the result of a MODIFY, STORE or ERASE DML verb, or indirectly when a record is inserted or removed from a set (DBMS updates the pointer clusters of the next and prior records within a set). An exclusive lock can also be requested explicitly by an application program by using the 'FOR UPDATE' option of the FIND and FETCH verbs. Once an exclusive lock has been placed on a record, that record remains locked until the next COMMIT or ROLLBACK. If the transaction is ended with a 'COMMIT RETAINING,' then records pointed to by a keeplist entry or a currency indicator are demoted to read-only locks while all others are unlocked.
Exclusive locks prevent all other run-units from accessing or passing through this record. This may cause serious locking problems when the locked record is an index node of a highly traversed indexed set or another highly-accessed record occurrence. Example: Run-unit blocked from passing through a locked record.
Application Design Issues
Currency indicators are place holders that mark a position in each logical structure in a subschema. Currency indicators can be active (they point to a record), or null (they don't point to anything). Currency indicators provide a mechanism for efficient database navigation. However, each active currency indicator maintains a read-only lock on the record to which it points. This read-only lock is maintained until all currency indicators have been removed from the record and the record is not pointed to by a keeplist entry. Currency indicators can point to:
Each active currency indicator maintains a read-only lock on the record to which it points. By default, DBMS automatically updates all currency indicators after executing each DML (Data Manipulation Language) statement. This allows you to use any of these currency indicators without pre-stating your intentions. Since relatively few currency indicators are generally used during a specific transaction, significantly more currency indicators are made active than are needed by an application. As a result, records are locked for longer than is necessary.
Mismanagement of currency indicators is the single biggest cause of excessive lock conflicts. While currency indicators are required for efficient navigation of a DBMS database, each active currency indicator maintains a read-only lock on the record it points to. The longer you maintain an active currency indicator on a record, the longer the record is locked.
Run-unit currency indicators point to the last record successfully accessed using a FIND, FETCH or STORE verb. There is only one run-unit currency indicator (per stream) and it points to the "current" position in the database. The run-unit currency indicator is the only currency indicator and is always updated whenever you access a DBMS record (it cannot be suppressed using the RETAINING clause).
Realm currency indicators point to a position (a record) within a realm. DBMS provides the ability to maintain separate realm currency indicators for each realm in your subschema. Multiple realm currency indicators can be active at a time. This allows you to find next, prior or current within a REALM - using a realm as the starting-point for database navigation. By default, realm currency indicators are automatically updated each time you access a record. To improve DML efficiency and to reduce locking, REALM currency indicators should be retained, except when your program accesses a record using the "WITHIN <realm-name>" DML syntax.
Record-type currency indicators point to an occurrence of a given record-type. Each record-type defined in the subschema can have an active currency indicator. The record-type currency indicator allows you to "find current <record-name>". By default, record-type currency indicators are automatically updated whenever you access a record. RETAIN record-type currency indicators except when your programs re-access a record using the 'FIND (or FETCH) CURRENT record-name.'
Set-type currency indicators mark a position within a given set-type. This allows you to navigate DBMS sets easily and efficiently. Each set-type defined in the subschema has a currency indicator, which may be current or null. By default, when you access a record, DBMS automatically updates set-type currency indicators for all set-types owned by the record just accessed, as well as all set-types in which the record participates as a member. Typical DML programs use only a few of the set-type currency indicators that are automatically updated by DBMS - thus adding overhead and holding locks longer than necessary.
The key to efficient use of currency indicators, is to RETAIN all currency indicators EXCEPT those needed for efficient database navigation. When a currency indicator is no longer required for further navigation (i.e., at an end-of-collection), it should be freed. Several benefits are derived from proper management of currency indicators, including:
The key to efficient use of currency indicators, is to RETAIN all currency indicators EXCEPT those needed for efficient database navigation. When a run-unit attempts to access a DBMS record that is locked by another run-unit (the granted lock), the lock request is placed in a lock conversion queue. If another run-unit attempts to access that record, in an access mode that is incompatible with either of the other processes, its request will be blocked and the request will be placed in the conversion queue as well – even if its request is compatible with the granted lock.
Run-unit 2 attempts to access PART '12345' for update (EX).
Run-unit 3 attempts to access PART '12345' for read (R).
Run-unit 2's exclusive request is blocked by run-unit 1's read-only lock.
Run-unit 3's read request is blocked by run-unit 2's exclusive request.
NOTE: This is NOT a deadlock – run-units 2 and 3 will wait until run-unit 1 releases the 'read-only' lock on PART '12345'.
The queued lock request mechanism insures equitable access to database resources. How-ever, queuing lock requests can degrade database concurrency if processes are not releasing locks promptly.
Excessive transaction duration
transactions degrade shared access to a database for two reasons. First,
long transactions tend to lock more records due to the additional
processing they are performing. Furthermore, many of these records are
locked for extended periods of time, preventing other processes from
accessing them. Concurrent read/write access to a DBMS database means
shared access. Ideally, each run unit sharing this database will have the
illusion that they have exclusive access to the database. However, to
create this illusion, all run-units must be willing to share database
resources. This requires that no process locks a resource for longer than
is necessary to accomplish their task.
A common misconception is that only update transactions must be kept to a minimum. All transactions, except for EXCLUSIVE (where concurrency is not an issue) and snapshot (BATCH RETRIEVAL) transactions should issue a periodic commit [retaining] or rollback.
The need for update transactions to commit or rollback is obvious – this releases locks on any modified records. While read-only record locks are released by using the FREE statement, a commit or rollback is necessary to release the QUIETPOINT and snap area cursor locks used by /ONLINE backups and deferred snapshot transactions. A record or entry-point into the database that is frequently updated can effectively single-thread access to your database.
Locking and Terminal I/O
Sound principals of online system development, dictate that resource (database) locks should not be held while waiting for terminal I/O. Typical update transactions can be broken down into three (3) discrete steps:
1. Inquiry phase: The operator views the current status of the data.
Input phase: The terminal operator enters new or changed data.
To improve database concurrency, always commit or rollback trans-actions at the end of the inquiry phase. This releases all record locks during the input (or terminal I/O) phase. Using this model, the update phase becomes a bit more complicated. First, records must be reaccessed (using either their logical or physical keys), then their values must be compared with the values obtained during the inquiry phase – if the values are unchanged, then the record can be modified to reflect the desired update. This check is necessary to ensure that your transaction does not overwrite an update made by another run-unit.
Improper Ready Modes
Inappropriate READY modes are usually fairly obvious in a concurrent environment. The run-unit will receive the AREABUSY exception condition or will stall if the WAIT qualifier was used in the READY statement. CONCURRENT RETRIEVAL and CONCURRENT UPDATE are the most common ready modes used in a shared DBMS environment.
It should be noted that there is no difference in record locking when accessing records when realms are readied for either CONCURRENT RETRIEVAL or CONCURRENT UPDATE. Readying a realm for CONCURRENT UPDATE enables you to update records in that realm and prevents others from readying the area for PROTECTED or EXCLUSIVE access.
BATCH RETRIEVAL is an effective ready mode for eliminating record lock conflicts in those environments where snap-shots allowed and enabled (or deferred). BATCH RETRIEVAL (or snapshot) transactions incur no record locks – thus, they neither block nor are blocked by other transactions. Note that if snap-shots are enabled and non-deferred (the default), then all update transactions except EXCLUSIVE UPDATE will write before images of the updated records to the snapshot file. While DBMS carefully manages writes to the snapshot file to minimize overhead, in some environments this I/O overhead may still be prohibitive.
Inefficient DML statements
DBMS does much work to minimize the use of VMS locks, and to resolve lock conflicts for you. Much of this work is done using the Blocking AST (BLAST) mechanisms of the VMS lock manager. For instance, DBMS writes modified pages back to the database and then demote locks when it receives a BLAST for a page lock, thus allowing concurrent access to a database page. However, in current releases of DBMS, BLASTs are only processed between DML verbs. Thus, if you are processing an inefficient DML statement (such as a FETCH using a non-key value), DBMS does not to respond to BLASTS (does not release a locked resource) until the DML verb completes.
While database design can significantly impact concurrency, many sites are faced with implementing a database design provided by a third-party vendor. Below are several common database design pitfalls that degrade database concurrency.
Sets provide efficient access paths to their member records. However, insertion and removal from sets physically modifies the next and prior pointers of the adjacent members in the sets. Even though the modification to these adjacent members was indirectly caused by the insertion or removal from a set, the impact is no less severe from a locking standpoint. When inserting or removing a record from a set, adjacent members within that set are locked for exclusive update until the end of the transaction.
Excessive Indexed Sets
Indexed sets tend to cause more locking problems than CALC (hashed) or CHAIN sets. Insertions and removals from indexed sets cause not only the adjacent records in the set to be updated, but also the set's index nodes. This places an exclusive lock on an index node, which is often a primary access path to its member records. Thus, when this one index node is locked, it can effectively block access to thousands of member records! SYSTEM owned index nodes are particularly problematic since they often provide a single entry-point to all members. Thus, a locked index node may effectively block access to all records in the set.
Index Node sizes
The larger the size of an index node, the more member records it can potentially point to. Larger indices tend to lock more records than smaller index nodes. By decreasing the node size of your indexed sets, you can decrease the number of records controlled by each node. This reduces the level of contention for the index nodes themselves. The trade off is that smaller index nodes result in more I/O's to find the member records. You must also balance index node size with index usage – an indexed set that is built once and then used only as a lookup table can have large node sizes without negatively impacting concurrency, while an index that is frequently updated in a concurrent environment will generally benefit from smaller node sizes.
Frequently updated record occurrence
A record or entry-point into the database that is frequently updated can effectively single-thread access to your database. For example, suppose your on-line order entry system uses sequential order numbers for tracking orders. Two common methods for obtaining the 'next' order number are:
Both of these methods suffer from the same problem – posting of order records are single threaded through either the indexed set or through the control record. Note that all chronological indexed sets will suffer from this anomaly.
If you application permits, you can assign order numbers by order-taker – using a unique ID to prefix the order numbers. Thus, each order-taker is then assigned their own 'thread' into the database. Locking can also be reduced by assigning a block of control numbers at a time, thus minimizing the need to access and update the control-record.
Single record deadlocks...
When inserting or removing a record from a set, adjacent members within that set are locked for exclusive update until the end of the transaction. Frequently accessed records are also the source of often misunderstood deadlocks. While the "deadly embrace" is often thought of as the only source of deadlocks, it is possible for two run-units to deadlock when accessing a single record. The following example illustrates this problem:
UNIT ACTION STATUS
To minimize the chance of single record deadlocks, records that are known to be updated during the transaction should be accessed for exclusive update using the FOR UPDATE qualifier of the FETCH or FIND statement. This will cause the second run-unit to wait until the first run-unit commits their transaction before being granted access to the record.
Separate static from volatile data
Reads do not conflict with other reads. Record lock conflicts occur between writers and other run-units. By grouping records according to their update patterns, improved database concurrency results. For example, suppose your application has a large look-up table that is initially loaded and infrequently updated, but often searched. By placing the records that make up this table in an area that is infrequently updated, you can virtually eliminate lock conflicts when accessing these records.
Database locking can be observed at the system, database or at the application level. Each tool is effective for identifying different types of lock resource problems.
Several of the RMU or DBO/SHOW STATISTICS screens can be used to isolate and identify locking problems. Summary Locking Statistics provide a good overview of lock utilization and contention. Key indicators to look at are "stall time x100". This provides some insight into how much time is being spent waiting for locked database resources. Once that you have identified that a significant amount of time is being spent waiting for locked database resource, the Locking (one lock type) screens can quickly identify which lock types are causing the greatest stall time. Record locks are the most frequent source of lock conflicts. The Locking (record locks) screen displays the record locking statistics. Again, "stall time x100" provides an indication of how much time is being spent waiting for record locks. If you have adjustable record locking granularity enabled for your database, compare the value of blocking ASTs to the number of locks requested. A number greater than 20 percent indicates that significant overhead is being incurred to manage the ALG tree. Disabling ALG or reducing the number of levels in its tree will reduce the Blocking ASTs and may improve overall database performance.
VMS MONITOR LOCKS
The VMS MONITOR LOCKS utility provides insight into total lock activity on your node and nodes in a VAXCluster. This utility is useful for identifying potential lock shortages as well as determining if total locking activity may be affecting system performance.
DBO/SHOW GETLKI (pre v4.3)
The RMU or DBO/SHOW GETLKI utility provides extensive database lock information in a dump-like format. While the format of this report does not lend itself to quick analysis, it contains information that can identify what processes are blocking other processes, which processes are being blocked and most of the data held in the database lock tree. Note that RMU or DBO-/SHOW GETLKI runs in EXEC mode, and cannot be terminated with a CTRL-Y. Since its output can be quite lengthy, you should direct its output to a file rather than SYS$OUTPUT.
DBO/SHOW locks (v4.3 and beyond)
Version 4.3 significantly enhanced the lock summary reports with the addition of the RMU or DBO/SHOW LOCKS command. This new command allows you to show processes that are blocked by another process, processes that are blocking other processes and to select locks held by a specific process. This command is particularly useful for determining who is blocking another process, and to identify the resource in contention.
At the application level, you can easily develop routines using callable DBQ to display or report on the records currently locked by a process. This information is extremely useful in identifying which records are locked at specific points within your application.
As discussed previously in this article, mismanaged currency indicators are frequently the largest cause of excessive lock conflicts. It is easy to instrument your code to display the status of currency indicators in your application.
It is also possible to generate a routine for reporting currency indicators by using the DBCS function 'DBM$-GET-_DBKEY'. This function allows you to return the DBKEY of records pointed to by each realm, record, and set currency indicator in the current subschema. Currently there is no mechanism to directly translate the currency indicator id's to their realm, record and set names – although this information can be obtained from a subschema listing.
By default, DBMS takes a very conservative approach to locking – generally, it locks much more than is actually needed by an application. DBMS also provides significant control over what it locks and how long it retains locks. By carefully managing locking in a DBMS environment, it is possible to provide virtually transparent "shared" access to your DBMS database!
Identify & Resolve
Armed with information on causes and cures for excessive locking, you are now ready to identify and resolve those nasty contention problems in your environment.