Transaction – Concurrency Control Techniques
This topic has everyone can comprehend and apply concurrency control techniques provided by DBMSs
Last updated
This topic has everyone can comprehend and apply concurrency control techniques provided by DBMSs
Last updated
A transaction is an executing program that forms a logical unit of database processing.
A transaction includes one or more database access operations: insertion, deletion, modification, or retrieval operations.
Atomicity(A): A transaction is an atomic unit of processing, it is either performed in its entirety or not performed at all.
Consistency(C): A transaction is consistent if its complete execution transforms the database from one consistent state to another.
Isolation(I): A transaction should appear as though it is being executed in isolation from other transactions.
Durability(D): The changes applied to the database by a committed transaction must persist in the database. These changes must not be lost because of any failure.
COMMIT TRANSACTION
Marks a successful end of the
transaction.
ROLLBACK TRANSACTION
Signals that the transaction has
ended unsuccessfully.
Interactions among transactions can cause the database state to become inconsistent, even when the transactions individually preserver correctness of the state, and there is no system failure.
A lost update occurs when two different transactions are trying to update the same column on the same row within a database at the same time. The result of the first transaction is then “lost”, as it is simply overwritten by the second transaction.
Ex1:
Begin Tran
Read A
Begin Tran
Read A
A:=A+10
Write A
A:=A*100
Write A
Commit Tran
Commit Tran
Ex2:
Begin Tran
Begin Tran
Write A
Write A
Commit Tran
Commit Tran
A Dirty read is a situation when a transaction reads data that has not yet been committed.
Begin Tran
Read A
A:=A+10
Write A
Begin Tran
Read A
Print A
Commit Tran
Rollback Tran
Non Repeatable read occurs when a transaction reads the same row twice, and gets a different value each time.
Begin Tran
Read A
Begin Tran
Read A
A:=A+10
Write A
Commit Tran
Read A
Commit Tran
Phantom Read occurs when two same queries are executed, but the rows retrieved by the two, are different. For example, suppose transaction T1 retrieves a set of rows that satisfy some search criteria. Now, Transaction T2 generates some new rows that match the search criteria for transaction T1. If transaction T1 re-executes the statement that reads the rows, it gets a different set of rows this time.
Begin Tran
Select * From SV
Begin Tran
Insert into SV values
(...)
Commit Tran
Select * From SV
Commit Tran