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