"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:
Post a Comment