Database Transaction, Isolation Levels and Locking Mechanism

 

 

 

 

 

 

 

 

 

Bad data Issues

 

Dirty Read

 

 

The below are the initial and sample data of this experiment.

 

 

 

 

Some sample transactions which changed the value of the records from one value to some other value.

 

 

 

First Issue:

Here, the highlighted value “40”  and the second record from last value has been inter changed by the transaction while other transactions reading it.  So, 40$ doesn’t reflected anywhere in our second record set.

 

 

 

Second Issue:

Here, the highlighted record “33” in the second records set, this was not committed yet.

 

 

 

Third Issue:

Here, the value 15$ red twice by the second transaction and reflected the same in result set.

 

 

 

 

Other type of Bad Data issues

 

 

 

Locks

 

 

 

 

SQL Server Lock manager will do the Dynamic locking based on the transaction load.  So, developers does not need to use Lock Hints, which is not preferable.

 

 

 

 

 

 

 

 

 

 

 

 

Isolation Levels

 

 

 

 

 

 

 

 

 

 

 

 

As per below chart some transaction requires Exclusive Lock (X) on objects, Shared Lock (S) is not possible on the same objects if other transaction requires it.  In our example, No Shared locks requested

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Dead Lock

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Blogger Labels: Database,Transaction,Isolation,Levels,Mechanism,data,Issues,Read,Some,transactions,Issue,Here,Second,Third,Locks,Server,Lock,manager,Dynamic,developers,Hints,Exclusive,example,Dead