Home > SQL Server > Lock_Timeout – SQL Server

Lock_Timeout – SQL Server


@@Lock_Timeout function returns the current session lock timeout setting value in milliseconds. It returns an integer value. It returns -1 value if the timeout value is not setup for the current session and -1 is the default value for this function. We can use SET Lock_Timeout  set statement to specify the timeout integer value.

Set Lock_Timeout values:

  • -1 : It is the default value and it indicates wait forever (that is, it wait for the resource)
  •  0  : It indicates no wait (that is, resource should be available for use immediately).
  • >0 : It indicates the actual time in milliseconds.

Set statement lock_timeout setting stays throughout the session. If we want to set the default value then we have to set the new value explicitly. It throws an error if the timeout value exceeds the limit.

Implementation

1. Executing the @@Lock_Timeout function

pic12. Set lock timeout value using set statement

pic23. This example is to show the lock timeout simple scenario

I am using a table ‘tblSales’ in this example. This is a dummy table that I have created for this demo. You can use your own table.

Script 1: This query updates the table value and wait for 10 seconds before it completes the transaction.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
UPDATE [dbo].[tblSales] SET [name] = ‘SQLServerRider’
WAITFOR DELAY ’00:00:10′  — 10 seconds   hh:mm:ss
ROLLBACK TRANSACTION

Script 2: This query will read the table ‘tblSales’ with no wait

Set Lock_Timeout 0
select top 1 * from tblSales

Experiment:

Step 1: Open two query execution windows in SSMS and paste the query 1 in first windows and paste the query 2 in window 2.

Step 2: Execute the query 1 and  query 2 in both window respectively.

Query Execution

pic3

Always make sure that you are using your goat machine for testing the sample queries.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: