Introduction

This script ensures that one thread does not enter a critical section of code while another thread is in the critical section. If another thread tries to enter a locked code, it will wait, block, until the critical section is released.

Background

First, we review concepts of temporary tables.

There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Read more here.

Using the Code

With this script, we ensure that the code within the BEGIN TRY will be executed by a process at a time.
We use a global temporary table as a flag to see if there is another process within the critical section.
OBJECT_ID SQL function checks for the table, if it does not return null there.
If the global temporary table does not exist, it enters the critical section, but waits in a loop until the section is unlocked.

The WAITFOR DELAY statement is used for the loop to wait a reasonable time to re-verify income section.
If an error occurs, go to the CATCH section and delete the temporary table releasing lock on the critical section.

WHILE (1=1)
BEGIN
      IF (OBJECT_ID('tempdb..##LOCK_SECTION_A') IS NULL) BEGIN     
            BEGIN TRY   
                        CREATE TABLE ##LOCK_SECTION_A (ID INT)
                            
                        /*-------------------*/    
                        --SCRIPT HERE----------
                        /*-------------------*/
                        
                        DROP TABLE ##LOCK_SECTION_A
                        BREAK;        
            END TRY                                        
            BEGIN CATCH
            
                  IF (OBJECT_ID('tempdb..##LOCK_SECTION_A') IS NOT NULL)
                        DROP TABLE ##LOCK_SECTION_A
                           
            END CATCH  
      END              
      
      WAITFOR DELAY '00:00:00:100'
END

History

23^rd October, 2015: Initial post

SQL Server Lock Critical Section