Use of Identity Property to Resolve Concurrency Issues

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

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

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!

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.