Transaction – Concurrency Control Techniques

This topic has everyone can comprehend and apply concurrency control techniques provided by DBMSs

What is a transaction?

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.

Properties of Transaction: ACID

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.

T-SQL for Transaction

BEGIN TRANSACTION	Marks the beginning of transaction execution.
COMMIT TRANSACTION	Marks a successful end of the transaction.
ROLLBACK TRANSACTION	Signals that the transaction has ended unsuccessfully.

Concurrency control

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.

LOST UPDATE

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:

T1	T2
Begin Tran
Read A
	Begin Tran
	Read A
A:=A+10
Write A
	A:=A*100
	Write A
	Commit Tran
Commit Tran

Ex2:

T1	T2
Begin Tran
	Begin Tran
Write A
	Write A
	Commit Tran
Commit Tran

UNCOMMITED DATA/DIRTY READ

A Dirty read is a situation when a transaction reads data that has not yet been committed.

T1	T2
Begin Tran
Read A
A:=A+10
Write A
	Begin Tran
	Read A
	Print A
	Commit Tran
Rollback Tran

UNREPEATABLE DATA

Non Repeatable read occurs when a transaction reads the same row twice, and gets a different value each time.

T1	T2
Begin Tran
Read A
	Begin Tran
	Read A
	A:=A+10
	Write A
	Commit Tran
Read A
Commit Tran

PHANTOM

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.

T1	T2
Begin Tran
Select * From SV
	Begin Tran
	Insert into SV values (...)
	Commit Tran
Select * From SV
Commit Tran