Transaction

What is Transaction.

A transaction is a set of operations performed so all operations are guaranteed to succeed or fail as one unit.


When to Use Transactions

You should use transactions when several operations must succeed or fail as a unit.

The following are some frequent scenarios where use of transactions is recommended:

In batch processing, where multiple rows must be inserted, updated, or deleted as a single unit

Whenever a change to one table requires that other tables be kept consistent

When modifying data in two or more databases concurrently

In distributed transactions, where data is manipulated in databases on various servers.


Understanding ACID Properties.

1. Atomicity:

2. Consistency

3. Isolation

4. Durability




Example:

1. 1. create procedure sp_Trans_Test  
   2. @newpersonid nvarchar(5),  
   3. @newfirstname nvarchar(10)  
   4. @newcompanyname nvarchar(15),  
   5. @oldpersonid nvarchar(5)  
   6. as  
   7. declare @inserr int  
   8. declare @delerr int  
   9. declare @maxerr int  
   10. set @maxerr = 0  
   11. BEGIN TRANSACTION  
   12.   
   13. -- Add a person  
   14. insert into person (personid, firstname, company)  
   15. values(@newpersonid, @newfirstname, @newcompanyname)  
   16.   
   17. -- Save error number returned from Insert statement  
   18. set @inserr = @@error  
   19. if @inserr > @maxerr  
   20. set @maxerr = @inserr  
   21.   
   22. -- Delete a person  
   23. delete from person  
   24. where personid = @oldpersonid  
   25.   
   26. -- Save error number returned from Delete statement  
   27. set @delerr = @@error  
   28. if @delerr > @maxerr  
   29. set @maxerr = @delerr  
   30.   
   31. -- If an error occurred, roll back  
   32. if @maxerr <> 0  
   33. begin  
   34. ROLLBACK  
   35. print 'Transaction rolled back'  
   36. end  
   37. else  
   38. begin  
   39. COMMIT  
   40. print 'Transaction committed'  
   41. end  
   42. print 'INSERT error number:'+ cast(@inserras nvarchar(8))  
   43. print 'DELETE error number:'+ cast(@delerras nvarchar(8))  
   44. return @maxerr