x

Rollback and Commit

can anybody explain difference between rollback and commit?
more ▼

asked May 02 '11 at 06:39 AM in Default

sathishkumar gravatar image

sathishkumar
234 20 24 26

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
Sure thing, a rollback is just that, it rolls back any changes from the transaction. Commit commits the changes from the transactions. If you have a BEGIN TRAN at the beginning of the SQL Statement then you must either rollback the transaction or commit the transaction.
more ▼

answered May 02 '11 at 06:48 AM

Tim gravatar image

Tim
35.5k 32 40 138

thanks for your reply.. for example ..my table name is login with two fields(username, password)

username password

1 sathish admin 2 kumar admin

now unfortunately i have updated two rows instead of 1 row like

update login set username = 'admin' where password = 'admin'

now the table is

username password

1 admin admin 2 admin admin

now can i rollback this transaction like below it will return the previous values

begin transaction update login set username = 'admin' where password = 'admin' rollback transaction
May 02 '11 at 07:05 AM sathishkumar
Afraid not my friend, you have already committed that change. If you had used BEGIN TRAN you could have. If you have to recover that table you could restore a copy of the database and update the production table from the restored backup.
May 02 '11 at 07:08 AM Tim
Thank you sir, now i understood correctly
May 02 '11 at 10:40 PM sathishkumar
Glad to have helped out @sathishkumar. If you don't mind click the check box under my answer to mark it as answering your question. This will show everyone that you have been helped, or if one of the other answers helped you more, check it.
May 03 '11 at 04:13 AM Tim
(comments are locked)
10|1200 characters needed characters left

ROLLBACK (TRANSACTION) and COMMIT (TRANSACTION) are only relevant when used in conjunction with BEGIN TRANSACTION. If you don't start a TRANSACTION then you can't ROLLBACK or COMMIT.

If you run a piece of code with BEGIN TRANSACTION in it then you have to finish it with either ROLLBACK or COMMIT depending on whether you want the changes made, or not. If you dont issue either of these commands then you will start to see lots of locks in your database and eventually it will stop working.

If you dont use BEGIN TRANSACTION then it is assumed that all the code you execute is wrapped in BEGIN TRANSACTION and COMMIT TRANSACTION. It is therefore not possible to undo changes without using a backup and restoring your database to a point in time.

more ▼

answered May 02 '11 at 07:43 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

Thank you sir, now i understood correctly
May 02 '11 at 10:40 PM sathishkumar
Good answer, I just want to add: you should some basic knowledge about T-SQL before you try to approach a production database, and this one of these fundamental statements!
May 02 '11 at 10:52 PM Håkan Winther
I hope a backup is done first! :-)
May 03 '11 at 05:46 AM xypher
(comments are locked)
10|1200 characters needed characters left

Rollback = "undo", I don't want to do this after all. commit = "do it", yes, I do want to do this.

more ▼

answered May 02 '11 at 06:56 AM

homebrew01 gravatar image

homebrew01
252 3 4 4

thanks for your reply.. for example ..my table name is login with two fields(username, password)

username password

1 sathish admin 2 kumar admin

now unfortunately i have updated two rows instead of 1 row like

update login set username = 'admin' where password = 'admin'

now the table is

username password

1 admin admin 2 admin admin

now can i rollback this transaction like below it will return the previous values

begin transaction update login set username = 'admin' where password = 'admin' rollback transaction
May 02 '11 at 07:05 AM sathishkumar
(comments are locked)
10|1200 characters needed characters left
They both are use if you call method connection.setAutoCommit(false). By default it is true. Rollback() method of connection interface.if you call this one then all above (NOTE:if commit() is invoke then rollback apply all query follow commit() method) query are execute but not apply any changes in database. commit()= method of connection interface. if you invoke this then all changes are save in data base. /////NOTE:if you invoke commit then invoke of rollback is not valuable
more ▼

answered Oct 10 '12 at 07:05 AM

tyagi gravatar image

tyagi
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472

asked: May 02 '11 at 06:39 AM

Seen: 2324 times

Last Updated: Oct 10 '12 at 07:05 AM