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:
- Tests.BE: contains the business entities, in this case only the typed dataset
ItemsData
.
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.
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:
ComPlusURC
: class running in COM+ with read committed isolation level.ComPlusURCUPDLOCK
: class running in COM+ with read committed isolation level and
UPDLOCK
hint.ComPlusUSR
: class running in COM+ with serializable isolation level.ComPlusUSRUPDLOCK
: class running in COM+ with serializable isolation level and
UPDLOCK
hint.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,
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:
- 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.
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. - 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. - 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
- 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 member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.