MS SQL: Locking

Locking is a feature to ensure transactional integrity and database consistency, it prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time.

If two users try to modify semantically-unrelated but physically-near data in two separate tables in reverse order, both users will start off with row locks, then try to upgrade them to page locks, and the situation will be that each user wants something the other user has, so they're stuck. This is called a deadlock and you need to kill a process to remove it (more details here).

Let's generates a lock using database PUBS:

PRINT 'Script 1''s SPID = ' +CAST (@@SPID as varchar)
DBCC TRACEON(2861)
USE Pubs
GO
PRINT 'Begin a transaction and create a block'
BEGIN TRAN
DELETE FROM authors WHERE au_id = '341-22-1782'
GO
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
PRINT 'Script 2''s SPID = ' + CAST(@@SPID as varchar)
USE Pubs
GO
SELECT * FROM authorsGO

You won't see the result of this query as it is blocked and if you run

sp_lock

You will see a status = WAIT for the process id related to script2

Microsoft SQL Server provide a NOLOCK statement that allow the Server to ignore locks and read directly from the tables.

The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and error message 1222 "Lock request time-out period exceeded" is returned to the application.

To determine the current LOCK_TIMEOUT setting, execute the @@LOCK_TIMEOUT function, for example:

select @@lock_timeout

A value of -1 (default) indicates no time-out period (that is, wait forever).

Share on Google Plus

About Vittorio Pavesi