<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=204641&amp;fmt=gif">

Database Locks - how to monitor and manage

Posted by Rey Lawrence Torrecampo on Jan 31, 2022 10:00:00 AM

Database locks have been a bane to most DBAs.

They cause unnecessary problems to databases and business processes. In this blog, we will examine what database locks are and issues that can arise from them and provide ways to deal with them on SQLServer.

Not only that, but 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. 1. Observing an all-or-nothing scenario for multiple and separate transactions
  2. 2. Preserving consistency in the database state 
  3. 3. Isolating transactions from being committed until that transaction is complete
  4. 4. Saving committed transactions even in the event of abnormal termination

They adhere to the ACID properties of a transaction. To put it in perspective, imagine a piece of paper being shared by three writers.

AcidProperties

 

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, work in the same fashion. They prevent simultaneous writing and updates from maintaining 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:

 

TabularFormatforTablelocks

 

Database locks most commonly appear 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 a problem when only a few active sessions transact 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: Lock contention. It happens when concurrent sessions compete to update the same data. As 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. Worse, 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. Like Lock Contention, Long Term Blocking happens when multiple users access the same database component. The notable difference occurs when the user holds on to that component for a long time. Dependent sessions and objects will be blocked from reading and writing.

Last, we have Deadlocks. Database Deadlocks happen when two or more database sessions hold to a database object dependent on their transaction. Hence, sessions can only continue if the other concedes hold of that dependent object.

 

ExampleofDeadlocks

 

 

The figure above simplifies how deadlocks occur. Database Management Systems (DBMS) can detect deadlocks. It will select a session and roll back that session’s transaction. The only caveat is that DBMS will choose the most straightforward transaction to roll over.

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 highly dependent on timing and data will always lead to writing or outputting incorrect information.

 

What can I do when database locks occur?

Now that we better understand the kinds of locks and how they occur, we can discuss how to deal with and prevent them in the future. Before that, let us simulate how a lock can occur in a session.

I have prepared two separate sessions to act as our users in this example. Session 1 will designate as our first user; then Session 2 will be our second user.

 

SimulatingDatabaselocks1

 

The figure above shows a simple query that locks the designated table test_table. After that, we will execute another query for Session 2 that will select the specified table.

 

SimulatingDatabaselocks2jpg

 

Use Master
GO
exec sp_who2
GO

 

The stored procedure above will display a tabularized format of information you need to know who is blocking who. As seen below, ID 54 (session 2) is being blocked by session 76 (session 1).

 

SimulatingDatabaselocks3

 

Similarly, you can use the following query to get the information you want:

USE Master
GO
SELECT session_id, start_time, command, status, blocking_session_id, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
GO

 

You will still end up with the same information as before.

 

SimulatingDatabaselocks4

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).

SimulatingDatabaselocks5

 


That seems more straightforward than expected. But this is still a bare-bone example with a negligible impact on our business processes and databases. In reality, killing a session rolls back the transaction before changes in the database state. 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 need sufficient knowledge when dealing with table locks. Do not nonchalantly kill the session to have a quick-fix solution to the problem. In queries using BEGIN TRANSACTION, you always have to save points after every critical alteration to the database. Remember to end your transactions with ROLLBACK or COMMIT since the transaction affects these lock tables. Finally, only kill sessions if they will have minimal impact on your business operations and data dependencies. This is your last resort.

Another option is to utilize 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 of 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 step-by-step using Control Center.

 

SimulatingDatabaselocks1dbwatch1

 

First of all, we will change both the warning threshold and the 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 will raise an alarm status when it is above 30 minutes.

 

SimulatingDatabaselocks1dbwatch2

 

I will alter it to notify me as a warning when it reaches 4 minutes and alarms me when it reaches over 12 minutes.

SimulatingDatabaselocks1dbwatch3

To show how that blocking has occurred, the screenshot above shows that the blocking has already taken effect.

SimulatingDatabaselocks1dbwatch4

 

Using the query before, I checked if table locks were occurring in the database. 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.

 

SimulatingDatabaselocks1dbwatch5

 

 

In the monitoring module, the blocking statistics job will check if table locks are 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 the following:

  •          Who?    -->  which sessions are blocking who
  •          What?   --> what query is being executed
  •          Where? --> what management tool host is this occurring in when the                                            blocking has occurred

 

SimulatingDatabaselocks1dbwatch6

 

 

After waiting a little while, the status is changed to an alarm, indicating that it has already passed the 12-minute mark.

SimulatingDatabaselocks1dbwatch7

 

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 see "Blocking Session.” Click on the arrow beside Blocking sessions to display "Blocking Session History,” which will help us determine if logs are present during the lock. As we can see in the above screenshot, the lock was recorded and sorted starting from the latest blocking.

SimulatingDatabaselocks1dbwatch8

 

 

After confirming the logs, we will return to "Blocking sessions" to manage our database. Two sections will appear on that view – "Blocking sessions" and “Blocked Sessions.” One record should appear in each section. By right-clicking on the record, you can "Kill the session" or “Show current SQL.” For now, we will not kill the session. We will investigate further.

SimulatingDatabaselocks1dbwatch10

 

 

Upon checking both sessions by "Show current SQL,” it shows that session 2 (SPID 76) only makes a select statement. In contrast, session 1 (SPID 58) is blank, indicating that a transaction is being done. Knowing this, I can terminate session two since it only makes a select statement or terminates session 1 to stop the transaction. I killed session two in this example since it's only a select statement.

 

SimulatingDatabaselocks5

 

Upon checking it, session two has been terminated, releasing the lock in the process. Session 1 continues to execute its transaction.

--------------------------------------------------------------------------------

In summary, tools like dbWatch make it much easier to deal with locks. You get alarm notifications, quick insight into the situation, and the ability to resolve the issue quickly.

Grab a free time-limited copy of dbWatch Control Center here if you want to check it out.

 

Topics: database operations, sql server monitoring, sql monitoring tools, database monitoring, sqlmonitor, sqlperformance, sqlmanager

control-center-cta