This is being added to the addendum (located here) for my “Pro SQL Server 2005 Database Design and Optimization” book and would have appeared in the pessimistic locking section on page 478 in Chapter 9: Coding for Integrity and Concurrency; Pessimistic Locking. You can download the entire addendum here: Addendum.

The problem of the critical section is a very common problem.  Very often it is troublesome for more than one connection to have access to a given section of code.  For example, when you need to fetch a value, increment it, and keep it unique amongst other callers that could be executing the code simultaneously.  Exclusively locking the part of the table works, but it can be troublesome when either:

• The code is being executed within another transaction that can end up keeping more data locked than actually necessary
• Only one minor section needs to be single threaded through, but allow simultaneous access otherwise.
• The speed in which the data is accessed is so fast that you are likely to fetch the same data within microseconds of each other, leading to duplicates or deadlocks
• When the single threading is not for table access.  You may want to write to a file of some sort, or use some other resource that is not table based.

A technique to leave tables unlocked but single thread access of some sort manually, we can use an application lock to lock a section of code. 

Note that the applock must be used/honored manually in every piece of code where this matters so there is a loss of safety associated with this process.  If there is any concern with what other processes might do, be sure to still assign proper concurrency and locking hints to the code.

To demonstrate a very common problem of building a unique value without using identities (for example, if you have to create an account number with special formatting/processing,) I have created the following table:

create table applock
(
    applockId int primary key,  –the value that we will be generating with the procedure
    connectionId int,                –holds the spid of the connection so you can who creates the row
    insertTime datetime default (getdate()) –the time the row was created, so you can see the
                                                             –progression
)

Next, a procedure that starts an applock, fetches some data from the table, increments the value, and stores it in a variable.  I have a delay parameter so you can tune up the problems by making the delay between increment and insert more pronounced.  There is a parameter to turn the applock on and off also, as that will help you test to see it work, and not work without the applock.

create procedure applock$test
(
    @connectionId int,
    @useApplockFlag bit = 1,
    @stepDelay varchar(10) = ’00:00:00′
) as
set nocount on
begin try
    begin transaction
        if @useApplockFlag = 1 –turns on and off the applock for testing
            begin
                declare @retval int
                exec @retval = sp_getapplock @Resource = ‘applock$test’, @LockMode = ‘exclusive’;
                if @retval < 0
                    begin
                        declare @errorMessage nvarchar(200)
                        set @errorMessage = case @retval
                                                            when -1 then ‘Applock request timed out.’
                                                            when -2 then ‘Applock request canceled.’
                                                            when -3 then ‘Applock involved in deadlock’
                                                            else ‘Parameter validation or other call error.’
                                                        end
                        raiserror (@errorMessage,16,1)
                    end
            end

    –get the next primary key value
    declare @applockId int   
    set @applockId = coalesce((select max(applockId) from applock),0) + 1

    –delay for parameterized amount of time to slow down operations
    –and guarantee concurrency problems
    waitfor delay @stepDelay

    –insert the next value
    insert into applock(applockId, connectionId)
    values (@applockId, @connectionId)

    –won’t have much effect on this code, since the row will now be exclusively locked, and
    –the max will need to see the new row to be of any effect.

    if @useApplockFlag = 1
           exec @retval = sp_releaseapplock @Resource = ‘applock$test’;

    –this releases the applock too
    commit transaction
end try
begin catch
    –if there is an error, rollback and display it.
    if @@trancount > 0
        rollback transaction
        select cast(error_number() as varchar(10)) + ‘:’ + error_message()
end catch

Now, you can see up a few connections using this stored procedure, like this, varying the parameters to get more or less clashing. of values.  Running it in such a tight loop it is not surprising that two connections will often get the same value and then try to insert the value:

waitfor time ’23:46′ –set for a time to run so multiple batches
–can simultaneously execute
go
exec applock$test @@spid,<1=use applock, 0 = don’t use applock>,’delay in hours:minutes:seconds.parts of seconds’
go 10000 –runs the batch 10000 times in SSMS

You will probably be amazed at how many clashes you get if you have applocks turned off. Doing 10000 on 3 connections on a Pentium 4 2.1 GHz Laptop, I got over 1000 clashes pretty much constantly. With applocks turned on, all rows were inserted in very close to the same amount of time.

To solidify the point that every connection has to follow the rules, turn off applocks on a connection or two and see the havoc it will wreak.  The critical section will now no longer be honored by the one connection, and it will clash quickly, especially if you use any delay (that is more interesting when you use locks. Without them, it will clash nearly every time.