Short Notes: Transactions and Concurrency Control | Short Notes for Computer Science Engineering - Computer Science Engineering (CSE) PDF Download

 Page 1


Transaction Management
A sequence of many actions which are considered to be one atomic unit of work. A 
transaction is a collection of operations involving data items in a database. There 
are four important properties of transactions that a DBMS must ensure to maintain 
data in the face concurrent access and system failures.
Atomicity
Atomicity requires that each transaction is all or nothing. If one part of .the 
transaction fails, the entire transaction fails and the database state is left 
unchanged.
Consistency
If each transaction is consistent and the data base starts on as consistent, it ends 
up as consistent
Isolation
Execution of one transaction is isolated from that of another transactions. It 
ensures that concurrent execution of transaction results in a system state that 
would be obtained if transaction were executed serially, i.e., one after the other.
Durability
Durability means that once a transaction has been committed, it will remain even in 
the event of power loss, crashes, or errors. In a relational database, for instance, 
once a group of SQL statements execute, the results need to be stored 
permanently.
Page 2


Transaction Management
A sequence of many actions which are considered to be one atomic unit of work. A 
transaction is a collection of operations involving data items in a database. There 
are four important properties of transactions that a DBMS must ensure to maintain 
data in the face concurrent access and system failures.
Atomicity
Atomicity requires that each transaction is all or nothing. If one part of .the 
transaction fails, the entire transaction fails and the database state is left 
unchanged.
Consistency
If each transaction is consistent and the data base starts on as consistent, it ends 
up as consistent
Isolation
Execution of one transaction is isolated from that of another transactions. It 
ensures that concurrent execution of transaction results in a system state that 
would be obtained if transaction were executed serially, i.e., one after the other.
Durability
Durability means that once a transaction has been committed, it will remain even in 
the event of power loss, crashes, or errors. In a relational database, for instance, 
once a group of SQL statements execute, the results need to be stored 
permanently.
If a transaction commits, its effects persist.
• A transaction starts with any SQL statement and ends with a COMMIT or 
ROLLBACK.
• COMMIT statement makes changes permanent to the database.
• ROLLBACK statement reverses changes.
A transaction includes one or more database access operations. These can include 
insertion, deletion, modification or retrieval operations.
Concurrency Control: Process of managing simultaneous execution of transactions 
in a shared database, is known as concurrency control. Basically, concurrency 
control ensures that correct results for concurrent operations are generated, while 
getting those results as quickly as possible.
Need of Concurrency Control: Simultaneous execution of transactions over a 
shared database can create several data integrity and consistency problems.
Lost Update: This problem occurs when two transactions that access the same 
database items, have their operations interleaved in a way that makes the value of 
some database items incorrect.
Dirty Read Problems: This problem occurs when one transaction reads changes the 
value while the other reads the value before committing or rolling back by the first 
transaction.
Inconsistent Retrievals: This problem occurs when a transaction accesses data 
before and after another transaction(s) finish working with such data.
We need concurrence control, when
• The amount of data is sufficiently great that at any time only fraction of the 
data can be in primary memory and rest should be swapped from secondary 
memory as needed.
• Even if the entire database can be present in primary memory, there may be 
multiple processes.
Schedule: A schedule (or history) is a model to describe execution of transactions 
running in the system. When multiple transactions are executing concurrently in an 
interleaved fashion, then the order of execution of operations from the various 
transactions is known as a schedule or we can say that a schedule is a sequence 
of read, write, abort and commit operations from a set of transactions. 
Classification of Schedules Based on Serializability: The schedules can be 
classified as Serial Schedule. A schedule in which the different transactions are not 
interleaved (i.e., transactions are executed from start to finish one-by-one).
Serial Scheduled schedule in which a new transaction is allowed to begin only 
after completion of previous transaction, no middle interleaving is allowed.
Page 3


Transaction Management
A sequence of many actions which are considered to be one atomic unit of work. A 
transaction is a collection of operations involving data items in a database. There 
are four important properties of transactions that a DBMS must ensure to maintain 
data in the face concurrent access and system failures.
Atomicity
Atomicity requires that each transaction is all or nothing. If one part of .the 
transaction fails, the entire transaction fails and the database state is left 
unchanged.
Consistency
If each transaction is consistent and the data base starts on as consistent, it ends 
up as consistent
Isolation
Execution of one transaction is isolated from that of another transactions. It 
ensures that concurrent execution of transaction results in a system state that 
would be obtained if transaction were executed serially, i.e., one after the other.
Durability
Durability means that once a transaction has been committed, it will remain even in 
the event of power loss, crashes, or errors. In a relational database, for instance, 
once a group of SQL statements execute, the results need to be stored 
permanently.
If a transaction commits, its effects persist.
• A transaction starts with any SQL statement and ends with a COMMIT or 
ROLLBACK.
• COMMIT statement makes changes permanent to the database.
• ROLLBACK statement reverses changes.
A transaction includes one or more database access operations. These can include 
insertion, deletion, modification or retrieval operations.
Concurrency Control: Process of managing simultaneous execution of transactions 
in a shared database, is known as concurrency control. Basically, concurrency 
control ensures that correct results for concurrent operations are generated, while 
getting those results as quickly as possible.
Need of Concurrency Control: Simultaneous execution of transactions over a 
shared database can create several data integrity and consistency problems.
Lost Update: This problem occurs when two transactions that access the same 
database items, have their operations interleaved in a way that makes the value of 
some database items incorrect.
Dirty Read Problems: This problem occurs when one transaction reads changes the 
value while the other reads the value before committing or rolling back by the first 
transaction.
Inconsistent Retrievals: This problem occurs when a transaction accesses data 
before and after another transaction(s) finish working with such data.
We need concurrence control, when
• The amount of data is sufficiently great that at any time only fraction of the 
data can be in primary memory and rest should be swapped from secondary 
memory as needed.
• Even if the entire database can be present in primary memory, there may be 
multiple processes.
Schedule: A schedule (or history) is a model to describe execution of transactions 
running in the system. When multiple transactions are executing concurrently in an 
interleaved fashion, then the order of execution of operations from the various 
transactions is known as a schedule or we can say that a schedule is a sequence 
of read, write, abort and commit operations from a set of transactions. 
Classification of Schedules Based on Serializability: The schedules can be 
classified as Serial Schedule. A schedule in which the different transactions are not 
interleaved (i.e., transactions are executed from start to finish one-by-one).
Serial Scheduled schedule in which a new transaction is allowed to begin only 
after completion of previous transaction, no middle interleaving is allowed.
S erial S ch ed u le
T i T i
R ( A )
W ( A )
RCB)
W ( B )
S eria l S c h e d u le
T i T :
RCB)
W ( B )
R C A )
W ( A )
Complete Schedule: A schedule that contains either a commit or an abort action for 
each transaction. Consequently, a complete schedule will not contain any active 
transaction at the end of the schedule.
C o m p le te S c h e d u le
T i T i
R ( A )
RCB)
W ( A )
W ( B )
C om m it
A b o rt
C o m p le te S c h e d u le
T i t 3
R ( A )
W ( A )
R (B )
C om m it
W ( B )
A b o rt
C o m p le te S c h e d u le
T i t 2
R ( A )
W ( A )
C om m it
RCB)
W { B )
A b o rt
Non-serial Schedules: Non-serial schedules are Interleaved schedules and these 
schedules improve performance of system (i.e., throughput and response time).
But concurrency or interleaving operations in schedules, might lead the database to 
an inconsistent state. We need to seek to identify schedules that are:
Page 4


Transaction Management
A sequence of many actions which are considered to be one atomic unit of work. A 
transaction is a collection of operations involving data items in a database. There 
are four important properties of transactions that a DBMS must ensure to maintain 
data in the face concurrent access and system failures.
Atomicity
Atomicity requires that each transaction is all or nothing. If one part of .the 
transaction fails, the entire transaction fails and the database state is left 
unchanged.
Consistency
If each transaction is consistent and the data base starts on as consistent, it ends 
up as consistent
Isolation
Execution of one transaction is isolated from that of another transactions. It 
ensures that concurrent execution of transaction results in a system state that 
would be obtained if transaction were executed serially, i.e., one after the other.
Durability
Durability means that once a transaction has been committed, it will remain even in 
the event of power loss, crashes, or errors. In a relational database, for instance, 
once a group of SQL statements execute, the results need to be stored 
permanently.
If a transaction commits, its effects persist.
• A transaction starts with any SQL statement and ends with a COMMIT or 
ROLLBACK.
• COMMIT statement makes changes permanent to the database.
• ROLLBACK statement reverses changes.
A transaction includes one or more database access operations. These can include 
insertion, deletion, modification or retrieval operations.
Concurrency Control: Process of managing simultaneous execution of transactions 
in a shared database, is known as concurrency control. Basically, concurrency 
control ensures that correct results for concurrent operations are generated, while 
getting those results as quickly as possible.
Need of Concurrency Control: Simultaneous execution of transactions over a 
shared database can create several data integrity and consistency problems.
Lost Update: This problem occurs when two transactions that access the same 
database items, have their operations interleaved in a way that makes the value of 
some database items incorrect.
Dirty Read Problems: This problem occurs when one transaction reads changes the 
value while the other reads the value before committing or rolling back by the first 
transaction.
Inconsistent Retrievals: This problem occurs when a transaction accesses data 
before and after another transaction(s) finish working with such data.
We need concurrence control, when
• The amount of data is sufficiently great that at any time only fraction of the 
data can be in primary memory and rest should be swapped from secondary 
memory as needed.
• Even if the entire database can be present in primary memory, there may be 
multiple processes.
Schedule: A schedule (or history) is a model to describe execution of transactions 
running in the system. When multiple transactions are executing concurrently in an 
interleaved fashion, then the order of execution of operations from the various 
transactions is known as a schedule or we can say that a schedule is a sequence 
of read, write, abort and commit operations from a set of transactions. 
Classification of Schedules Based on Serializability: The schedules can be 
classified as Serial Schedule. A schedule in which the different transactions are not 
interleaved (i.e., transactions are executed from start to finish one-by-one).
Serial Scheduled schedule in which a new transaction is allowed to begin only 
after completion of previous transaction, no middle interleaving is allowed.
S erial S ch ed u le
T i T i
R ( A )
W ( A )
RCB)
W ( B )
S eria l S c h e d u le
T i T :
RCB)
W ( B )
R C A )
W ( A )
Complete Schedule: A schedule that contains either a commit or an abort action for 
each transaction. Consequently, a complete schedule will not contain any active 
transaction at the end of the schedule.
C o m p le te S c h e d u le
T i T i
R ( A )
RCB)
W ( A )
W ( B )
C om m it
A b o rt
C o m p le te S c h e d u le
T i t 3
R ( A )
W ( A )
R (B )
C om m it
W ( B )
A b o rt
C o m p le te S c h e d u le
T i t 2
R ( A )
W ( A )
C om m it
RCB)
W { B )
A b o rt
Non-serial Schedules: Non-serial schedules are Interleaved schedules and these 
schedules improve performance of system (i.e., throughput and response time).
But concurrency or interleaving operations in schedules, might lead the database to 
an inconsistent state. We need to seek to identify schedules that are:
1. As fast as interleaved schedules.
2. As consistent as serial schedules.
.N o n Serial gr C pncm Tgpt E ram pit: ¦
T 1 TZ til TZ
R {A )
R (A ]
W (E ) W fA )
m * )
R (R )
R (E )
W (B )
C O M M IT
C O M M IT R(Et]
R ( n )
C O M M IT C O M M IT
CONSISTENT INCONSISTENT
Conflicting Operations: When two or more transactions in a non-serial schedule 
execute concurrently, then there may be some conflicting operations. Two 
operations are said to be conflicting, if they satisfy all of the following conditions.
• The operations belong to different transactions.
• Atleast one of the operations is a write operation.
• The operations access the same object or item.
The following set of operations is conflicting
T, T 2
______
1
1 W(X)
While the following sets of operations are not conflicting
T , T, T3
s
«(X)N
* V
*<X)
W()N
w(y)N
R(X)
//No write on same object //No write on same object
Serializable Schedule: A schedule S of n transactions is serializable, if it is 
equivalent to some serial schedule of the same n transactions.
A non-serial schedule S is serializable is equivalent to saying that it is correct, 
because it is equivalent to a serial schedule. There are two types of serializable 
schedule
1. Conflict serializable schedule
2. View serializable schedule
Conflict Serializable Schedule: When the schedule (S) is conflict equivalent to some 
serial schedule (SI), then that schedule is called as conflict serializable schedule.
In such a case, we can reorder the non-conflicting operations in S until we form the 
equivalent serial schedule S'.
Page 5


Transaction Management
A sequence of many actions which are considered to be one atomic unit of work. A 
transaction is a collection of operations involving data items in a database. There 
are four important properties of transactions that a DBMS must ensure to maintain 
data in the face concurrent access and system failures.
Atomicity
Atomicity requires that each transaction is all or nothing. If one part of .the 
transaction fails, the entire transaction fails and the database state is left 
unchanged.
Consistency
If each transaction is consistent and the data base starts on as consistent, it ends 
up as consistent
Isolation
Execution of one transaction is isolated from that of another transactions. It 
ensures that concurrent execution of transaction results in a system state that 
would be obtained if transaction were executed serially, i.e., one after the other.
Durability
Durability means that once a transaction has been committed, it will remain even in 
the event of power loss, crashes, or errors. In a relational database, for instance, 
once a group of SQL statements execute, the results need to be stored 
permanently.
If a transaction commits, its effects persist.
• A transaction starts with any SQL statement and ends with a COMMIT or 
ROLLBACK.
• COMMIT statement makes changes permanent to the database.
• ROLLBACK statement reverses changes.
A transaction includes one or more database access operations. These can include 
insertion, deletion, modification or retrieval operations.
Concurrency Control: Process of managing simultaneous execution of transactions 
in a shared database, is known as concurrency control. Basically, concurrency 
control ensures that correct results for concurrent operations are generated, while 
getting those results as quickly as possible.
Need of Concurrency Control: Simultaneous execution of transactions over a 
shared database can create several data integrity and consistency problems.
Lost Update: This problem occurs when two transactions that access the same 
database items, have their operations interleaved in a way that makes the value of 
some database items incorrect.
Dirty Read Problems: This problem occurs when one transaction reads changes the 
value while the other reads the value before committing or rolling back by the first 
transaction.
Inconsistent Retrievals: This problem occurs when a transaction accesses data 
before and after another transaction(s) finish working with such data.
We need concurrence control, when
• The amount of data is sufficiently great that at any time only fraction of the 
data can be in primary memory and rest should be swapped from secondary 
memory as needed.
• Even if the entire database can be present in primary memory, there may be 
multiple processes.
Schedule: A schedule (or history) is a model to describe execution of transactions 
running in the system. When multiple transactions are executing concurrently in an 
interleaved fashion, then the order of execution of operations from the various 
transactions is known as a schedule or we can say that a schedule is a sequence 
of read, write, abort and commit operations from a set of transactions. 
Classification of Schedules Based on Serializability: The schedules can be 
classified as Serial Schedule. A schedule in which the different transactions are not 
interleaved (i.e., transactions are executed from start to finish one-by-one).
Serial Scheduled schedule in which a new transaction is allowed to begin only 
after completion of previous transaction, no middle interleaving is allowed.
S erial S ch ed u le
T i T i
R ( A )
W ( A )
RCB)
W ( B )
S eria l S c h e d u le
T i T :
RCB)
W ( B )
R C A )
W ( A )
Complete Schedule: A schedule that contains either a commit or an abort action for 
each transaction. Consequently, a complete schedule will not contain any active 
transaction at the end of the schedule.
C o m p le te S c h e d u le
T i T i
R ( A )
RCB)
W ( A )
W ( B )
C om m it
A b o rt
C o m p le te S c h e d u le
T i t 3
R ( A )
W ( A )
R (B )
C om m it
W ( B )
A b o rt
C o m p le te S c h e d u le
T i t 2
R ( A )
W ( A )
C om m it
RCB)
W { B )
A b o rt
Non-serial Schedules: Non-serial schedules are Interleaved schedules and these 
schedules improve performance of system (i.e., throughput and response time).
But concurrency or interleaving operations in schedules, might lead the database to 
an inconsistent state. We need to seek to identify schedules that are:
1. As fast as interleaved schedules.
2. As consistent as serial schedules.
.N o n Serial gr C pncm Tgpt E ram pit: ¦
T 1 TZ til TZ
R {A )
R (A ]
W (E ) W fA )
m * )
R (R )
R (E )
W (B )
C O M M IT
C O M M IT R(Et]
R ( n )
C O M M IT C O M M IT
CONSISTENT INCONSISTENT
Conflicting Operations: When two or more transactions in a non-serial schedule 
execute concurrently, then there may be some conflicting operations. Two 
operations are said to be conflicting, if they satisfy all of the following conditions.
• The operations belong to different transactions.
• Atleast one of the operations is a write operation.
• The operations access the same object or item.
The following set of operations is conflicting
T, T 2
______
1
1 W(X)
While the following sets of operations are not conflicting
T , T, T3
s
«(X)N
* V
*<X)
W()N
w(y)N
R(X)
//No write on same object //No write on same object
Serializable Schedule: A schedule S of n transactions is serializable, if it is 
equivalent to some serial schedule of the same n transactions.
A non-serial schedule S is serializable is equivalent to saying that it is correct, 
because it is equivalent to a serial schedule. There are two types of serializable 
schedule
1. Conflict serializable schedule
2. View serializable schedule
Conflict Serializable Schedule: When the schedule (S) is conflict equivalent to some 
serial schedule (SI), then that schedule is called as conflict serializable schedule.
In such a case, we can reorder the non-conflicting operations in S until we form the 
equivalent serial schedule S'.
Serial schedule Serializable schedule A Serializable schedule B
T ,
R{A)
W (A ) v
m '
W{B)
\
\ a )
\ W ( A )
« < 8 )
W (B )
*
B(A)
W(A) ,
'¦ '**[*)
W(A)
m
W(8) v
N . fl(B)
W(fl)
r a
«(A) -
R(A )
W (A )
W (A )
m * -
W [B )
R(B)
W (8)
Conflict Equivalence: The schedules Si and S2 are said to be conflict equivalent, if 
the following conditions are satisfied:
• Both schedules Si and S2 involve the same set of transactions (including 
ordering of operations within each transaction).
• The order of each pair of conflicting actions in Si and S2 are the same.
Testing for Conflict Serializability of a Schedule: There is a simple algorithm that 
can be used to test a schedule for conflict serializability. This algorithm constructs 
a precedence graph (or serialization graph), which is a directed graph. A 
precedence graph for a schedule S contains
1. A node for each committed transaction in S.
2. An edge from Tj to Tj, if an action of Tj precedes and conflicts with one of Tj’s 
operations.
5 r * T3
m \
W (A)>
J
? W(A)
Conflict serializability
A schedule S is conflict serializable if and only if its precedence graphs is acyclic.
Note: The above example of schedule is not conflict serializable schedule.
View Serializable Schedule: A schedule is view serializable, if it is view equivalent 
to some serial schedule. Conflict serializable I View serializable, but not vice-versa.
______ All schedules______
View serializable 
Conflict serializable 
Serial schedule
View Equivalence: Two schedules Si and S2 are view equivalent,