Rollback and Commit
can anybody explain difference between rollback and commit?
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.
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
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.
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.
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.
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!
Rollback = "undo", I don't want to do this after all. commit = "do it", yes, I do want to do this.
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
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
