Lock contention issues can be frustrating problems to investigate and debug. But anytime a database system is involved, the potential exists for locking to cause concurrency problems. Before blaming the database system (as is the usual response), try to answer the following questions to help identify the cause of the locking problems:
- Has the application run in the past without locking problems?
- Have the lock timeouts or deadlocks started recently?
- What version and level of the DBMS are you running?
- Does the problem only occur at certain times?
- What has changed on the system (e.g., number of users, number of applications, amount of data in the tables, database maintenance/fixpacks, changes to any other relevant software, etc.)
- What, if anything, has changed in the application (e.g., isolation level, concurrent executions, volume of data, etc.)?
Anyone who has written applications to access database data probably has had to deal with concurrency problems at some point in their career. When one application program tries to read data that’s in the process of being changed by another, the DBMS must control access until the modification is complete to ensure data integrity. Typically, DBMS products use a locking mechanism to control access and modifications while ensuring data integrity.
When one task is updating data on a page (or block), another task can’t access data (read or update) on that same page (or block) until the data modification is complete and committed. When multiple users can access and update the same data at the same time, a locking mechanism is required. This mechanism must be capable of differentiating between stable data and uncertain data. Stable data has been successfully committed and isn’t involved in an update in a current unit of work. Uncertain data is currently involved in an operation that could modify its contents.
Most modern DBMS products allow you to control the level of locking (table, page/block, row), as well as to adjust other locking criteria (for example, locks per users, time to wait for locks, etc.). But I do not want to get into all of the nuances and implementation details of database locking. This is a simplified discussion of locking. It is not the intent of this discussion to explain all the nuances of database locking. And, indeed, each specific DBMS implements locking techniques a little bit differently. Instead, we will take a look at the types of issues and some techniques for remediating locking problems.
Lock timeouts are one of the most perplexing issues encountered by database professionals. The longer a lock is held, the greater the potential impact to other applications. When an application requests a lock that’s already held by another process, and the lock can’t be shared, that application is suspended. A suspended process temporarily stops running until the lock can be acquired. When an application has been suspended for a pre-determined period of time, it will be terminated. When a process is terminated because it exceeds this period of time, it’s said to timeout. In other words, a timeout is caused by the unavailability of a given resource.
To minimize lock timeouts, be sure to design your application programs with locking in mind from the start. Limit the number of rows accessed by coding predicates to filter unwanted rows. Doing so reduces the number of locks on pages containing rows that are accessed but not required, thereby reducing timeouts and deadlocks. Also, you should design update programs so the update is issued as close to the COMMIT point as possible. Doing so reduces the time that locks are held during a unit of work, which also reduces timeouts (and deadlocks).
Deadlocks also cause concurrency problems. A deadlock occurs when two separate processes compete for resources held by one another. For example, a deadlock transpires when PGMA has a lock on PAGE1 and wants to lock PAGE2 but PGMB (at the same time) has a lock on PAGE2 and wants a lock on PAGE1. One of the programs must be terminated to allow processing to continue. One technique to minimize deadlocks is to code your programs so that tables are accessed in the same order. By designing all application programs to access tables in the same order, you reduce the likelihood of deadlocks.
Earlier, I mentioned the COMMIT statement. It is important to design all your programs with a COMMIT strategy. A COMMIT externalizes the modifications that occurred in the program since the beginning of the program or the last COMMIT. A COMMIT ensures that all modifications have been physically applied to the database, thereby ensuring data integrity and recoverability. Failing to code COMMITs in a data modification program can cause lock timeouts for other concurrent tasks.
You can also control the isolation level, or serializability,of the data requests in your programs.
Programs using the repeatable read locking strategy hold their locks until a COMMIT is issued. If no COMMITs are issued during the program, locks aren’t released until the program completes, thereby negatively affecting concurrency. This can cause lock timeouts and lock escalation.
There are some techniques available to DBAs to minimize lock timeouts. When an object is being accessed concurrently by multiple programs or users, consider increasing free space, causing fewer rows to be stored on a single page, at least until data is added. The fewer rows per page, the less intrusive page locking will be because fewer rows will be impacted by a page lock.
Locking is a complex issue and can be at the root of many performance problems. But if you follow the guidance offered here you can reduce the frequency of locking issues in your shop.