The Tech forum

Understanding Locks - II

Article summary

In the last article we explored database locking. We will continue the journey in this article. Locks are placed implicitly. However, understanding the logic behind locking helps you write more scaleable applications. We will discuss types of locks, duration for which they are held and how to minimise the impact of locking on other transactions that are waiting for locks to be released.

How are locks held during transactions?

Locks are held depending upon what type of transactions you have.

Try this simple experiment.

1. Start Query Analyzer.

2. Connect to database PUBS.

3. Execute the commands:

Begin transaction
Select * from authors

4. Now open another connection to the PUBS database.

5. Execute the command:

Delete from authors

6. Now watch current activity. The delete command is blocked because of the first ‘Select’ statement.

In the earlier example, we had a read-write activity blocking a read activity. Which is easy to understand. Here a simple read-only query is blocking a read-write one.

A lesser level lock can block a higher level lock. This is a very important concept to understand.

Type of locks

Let us understand the types of locks. The list is long and complex. You may even get bored reading through it. However, you must realise that behind every transaction in your application, all this is happening behind the scenes to protect data integrity.

So it is important for us to know the details.

Lock request mode: This column is the lock mode of the requester and represents either the granted mode, or the convert or waiting mode. It can be:

0 = NULL. No access is granted to the resource. Serves as a placeholder.

1 = Sch-S (Schema stability). Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.

2 = Sch-M (Schema modification). Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.

3 = IS (Intent Shared). Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.

4 = SIU (Shared Intent Update). Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.

5 = IS-S (Intent Shared-Shared). Is a composite key-range lock mode that is used to ensure serializability in index row-level locking.

6 = IX (Intent Exclusive). Indicates that the locking session will acquire X locks on subordinate resources in the lock hierarchy.

7 = SIX (Shared Intent Exclusive). Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.

What are Latches?

These are lightweight (less overheads) versions of locks which are used temporarily by the locking engine. These are used to protect rows reading operations during transactions.

This lock is held when the database is reading a row and transferring it to a client connection. Once the row is transferred, the latch is released.

Lock hierarchy

During the course of a transaction, various types of locks may be applied to resources. For read-only operations, shared locks are applied. This allows other connections trying to read the same data to work without a problem. However, if an update needs to happen, the resource can not have any other lock held on it—not even a shared one.

Now what happens if there is a request for updating the data, while a shared lock is already in place and a queue is created. All locks have to wait in the queue till all locks in front of the queue are released. Once the connection wanting to perform a write operation gets its turn, an exclusive lock is applied on the required resource and the update is completed.

There are many variations to this simplistic methodology. For example, consider the Serializable transaction isolation level. In this case, phantom rows need to be prevented. What are phantom rows? While one transaction is updating certain records (say with primary key values between range 1 to 10, which happened to be 1, 3 and 5), another transaction should not be able to add item with primary key 4. This is achieved with key range locks. This lock is put in addition to the base exclusive locks on the table data.

This way, different combinations of lock types at various stages of the transaction protects data from corruption and mishaps.

Lock status

Waiting, Converting, and Granted locks: A lock is requested by a connection. And it is granted by the lock manager. This is not an immediate process. Further, there is no guarantee that a requested lock will be honoured. Further, one type of lock may get converted to another type depending upon the type of transactional activity.

Therefore, locks can go through various phases. Initially it is waiting to be granted. Then it is converting to the granted type and then it is of granted type.

Information about each lock and its status is stored in a system table in Master database called

syslockinfo. Given below are only some of the fields and their usage.

Syslockinfo:

Controlling locks programmatically

It is possible to control locking behavior in a programmatic manner. This are called locking hints. These are instructions to the query processor to explicitly use user-specified types of locks on resources.

Although this is a very powerful feature, it is best to leave the locking decisions to SQL Server in most cases. Only in special cases where you have a good justification that the locking behavior needs to be tweaked, should you do otherwise.

These are additional arguments which are supplied with Select, Insert, Update, Delete statements which can override and enforce specific locking behaviour.

Refer to the the following page for a description of these locking hints.

Monitoring locking behavior

I have already mentioned the usage of Current Activity from the Enterprise manager. In addition, the performance monitor has many counters which provide an online monitoring facility for various aspects of locking. Here are some of the available counters.

Base counters provide information about number of acquired locks, deadlocks, escalated locks and timed out locks. It also provides some summary information like Average Wait Time, lock requests per second, timeouts per second, and deadlocks per second.

For each of these, you can further select one or more of the actual resource types. For example, you can monitor, lock requests per second only for table locks.

Here is how the performance counter dialog looks.

Depending upon the problem at hand, you have to choose the correct set of counters in order to monitor, troubleshoot and correct issues.

Summary

I hope these articles help you in realising the sophistication, complexity and flexibility the query execution engine offers. I urge you to read further details, and even perform group sessions with colleagues where you can run live, complex queries and view the locking behaviour. Of course, you should do this on a test setup. This will help you in realising the way the system is working internally. This knowledge will further help you in writing high performance applications and create best practices for your organisation which can then be followed by less experienced developers.

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