There is another interesting SQL Server feature called application locks. This feature allows an application to place the lock to the application resource, which is not related to the database objects and identified by the name only. The lock would follow the regular rules in terms of the lock compatibility, and it can be one of the following types: shared (S), update (U), exclusive (X), intent shared (IS), and intent exclusive (IX).

An application needs to call sp_getapplock stored procedure to acquire the lock using the following parameters:

• @Resource: specifies the name of the application lock
• @LockMode: specifies the lock type
• @LockOwner: should be one of two values—Transaction or Session—and control the owner (and scope) of the lock
• @LockTimeout: specifies the timeout in milliseconds. If stored procedure cannot acquire the lock within this interval, it would return an error.
• @DbPrincipal: specifies security context (The caller needs to be a member of database_principal, dbo, or the db_owner roles.)

This procedure returns a value greater than or equal to zero in the case of success, and a negative value in the case of the failure. As with the regular locks, there is the possibility of deadlocks, although this would not rollback the transaction of the session that is chosen as the victim, but rather return the error code that indicates the deadlock condition.

An application needs to call sp_releaseapplock stored procedure to release the application lock. Alternatively, in case @LockOwner of the lock is transaction, it would be automatically released when a transaction commits or rollbacks. This is similar to the regular locks.

There is a concept in Computer Science called Mutual Execution. It signifies that multiple threads or processes cannot execute specific code at the same time. As an example, think about the multi-threaded application in which threads use the shared objects. In those systems, we often need to serialize the code that accesses those objects preventing the race conditions when multiple threads read and update them simultaneously.

Every development language has the set of synchronization primitives that can accomplish such tasks (for example, mutexes and critical sections). Application locks do the same trick when we need to serialize some part of the T-SQL code.

As an example, let’s think about the system that collects some data, saves it into the database, and has the farm of the stateless application servers for the data processing. Each application server reads the package of data, processes it, and finally deletes processed data from the original table. Obviously, we do not want different application servers processing the same rows, and serializing the data loading process is one of the options we have. Exclusive (X) table lock would not work, because it blocks any table access, rather than data loading. Implementing serialization on the application server level is not a trivial task either. Fortunately, application locks could do the trick.

Let’s assume that we have the table shown in Listing 22-1. For simplicity sake, there is a column called Attributes that represents all of the row data.

Listing 22-1.  Table structure

create table dbo.RawData
(
        ID int not null,
        Attributes char(100) not null
                constraint DEF_RawData_Attributes
                default 'Other columns',
        ProcessingTime datetime not null
                constraint DEF_RawData_ProcessingTime
                default '2010-01-01',
         
        constraint PK_RawData
        primary key clustered(ID)
)

There are two important columns: ID, which is the primary key, and ProcessingTime, which represents the time the row was loaded for processing. We should use UTC time here to support the situations in which application servers are residing in the different time zones, as well as to prevent issues when the clock is adjusted to Daylight Saving Time. This column also helps to prevent other sessions from re-reading the data while it is still processing. Boolean (bit) column would not work, because, if the application server crashes, the row would remain in the table forever. With the time column, we can read it again based on some processing timeouts.

Now let’s create the stored procedure that reads the data as shown in Listing 22-2.

Listing 22-2.  Stored procedure that reads the data

create proc dbo.LoadRawData(@PacketSize int)
as
begin
        set nocount on
        set xact_abort on
         
        declare
                @EarliestProcessingTime datetime
                ,@ResCode int
                 
        declare
                @Data table
                (
                        ID int not null,
                        Attributes char(100) not null,
                        primary key(ID)
                )
                 
        begin tran
                exec @ResCode = sp_getapplock
                        @Resource = 'LoadRowDataLock'
                        ,@LockMode = 'Exclusive'
                        ,@LockOwner = 'Transaction'
                        ,@LockTimeout = 15000 -- 15 seconds
 
                if @ResCode >= 0 -- success
                begin
                        -- We're assuming that app server would process the packet
                        -- within 1 minute unless crashed
                        select @EarliestProcessingTime =
                                dateadd(minute,-1,getutcdate())
                                 
                        ;with DataPacket(ID, Attributes, ProcessingTime)
                        as
                        (
                                select top (@PacketSize)
                                        ID, Attributes, ProcessingTime
                                from dbo.RawData
                                where ProcessingTime <= @EarliestProcessingTime
                                order by ID
                        )
                        update DataPacket
                        set ProcessingTime = getutcdate()
                        output inserted.ID, inserted.Attributes
                                into @Data(ID, Attributes)
                end
                -- we don't need to explicitly release application lock
                -- because @LockOwner is Transaction
        commit
         
        select ID, Attributesfrom @Data
end

Stored procedure obtains exclusive (X) application lock at the beginning of a transaction. As a result, all other sessions calling the stored procedure would be blocked until the transaction is committed and application lock is released. It guarantees that only one session can update and read the data simultaneously from within the stored procedure. At the same time, other sessions can still work with the table (for example, insert the new or delete the processed rows). Application locks are separate from the data locks, and sessions would not be blocked, unless they are trying to obtain the application lock for the same @Resource with sp_getapplock call.

It is worth mentioning that, if our goal is to simply guarantee that multiple sessions cannot read the same rows simultaneously, rather than serializing the entire read process, there is another, simpler, solution. We can use locking table hints, as shown in Listing 22-3.

Listing 22-3.  Serializing access to the data with the table hint

;with DataPacket(ID, Attributes, ProcessingTime)
as
(
        select top (@PacketSize)        ID, Attributes, ProcessingTime
        from dbo.RawData with (updlock, readpast )
        where ProcessingTime <= @EarliestProcessingTime
        order by ID
)
update DataPacket
set ProcessingTime = getutcdate()
output inserted.ID, inserted.Attributes into @Data(ID, Attributes)
                        

By specifying UPDLOCK hint, we force SQL Server to use update (U), rather than shared (S) locks during select. This prevents other sessions from reading the same rows simultaneously. At the same time, READPAST hint forces the sessions to skip the rows with incompatible locks held, rather than being blocked.

Although both implementations accomplish the same goal, they use different approaches. The latter serializes access to the same rows by using data (row level) locks. Application locks serialize access to the code and prevent multiple sessions from running the statement simultaneously. This can be very useful in cases in which we want to prevent some code from being executed in parallel.