Tech Forum

Understanding Locks

Article summary

In a previous article I covered ‘transaction isolation’ levels. A related topic is locking. Earlier, we had seen locking in the context of blocking and deadlocks (“Learning from other’s mistakes” – 3rd Feb 2003). In today’s article we cover more details related to locking and various ways of minimising / controlling it.

Why use locks?

In a database related activity, there is a need to ensure that while one user is working on a resource, that resource is not available to other users. This is why the resource needs to be locked till the original user finishes the desired activity.

Of course, this is a very simplistic description. Starting with this base thought, locking gets more and more refined to cater to various needs of database handling and integrity management.

Types of resources locked

Typically we feel that only tables and rows are locked. But that is not so. There are many other resources that need to be locked.

This is an important concept that must be understood. As described earlier, when a transaction requires to lock a particular area of the table, it starts off with row level locking.

Now, each query may require to lock differing number of rows.

Consider a table (called authors) with 100 rows.

The query:

Update authors
set au_fname = “not known”
where au_id = 123

Now in this case, the au_id is the primary key and therefore, there can be only one row with a given au_id. This query will require to lock only one row.

Now consider another query on the same table

Update authors
set au_fname = upper(au_fname)

This query will have to lock every row of the table because there is no ‘where’ clause. This means there would be 100 row level locks.

Each lock requires time and effort. SQL server needs to remember the lock, the range locked by it, ensure that other connections do not interfere with the lock, it has to maintain a queue for other processes trying to acquire a lock on these rows, and so on.

In short, it is very complex and therefore time consuming to keep track of too many locks.

But as you know, the locking starts with row level locking. However, SQL Server does not go on locking rows blindly. Every time it is about to lock a row, it thinks whether locking more and more rows is the best option available, or would it be better to lock the page, extent or the entire table.

If the query execution engine feels that locking more rows is going to make things time consuming, it simply decides to ‘escalate’ to the next level of lock.

The next level is page level. A ‘page’ is 8k of data. Depending upon the size of a row, one page will typically contain many rows. If one row is being updated, the entire page is locked. This means that some rows, which ideally do not need to be locked, are also locked now. This obviously would lead to more transactions having to wait for this lock to be released. If so, such escalated locks can actually reduce the concurrent performance.

This is logically right. But in reality, the decision of escalation from row to page level is taken based upon some smart calculation by SQL Server.

It calculates how much concurrency would be affected if all row level locks are held as required by the query. It also compares how much time and processing would it save by using page rather than row level locking. If the second option is less time consuming, it chooses to escalate to page level locking.

Now at what point of time does holding individual row level locks become inefficient? The point at which page level becomes cheaper (or with lesser overheads) than continuing row level locking is called the ‘escalation threshold’.

In earlier versions, we had to manually set the escalation threshold. Of course there was a default, but you had to configure this setting if you felt the default threshold is not right for your query.

Now that additional work is taken off from your shoulders. SQL Server automatically keeps monitoring the query execution and then decides if and when it will escalate the locking behaviour from row to page to table. This is called Automatic Lock Escalation.

If this is automatic, why do we need to know about it? Simply because, this understanding is the basis of grasping the full power and complexity of the locking behaviour and the transaction isolation types. This knowledge will help you in designing more efficient transactions, which minimise blocking of other transactions.

I have observed that often queries are written without any regard to their locking impact. This is probably the most common reason why performance issues appear in systems that are running without a problem for long periods.

Initially the system contains small amounts of data. Therefore, even if there is abnormal or non-optimal blocking across concurrent users, it is not noticed. When the data sizes grow, these lock contentions become more and more apparent and system performance slows down.

It is even more surprising that many DBAs (and developers) do not know where to view the current status of locks held in the database.

Where to look for current locking status?

When there is a problem and the system slows down, most DBAs simply restart the system. This is wrong. This way, you lost a great chance you had to analyse the state of the system in the slowed down state and actually arrive at the corrective steps.

If you reboot the machine, then you will not have any way to reproduce the exact live state of various connections and parameters. Yes there may be few entries in the event log. But those are not always enough to solve a dynamic problem. There are two ways of knowing the locking status across concurrent connections across databases in the server.

• sp_lock stored procedure

This lock lists all existing locks across databases. The syntax is:

use master
EXEC sp_lock

This lists all active locks. It provides the following information about each lock: spid, object/database/index identification numbers, type of lock, lock resource, mode and status

Sometimes, interpreting and analysing this output is difficult. There is a better, visual way using Enterprise Manager.

• Current activity monitor

This is a tool available in Enterprise Manager. This appears under the Management node in the left pane of enterprise manager for a given server. Current activity node has a date time stamp. This is because, current activity in a live database server is so dynamic and complex that it would be impossible for us to view and interpret that data, if we were shown this in real time. Therefore, current activity is a snapshot of the state of various processes across databases collected at the specified timestamp. You need to refresh the current activity to get the latest information.

The current activity shows three different types of views for existing locks:

This diagram shows a blocking transaction. Observe the rows that I have highlighted. There are two columns. ‘Blocking’ and ‘Blocked By’. The Blocked By column shows the PID of the process that is blocking the current process. The blocking columns indicate the number of PIDs that are currently blocked by this process.

Now, if you find a particular transaction that is blocking others, what would you want to do? You would like to know which workstation and user is involved. You would also like to know whether this transaction is generally in hung state or if it is taking a long time to complete. You may want to see the command that was last issued. All this can be viewed by simply double-clicking the item in the processes listing.

Using this dialog, you can send the offending machine a message, kill the process, or wait for it to resolve.

All this needs to be done while troubleshooting excessive locking problems.

I suggest you try out understanding how various parts of your database interact on a live server. Try this when there is no problem being faced. This will provide you with a good deal of expertise in understanding locking behaviour, blocking functionality and related aspects.

Please note that locking is not an abnormal thing. There is no need to look at is as a troublesome feature and then try to troubleshoot it. Without locking, transaction isolation will simply not work. It would have created havoc worldwide if locking did not exist!

In the next article we will continue to explore the fascinating world of locking.

About the Author:Dr Nitin Paranjape is the Chairman and MD of Maestros (Mediline). He is a consultant with many organisations, covering appropriate technology utilisation, business application of relevant technology, application architecture and audit as well as knowledge transfer. He has authored more than 650 articles on various technology-related subjects. He can be contacted at nitin@mediline.co.in