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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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
jimtpowersCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
tsellsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.