The source for this problem can be found http://www.wellytop.com/SQLProblem.zip
This test creates two threads each with a database connection and uses transactions to insert values into the same table.
The objective of this test is to check that a thread cannot read the results from a pending transaction on a different thread.
In effect this checks dirty reads do not happen and transaction locking.
The test runs correctly and displays "PASSED" with MySQL indicating the transaction and threading worked.
When running with MSSQL Express 2005 it reports a deadlock error during a transaction.
It's not really possible to re-run the transaction and I would like MS SQL to operate similar to MySQL, i.e. MySQL waits for the other transaction to finish before the next transaction can operate on those table rows. I'd like to use MSSQL but I am wondering why this error doesn't happen with MySQL and so have, for the moment, chosen to use it as my preferred database solution.
I have experimented with transaction isolation levels and this doesn't seem to solve the problem.
I've tested this with a fresh install of Windows XP SP2 and no firewall turned on.
To run this test with MSSQL Express2005 use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named MyExpressTest and attach this to an empty database that has been created with the default values. Enable the #define MSSQL in the coude otherwise it tests with MySQL.
To run this test with MySQL (to show how this test should work) use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named mySQLNewTest and attach this to an empty database that has been created with the default values. Comment out the #define MSSQL to switch to MySQL mode.
Replying to my own old post is a bad sign, it's one step away from talking to myself. 
However that said I thought it would be useful to summarise my findings. The problem above is a limitation of the MSSQL ODBC interface. Basically, MSSQL ODBC connections with transactions from different processes work whereas if two or more connections come from the same process there is no transaction waiting. The workaround is to not use more than one MSSQL ODBC connection from each process. An alternative would be to use something like ADO instead, but that would break the example given in the problem where the code is meant to be cross platform and have a choice of database.
|||
Hi Martin,
After running your repro and experimenting with sqlcmd, I can see the same thing happening in sqlcmd with two separate processes running your statements in transactions. If I set the transaction isolation level to 'snapshot', the problem does not surface and each thread sees only the rows that existed before their transaction started or were inserted during the transaction, so perhaps snapshot isolation is for you.
For reference, I ran sqlcmd twice and executed:
SETUP:
create table t2 (id integer identity primary key, thing integer)
WINDOW 1:
-
begin tran
go
insert into t2(thing) values(10)
insert into t2(thing) values(14)
insert into t2(thing) values(15)
go
select * from t2
go
WINDOW 2:
begin tran
go
insert into t2(thing) values(100)
insert into t2(thing) values(110)
insert into t2(thing) values(120)
go
select * from t2
go
However, setting the transaction isolation level to 'serializable' seems as though it should also address your issue; but it does not.
From looking at the locks that get held, it is clear that thread 1 and thread 2 each obtain an exclusive lock on a subset of the rows in the table, then try to obtain a shared lock on the whole table to select. Since each holds an exclusive lock on a subset of the table, they reach deadlock. Effectively, the isolation level needs to be set such that they do not see each other's rows during the transaction so they can read without locking the table -- or what they see as the table. Snapshot isolation level clearly does this for you, but one would expect serializable to do it as well.
In order to ensure that your question is addressed by experts in this particular domain, I am transferring this question to T-SQL forums.
I hope this helps,
John
|||
Thank you very much for your extremely helpful answer. 
I just tried the test source here (configured for MSSQL) and found your suggestion does indeed solve the problem without any code changes needed.
After a bit more digging and searching for ALLOW_SNAPSHOT_ISOLATION this link http://msdn2.microsoft.com/en-us/library/tcbchxcb(vs.80).aspx describes the same solution.
So with the RNLobby database created (or in a new create database script) I then issue this command:
ALTER DATABASE [database name] SET READ_COMMITTED_SNAPSHOT ON
GO
Turning this option on by default for all connections means the code doesn't need changing to enable the snapshot isolation level for each connection which is perfect for this test case but may not be perfect for everyone.
