COMMIT and ROLLBACK in Sql Server

SQL provides many transaction management features. SQL commands COMMIT, ROLLBACK and SAVEPOINT helps in managing the transaction.

The COMMIT command is the transactional command used to save changes made by a transaction to the database.The COMMIT command will save all changes to the database since the last COMMIT or ROLLBACK command. Frequent commits in the case of transaction involving large amount of data is recommended. But too many commits can affect performance. In many implementations, an implicit commit of all the changes is done, if user logs off from the database.

The ROLLBACK command is the transactional control command to undo the transactions that have not already been committed to the database. The ROLLBACK command can be issued to undo the changes since the last COMMIT or ROLLBACK.

Illustration with an Example

There are two relational tables one for user registration information (tbl_userinfo) and another for user login information(tbl_login). Both tables have primary key foreign key relationship.

(A).User registration Information(tbl_userinfo)

Column Name	Data Type	Constraint/Description
user_id	Int	Identity(1,1) Primary Key
name	Varchar(50)	Name of User
email	Varchar(50)	Email of User

(B).user login information(tbl_login)

Column Name	Data Type	Constraint/Description
login_id	Int	Identity(1,1) Primary Key
user_id	Int	Foreign Key with tbl_userinfo
user_name	Varchar(50)	User Name for login
password	Varchar(50)	User Password for login

(A)General Transaction: In this transaction userreg , one table tbl_userinfo is affected and one row is entered and another table tbl_login is not affected. Here first insert query is successful executed but second insert query is not successful executed due to table tbl_login where column login_id is primary key and auto increment so we can't pass value and CATCH block will be executed.

begin try

begin tran userreg

declare @userid int

insert into tbl_userinfo(name, email)

values('sandeep','[email protected]')

select @userid=max(user_id) from tbl_userinfo

insert into tbl_login(login_id, user_id, user_name, password)

values(2,@userid,'singh','singh')

end try

begin catch

print 'ónly one table entery'

end catch

(B)Implementation of ROLLBACK command: In this transaction userreg , one table tbl_userinfo is affected and one row is entered and another table tbl_login is not affected. Here first insert query is successful executed but second insert query is not successful executed due to table tbl_login where column login_id is primary key and auto increment so we can't pass value. So CATCH block is executed here and ROLLBACK Command executed. After ROLLBACK Command execution our database change are undo means here tbl_userinfo row entry remove and both tables have no row .

begin try

begin tran userreg

declare @userid int

insert into tbl_userinfo(name, email)

values('sandeep','[email protected]')

select @userid=max(user_id) from tbl_userinfo

insert into tbl_login(login_id, user_id, user_name, password)

values(2,@userid,'singh','singh')

end try

begin catch

rollback tran userreg

end catch

(C)Implementation of COMMIT command: In this transaction userreg, both Insert queries successful executed. After successfully execution of queries, transaction userreg is committed and permanently save database changes.

begin try

begin tran userreg

declare @userid int

insert into tbl_userinfo(name, email)

values('sandeep','[email protected]')

select @userid=max(user_id) from tbl_userinfo

insert into tbl_login( user_id, user_name, password)

values(@userid,'singh','singh')

commit tran userreg

end try

begin catch

rollback tran userreg

end catch