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)
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
- 23rd October, 2015: Initial post