how rollback works in sql server?

Question

how rollback works in sql server?

gary1

SSC-Insane

Points: 21152
More actions
August 31, 2011 at 3:08 pm

#242637

Hi,
In Oracle, If you do an update, delete and insert, then we can use COMMIT to commit the transaction and ROLLBACK to rollback the transaction.
How does this works in SQL Server 2008 R2.
Because, in sql server, if run the update,insert and Delete command then it automatically commits the data and we DONOT need to execute the COMMIT command.
Does it mean If something goes wrong, we cannot roll it back?
Please advice me how COMMIT & ROLLBACK works in SQL Server 2008 R2
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

gmamata7 (8/31/2011)
Hi,
In Oracle, If you do an update, delete and insert, then we can use COMMIT to commit the transaction and ROLLBACK to rollback the transaction.
How does this works in SQL Server 2008 R2.
Because, in sql server, if run the update,insert and Delete command then it automatically commits the data and we DONOT need to execute the COMMIT command.
Does it mean If something goes wrong, we cannot roll it back?
Please advice me how COMMIT & ROLLBACK works in SQL Server 2008 R2
Thanks

Take a look at BOL (http://msdn.microsoft.com/en-us/library/ms174377.aspx). This will explain pretty much anything you need to know about transactions in sql server.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Depends on SET IMPLICIT_TRANSACTIONS setting in the connection/database level property.

But generally , if u wont want to auto-commit , u can have something like this

BEGIN TRAN

insert

update

delete

and once you confrm that things are fine, you can

commit

if things look ugly , you can

rollback

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

The default connection settings for Oracle is explicit transactions. Which means you have to specifically issue a commit or rollback.

In SQL Server, the default is implicit transactions - which means you don't have to specifically issue a commit or rollback.

This setting is controllable - as was pointed out in the previous post.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

gary1 SSC-Insane Points: 21152 More actions · Answer 4

The default connection settings for Oracle is explicit transactions. Which means you have to specifically issue a commit or rollback.
In SQL Server, the default is implicit transactions - which means you don't have to specifically issue a commit or rollback.
This setting is controllable - as was pointed out in the previous post.

I went though the BOL.. and want to know that IS THERE ANY SERVER (INSTANCE LEVEL) LEVEL SETTING THAT CAN WE SET in order NOT TO HAVE AUTO COMMIT?

SQL Server operates in the following transaction modes.

Autocommit transactions

Each individual statement is a transaction.

Explicit transactions

Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions

A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

Batch-scoped transactions

Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server

Loundy SSCertifiable Points: 6080 More actions · Answer 5

I went though the BOL.. and want to know that IS THERE ANY SERVER (INSTANCE LEVEL) LEVEL SETTING THAT CAN WE SET in order NOT TO HAVE AUTO COMMIT?

I've just had a scan through the SQL Server options and can't see an option to NOT auto commit a transaction... Looks like you have to explicitly control transactions with BEGIN TRAN / COMMIT/ROLLBACK TRAN.

[font="Times New Roman"]There's no kill switch on awesome![/font]

HowardW One Orange Chip Points: 29109 More actions · Answer 6

You can set implicit transactions on by default as part of the user options server configurations parameter. The easiest way to modify this is through SSMS (right click on the server and go to properties, then the connections tab).

Please note that user entered set options (e.g. embedded in a stored proc, in a query window etc.) will override this default if specified.

Also, I would test this works correctly with your application as it may rely on the default setting.

Lowell SSC Guru Points: 323518 More actions · Answer 7

just to confirm and expand on what Howard said...

as soon as you change the ansi setting shown in my screenshot below, and in a new window...

if you perform some updates or whatever and then try to close the window, you get the warning message below...i think that's what you are after.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

HowardW One Orange Chip Points: 29109 More actions · Answer 8

Actually, I was referring to the Server Configuration values rather than the client connection settings in SSMS. E.g. the ones held here:

sp_configure 'User_options'

Although I think you may be right that you can only really affect this client side in SSMS as it explicitly sets your options when you connect, so defaults would not be overridden and therefore it's pretty much useless

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 9

Note if you set this behavior then you have to make sure all your code includes commits. Otherwise you will have severe locking/blocking issues.

Most SQL Server code does not expect this, so make sure you document this well and ensure all code, not just what you do with SSMS, allows for this setting change.

Lowell SSC Guru Points: 323518 More actions · Answer 10

wow...changing that setting that i posted the screenshot of is SUCH a bad idea in SQL server.

testing that, i updated one row in a table, and saw i got the erorr about closing the window...

so i left that window open, and went to lunch.

an hour later, the QA team is reporting that the "search" functionality in our application, on the database i touched witht eh one row update is locked up...

because i had an open transaciton and went to lunch.

Oracle handles those transactions differently...the changes are your own till committed, but in SQL, it prevents others from touchign the sam e data.

ouchies.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 11

Lowell (9/2/2011)
Oracle handles those transactions differently...the changes are your own till committed, but in SQL, it prevents others from touchign the sam e data.ouchies.

Depends on the isolation level and database settings. This can be avoided if READ_COMMITTED_SNAPSHOT is enabled on the database and you are using the READ COMMITTED isolation level, or if the ALLOW_SNAPSHOT_ISOLATION is set on the database and you're using SNAPSHOT isolation.

HowardW One Orange Chip Points: 29109 More actions · Answer 12

Agreed, depends on the isolation level. Even in Oracle, no-one could run an update on a row that had been affected by another user transaction if it hadn't been committed, the default settings just allow consistent pre-transaction reads (read committed snapshot)