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