SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 What used to be ONE transaction
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MrRenn
Starting Member

8 Posts

Posted - 05/11/2009 :  08:38:51  Show Profile  Reply with Quote
Hi

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.


Any ideas of what can cause this?



tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 05/11/2009 :  11:06:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
How do you know it's happening in more than one transaction, how did you see that information? Have you viewed what is getting passed to SQL Server by running SQL Profiler?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 05/11/2009 :  11:07:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

MrRenn
Starting Member

8 Posts

Posted - 05/11/2009 :  11:28:03  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 05/11/2009 :  11:39:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
There is no way to change a timeout value from within SQL Server. Timeouts are set at the application layer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

MrRenn
Starting Member

8 Posts

Posted - 05/11/2009 :  11:55:27  Show Profile  Reply with Quote
The query timeout is set to 400 seconds in the code. It is something else that makes the transaction end at 30 seconds.
Go to Top of Page

MrRenn
Starting Member

8 Posts

Posted - 05/12/2009 :  02:27:10  Show Profile  Reply with Quote
The problem surfaced once before and then disappeared after some days. I really don't think it's the SQL Server, I think it's something else.
Go to Top of Page

MrRenn
Starting Member

8 Posts

Posted - 05/13/2009 :  05:17:59  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 05/13/2009 :  13:40:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
I am not aware of anything like that affecting the connection.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 05/13/2009 :  14:35:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why are you suddenlt getting locks?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

MrRenn
Starting Member

8 Posts

Posted - 05/14/2009 :  13:44:05  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Why are you suddenlt getting locks?


E 12°55'05.63"
N 56°04'39.26"




You tell me! :) The same COM application in production for a year and suddenly it fails, not randomly but everytime at the exact same place. I don't know why my explicit transaction disappeared.
Go to Top of Page

MrRenn
Starting Member

8 Posts

Posted - 05/14/2009 :  13:52:01  Show Profile  Reply with Quote
What's even more strange is that I got it to work when I directly loaded the DLL from a test application. But it fails when the webservice loads the DLL using COM from the .NET application.

I've used "Process explorer" to see if it's te right DLL that is loaded and the DLL is located in the right directory. I'm confused.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 05/17/2009 :  03:39:20  Show Profile  Reply with Quote
Sounds like someone has introduced a rogue 'commit' into a SP or something. Maybe logging?
Or if COM+ is managing your transactions then someone could have flicked some switches.

Have you checked your change control?
Go to Top of Page

MrRenn
Starting Member

8 Posts

Posted - 05/18/2009 :  08:09:10  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000