Database locks have been a bane to most DBAs. They cause unnecessary problems to database and business processes. In this blog, we will examine what database locks are, issues that can arise from it, and provide ways on how to deal with it on SQLServer. Not only that, we will also implement it in dbWatch Control Center as an example.
What is a database lock?
Database locks occur to secure shared resources during transactions. Locks are safeguards for databases as a means of:
- 1. Observing an all or nothing scenario for multiple and separate transactions
- 2. Preserving consistency in database state
- 3. Isolating transactions from being committed until that transaction is complete
- 4. Saving committed transactions even in the event of abnormal termination
Basically, they adhere to ACID properties of transaction. To put it in perspective, imagine a piece of paper being shared by three writers.
The first writer finishes his message and gives the paper to the second writer. Then, the second writer begins to draft his message. While the last writer must wait for the second writer to finish his message before he can write anything on that piece of paper. Locks, like the above example, works in the same fashion. They prevent simultaneous writing and updates to maintain data integrity.
Locks can occur in a variety of forms. They appear in levels of access to a database. Below are the kinds of locks that can occur:
Database locks most commonly appears during record updates or uncommitted SQL statements. They primarily isolate a database component, such as tables, so that session updates and alterations will successfully take effect and no data loss will be experienced. Locks are not that problematic when there are only a few active sessions transacting in your database. However, with more database users accessing and utilizing your databases, locks will impact your database resources and potentially your business. There are three kinds of locking that I will discuss: lock contention, long term blocking and deadlocks.
First, we will discuss the most common form of locking which is Lock Contention. It happens when concurrent sessions are competing for the right to apply updates on the same data. As we have discussed earlier, locks are enforced when a user alters a table property or updates data. This in turn blocks other session users from accessing that same database component, i.e. table. SELECT and INSERT statements run slower when accessing that table as it waits for the lock to end. What’s worse is lock contention may lead to indefinite access to a particular table and high CPU usage on that database server. This leads us to the second kind of locking - Long Term Blocking.
Similar to Lock Contention, Long Term Blocking happens when multiple users are accessing the same database component. The notable difference occurs when the user is holding on to that component for a very long time. Dependent sessions and objects will be blocked from reading and writing.
Last, we have Deadlocks. Database Deadlocks happen when 2 or more database sessions holds to a database object which is a dependent object for their transaction. Hence, neither session can continue unless the other concedes hold of that dependent object.
The Figure above simplifies how deadlocks occur. Database Management Systems (DBMS) can detect deadlocks. It will select a session and rollback that session’s transaction. The only caveat is DBMS will choose the easiest transaction to rollover.
Sure, deadlocks are manageable when it concerns user-driven ad-hoc. But, when applications and sql jobs are involved, this can lead to data loss, and logic problems. Transactions that are high dependent in timing and data will always lead to writing or outputting the incorrect information.
What can I do when database locks occur?
Now that we have a better understanding the kinds of locks and how they occur, we can discuss further how to deal with locks and prevent them in the future. Before that, let us simulate how a lock can occur in a session.
In this example, I have prepared separate two sessions to act as our users. Session 1 will designate as our first user then Session 2 will be our second user.
The figure above shows a simple query which locks the designated table test_table. After that, we will execute another query for Session 2 that will select the designated table.
The stored procedure above will display a tabularized format of information you will need to know who is blocking who. As seen below, ID 54 (session 2) is being blocked by session 76 (session 1).
Similarly, you can use the following query to get the information you want:
SELECT session_id, start_time, command, status, blocking_session_id, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
You will still end up with the same information as before.
After knowing the blocking session, you can kill the query by using the command below:
KILL - - blocking_session_id
-- in this example two queries were executed:
-- KILL 76
-- KILL 54
-- You can also use: KILL [session_id] WITH STATUSONLY
After killing them, it will restart the session. This was also done by killing session_id 54 (session 1).
That seems easier than expected. But this is still a bare bone example with inconsequential impact to our business processes and databases. In reality, killing a session rolls back the transaction prior to any changes in state of the database. Use killing with precaution since it may take longer for the session to recover the lost transaction and it may affect dependent objects during data updates and writing.
You should have a sufficient knowledge when dealing with table locks. Do not nonchalantly kill session just to have a quick fix solution to the problem. In queries using BEGIN TRANSACTION, always have save points after every important alteration to the database. Do not forget to end your transactions with ROLLBACK or COMMIT since this lock tables affected by the transaction. Finally, only kill sessions if it will have minimal impact to your business operations and data dependencies. This servers as your last resort.
Another option is to utilized database monitoring and management tools such as dbWatch Control Center. This not only saves you time with querying for information but also gives you an overall view on the state of your database.
How to do it with dbWatch Control Center?
Since dbWatch Control Center is both a monitoring and management solution, we will do both in one active window. For reference, we will simulate the previous example and do it step-by-step using Control Center.
First of all, we will change both warning threshold and alarm threshold. The above picture shows the default values of the blocking statistics. This means that a warning status will appear when blocking occurs for more than 5 minutes and it will raise an alarm status when it is above 30 minutes.
I will be altering it to notify me as a warning when it reaches 4 minutes and alarms me when it reaches over 12 minutes.
To show how that blocking has occurred, the screenshot above shows that the blocking has already taken effect.
Using the query before, I checked if there are table locks occurring in the database. Apparently, there is. We now know that session 75 is blocking session 58. For the following steps, we will not need any Management Studio opened. These previous two screenshots are only here to confirm that table locks are occurring.
In the monitoring module, the blocking statistics job will check if there are table locks occurring in the database. As we defined earlier, the monitoring job warns us after 4 minutes of blocking has happened. By clicking on the blocking statistics, it shows a complete overview of:
- Who? --> which sessions are blocking who
- What? --> what query is being executed
- Where? --> what management tool host this is occurring in when the blocking has occurred
Waiting for a little while, the status is changed to alarm indicating that it has already passed the 12-minute mark.
Now, we will go to the monitoring module. Click on the tree. Then, find “Performance” and click on the arrow adjacent to it. Underneath it, you will find “Blocking Session”. Click on the arrow beside Blocking sessions to display “Blocking Session History” which will help us determine if there are logs present during the lock. As we can see in the above screenshot, the lock was recorded and sorted starting from the latest blocking.
After confirming the logs, we will go back to “Blocking sessions” to manage our database. On that view, two separate sections will appear – “Blocking sessions” and “Blocked Sessions”. One record should appear on each section. By right clicking on the record, you have the option to “Kill the session” or “Show current SQL”. For now, we will not kill the session. We will investigate further.
Upon checking both sessions by utilizing “Show current SQL”, it shows that session 2 (SPID 76) is only doing a select statement while session 1 (SPID 58) is blank indicating that a transaction is being done. Knowing this, I can either terminate session 2 since it only does a select statement or terminate session 1 to stop the transaction. In this example, I killed session 2 since it’s only a select statement.
Upon checking it, session 2 has been terminated releasing the lock in the process. Session 1 continues to execute its transaction.
In summary, tools like dbWatch makes it much easier to deal with locks. You get alarm notifications and quick insight into the situation, and ability to quickly resolve the issue at hand.