Friday, February 3, 2012

Transaction Management Sql Server 2008

"How to Manage Sql Transactions "

"I am working on a project where I need to execute two or more SQL commands in such a way that if any one of the statements fails, then no other statement will be able to change the database.  So this is the core requirement of transaction to handle this kind of situations"


The need of Transaction can be defined with the help of an example for transferring money from a bank account to another bank account:
UPDATE Accounts SET Balance = Balance – 10 WHERE Customer = 1;
UPDATE Accounts SET Balance = Balance + 10 WHERE Customer = 2;
If the first SQL statement was to execute and the second SQL statement was to fail, then ten dollars would be removed from the first customer’s account but will never be added to the second customer’s account. 
Every transaction must follow the ACID properties.
  • Atomicity: All statements in a group must execute, or no statement in a group must execute.
  • Consistency: This follows naturally from atomic – a group of SQL statements must take the database from a known starting state to a known ending state. If the statements execute, the database must be at the known ending state. If the statements fail, the database must be at the known starting state.
  • Isolation: A group of statements must execute independently from any other statement groups being executed at the same time. If this wasn’t the case, it would be impossible for statement groups to be consistent – the known ending state could be altered by a code you have no control over or knowledge of. This is one of those concepts that are great in theory, but total isolation has important performance implications in the real world. More on how SQL Server implements this is explained later.
  • Durability: Once the group of SQL statements execute, the results need to be stored in a permanent media – if the database crashes right after a group of SQL statements execute, it should be possible to restore the database state to the point after the last transaction committed.
                                               
                                                   Example
CREATE  PROCEDURE  UpdateMobile
@newMobileNumber BIGINT,
@userId INT
AS
BEGIN
    DECLARE @message VARCHAR(300)
    BEGIN TRY
        BEGIN TRANSACTION
            UPDATE  Users SET MobileNumber=@newMobileNumber,[Active]=0 WHERE UserID=@userId
            SET @message='Mobile Updated Successfully'
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SET @message='ERROR at Line number :'+ CONVERT(VARCHAR(5),ERROR_LINE()) +' '+ ERROR_MESSAGE()
    END CATCH
    Select @message
End

0 comments:

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Powered by Code Imagine