Recently, I came across an interesting concurrency problem. A database I work with started experiencing intermittent locking and blocking issues. This resulted in timeouts being observed from an application which used the database as its backend.

To analyse this, I wrote a script and scheduled it as a job which would execute between a given timeslot the next day. The script would look for blocking in the database and when a block was found, it would log relevant information in a table. This included the SQL routine causing the blocking, the SQL routine being blocked, the statement within the SQL routine that was blocked and the database object that was the source of contention.

The trace Script (Main SQL)

SELECT
   DB_NAME() AS database_name, 
   GETDATE() AS audit_time, 
   s.spid AS process_id, 
   s.blocked AS blocking_process_id,
   s.hostname, 
   s.loginame, 
   s.program_name,
   blocking_s.hostname AS blocking_hostname, 
   blocking_s.loginame AS blocking_loginame, 
   blocking_s.program_name AS blocking_program_name,
   REPLACE(REPLACE(buffer.[text], CHAR(10), ''), 
           CHAR(9), '') AS sql_statement,
   SUBSTRING (buffer.[text], request.statement_start_offset/2, 
   (CASE 
      WHEN request.statement_end_offset = -1 
      THEN LEN(CONVERT(NVARCHAR(MAX), buffer.[text])) * 2 
      ELSE request.statement_end_offset 
    END - request.statement_start_offset)/2) AS specific_sql,
   REPLACE(REPLACE(blocking_buffer.[text], CHAR(10), ''), 
           CHAR(9), '') AS blocking_sql_statement,
   o.[name] AS blocking_object, 
   blocking_tr_locks.request_mode
FROM 
   sys.sysprocesses s INNER JOIN
   sys.dm_exec_connections conn
ON
   s.spid = conn.session_id CROSS APPLY 
   sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS buffer 
   LEFT JOIN sys.dm_exec_requests request
ON
   conn.session_id = request.session_id INNER JOIN
   sys.dm_exec_connections blocking_conn 
ON
   s.blocked = blocking_conn.session_id CROSS APPLY 
   sys.dm_exec_sql_text(blocking_conn.most_recent_sql_handle) 
   AS blocking_buffer INNER JOIN
   sys.dm_tran_locks blocking_tr_locks
ON
   s.blocked = blocking_tr_locks.request_session_id INNER JOIN
   sys.objects o 
ON
   blocking_tr_locks.resource_associated_entity_id = o.object_id 
   INNER JOIN sys.sysprocesses blocking_s
ON
   s.blocked = blocking_s.spid
WHERE
   s.blocked <> 0

The next day, I looked at the data logged by the script. It was evident that most blocks were being caused by the execution of a stored procedure. This procedure made use of a transaction, which was getting blocked and causing the timeouts.

Since many SQL sessions were trying to execute the same stored procedure, one session ended up blocking one or more of the other sessions. The locks were always placed on a single table in the database, and the specific SQL statement being blocked within the procedure was always an update statement on this table.

On closely reviewing the data, I found that this table was created by an application developer to serve as a "Key Value Pair" (KVP) generator. The table contained 2 columns, one for a key and the other for the corresponding value. The key would be a static string and the value would be incremented by 1 each time. This incremented value would be used in several insert statements, which affected many tables.

It was obvious that since one procedure call would update this value and hold on to it until the stored procedure completed and executed a commit or a rollback, the other calls coming from the other sessions would get blocked.

For you to visualise this, I have written some sample code and created a sample table, which are simplified versions of the database objects mentioned above.

Here is the "Key Value Pair" (KVP) table and some sample data

CREATE TABLE [dbo].[tbl_kvp](
 [column_key] [nvarchar](50) NOT NULL,
 [column_value] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT tbl_kvp (column_key, column_value) VALUES (N'PR1', 40)
GO

The stored procedure which was used to increment the value for a given key

CREATE PROCEDURE [dbo].[USP_Get_Value_For_Key]
(
 @key NVARCHAR(50), 
 @value INT OUTPUT
)
AS
BEGIN
 SELECT @value = 0
 UPDATE tbl_kvp SET column_value += 1 WHERE column_key = @key
 SELECT @value = column_value FROM tbl_kvp WHERE column_key = @key
END

The stored procedure causing the contention

CREATE PROCEDURE [dbo].[USP_Business_Process]
AS
BEGIN
 BEGIN TRANSACTION Business_Process
 BEGIN TRY
  DECLARE @val INT = 0
  -- Call key value incrementing stored procedure
  EXEC USP_Get_Value_For_Key 'PR1', @val OUTPUT
  
  SELECT @val -- Print key value for display
  -- There would be code present here in the actual stored procedure
  -- to insert the key value into multiple tables. To simulate this and
  -- also give enough time to review the locks, the transaction will
  -- be kept open for 10 seconds
  
  WAITFOR DELAY '00:00:10'
  COMMIT TRANSACTION Business_Process
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION Business_Process
 END CATCH
END

Trace Script Output

If you were to execute 2 calls to stored procedure, the typical output you would see from the trace script would be as shown below.

As you can see, the stored procedure that updates the KVP table (which gets called in the business process procedure) is blocked by another call to the business process procedure. The update statement for the KVP table is blocked and the source of contention is the KVP table.

Now that I knew the source of the problem, I had to find ways of addressing it. The KVP table had been introduced with several keys in it, and there were many areas in the code which were calling the USP_Get_Value_For_Key stored procedure, thereby affecting not just the business process mentioned above, but several other processes.

I was keen on finding a solution that could be implemented for all keys in the KVP table, without having to modify much of the existing code. This would mean that all processes suffering from this problem would benefit, and only few changes would be required which would make deployment of the fix easier.

Having worked with Oracle, I kept thinking that if I had to implement such an 'incrementing' logic, I would probably have made use of sequences. Since SQL Server 2008 doesn't have sequences, the closest I could get to such an object would be by making use of an identity column (The upcoming version of SQL Server 2011 code name 'Denali' does have sequences).

I therefore created a table that had an identity field and a dummy char(1) column into which I would keep inserting a dummy value to generate the next identity value. With this approach, I could convert the single horizontal row for a key into a vertical structure provided by the identity field. Building a vertical structure meant that a transaction could insert records into this new table and hold on to those records, while other transactions were free to insert their own records and get the next value in the sequence.

Here's some sample code that demonstrates this. First the Vertical table for PR1 key (The seed value is the next key value in sequence)

CREATE TABLE tbl_vert_pr1(id int identity(45, 1), dummy_col char(1))

Here is the modification to USP_Get_Value_For_Key to cope with the vertical structure

ALTER PROCEDURE [dbo].[USP_Get_Value_For_Key]
   (@key NVARCHAR(50), @value INT OUTPUT)
AS
BEGIN
 -- Replaced logic to look up vertical structure table
 -- Naming convention chosen for the vertical tables was tbl_vert_<key>
 
 DECLARE 
  @exec_sql NVARCHAR(4000), 
  @param_defn NVARCHAR(1000), 
  @value_OUT INT
 
 -- By making use of the output clause, the identity value could be 
 -- obtained directly, instead of having to perform a "select max(id)"
 -- operation, which could get blocked if another insert statement was 
 -- holding on to a record as part of a transaction
 SELECT @exec_sql = 
 N'DECLARE @op_table TABLE (id INT); ' +
 N'INSERT tbl_vert_' + @key + N' OUTPUT inserted.id 
   INTO @op_table VALUES ('' ''); ' + 
 N'SELECT @value_OUT = id from @op_table'
 
 SELECT @param_defn = N'@value_OUT INT OUTPUT'
 
 EXEC SP_EXECUTESQL @exec_sql, @param_defn, @value_OUT = @value OUTPUT
END

If you were to now execute two calls to the business process stored procedure in separate concurrent sessions and run the trace script in a separate session, you will observe that no blocks get picked up.

You will also notice the procedure that was executed first fetches the next available key value in sequence, while the procedure that was executed second fetches the key value after the one fetched by the first, thus proving that both transactions could obtain and make use of their respective keys without blocking each other. The above mentioned logic was applied to all keys in the KVP table, which resolved the concurrency issues associated with it.

Conclusion

Every real world concurrency problem tends to have its own unique solution. In the example mentioned above, the solution involved using the identity property, since it could convert a single horizontal record into a vertical structure. The best solution of course, is to not build database objects that do not satisfy good design practices!