As a database consultant, one of the things I am most frequently asked about is dealing with database locking issues. Usually, the question is posed by someone who is experiencing a slowdown in their database application, typically in an online environment, and they want to know how to eliminate locking problems. Here is some guidance.
First of all, keep in mind that every DBMS has different settings and parameters to control locking at the system and database levels. And, yes, it is important to set these up correctly, but they are rarely the core cause of performance problems caused by locking. So let’s focus on the universal issue that is almost always the culprit: poor coding techniques and inappropriate oversight before code is moved to production.
Before moving on, we need to at least have a rudimentary understanding of locking. This is not the venue to teach database locking, but the general idea is that the DBMS takes a lock on data to prohibit others from changing it. When you access data with the intent to change it, a lock prohibits others from accessing the data while it is being modified. Simply stated, database locks promote data integrity; without them, your data would get all messed up. Of course, when data is locked, performance can slow down as other tasks wait to access the data.
When you experience a lock timeout, it means that another process holds an incompatible lock on the data that you are trying to modify. And the lock has been held longer than the DBMS has been configured to wait for it to be released. The “amount of time to wait” is one of those “system parameters” I mentioned, different for each DBMS, but configurable by the DBA or system administrator.
So, if existing locks are a barrier to accessing data, it stands to reason that you should try to minimize the duration of the locks that are being held by your applications. There are some approaches you can take to achieve this, but for the most part, they require programming changes.
First of all, make sure that all of your batch processes—especially any that run during the same timeframe, but really all batch process -- have a Commit strategy. One of the most common programming errors that I see is programs that make many changes, perhaps hundreds of thousands or even millions, and do so without issuing any Commits. This means that every row or page that has been modified is locked and cannot be accessed by any other process until the offending program completes and thereby issues an automatic Commit.
This best practice for avoiding lock timeout problems is that every program should issue a Commit after processing "a set number of" inserts, updates, and deletes. For some DBMSes, issuing a Commit even for read-only activity can be beneficial, but we won’t go into those details here. Issuing a Commit tells the DBMS to make the changes permanent and releases all locks.
To implement this tactic, set a counter that is incremented after every modification. Then, check it and, when it exceeds a predefined threshold—let's say 25 or 50 or 100 modifications—issue a Commit statement. You should make the threshold an input parameter so that you can change it as the workload in your system changes. For example, make it 25 when concurrent activity is high, but ramp it up to 100 or higher when it is low. (Please note that these are just sample numbers and not necessarily the correct numbers to start with at your shop.) Failure to issue sufficient (or any) Commits will result in lock timeouts, as well as possibly deadlocks and lock escalation.
There are software products that can be used to simplify and automate the issuance of Commits in application programs, but the availability is different for each DBMS, so you will need to do some investigation if you are looking for such tools.
Another useful tactic for minimizing database lock timeouts, is to issue the data modification statements as close to the Commit statement as possible. By saving the data modification until right before you issue a Commit, you reduce the overall average lock duration. This should result in reducing contention and therefore, the number of timeouts.
The guidance discussed here requires making program changes, not database changes. The pattern of locking is different for each and every application system, meaning changes to the application code are required; there really is no way to effectively control lock timeouts at a system level. So, practice good database coding techniques and make life easier for your end users and operations folks!