Best Way to Find SQL Locks in SQL Server 2008

How to check which locks are held on a table

To add to the other responses, sp_lock can also be used to dump full lock information on all running processes. The output can be overwhelming, but if you want to know exactly what is locked, it's a valuable one to run. I usually use it along with sp_who2 to quickly zero in on locking problems.

There are multiple different versions of "friendlier" sp_lock procedures available online, depending on the version of SQL Server in question.

In your case, for SQL Server 2005, sp_lock is still available, but deprecated, so it's now recommended to use the sys.dm_tran_locks view for this kind of thing. You can find an example of how to "roll your own" sp_lock function here.

How to find out what is locking my tables?

Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS):

• sp_who
• sp_lock

Also, in SSMS, you can view locks and processes in different ways:

Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu when you right-click on a server node.

Finding blocking/locking queries in MS SQL (mssql)

You may find this query useful:

SELECT * 
  FROM sys.dm_exec_requests
  WHERE DB_NAME(database_id) = 'YourDBName' 
    AND blocking_session_id <> 0

To get the query itself use this one:

SELECT text,* 
  FROM sys.dm_exec_requests
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
  WHERE DB_NAME(database_id) = 'YourDBName' 
    AND blocking_session_id <> 0

Is there a way to check locks granted by a transaction

Answer (1):

if the transaction hasn't been blocked by any pre-existing locks, then all rows in the Student table with a subjectCode = 'tech' will now have a (X) (exclusive) lock on them.

On the page and table level, there will be (IX) (intent exclusive) locks - signaling that somewhere further down in the "hierarchy" (table -> page -> row) there are exclusive locks in place.

If you're updating more than 5000 rows, then lock escalation will have kicked in and SQL Server has replaced the 5000+ individual row-level exclusive locks with a single table-level exclusive lock. That means no one can do anything with that table, basically.

For your question (2) - see the answers to this other SO question.

Finding out which locks that are acquired in a query on SQL Server?

I would suggest that you turn on the Deadlock Detection Trace Flags in the first instance, rather than running a Profiler Trace indefinitely.

This way, the event details will be logged to the SQL Server Error Log.

Review the following Books Online reference for details of the various trace flags. You need to use 1204 and/or 1222

http://msdn.microsoft.com/en-us/library/ms188396(SQL.90).aspx

Be sure to enable the trace flags with server scope and not just the current session. For example use:

DBCC TRACEON(1222,-1)

Default lock settings SQL Server 2008 R2

I want to know, by default, does a SELECT statement lock a table?

Yes. All transactions create locks. Not all locks are blocking (shared locks usually are not for example) but all transactions [that interact with the database] create locks. Locks are how the query engine keeps track of what it's writing or reading.

Even if you specify NOLOCK or READUNCOMMITTED (which you generally don't want to do) you'll still get locks because the data needs to be consistent even if it's dirty, and the locks ensure that it is. That is to say, even if it returns uncommitted records (dirty), there are no records that are incomplete or partially changed (inconsistent).

I found that if I use table hints such as HOLDLOCK or TABLOCK the select would lock the table [until] it's completion, but when no table hint is specified what is the behavior of SQL?

There are no default lock hints. The query engine determines what it needs dynamically. SELECT will tend towards Shared or RangeS key-range depending on the query. INSERT/UPDATE/DELETE will tend towards exclusive locks. The system will generally favor the lowest granularity, row locks, but will escalate them to page locks or table locks if it thinks it should. Note: they're called "lock hints" and not "lock commands" because the query engine will ignore them if it has to.

The closest thing you'll find to default lock hints is the transaction isolation level, which generally controls how queries in a transaction function and defaults to READ COMMITTED.

Dynamic locking, lock granularity, lock escalation, and the lock hierarchy are all explained in the MS doc I already linked in the comment.

There's a huge amount of nuance and complexity to the locking system. The same query on a different system might get different locks because the table sizes were different or memory pressures were different.

You can also modify the locking system significantly with ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT database options, but I would not make those changes lightly. It does make the database perform somewhat more like Oracle, however.

Joins would affect it's behavior?

Very much so. To do a join, the query engine typically has to either scan indexes or the actual tables. Indexes are subject to locks just like tables are, so I'd expect Shared or RangeS-S locks to appear on objects in the database.

And another question, picture the following scenario: I start a connection and transaction at my .NET application and bind them to a command, which runs a stored procedure containing only a SELECT statement. Will the select lock the table due to the transaction or not?

Yes.

If you're using System.Data.SqlClient.SqlTransaction, the default isolation level is ReadCommitted, which (unsurspisingly) maps to the READ COMMITTED isolation level. If you're using System.Transactions, however, then you will default to Serializable, which maps to SERIALIZEABLE (the strictest isolation level).

How to check if a table is locked in sql server

Better yet, consider sp_getapplock which is designed for this. Or use SET LOCK_TIMEOUT

Otherwise, you'd have to do something with sys.dm_tran_locks which I'd use only for DBA stuff: not for user defined concurrency.

---

Related Topics