SQL Server-Common Concurrency Problem


Problem Everywhere.. Solution Everywhere..

If two or more transaction is accessing the same resource at same time. There is a problem of concurrency. Lets discuss what are the common problem arises in SQL Server.
1) Dirty Read
2) Lost Update
3) Non-repeatable Reads
4) Phantom Reads

Common Concurrency Problem | Lost update | Academy4Code.com
Common Concurrency Problem | Lost update | Academy4Code.com

 

1)Dirty Read – this problem is already discussed in the last posts.
Please follow at

Understand Concurrency : Pictorial Representation -Phase#1

Isolation level-ReadUnCommitted: Pictorial Representation -Phase#2

2) Lost Update problem
To make it understand, we need to take one of the example.

  • Lets John and Maria are working together on the school portal.
    Both of them generated the all student report and found that one of the student age is in correct.
    Currently,It is 17 years old in school record.
    John, thinks it should be 14 instead of 17 and Maria thinks it should be 15 instead of 17.
    Unfortunately, both of them started updating the record in the same Timestamp.
    But, John takes more time to commit the record from age 17 to 14 as compared to Maria.
    She commit the record from age 17 to 15 earlier than John.

Now, Maria wants to take the print of the currently updated record.
She found that lost the updated record of age, from 17 to 15.

this is the lost update problem in SQL server in concurrent transaction.

The (3) and (4) problem will be discussed in the next upcoming posts.

please write at us if you have any query over ravindra@academy4code.com and academy4code@gmail.com

Ravindra Kumar is a Programmer and an independent consultant from India. He has been a part of the industry for more than 6 years. During his career, he has worked on mutiple projects of the USA and of India (Technology Evangelist at Microsoft). He received his Bachelors of Engineering from Punjan Technical University. He has been a regular speaker of SQL Sessions in Indian IT Company.

For more, you can consult at ravindra@Academy4Code.com