Click here to Skip to main content
15,879,683 members
Articles / Programming Languages / SQL

Performance of isolation levels and UPDLOCK

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
9 Aug 2012CPOL5 min read 26.8K   156   3   2
This article provides a program to measure the performance of the different transaction isolation levels. It also examines the effect of using the UPDLOCK hint in SQL.


A common operation in database applications is to read one record from the database, alter some fields on the record, and save the updated record back to the database. This is usually done in one transaction. This project, uses the functionality given by EnterpriseServices (COM+) to implement the transaction. EnterpriseServices provide different transaction isolation levels. The read committed and serializable isolation levels are used here. The performance of these isolation levels are measured by running this transaction repeatedly and concurrently by different threads. The combination of these isolation levels together with the SQL UPDLOCK hint is also examined.


Isolation levels

A transaction with read committed isolation level, may read one record and start doing something else. The record that was read is not locked, and a second transaction may update the record. With transaction isolation level serializable, the record that was read is locked until the end of the transaction, and no transaction may update that record before the first transaction has completed. Therefore, with the read committed isolation level, fewer transactions may run simultaneously. However, because the read committed isolation level does not lock the records when reading from the database, two simultaneous transactions may read the same record at the same time. The first transaction that will update the record will succeed, but the second will fail, a concurrency violation error will be thrown. With the serializable isolation level, the same sequence will result in a deadlock.


SQL Server provides the lock hint UPDLOCK, which instructs the database engine to lock the record until the end of the transaction. For example the following statement reads and locks one record in table Items:


Using this hint with the read committed isolation level, prevents the concurrency violation errors, while using this hint with the serializable isolation level prevents the deadlocks.

Using the code

The solution contains three projects:

  1. Tests.BE: contains the business entities, in this case only the typed dataset ItemsData. The post build events register the assembly in GAC.
  2. Tests.DA: data-access layer that provides methods to retrieve and update records in a database. The method UpdateItemById retrieves in one transaction a record from table Items, updates the Name field, and saves back the record to the database. The post build events register the assembly in GAC and COM+ and copy the config file to the respective GAC folder.
  3. The connection string is defined in the app.config file. The assembly contains following classes:

    1. ComPlusURC: class running in COM+ with read committed isolation level.
    2. ComPlusURCUPDLOCK: class running in COM+ with read committed isolation level and UPDLOCK hint.
    3. ComPlusUSR: class running in COM+ with serializable isolation level.
    4. ComPlusUSRUPDLOCK: class running in COM+ with serializable isolation level and UPDLOCK hint.
    5. NonComPlus: class not running in COM+.

To run the program follow the following steps:

  1. With Visual Studio 2010 open solution Tests.ReadCommitted.sln.
  2. Set the connection string in app.config. It must be a valid connection string to a database, on which you have the right to create tables.
  3. Build the solution. If it fails, check that the paths to gacutil and regsvcs in the post-build events are correct.
  4. You may look in Component Services (Start/Run/comexp.msc) that the COM+ application Tests.DA has been created.
  5. Start Tests.UI either in the debugger, or with the executable Tests.UI\Debug\bin\Tests.UI.exe.
  6. The following form appears:

    Empty form

  7. Choose the menu item File/Recreate table. This will create the tables Items and Items_Audit on the database.
  8. Press the button Prepare test suites. This will display on the grid the necessary columns and rows to fill the test data:
  9. Form after prepare test suites

  10. Press the button Run test suites. This will enter as many records as specified in the Number randoms textbox with a random generated name. This may take one or two minutes. After that, for each of the displayed classes, the method UpdateItemById will be called repeatedly for one second. The number of successful calls will be displayed in the grid. The test suite, starts with one thread in the first column, and continues with powers of two up to the power given in the text box Number threads. For example if Number threads is 4, the test suite will run test cases for threads 1, 2, 4, 8, and 16. At the end the form will look similar to this:
  11. Form after run test suites

  12. You may experiment with different table sizes. For example, if Number randoms is set to 1000, and Max table size is 10000, the test suite will first add 1000 records and run through all test cases of the different classes and the different threads. After that, it will add 1000 records to the table, and rerun all test cases, and so forth until the table has 10000 rows.

Points of Interest

Looking at the results, one can make the following observations:

  1. 1 Thread: The NonComPlus class performs much worse than the COM+ classes, I can not explain why. The serializable classes perform the same as the read committed classes. As there is only one thread, the UPDLOCK hint has no effect.
  2. 2 Threads: The NonComPlus class performs even worse, this is because the NonComPlus class does not create a transaction, and many concurrency violation errors occur. The ComPlusUSR class (serializable) performs worse than the ComPlusURC class (read committed), this is due to the many deadlocks. The ComPlusUSRUPDLOCK class (serializable with the UPDLOCK hint) and the ComPlusURCUPDLOCK class (read committed with the UPDLOCK hint) perform the same.
  3. 4 Threads and more: All classes without the UPDLOCK hints perform even worse due to the concurrency violation errors in the NonComPlus and ComPlusURC classes and due to the deadlocks in the ComPlusUSR class. However the classes with the UPDLOCK hints perform very well. It is not certain which of the UPDLOCK classes, the read committed one or the serializable one, performs better. Running the test several times, has not given a clear winner.
  4. The following figure shows the performance of the COM+ classes without and with the UPDLOCK hint. The Excel sheet Notes.xlsx that produced this figure is included in the source. The results in the grid may be pasted to this Excel sheet.
  5. Chart of results


  1. Non COM+ and COM+ classes with read committed and serializable isolation levels with or without UPDLOCK. Test suite to run different cases for different table sizes and different threads.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior) Unisystems
Greece Greece
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionWhat is the difference in using standard sql or the hint? Pin
JesperMadsen1239-Aug-12 9:49
JesperMadsen1239-Aug-12 9:49 
AnswerRe: What is the difference in using standard sql or the hint? Pin
Alexandros Pappas22-Aug-12 4:35
professionalAlexandros Pappas22-Aug-12 4:35 
1. for update works only for cursors, for example the sql statement:
select * from Items for update where id = 1
produces the error:
Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.

2. Yes, this is true about what you write about deadlocks. Even if you execute the statements in the same sequence you may cause deadlocks. For example, open two SQL sessions and execute the following commands:
set transaction isolation level serializable
begin tran
select * from Items where id = 1
set transaction isolation level serializable
begin tran
select * from Items where id = 1
update Items set Name = '1' where id = 1
update Items set Name = '1' where id = 1

In session1 a deadlock will occur:
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

However, if you add the with(updlock) hint, no deadlock will occur.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.