the Lock Manager in SQL server is great, but sometimes your application needs might need a little adjustment to the engine, here where query hints, lock timeouts, custom lock modes, and several other ways of controlling the engine come in handy, today we will explore these.

SET TRANSACTION ISOLATION LEVEL <any level>;

Check out our previous article to learn about them here(here), and for some demos check out here(HERE)

Lock Timeout:

how long does SQL Server hold a lock? Until the end of the transaction, how long does the blocked transaction wait for the lock to be released? default behavior is indefinite. what do you call that? Lock timeout how do you check for it?

SELECT @@LOCK_TIMEOUT

Mine was which is the default behavior so -1 if you see 0 then it will not wait for any locks, once a resource can not be acquired it will give up other values means you set it to whatever you want for example

SET LOCK_TIMEOUT 7000;
SELECT @@LOCK_TIMEOUT AS LOCK_TIMEOUT_MS

the results were now let’s reset it to the default behavior

SET LOCK_TIMEOUT -1;
SELECT @@LOCK_TIMEOUT AS LOCK_TIMEOUT_MS

if the lock timeout was exceeded it will return error 1222 for example

use ContosoRetailDW;
BEGIN TRAN 
UPDATE dbo.DimCurrency WITH(SERIALIZABLE)
set CurrencyName = 'CAD'
where CurrencyName ='USD'
WAITFOR DELAY '00:00:10'
--- IN THE FIRST SESSION THEN IN ANOTHER SESSION(NEW QUERY BUTTON)
USE ContosoRetailDW
SET LOCK_TIMEOUT 1
SELECT
@@LOCK_TIMEOUT
BEGIN TRAN 
SELECT CurrencyName
FROM DimCurrency
COMMIT TRAN

and this will pop up this option is at the session level so you have to set it at each session.

Locking Hints:

you have instance-level configurations, they apply to the whole thing, but sometimes applying table-specific instructions is more optimal, locking hints apply themselves on table level, so if you want a specific table to read in SERIALIZABLE you hint ( order ) the engine to use that. hints are forced on the engine, they are like a general’s orders, the engine doesn’t get to choose what he should apply, you choose for him, even if the choice you made was worse than the original engine’s decision, so you should test rigorously before you hint, and revisit manually every now and then to see if the decision you made still viable after the first test. You could specify which isolation level you want to use on the table like SERIALIZABLE, what kind of resource you want to use like ROWLOCK or PAGLOCK, or you could specify which mode you want to use like UPLOCK. and there is the READPAST hint which will skip the rows ( and only the rows, not other resources)that already have locks on them and read the rest, but if the resource was a PAGE or an OBJECT the engine will ignore the hint and the statement will be blocked.

USE ContosoRetailDW
BEGIN TRAN 
UPDATE DimCurrency WITH(SERIALIZABLE)
SET CurrencyName ='AUD'
WHERE CurrencyName ='USD';
WAITFOR DELAY'00:00:05'
SELECT*
FROM DBlocks
COMMIT TRAN

 Then in another session almost immediately 

BEGIN TRAN 
SELECT CurrencyName
FROM DimCurrency WITH(NOLOCK)
COMMIT TRAN 

• now even though our isolation level is READ COMMITTED our DBLocks(Kalen Daleny’s) shows Key-Range locks

• and even though our first session blocks anything that needs locks our second session’s NOLOCK( READUNCOMMITTED) hint does not need any locks so it reads the new information before the other transaction is done

the other session is still executing now

USE ContosoRetailDW
BEGIN TRAN 
UPDATE DimCurrency WITH(PAGLOCK,SERIALIZABLE)
SET CurrencyName ='AUD'
WHERE CurrencyName ='USD';
WAITFOR DELAY'00:00:05'
SELECT*
FROM DBlocks
COMMIT TRAN

• no Key-Range locks cause it acquired one U on the PAGE because of the hint

now let’s try READPAST we will swap AUD and USD and we will see how the second session will read the stuff

USE ContosoRetailDW
BEGIN TRAN 
UPDATE DimCurrency WITH(SERIALIZABLE)
SET CurrencyName ='USD'
WHERE CurrencyName ='AUD';
WAITFOR DELAY'00:00:05'
SELECT*
FROM DBlocks
COMMIT TRAN

and the second session

BEGIN TRAN 
SELECT CurrencyName
FROM DimCurrency WITH(READPAST)
COMMIT TRAN 

• and no AUD or USD there are rowlocks on those

Bound Connections( How To Prevent Application Deadlocks)

an application might need to update a row so it opens 2 sessions

• one session selects and finds the row
• then it sends another session to update the row It selected
• deadlock happens since it can’t update the row it read
• SQL Server can’t recognize it it only sees a block on a select and a WAIT X lock
• the application is stuck because it can’t modify the row it wants
• this is an application deadlock that can’t be recognized by SQL Server

the solution is to bound connection locks = make two sessions share the same locks so if session A has an S lock on a row, session B can use it as well = no application deadlock. how could you do that? through a magic token = bound token let’s do that:

use ContosoRetailDW
go
DECLARE @magic_token VARCHAR(300);
BEGIN TRAN 
EXEC sp_getbindtoken @magic_token output ;
SELECT @magic_token;
GO

and this is what I got

--SBZJP<TFl1ge7aM;^Qc1j-5---/3HM--

copy paste whatever shows up in your results into this in the second session

use ContosoRetailDW
go
EXEC sp_bindsession'SBZJP<TFl1ge7aM;^Qc1j-5---/3HM--'-- here 
go

and the sessions will share locks, now the app will store and do this itself but for demonstration purposes, we copy pasted it manually now lets try it first session

BEGIN TRAN 
UPDATE DimCurrency 
SET CurrencyName ='AUD'
WHERE CurrencyName='USD';
WAITFOR DELAY '00:00:02'
COMMIT TRAN 

now before executing in the second session

SELECT CurrencyName 
from DimCurrency 
WHERE CurrencyName='AUD'
COMMIT TRAN 

Since the second one had no BEGIN, both have been treated by the engine as the same one. in sys.dm_tran_session_transactions you can see which sessions are bound under is_bound column for example

SELECT 
     session_id,
     is_bound
FROM sys.dm_tran_session_transactions

Application Locks= User Defined Locks=Code Locks

if you want a code, a stored procedure, not be used by anybody else but, you don’t want to lock the whole table for it, we can use this feature. how could we do it and what could we specify on this code?

use ContosoRetailDW
go
CREATE PROCEDURE dbo.APLl
as 
BEGIN TRAN 
EXEC sys.sp_getapplock
  @Resource = 'appk' -- random name 
    ,  @LockMode ='Exclusive'-- what kind of lock would the proc hold on the actual PAGE,KEY,RID,TABLE ETC.
	,  @LockOwner='Session'--- which place has the lock who can use it and nobody else can 
    ,  @LockTimeout = 1000--1 secs what is the lock time out 
  Update DimCurrency
  SET CurrencyName = 'U' 
  WHERE CurrencyName ='AUD';
COMMIT TRAN
BEGIN TRAN 
EXEC dbo.APLl;
waitfor delay'00:00:05';
COMMIT TRAN 
go

and in another window

BEGIN TRAN 
EXEC dbo.APPL;
COMMIT TRAN
begin tran
select*
from DBLocks
commit tran 

and then in a third session

begin tran
select*
from DBLocks
commit tran 

there is an application lock from the first session and a WAIT on the second so no one could use our PROC we can release it this way

 begin tran 
 EXEC  sp_releaseapplock @Resource = 'appk';
 commit tran -- if it is deadlocking 

if there is no deadlock then there is no problem and an error would show up telling us just that. and we are done for today, I'll catch you on the next one.  

---

Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners by Dmitri Korotkevitch Pro SQL Server Internals by Dmitri Korotkevitch SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Technique by Dmitri Korotkevitch SQL Server Concurrency by Kalen Delaney Microsoft SQL Server 2012 Internals by Kalen Delaney