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
                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.
    exec @retval = sp_releaseapplock @Resource = 'applock$test', @LockMode = 'exclusive';

    --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. 

To page 257 of my Pro SQL Server 2005 Database Design and Optimization, I want to add the following example about getting the metadata about computed columns.

To get information about your computed column, you can use a couple of catalog views. The first is sys.columns, and the second is sys.computed_columns. As an example, using the tables that were created in the original text:

--This code was from the original book:
create schema alt
go
CREATE TABLE alt.testCalc
(
      value varchar(10),
      valueCalc AS UPPER(value),
      value2 varchar(10)
)
GO
INSERT INTO alt.testCalc --this name was wrong in the original text
VALUES ('test','test2')
GO
SELECT *
FROM alt.testCalc
go

You can determine when a column is a computed column by looking at the sys.columns view:

select cast(name as varchar(20)) as name, is_computed
from sys.columns
where object_id('alt.testCalc') = object_id

This returns:

name                 is_computed
-------------------- ------------
value                0
valueCalc            1
value2               0     

If you want to see the specific metadata about the computed column, you can use the sys.computed_columns view, which will give you several pieces of information, including if the column is persisted, and the definition of the computed column:

select cast(name as varchar(20)) as name, is_persisted, definition
from sys.computed_columns
where object_id('alt.testCalc') = object_id

Which in our case is:

name                 is_persisted    definition
-------------------- --------------- --------------------------------
valueCalc            0               (upper([value]))

Most contests are of the sort where you win a book that you don't have, probably by some sort of chance.  This contest, on the other hand, is for people who have purchased the book (or have a copy from a library, or just "borrowed" it from a coworker's desk, etc.)  If you own the book, you probably noticed that I tried to lighten up the topic with the occasional joke, or humorous tidbit.  Obviously the topic is pretty dry in and of itself, and I am a bit of a cut up, as any of the ten people who have come to hear me speak know. 

So when I was during the final layout phase of the book, I thought I would have a bit of fun for later to insert a hidden message in the book.  Over the next 12 weeks, I am going to present on my website page for the book (http://drsql.org/ProSQLServerDatabaseDesign.aspx,) a series of twelve clues to find the hidden message, which is very inconspicuously hidden in the book for you to find, as a reward to those of you who really, really pay attention!  Or at least those who can follow a set of clues.

Grand prize (1) - 250 GB WD USB Hard disk
First Prize (1) - Bundle of Apress books  (Pro SQL Server 2005, Pro SQL Server 2005 Assemblies, Pro SQL Server 2005 Reporting Services, SQL Server 2005 T-SQL Recipes, as well as another copy of Pro SQL Server 2005 Database Design and Optimization to give to a friend, assuming you used your own copy to win the contest with!)
Second prizes (10) - Apress T-Shirt

I will review the answers and the first person to correctly identify the quote (including punctuation,) where the quote is from, the page number from the book, and what made this a hidden message, will get the grand prize.  The second will get the first prize, and so on.  If no one correctly identifies the information identified above, I will choose the closest response and award the prizes.  If persons get the correct answer, I will reveal that a winner has been found, but the names of the actual winners and hidden message will be revealed on December 27.

Fine print: All decisions by the judges will be final.  First and Grand prizes will not be awarded to any person who has received a promotional copy of the book, nor persons employed by Compass Technology in Chesapeake VA.  Any winners from these two groups will be awarded Second Prize.  All submissions become the property of drsql.org and may be posted anonymously here or on the blog at drsql.spaces.live.com at any time. 

This returns: