Diagnosing the deadlock problem of SQL Sever with sp_lock

Find out what's locked up.

The slow response of the system means you should do some research. Your search is best started by measuring the number and frequency of locks occurring on the system. If your system environment is highly transactional, it is common for individual applications to compete for resources, which can lead to locking. The key to solving these problems is the ability to identify locked resources and the process of competing for resources.

Sp_lock

Sp_lock This system stored procedure is packaged with SQL Server 2000, which gives you a deep understanding of the locks that take place in your system. This program returns a large amount of information related to locking from the syslockinfo in the primary database, which is a system workbench that includes all the allowed, transformed, and waiting lock request information.

Let's take a look at what information it will provide to us after running the sp_lock program:

EXECUTE sp_lock

In my system, this is what the stored procedure returns. The information returned by Sp_lock is not at a glance, and some search is needed to get useful data. However, you can also copy the text of the stored procedure and then create a new one to get a better explanation of the system process. (In this article, we'll focus on the data returned by sp_lock.) ）

From the above results we can see the SPID, dbid, ObjID, indid, type, resource, mode, and Status fields. The SPID is the process identification number that is used to identify the connection to the SQL server. To discover which users are connected to the SPID, you execute the stored procedure sp_whoand transfer the SPID as a parameter to the program. Dbid is the database where the lock occurs, and you can find it in the sysdatabases table in the primary database. The field ObjID is used to display the object where the lock occurred in the database. To view this object, you can query the specified objid in the sysobjects table in the primary database.

A single record generated on the above screen does not necessarily show what is happening in your working environment. When running this program, you want to find 500 to 1000 or even more results. Every time you execute a sp_lock, it is possible to get different results because new locks have taken place, and some of the old locks have been lifted. If you find that sp_lock returns a large number of results with the same SPID, it is likely that the process is undergoing large processing, and these locks may begin to prevent new transactions from occurring.

When you find that a SPID acquires a large number of database locks, this will help determine what stored procedures or statements are running. To achieve this, run the following DBCC command:

Check for deadlock information

DBCC InputBuffer (SPID)

This DBCC command returns information about the statement that is running in the EventInfo field and displays the SQL command being executed.

?

Unlock deadlock

Kill spid

A reliable starting point

A slow system may indicate a large number of locks on your table. These locks are caused by a number of reasons, such as a user running a fairly long query on your system, a process that consumes a lot of resources, or two critical processes competing for the same resource, often resulting in deadlocks.

What should you do once you find a process that you think is slowing down your system speed? In most cases, no action can be taken to monitor the system. It is not advisable to end this process because it includes a lot of system locking, unless you are absolutely sure that there will be no other negative effects. Otherwise, you should find a way to automatically analyze the lock status. Another solution is to come up with a way to make a notification when the number of system locks reaches the limit at a certain time of day.

The more information you collect about your system, the greater your advantage in solving the problem.

Diagnosing the deadlock problem of SQL Sever with sp_lock