Locking Trace Flags
There are a few trace flags that can help you fine tune your locking and find deadlock problems.
Trace flags are used to turn on or off certain behaviors in SQL Server. You can set a trace flag using DBCC TRACEON, and if you want a trace flag to be turned on every time SQL Server starts, you can specify it as a start parameter using ‘-T’.
- 1200: Shows all locks acquired by all connections. Very voluminous output, my recommendation would be to use this in a controlled environment, where only a single connection is working at the same time.
- 1204: Outputs a lot of information about the participants in a deadlock. This is an example, beautified for your pleasure.
KEY: means that the resource we’re deadlocking on is an index key. It could also be any of the other sizes of locks, like page, RID, table, etc.
ECID is taken from master.dbo.sysprocesses. It’s used to distinguish between the locks taken by different threads. Mode is the requested mode on the deadlocking lock, like S, X or U.
The string “6:885578193:2” means: database with database id 6, object with id 885578193 and the index with index id 2. The number inside the parentheses is the hash value used to recognize the lock. You can see this in the column rsc_text in master.dbo.syslockinfo. Unfortunately, this is a one-way hash, which means that you can’t find out the row locked with only the hash value. Spid is, of course, the spid holding the lock.
The way this output should be read is by first looking at the two nodes, Node 1 & 2, which show you the locks already granted and involved in the deadlock. Then you see the two locks that are in the wait queue, marked with “Requested By:”.
- 1205: Prints out information about the lock manager’s work. Every time a deadlock search is initiated, this trace flag makes the lock manager print out information about the search. Only works if trace flag 1204 has been given.
- 1211: Disable all lock escalation. This trace flag forces the lock manager not to escalate locks, even if it’s running out of locks.
Column Locks
As you probably know, the smallest unit of locking in SQL Server 2000 is the rowlock. Column locking is not directly available from SQL Server. Here, I’ll show you how you can use index locking to mimic column level locks.
Column locks are often deemed as too slow for practical purposes, and SQL Server is no exception. But since a rowlock doesn’t automatically mean that the index on the table is locked, you can always use the data on the index pages to work with data that’s really locked. Again, I’ll explain using the Region table in the Northwind database.
The Region table is a heap, with two columns, RegionDescription and RegionId. A unique, non-clustered index exists on the RegionId column.
What we’ll do now, is to change the RegionDescription on one of the rows, with a simple update, like this:
