Avoid Database Locking

It can be very frustrating problems to investigate and debug the lock contention issues. Its happened due to concurrency problems. We have to first find out the self-question before blaming the Database system as.

• 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 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/fix packs, 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.)?
  

A developer 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 of modern DBMS products allow us 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. 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 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, we 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 a lock on PAGE1 and wants to lock PAGE2 but at the same time 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.

It is important to design all 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 serialization, of the data requests in our 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.

For these, a DBA have techniques 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.

Happy Reading …. Please suggest your views and experience on this topic.

Locking v/s Multi-Versioning

Locking is a means of not allowing any other transaction to take place when one is already in progress. In this the data is locked and there won’t be any modification taking place till the transaction either gets successful or it fails. The lock has to be put up before the processing of the data whereas Multi-Versioning is an alternate to locking to control the concurrency. It provides easy way to view and modify the data. It allows two users to view and read the data till the transaction is in progress. Multiversion concurrency control is described in some detail in the 1981 paper “Concurrency Control in Distributed Database Systems” by Philip Bernstein and Nathan Goodman.
Multi-Versioning, As the name implies, each record in the system might have multiple versions visible to different transactions. When a transaction modifies a record, a new version is written to the database, and a previous version, representing only the difference between the version of the record that was read by the transaction and the new value of the record, is written as a back version of that record.
Read committed isolation using row versioning is somewhere in between Locks and Multi-Versioning. Under this isolation level, read operations do not acquire locks against the active live data. However, with update operations the process is the same for this isolation level as it is for the default read committed isolation level, The selection of rows to update is done by using a blocking scan where an update lock is taken on the data row as data values are read.
Snapshot isolation uses purely Multi-Versioning because data that is to be modified is not actually locked in advance, but the data is locked when it is selected for modification. When a data row meets the update criteria, the snapshot transaction verifies that the data has not been modified by another transaction after the snapshot transaction started. If the data has not been modified by another transaction, the snapshot transaction locks the data, updates the data, releases the lock, and moves on. If the data has been modified by another transaction, an update conflict occurs and the snapshot transaction rolls back.
Although locking can be the best concurrency-control choice for applications that require data consistency and inter-transaction protection, it can cause writers to block readers. If a transaction changes a row, another transaction cannot read the row until the writer commits. There are cases where waiting for the change to complete is the correct response; however, there are cases where the previous transactionally consistent state of the row is enough.