The source for this problem is attached.
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 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.
If I setup the program to operate one thread each in two processes the test works with MSSQL. This indicates the transaction locking is not working correctly with two threads from the same process.
I have experimented with transaction isolation levels and this doesn't seem to solve the problem.
To run this test with MSSQL Express2005 use the ODBC Data Source Administrator ([url removed, login to view]) 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 ([url removed, login to view]) 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.
1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables):
a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment.
b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.
3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).
4) I know I'm missing some kind of configuration of MSSQL or SQL command to MSSQL to make MSSQL operate in the same way as MySQL. Basically the solution must use the minimum number of code changes and still use SQL through the ODBC interface in the sample code. This is to make the code as generic as possible so I can easily switch between MySQL to MSSQL within the existing ODBC API I provide.
Tested with a fresh install of Windows XP SP2 and no firewall turned on.