Designing and implementing a proper database for application development is a complex and time-consuming task. The choices made during database and application design will impact the usefulness of the final, delivered application.
Indeed, an improperly designed database or application may need to be redesigned and recoded from scratch if it is inefficient, ineffective, or not easy to use.
One of the most important design considerations is the impact of database locking on concurrent processes. To be able to properly code applications for concurrent access you must first understand database locking.
Of course, the exact locking mechanism used for each DBMS will differ somewhat, and the topic is sufficiently complex so to make it impractical to completely explain in a short column such as this.
At a high level though, locks are used to ensure the integrity of data. When a database resource is locked by one process, another process is not permitted to change the locked data. Locking is necessary to enable the DBMS to facilitate the ACID properties of transaction processing.
Data may be locked at different levels within the database. For example, locking may happen at the table level, at the block or page level, or even at the row level. It is the DBA’s job to determine the appropriate level of locking for each database object, based on how the data will be accessed and to what extent concurrent users will access the data. The level of locking is known as lock granularity.
So, with that short introduction aside, let’s take a look at two of the most vexing database locking issues that can impact application performance and usability: lock timeouts and deadlocks.
When data is locked by one process, other processes must wait for the lock to be released before processing the data. A lock that is held for a long time has the potential to degrade performance because other processes must wait until the lock is released and the data becomes available. Furthermore, if the application is designed improperly or has a bug, the blocking lock may not be released until the program fails or the DBA intervenes.
The locking mechanism of the DBMS prevents processes from waiting forever for a lock to be released by timing out. Each DBMS provides a parameter to set a lock time-out value. Depending on the DBMS, the lock time-out value might be set at the DBMS level, the process level, or the connection level. Regardless of the level, after a process waits for the predetermined amount of time for a lock to be granted, the process will receive an error message informing it that the time-out period has been exceeded. Lock timeouts prevent a process from waiting indefinitely for locks—the rationale being that it is better for a process to give up and release its locks than to continue to wait and perhaps block other processes from running.
Another locking problem that can occur is deadlocking. A deadlock occurs when concurrent processes are competing for locks on the same resources. For example, assume Program1 holds a lock on row 3 and is requesting a lock on row 7. If Program2 holds a lock on row 7 and is requesting a lock on row 3, processing cannot continue; it is deadlocked.
You can think of a deadlock as a specific type of lock time-out. It occurs when one process holds a lock that another process is requesting at the same time the second process holds a lock that the first process is requesting. This is also sometimes called a “deadly embrace.” The DBMS will choose one of the processes to abort and roll back so the other process can continue.
Dealing with Timeouts and Deadlocks
When locks are held too long, time-outs will make data less available. If applications request locks in a disorganized manner, deadlocks can occur, causing further availability problems.
Developers can help to minimize locking problems by deploying good programming techniques, such as choosing the correct isolation level and coding commit statements with units of work in mind.
One technique that can reduce deadlock issues is to standardize the sequence of updates within all programs. When the sequence of updates is the same for all programs, deadlocks should not occur.
Another programming technique is to save all data modification requests until the end of a unit of work. The later modifications occur in a transaction, the shorter the lock duration will be. Grouping modification statements (such as INSERT, UPDATE, and DELETE) and issuing them near the end of the transaction can improve concurrency because resources are locked for shorter durations.
Additionally, it is a good practice for programs to retry an operation when a lock time-out or deadlock error is received. If multiple lock time-outs occur for the same operation, the program should log the problem and inform the user that it cannot proceed. If multiple deadlocks occur for the same operation, the program should log the problem and inform the user that it cannot proceed.