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.
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:
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:
- Tests.BE: contains the business entities, in this case only the typed dataset
The post build events register the assembly in GAC.
- Tests.DA: data-access layer that provides methods to retrieve and update records in a database.
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:
ComPlusURC: class running in COM+ with read committed isolation level.
ComPlusURCUPDLOCK: class running in COM+ with read committed isolation level and
ComPlusUSR: class running in COM+ with serializable isolation level.
ComPlusUSRUPDLOCK: class running in COM+ with serializable isolation level and
NonComPlus: class not running in COM+.
To run the program follow the following steps:
- With Visual Studio 2010 open solution Tests.ReadCommitted.sln.
- 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.
- Build the solution. If it fails, check that the paths to gacutil and regsvcs in the post-build events are correct.
- You may look in Component Services (Start/Run/comexp.msc) that the COM+ application Tests.DA has been created.
- Start Tests.UI either in the debugger, or with the executable Tests.UI\Debug\bin\Tests.UI.exe.
The following form appears:
- Choose the menu item File/Recreate table. This will create the tables Items and Items_Audit on the database.
- Press the button Prepare test suites. This will display on the grid the necessary columns and rows to fill the test data:
- 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,
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:
- 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 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 Threads: The
NonComPlus class performs even worse,
this is because the
NonComPlus class does not create a transaction, and many concurrency violation errors occur.
ComPlusUSR class (serializable) performs worse than the
ComPlusURC class (read committed),
this is due to the many deadlocks. The
ComPlusUSRUPDLOCK class (serializable with the
ComPlusURCUPDLOCK class (read committed with the
UPDLOCK hint) perform the same.
- 4 Threads and more: All classes without the
UPDLOCK hints perform even worse due to the concurrency violation errors in the
ComPlusURC classes and due to the deadlocks in
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.
- The following figure shows the performance of the COM+ classes without and with the
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.
- Non COM+ and COM+ classes with read committed and serializable isolation levels with or without
Test suite to run different cases for different table sizes and different threads.