Menu
We have a very difficult to track down bug in one of our software solutions that sometimes leaves an open transaction. We have this application in production at a number of sites (read: 70+), and we've only seen this issue twice so far this year at separate locations.
The issue we're having is a transaction that's being left open from the constant connection to the SQL Server. Using dbcc opentran shows that there is a single transaction left open. In today's case, it was open from 9:30 AM before we realized it at 1:00 PM. Closing the program with the connection will result in the transaction being closed/cancelled, and all the data from the day thus far lost.
Using dbcc opentran it responds with the name of the open transaction was user_transaction. Trying to close it with commit tran user_transaction gives an error of The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION..
I can understand you would almost never want to force the transaction to be committed without the prior connection's knowledge, but is there any way to do so? In this case, we closed the program and we lost a half of day's business worth of data.
Thanks.
541
You can simply fire commit command , if you are using transactions in SQL. You can also use Autocommit to automatically commit the transaction when it is finished. This will not let you to fire commit command. You can find all the uncommited transactions through DBCC opentran; command.
By default, in all app server and oracle db, autocommit is true. No matter you use connection pooling or direct connection[DriverManager] since commit is the operation on connection object it doesn't matter.
As long as you don't COMMIT or ROLLBACK a transaction, it's still "running" and potentially holding locks. If your client (application or user) closes the connection to the database before committing, any still running transactions will be rolled back and terminated.
You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction. This does not include changes made to local variables or table variables. These are not erased by this statement.
If it were possible for a transaction to be committed by another conenction, then it's not a transaction. If this were possible, it would open the door to way more issues than it would solve.
See this link: http://ask.sqlservercentral.com/questions/3865/forcing-a-transaction-to-commit.html
75
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
