Why is SQL Server transaction committed if I stop debugging?

I'm confused about this.

I have a c# ado.net application that opens a connection and begins a transaction.

It calls into a sproc that saves a new row.

I can see that new row outside the app if I query with (nolock). Fine.

I stick a breakpoint on the commit and before allowing it to execute abort the debugger.

What I want is for the transaction to roll back if the application were to abort before it explicitly commits the transaction. The whole idea of a transaciton IMO is that it should not be committed until all related logic is successful and the calling app is the judge of that.
LVL 16
ToddBeaulieuAsked:
Who is Participating?
 
jimtpowersConnect With a Mentor Commented:
See this article:

SET IMPLICIT_TRANSACTIONS

Note the line in the article: "When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode."
0
 
tsellsCommented:
If you stop the debugger before calling commit the transaction should be rolled back when the session is closed.  

Are you sure the data is being persisted?  Can you get to it after closing the debugger without the nolock?

I would run a SQL Profiler to confirm the transaction commit is being called from the IDE.  
0
 
jimtpowersCommented:
When the statement is executed from your application, the application waits until SQL Server tells it the statement has finished. SQL Server commits the transaction before returning a success or failure to your application.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
ToddBeaulieuAuthor Commented:
Why would SQL server commit a transaction without being told to do so? I futzed with this thing for a couple of hours this morning and got nowhere. I see the transaction begin comming in with profiler. I then execute the sproc which does not commit. Trying to query the new data hangs, showing that it is in fact pending while the debugger sits on the commit line. If I then stop the debugger w/o executing the commit, the data is committed. Never sent the commit command. Wierd!
0
 
jimtpowersCommented:
It is the nature of SQL Server. If you do not explicitly ROLLBACK, it auto COMMITS.
0
 
jimtpowersCommented:
Can you post your code?
0
 
jimtpowersCommented:
Send the following command first, right after opening your connection:

SET IMPLICIT_TRANSACTIONS ON
0
 
tsellsCommented:
The above comment is incorrect.  If you do not commit the transaction - it is automatically rolled back.  I was doing some DB testing this weekend and was using this very same function.  I needed it to roll back as I was in the middle of testing a new feature while debugging inside a transaction.   If I stopped debugging - the transaction is rolled back.  
0
 
tsellsCommented:
These are two separate concepts.  SQL Server runs everything in transactions (whether or not you specify them or not).  When create your own transaction - if you do not commit it - SQL server will roll it back.
0
 
tsellsConnect With a Mentor Commented:
To add more detail here.

What jimtpowers is referring to are implicit transactions - or ones provided by SQL server for every operation being performed.  

What the author is referring to are explicit transactions.  These are created by the programmer and are only committed once the program calls Commit.  

http://msdn.microsoft.com/en-us/library/ms190295.aspx

1st line under the remarks section.

It is the responsibility of the Transact-SQL programmer to issue COMMIT TRANSACTION only at a point when all data referenced by the transaction is logically correct.
0
 
ToddBeaulieuAuthor Commented:
Unfortuantely, by the time this healthy debate concluded, I had abandoned the idea of controlling the transaction from .net, opting for transactions internal to the sproc.

Although it's entirely possible that I missed something, I stand by my claim that if I killed debugging session the uncommitted transaction was being committed. It made no sense to me, but I ran out of time and it made no sense to keep down that road.

I'm awarding you points because you both put in the effort.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.