22

I am connecting to a SQL Server using no autocommit. If everything is successful, I call commit. Otherwise, I just exit. Do I need to explicitly call rollback, or will it be rolled back automatically when we close the connection without committing?

In case it matters, I'm executing the SQL commands from within proc sql in SAS.

UPDATE: It looks like SAS may call commit automatically at the end of the proc sql block if rollback is not called. So in this case, rollback would be more than good practice; it would be necessary.

Final Update: We ended up switching to a new system, which seems to me to behave the opposite of our previous one. On ending the transaction without specifying committing or rolling back, it will roll back. So, the advice given below is definitely correct: always explicitly commit or rollback.

sactiw
  • 21,935
  • 4
  • 41
  • 28
Derek
  • 3,087
  • 1
  • 21
  • 23

1 Answers1

11

It should roll back on close of connection. Emphasis on should for a reason :-)

Proper transaction and error handling should have you always commit when the conditions for commit are met and rollback when they aren't. I think it is a great habit to always commit or rollback when done and not rely on disconnect/etc. All it takes is one mistake or incorrectly/not closed session to create a blocking chain nightmare for all :-)

Mike Walsh
  • 869
  • 7
  • 10
  • 1
    What affect would connection pooling have on this? – Joe Stefanelli Nov 01 '11 at 19:47
  • If you manage your transactions with best practices in mind you shouldn't have to worry, @JoeStefanelli :-) What I mean - 1.) in and out of your transaction as fast as you can - only do what is necessary within it to keep the footprint and impact small and 2.) Proper error handling and cleanup to commit when done right and rollback when not. I won't approve a change in an environment I manage if it violates these two basic principles. So connection pooling won't have an effect there. – Mike Walsh Nov 01 '11 at 19:52
  • Agreed that it's best to do your own explicit commit when things are done right. My question was aimed more at the "***should*** roll back on close of connection" portion of your answer. – Joe Stefanelli Nov 01 '11 at 19:54
  • 1
    I should clarify. Yes - on the proper close of a session's connection, the transaction will rollback. You can verify that easily enough playing in SSMS. Create a table, insert a value into it and commit it all in implicit transaction mode. Then insert a value, don't commit it. Open a new connection and query the table with default isolation level, it will be blocked. Disconnect that first session forcibly, second query comes back with just that original, committed row. The rollback happened. So it will but if you don't properly close that session it won't. – Mike Walsh Nov 01 '11 at 20:03
  • I haven't tested the impact of connection pooling and I haven't ever bumped into a situation that used connection pooling and implicit transactions but I imagine that if the connection management was working properly and cleaning up after itself, it would, too, rollback properly. I would recommend sticking with explicit transactions here, though. There aren't a ton of areas where I'd suggest going with implicit transactions for an app and its connections. – Mike Walsh Nov 01 '11 at 20:06
  • 3
    And you better never let me review your code if you have a commit statement without a rollback path. The try catch blocks are there for a reason. Anytime you have an explicit transaction you need both a commit and a rollback in case of errors. – HLGEM Nov 01 '11 at 20:10
  • +1 @HLGEM Anytime you have an explicit OR implicit transaction you better have that :-) – Mike Walsh Nov 01 '11 at 20:15
  • I agree. For what it's worth, I was trying to get rollbacks put into the code (which wasn't mine), I was just looking for technical justification. :) – Derek Nov 01 '11 at 20:34
  • The other beauty of an explicit rollback is that if you use a table varaible to store the exception reason and bad data, you can put the data that cause the issue and the exception reason into a table making researching errors much easier. – HLGEM Nov 01 '11 at 21:22