All to often developers need to force some locks on a table so that they can be sure that the records aren’t going to change between the time that they first look at the records and when the transaction is completed. The most common method that I’ve seen to do this involves at the top of the transaction running a select statement against the table, with the UPDLOCK or XLOCK which forces the database engine to take higher locks than it normally would against the table. While this does have the desired end result of locking the table, is causes a lot of unneeded IO to get generated, and takes a lot more time than is needed.
For example, lets assume that we want to lock the Sales.SalesOrderHeader table in the AdventureWorks database so that we can do some processing on it without allowing anyone else to access the table. If we were to issue a SELECT COUNT(*) FROM Sales.Individual WITH (XLOCK) against the database we lock the table as requested, however it generates 3106 physical reads against the database as we can see below in the output from the Messages tab.
/*————————
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
BEGIN TRAN
SELECT COUNT(*)
FROM Sales.Individual WITH (TABLOCK)
/*Business Logic*/
COMMIT
GO
————————*/
DBCC execution completed. If DBCC printed error messages, contact your system administrator.(1 row(s) affected
Table ‘Individual’. Scan count 1, logical reads 3090, physical reads 8, read-ahead reads 3098, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
If we look at the sys.dm_tran_locks DMV we’ll now see that we have taken an exclusive lock against the table (don’t forget that you have to query the DMV within the transaction in order to see the lock). That is a lot of IO to generate in order to generate a single lock within the database engine. You can imagine what would happen if this was a much larger table, say a fact table within a data warehouse. A large multi-year fact table could end up generating millions of IO just to lock the table.
A better solution to this sort of problem would be the sp_getapplock system stored procedure. This procedure allows you to table table level locks without running queries against the table. It can lock tables with are Gigs in size in just a second. When we run the command telling it to lock the Sales.Individual table, we get no IO being generated and yet we still see the object being locked. In this case we would run the below command to generate the needed lock.
exec sp_getapplock @Resource=’Sales.Individual’, @LockMode=’Exclusive’
The only difference we should see in the output of the sys.dm_tran_locks DMV is that the value in the resource_type column has changed from OBJECT to APPLICATION. Once the lock has been taken against the database we can do all the processing that we want to against the table without having to worry about another user coming in and changing the base data of the table.
The sp_getapplock procedure must be run within an explicit transaction, and has several parameters so that you can control what it is doing.
The first parameter is @Resource which we used above. This parameter is how you tell the stored procedure what object you wish to lock. It accepts the input as schema.object or just the object if the object is within your default schema. It is recommended that you use the two part name to ensure that you are always locking the correct object.
The next parameter is @LockMode which we also used above. This parameter allows you to tell the database engine what locking level you used. Your options are "Shared, Update, IntentShared, IntentExclusive, and Exclusive”. Any other value specified will throw an error.
The third parameter is @LockOwner. This parameter allows you to tell the stored procedure to take the lock for the duration of the transaction (the default) or the duration of the session. To explicitly specify that you want to take the lock for the duration of the transaction specify the value of “Transaction”. To specify that you want to take the lock for the duration of the session specify the value of “Session”. When the value of “Session” is used the procedure does not need to be called within a transaction. If a value of “Transaction” or no value is specified then the procedure does need to be called within an explicitly defined transaction.
The fourth parameter is @LockTimeout. This parameter allows you to tell the procedure how many milliseconds to wait before returning an error when attempting to take the lock. If you want to procedure to return immediately then the specify a value of 0. The default value for this parameter is the same as the value returned by querying the @@LOCK_TIMEOUT system function.
The fifth and final parameter is @DbPrincipal. This parameter allows you to tell the procedure the name of the user, role or application role which has rights to the object. Honestly I haven’t really figured out what this parameter is used for. What I do know, is that if you specify a user, role or application role which doesn’t have rights to the object the procedure call will fail. This parameter defaults to the public role, if you get an error when using the default value create a role with no users in it, and grant the role rights to the object then specify the role within the parameter. No users need to be assigned to the role to make this work.
Releasing the lock that you’ve just taken can be done in a couple of different ways. The first is the easiest, commit the transaction using COMMIT (ROLLBACK will also release the lock, but you’ll loose everything that you’ve done). You can also use the sp_releaseapplock system stored procedure. The sp_releaseapplock procedure accepts three parameters which are @Resource, @LockOwner and @DbPrincipal. Simply set these values to the same values which you used when taking the lock and the lock will be release. The procedure sp_releaseapplock can only be used release locks which were taking by using the sp_getapplock procedure, it can not be used to release traditional locks that the database engine has taken naturally, and it can only be used to release locks which were created by the current session.
Hopefully some of this knowledge can help speed up your data processing times.
Denny
One Response
This method will generate 0 I/O’s and will take a table lock that gets held for the duration of transaction:
[CODE]Checkpoint;
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
BEGIN TRAN
SELECT *
FROM Sales.SalesOrderDetail WITH (tabLOCK, holdlock)
Where 1 = 2
Select *
from sys.dm_tran_locks
Where request_session_id = @@SPID
/*Business Logic*/
COMMIT
GO [/CODE]