7.11- Why Concurrency Control Is Need - All Problems in concurrency in dbms | Complete Syllabus 

7.11- Why Concurrency Control Is Need - All Problems in concurrency in dbms | Complete Syllabus
PLAYLIST:
DBMS - Data Base Management System Tutorials | All University Exams - UGC NET- GATE DBMS Lectures
• DBMS Complete Syllabus...
Transactions & Concurrency Control In DBMS | Serializability | Recoverability | recovery System | Time stamp
• Transactions & Concurr...
database management system tutorial,
database tutorials,
transaction in dbms,
transaction management system in dbms,
dbms tutorials,
dbms tutorials in hindi,
dbms lectures,
dbms lectures in hindi,
database management tutorials,
database management lectures,
dbms tutorials online,
dbms lectures online,
dbms gate lectures in hindi,
dbms ugc net lectures,
dbms gate lectures,
#gatedbmslecturesinhindi
#ugcnetdbmslecturesinhindi
#gatedbmslectures
#ugcnetdbmslectures
#dbmsLectures
#dbmsTutorials
Problem 1: Lost Update Problems (W - W Conflict)
The problem occurs when two different database transactions perform the read/write operations on the same database items in an interleaved manner (i.e., concurrent execution) that makes the values of the items incorrect hence making the database inconsistent.
For example:
Consider the below diagram where two transactions TX and TY, are performed on the same account A where the balance of account A is $300.
DBMS Concurrency Control
At time t1, transaction TX reads the value of account A, i.e., $300 (only read).
At time t2, transaction TX deducts $50 from account A that becomes $250 (only deducted and not updated/write).
Alternately, at time t3, transaction TY reads the value of account A that will be $300 only because TX didn't update the value yet.
At time t4, transaction TY adds $100 to account A that becomes $400 (only added but not updated/write).
At time t6, transaction TX writes the value of account A that will be updated as $250 only, as TY didn't update the value yet.
Similarly, at time t7, transaction TY writes the values of account A, so it will write as done at time t4 that will be $400. It means the value written by TX is lost, i.e., $250 is lost.
Hence data becomes incorrect, and database sets to inconsistent.
Dirty Read Problems (W-R Conflict)
The dirty read problem occurs when one transaction updates an item of the database, and somehow the transaction fails, and before the data gets rollback, the updated database item is accessed by another transaction. There comes the Read-Write Conflict between both transactions.
For example:
Consider two transactions TX and TY in the below diagram performing read/write operations on account A where the available balance in account A is $300:
DBMS Concurrency Control
At time t1, transaction TX reads the value of account A, i.e., $300.
At time t2, transaction TX adds $50 to account A that becomes $350.
At time t3, transaction TX writes the updated value in account A, i.e., $350.
Then at time t4, transaction TY reads account A that will be read as $350.
Then at time t5, transaction TX rollbacks due to server problem, and the value changes back to $300 (as initially).
But the value for account A remains $350 for transaction TY as committed, which is the dirty read and therefore known as the Dirty Read Problem.
Unrepeatable Read Problem (W-R Conflict)
Also known as Inconsistent Retrievals Problem that occurs when in a transaction, two different values are read for the same database item.
For example:
Consider two transactions, TX and TY, performing the read/write operations on account A, having an available balance = $300. The diagram is shown below:
DBMS Concurrency Control
At time t1, transaction TX reads the value from account A, i.e., $300.
At time t2, transaction TY reads the value from account A, i.e., $300.
At time t3, transaction TY updates the value of account A by adding $100 to the available balance, and then it becomes $400.
At time t4, transaction TY writes the updated value, i.e., $400.
After that, at time t5, transaction TX reads the available value of account A, and that will be read as $400.
It means that within the same transaction TX, it reads two different values of account A, i.e., $ 300 initially, and after updation made by transaction TY, it reads $400. It is an unrepeatable read and is therefore known as the Unrepeatable read problem.
Thus, in order to maintain consistency in the database and avoid such problems that take place in concurrent execution, management is needed, and that is where the concept of Concurrency Control comes into role. Notes Link:
www.tutorialssp...