January 30, 2006 at 2:36 am
In one of the modules of our application (SqL server 2000, Windows 2003,vb.net), we get ocassional timeouts. User ignores these timeout message and proceeds capturing further transactions. Though these transaction are get saved into the database, however transations captured after the timeout are getting rolledback, once the user logs out of the database. User were able to print all reports with these transactions before logging out of the system.
What could be the reason for these transactions being rolled back (I am referring to the transactions captured after the timeout)?
thanks
rangark.
January 30, 2006 at 11:35 am
Not sure but are all commands done within the same connection?
Can't find it immediately but the application is responsible for committing/rollbacking the transaction in case of an error.
When the connection closes all open transactions are rolled back. (when the users logs out?)
If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
It is the responsibility of the programmer to code the application to specify the correct action (COMMIT or ROLLBACK) if a run-time or compile error occurs.
*bold text not intended
January 30, 2006 at 11:41 am
My guess is that you have a problem with your transactions handling.
You didn't mention what is the programming tool you are using, this piece of information might help to analyze the problem.
You can try to record the transaction "name" and @@trancout and user info (if you can) into a table when the transaction starts and ends and see what you get.
Michelle.
January 30, 2006 at 9:53 pm
Many thanks for your inputs.
In case of any disconnection from the client during a process,we check for the connectivity and try to have normal logout process. We poll constantly for checking the connectivity. When a connection happens again the transaction is resumed w/o the need of logging out and login.
During this connectivity checking process, we reset the "set implicit_transations to off"
rangark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply