ACID properties

The transaction has four properties. These properties are used to maintain consistency in the database, before and after the transaction.

Property of Transaction

1) Atomicity
2) Consistency
3) Isolation
4) Durability

Atomicity-

It means either all or None.

Consistency-

Before and after the Transaction Database should be in consistent state.

if Issue occur- previous state.

if all fine, no issue then next stable state.

Isolation

Transaction not depend on each other. Execute independently.

Durability

It state that the transaction made the permanent changes.

Transaction Data in persistent state.

---------------------------------

Transaction 

Block of SQL Code- contains multiple SQL statements.

transaction should be as small as possible.

it impacts, Blocking and locking ,Timeout issue because hold lock on resources.

When to use?

---------------------------------

Syntax 

Create procedure procName

(

@param int

)

As

Begin

Begin Try

Begin Transaction T1

sql stmt 1

sql stmt 2

sql stmt 3

--power failure

sql stmt 4

sql stmt 5

Commit Transaction T1

End Try

Begin catch

Select error_message()

Rollback Transaction T1

End Catch 

End

Example 

Drop table If Exists Student

Drop table If Exists StudentContact

Drop table If Exists StudentIdentityInfo

Create Table Student 

(

Student_Id Int Identity(1,1) Primary Key,

FirstName Varchar(50) Not Null,

MiddleName Varchar(50) Not Null,

LastName Varchar(50) Not Null

)

GO

Create Table StudentContact

(

StudentContactId Int Identity(1,1) Primary Key,

Student_Id Int,

MobileNumber Bigint,

PhoneNumber Bigint,

ParentContactNumber Bigint

)

GO

Create Table StudentIdentityInfo

(

StudentContactId Int Identity(1,1) Primary Key,

Student_Id Int,

PANCardNumber Varchar(10),

aadharcardNumber bigint,

EPICNumber Varchar(10),

PassportNumber Varchar(10) ,

DrivingLicenNumber Varchar(20),

DateofBirth DateTime

)

GO

Stored Procedure 

Create or alter Procedure Insert_StudentData

As

BEGIN

    BEGIN TRY

        BEGIN Transaction T1

        Declare @student_id Int        

        Insert Into Student(Firstname,MiddleName,LastName) Values ('Mahesh','Ramlal','Ahire')

Set @student_id=(Select SCOPE_IDENTITY())

        Insert Into StudentContact(Student_Id,MobileNumber,PhoneNumber,ParentContactNumber) 

Values (@student_id,984514263,020154545748,8744545396)       

        Commit Transaction T1

    END TRY

    BEGIN CATCH

        Rollback Transaction T1

        INSERT INTO [dbo].[ErrorLogHandle]

        (

            [ErrorNumber],

            [ErrorLine],

            [ErrorProcedure],

            [ErrorMessage],

            [ErrorDateTime]

        )

        SELECT ERROR_NUMBER() AS ErrorNumber,

               ERROR_LINE() AS ErrorLine,

               ERROR_PROCEDURE() AS ErrorProcedure,

               ERROR_MESSAGE() AS ErrorMessage,

               GETDATE() AS ErrorDateTime

    END CATCH

END

Call SP - Exec Insert_StudentData

Select * from Student

Select * from StudentContact

Select * from StudentIdentityInfo

------------------------------------------------------------------------------------------------------------------

Transaction

it follows ACID properties.

Nested Transaction - we can create transaction inside transaction.

Create or alter Procedure Insert_StudentData

As

BEGIN

BEGIN TRY

BEGIN Transaction T1

Select @@TRANCOUNT As [Tran Count main SP- Try Block]

Declare @student_id Int

Insert Into Student(Firstname,MiddleName,LastName) Values ('Mahesh','Ramlal','Ahire')

Set @student_id=(Select SCOPE_IDENTITY())

        Insert Into StudentContact(Student_Id,MobileNumber,PhoneNumber,ParentContactNumber) 

Values (@student_id,984514263,020154545748,8744545396)

Exec childSP @student_id----------Nested Transaction

 

Commit Transaction T1

END TRY

BEGIN CATCH

Rollback Transaction T1

Select @@TRANCOUNT As [Tran Count main SP- catch Block]

INSERT INTO [dbo].[ErrorLogHandle]

([ErrorNumber],[ErrorLine],[ErrorProcedure],[ErrorMessage],[ErrorDateTime])

SELECT   ERROR_NUMBER()    AS ErrorNumber,  

ERROR_LINE()      AS ErrorLine, 

ERROR_PROCEDURE() AS ErrorProcedure, 

ERROR_MESSAGE()   AS ErrorMessage,

GETDATE()        AS ErrorDateTime

END CATCH

END

------child SP

Create or alter Procedure childSP

(

@student_id  bigint

)

As

BEGIN

BEGIN TRY

BEGIN Transaction T1_Internal

Select @@TRANCOUNT As [Tran Count Child SP]

        Insert Into StudentIdentityInfo

(Student_Id,PANCardNumber,aadharcardNumber,EPICNumber,PassportNumber,DrivingLicenNumber,DateofBirth) values (@student_id,'DLDY112745',15428125451321,'ZSH54125K','K98475547','MH20-5454745','1988-01-09')

Commit Transaction T1_Internal

END TRY

BEGIN CATCH

Rollback Transaction T1_Internal

Select @@TRANCOUNT As [Tran Count Child SP- catch Block]

INSERT INTO [dbo].[ErrorLogHandle]

([ErrorNumber],[ErrorLine],[ErrorProcedure],[ErrorMessage],[ErrorDateTime])

SELECT   ERROR_NUMBER()    AS ErrorNumber,  

ERROR_LINE()      AS ErrorLine, 

ERROR_PROCEDURE() AS ErrorProcedure, 

ERROR_MESSAGE()   AS ErrorMessage,

GETDATE()        AS ErrorDateTime

END CATCH

END

-------------------------------------------------------------------------------------------------------

Save Transaction is a point in a transaction in which you can roll the transaction back to specific point without rolling back the entire transaction. 

Create or alter Procedure Insert_StudentData

As

BEGIN

BEGIN TRY

BEGIN Transaction T1

Select @@TRANCOUNT As [Tran Count main SP- Try Block]

Declare @student_id Int

Insert Into Student(Firstname,MiddleName,LastName) Values ('Mahesh','Ramlal','Ahire')

Set @student_id=(Select SCOPE_IDENTITY())

        Insert Into StudentContact(Student_Id,MobileNumber,PhoneNumber,ParentContactNumber) 

        Values (@student_id,984514263,020154545748,8744545396)

Save Transaction S1

        Insert Into Student(Firstname,MiddleName,LastName) Values ('Ajay','Samadhan','Aher')

        Set @student_id=(Select SCOPE_IDENTITY())

                Insert Into StudentContact(Student_Id,MobileNumber,PhoneNumber,ParentContactNumber) 

        Values (@student_id,984514263,020154545748,8744545396)

        Save Transaction S2

Exec childSP @student_id

 

Commit Transaction T1

END TRY

BEGIN CATCH

Rollback Transaction S2

Select @@TRANCOUNT As [Tran Count main SP- catch Block]

INSERT INTO [dbo].[ErrorLogHandle]

([ErrorNumber],[ErrorLine],[ErrorProcedure],[ErrorMessage],[ErrorDateTime])

SELECT   ERROR_NUMBER()    AS ErrorNumber,  

ERROR_LINE()      AS ErrorLine, 

ERROR_PROCEDURE() AS ErrorProcedure, 

ERROR_MESSAGE()   AS ErrorMessage,

GETDATE()        AS ErrorDateTime

END CATCH

END