0

Say I run the following code in SQL Studio Manager:

begin transaction
update Person set Name='Ian' where id=1

I then close SQL studio manager and I see the following warning:

There are uncommitted transactions. Do you wish to commit these transactions before closing the window?

If I select yes, then the transaction is committed. If I select no then it is rolled back.

How does this work with a VB6/.NET program? Say I have code that causes an error/exception and the transaction is neither committed nor rolled back (like it should be). Does the transaction remain active indefinitely or is there a time limit? Is the transaction committed or rolled back?

Update I have written a VB6 program, which:

1) Establishes a connection to a database
2) Executes the following SQL statement inside a serialized  transaction: UPDATE Person SET Name='Ian' WHERE ID=1 (Name  changed from Ian)
3) Throw an error and close application without committing nor rolling back the transaction

I then open SQL studio manager and execute the following statement:

select * from person where id=1

SQL Studio hangs i.e. it does not return a result. I then run steps 1-3 again, this time rolling back the transaction properly. The program completes successfully i.e. the row is not locked for the VB6 client. I am then able to run the SELECT statement in SQL Studio Manager (it returns a result): 

select * from person where id=1

Does the connection pool keep the transaction open?

w0051977
  • 15,099
  • 32
  • 152
  • 329
  • 1
    Why are you letting yourself get into such a state and then wondering what the behaviour is? Surely, the right thing to do is to change your code so that you *don't* end up in this state to begin with? – Damien_The_Unbeliever Nov 20 '15 at 10:53
  • @Damien_The_Unbeliever, yes I agree. I was just curious about what was happening. – w0051977 Nov 20 '15 at 14:07

2 Answers2

2

Any uncommited transaction is rolled back when (re)starting the sql-server-service. Earlier roll backs depend on multiple factors like connection pooling and are explained at What happens to an uncommitted transaction when the connection is closed?

Does the connection pool keep the transaction open?

The hanging of the SQL studio manager is most propably from the Isolation level set to something like serializable and the new query waiting for "clean" data (it should not happen with dirty read or similar). So it means that the transaction is not committed nor rolled back (or unlikely your server could be working to full capacity). Therefore it is likely that this is because of the connection pool or the TCP-connection was not closed yet/TCP-timeout has not occured yet.

You might also want to look at the connection parameters command timeout and connection timeout to improve the behavior of your application.

Community
  • 1
  • 1
H. Idden
  • 403
  • 3
  • 7
1

Quick Answer. The transaction sits in an uncommitted state until, you Commit, Rollback or the Connection is closed. If the connection is closed then it is rolled back.

John Hartsock
  • 85,422
  • 23
  • 131
  • 146