Performance of isolation levels and UPDLOCK

• Download source - 64.4 KB

Introduction

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.

Background

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.

UPDLOCK

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:

SELECT * FROM Items WITH(UPDLOCK) WHERE Id=1

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.

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.

The following form appears:



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



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



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

History

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.