x

Rollback and Commit

can anybody explain difference between rollback and commit?

more ▼

asked May 02, 2011 at 06:39 AM in Default

avatar image

sathishkumar
234 23 24 29

(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, 2011 at 06:48 AM

avatar image

Tim
40.4k 39 84 166

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, 2011 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, 2011 at 07:08 AM Tim

Thank you sir, now i understood correctly

May 02, 2011 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, 2011 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, 2011 at 07:43 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

Thank you sir, now i understood correctly

May 02, 2011 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, 2011 at 10:52 PM Håkan Winther

I hope a backup is done first! :-)

May 03, 2011 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, 2011 at 06:56 AM

avatar image

homebrew01
262 3 6 9

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, 2011 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, 2012 at 07:05 AM

avatar 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.

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:

x501

asked: May 02, 2011 at 06:39 AM

Seen: 23746 times

Last Updated: Oct 10, 2012 at 07:05 AM

Copyright 2016 Redgate Software. Privacy Policy