I have an MFC application with explicit transaction handling. Within the transactions are CRecordSets used. ODBC is used to connect to a server (MSSQL 2000). The application is a COM-dll.
Example: Begin transaction Save record A Updates Save record B Commit transaction
This must be executed in ONE transaction and the software has worked like this for years but now something strange is happening. In one installation every save and update come in different transactions that result in lock-timeouts. Then rollback fails to rollback everything, cause it's no longer one transaction.
By the way, your pseudocode isn't using proper error handling. You need to check the value of @@ERROR after every DML operation in a transaction in order to determine whether or not to proceed or rollback.
I've used the SQL Profiler on two separated installations and compared the results. On the working installation everything is executed in a single transaction. In the failing one it's not.
I don't get any errors or warnings but I do get a "look:timeout". After 15 seconds I get an "Attention" and then the code continues to execute. This happens twice and causes a timeout for the whole transaction at about 30 seconds into the whole transaction. If I could just change one of these two timeout values the transaction would probably pass through.
It seems like this is not a problem with the application or the database. It must be something higher up. Can any restrictions from .NET or IIS effect the type of connection I get to the database in the COM application?
I suspected the logging. The application logs into one table from triggers fired on update and insert on multiple tables. But then again, it used to work.
I haven't found anything in the event viewer that indicates any strange changes. I've deleted the COM interface and then registered it again with what I think is the correct parameters, without any luck.
I know that there were changes made to the network outside this application environment but I can't see how that would affect the IIS and the other applications inside the closed network. Is it possible that some permissions are in some way inherited from this network outside causing MSDTC, connection pooling or something else to not work properly?