SQL Server Transaction Processing

             SQL Server Transaction Processing. Transaction is used for the system ,where data mismatch is a very sensitive issue. For example  we are doing an ATM transaction, card processing done, currency not been disposed, due to some unavoidable reason , the situation is , the amount is debited from the user account, but the user actually did not get any money from the ATM machine. This kind of situation is not acceptable  in real life transaction. 


             Another example is,  there are two server for a multinational company .Two  server concurrently handle same data, and do load balancing for huge data. When a transaction is done, the first server is updated , the second server need to be update concurrently. If second server does not get updated ,second server will result mismatch of data, the company will face  seviour loss of data.

              Here come the Transaction Processing system. Transaction Processing system guarantee that  the processing either succeed or rollback all. Transaction is atomic unit of processing with two stage ,commit or rollback. It help to retain in ACID property of a  relational databases system. 

Transaction Processing  is mainly used for

• Batch processing
• Separate databases update concurrently
• distributed transaction

Transaction Processing System has four Component

• begin transaction
• end transaction
• commit
• rollback

begin transaction is a beginning of a transaction .
end transaction is the ending of a transaction, if the transaction success , then commit command is executed either to rollback command is executed to rollback all SQL statement.
 

Generally, transaction is kept under Try-catch statement, to detect any error or exception has been occurred during the processing. 
 
Rollback statement is written under the catch blockt to detect any exception has been Occurred or not.


Transaction processing  lock the  data  used by himself. No other transaction can concurrently update or delete the same data, untill the transaction has been completed and it has been released.


There are two type of transaction
  Implicit Transaction : Implicit transaction are two , type DDL and DML. 
DDL are CREATE ,ALTER,DROP,TRUNCATE etc.
DML are INSERT,UPDATE,DELETE delete etc.


Explicit Transactions
 

Below is example of transaction processing. Here student details updated, and with this data, School details are updated. In between,if any exception is occurred, the data will be rollback. 

USE ex311316

GO

Declare @myError varchar(500)

BEGIN TRAN

INSERT INTO school(student_name)

SELECT NAME FROM STUDENT

SELECT @myError = @@ERROR

IF (@myError <> 0) GOTO ERRORBLOCK

COMMIT TRAN

ERRORBLOCK:

IF (@myError <> 0)

BEGIN

PRINT 'Error Occured!'

ROLLBACK TRAN

END

Transaction processing  can be nested. One transaction processing can be nested under another transaction processing.

@@TRANCOUNT return the level of transaction.Untill the inner most 

transaction is committed, the outer transaction does not 
commit. When inner most transaction is committed and successfully 

then outer transaction committed only. 

SELECT 'Staring Transaction', @@TRANCOUNT

BEGIN TRANSACTION TRAN1

INSERT INTO school(student_name)

SELECT NAME FROM STUDENT

-- Second Transaction Start

BEGIN TRANSACTION TRAN2

UPDATE STUDENT_PARENTS

SET PARENT_NAME='Mr.'+PARENT_NAME

WHERE 1=1

COMMIT TRANSACTION TRAN2

-- End Of Second Transaction

SELECT ' Commiting Second Transaction', @@TRANCOUNT

COMMIT TRANSACTION TRAN1