Transactions
Transactions are set of tasks for single execution, it have has only two way success or failure and If any of the tasks fail, then transaction will fail else success.
Transaction has two action rollback and commit.
Rollback:
if anyone task is failed then changes will not apply in Database and all open transaction will closed automatically.
Commit:
commit like a save command in database if anyone task not failed then it will allow and it is made changes in Database permanently, if transaction is open and till not committed then data hold in log files and at a time only one transaction committed.
ACID Properties in SQL Server
Atomicity:
In Simple word say in side transaction’s all operation (insert, update, delete) is either successfully committed or rolled back, it is never allow to some transaction committed and rollback.
Consistency:
it is provide the guarantees that transaction never leave on half-finished state, like
- If the transaction is completed then it will apply all the changes in database.
- If any error comes or system failure inside of transaction then must be all changed rolled back.
Isolation:
Each and every transaction is individual.
Durability:
Ability of the system to recover committed transaction updates if either the system or the storage media fails.
Ex.1: Simple transaction with Commit command
Their I have create test1 table and it’s have 2 column id, name after that just selecting (for verification purpose) but no data there
After that start transaction with begin tran (transaction and tran are same) and inserting 1 records and there any kinds of error not comes so it’s eligible for commit and data has save in permanent table.
Ex.2 : simple rollback case for consistency check , there try to insert 2 row but 1 row insert successfully but other one raised some error and both task inside of transaction so full transection has rollback, in below example we have not using rollback command but when error comes during any transaction then rollback command automatically implicitly fire so there @@Trancount variable used because @@Trancount return number of open transaction in current session and below case open connection not found after raised error so one more error will come when commit command execute so this one handled
Ex.3: Error handling with transactions, see in below example there try-catch used with transaction so when error come in 2nd task (2nd insert) then compiler jump in catch block there rollback command execute if any case error not come then commit will execute in try block.
Nested Transaction
As we know all child transection also as task for main transection and rollback command in single execution close all pending transaction because rollback always rollback main transection and commit command always commit LIFO(last transaction will commit first) based. In case all child transaction committed but main transaction rollback then all transaction will also rollback those are a task for main transaction. Let’s suppose we have 1 main transaction and 3 is child and 1st child want to rollback then all transaction will rollback
Exp.1 Rollback Last one transaction but close all transaction
SELECT ‘1. Transaction count :’, @@TRANCOUNT
begin transaction –tran1
SELECT ‘2. Transaction count :’, @@TRANCOUNT
insert into temp1 (id,name) values(1,’aaa’);
begin transaction –tran2
SELECT ‘3. Transaction count :’, @@TRANCOUNT
insert into temp1 (id,name) values(2,’aaa’);
begin transaction — tran3
SELECT ‘4. Transaction count :’, @@TRANCOUNT
insert into temp1 (id,name) values(3,’aaa’);
ROLLBACK –tran3
SELECT ‘5. After 3rd Transaction rollback count :’, @@TRANCOUNT
Exp.2 Commit child’s transactions and rollback main transaction and all inserted records rollback
SELECT ‘1. Transaction count :’, @@TRANCOUNT
begin transaction –tran1
SELECT ‘2. after tran1 count :’, @@TRANCOUNT
insert into temp1 (id,name) values(1,’aaa’);
begin transaction –tran2
SELECT ‘3. after tran2 count :’, @@TRANCOUNT
insert into temp1 (id,name) values(2,’aaa’);
COMMIT — tran2
begin transaction — tran3
SELECT ‘4. after tran2 commit and tran3 open count :’, @@TRANCOUNT
insert into temp1 (id,name) values(3,’aaa’);
COMMIT — tran3
SELECT ‘5. after tran3 commit count :’, @@TRANCOUNT
ROLLBACK –main tran1
SELECT ‘5. After Ist Transaction rollback count :’, @@TRANCOUNT
select * from temp1
Save Point
Save points used for rollback transactions on specified point, it’s always create nested block
EX.1
TRUNCATE TABLE temp1
BEGIN tran
insert into temp1 (id,name) values(1,’aaa’)
— start 1st savepoint (sp1)
SAVE tran sp1
insert into temp1 (id,name) values(2,’aaa’)
— start 2nd savepoint (sp2)under sp1
SAVE tran sp2
insert into temp1 (id,name) values(3,’aaa’)
/*if their chaking @@trancount then it will show 1 only because save tran create number of sdavepoint not transaction,
transaction always open with begin tran only*/
— this will rollback 2nd
ROLLBACK tran sp2 — if we rollback sp1 then sp1 and sp2 both are rollback
/*and if sp2 rollback then only sp2 rollback
keep in mind thier only savepoint rollback not a transection so transaction still opend thier*/
— their main transection will commit
COMMIT
SELECT * FROM temp1
EX.2
TRUNCATE TABLE temp1
BEGIN tran
insert into temp1 (id,name) values(1,’aaa’)
— start 1st savepoint (sp1)
SAVE tran sp1
insert into temp1 (id,name) values(2,’aaa’)
BEGIN tran
insert into temp1 (id,name) values(11,’bbb’)
SAVE tran sp21
insert into temp1 (id,name) values(12,’aaa’)
/*their rollbak all task rollbak from sp1 savepoint and keep in mind
thier savepoint rollbak not a transection so 2 transactions still open */
ROLLBACK tran sp1
COMMIT
ROLLBACK
Recent Comments