SELFSTUDYPRO

Unit-2
Transaction Management and Concurrency Control
❖ What is a Transaction?
Transaction Properties

❖ Concurrency Control
Lost Updates
Uncommitted Data
Inconsistent Retrievals

❖ Concurrency Control with Locking Methods

Lock
Lock Manager
Lock Granularity
• Database-Level Lock
• Table-Level Lock
• Page-Level Lock
• Row-Level Lock
• Field-Level Lock
Types of Locks
• Binary Lock
• Shared and Exclusive Lock
Two Phase Locking Protocol (2PL)

❖ Concurrency Control with Stamping Methods

Wait/Die and Wound/Wait Schemes

❖ Concurrency Control with Optimistic Methods

Read Phase
Validation Phase
Write Phase

❖ Database Recovery Management

Types of Backup
• Full Backup
• Differential Backup
• Transaction Log Backup

Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 1

By: Chirag SIR
 SELFSTUDYPRO
❖ What is a Transaction?
- In database terms, a transaction is any action that reads from and/or writes to a database.
- A transaction may consist of:
✓ A simple SELECT statement to generate a list of table contents.
✓ It may consist of a series of related UPDATE statements to change the values of
attributes in various tables.
✓ It may consist of a series of INSERT statements to add rows to one or more tables.
✓ It may consist of a combination of SELECT, UPDATE, and INSERT statements.

- A transaction is a logical unit of work that must be entirely completed or ended.

- All of the SQL statements in the transaction must be completed successfully.
- If any of the SQL statements fail, the entire transaction is rolled back to the original
database that existed before the transaction started.
- A successful transaction changes the database from one consistent state to another.
- A consistent database state is one in which all data integrity constraints are satisfied.
- Most real-world database transactions are formed by two or more database requests.
- A database request is the equivalent of a single SQL statement in an application
program or transaction.
- For example, if a transaction is composed of two UPDATE statements and one INSERT
statement, the transaction uses three database requests.
- In turn, each database request generates several input/output (I/O) operations that read
from or write to physical storage media.

Transaction Properties
- Each individual transaction must display atomicity, consistency, isolation, and durability.
These properties are sometimes referred to as the ACID test.
- In addition, when executing multiple transactions, the DBMS must schedule the parallel
execution of the transaction’s operations. The schedule of such transaction’s operations
must show the property of serializability.
• Atomicity
- It requires that all operations/SQL requests of a transaction be either completed or
aborted.
• Consistency
- Consistency indicates the stability of the database’s consistent state.
- A transaction takes a database from one consistent state to another consistent state. When
a transaction is completed, the database must be in a consistent state.
- If any of the transaction parts violates an integrity constraint, the entire transaction is
aborted.
• Isolation
- Isolation means that the data used during the execution of a transaction cannot be used by
a second transaction until the first one is completed.
• Durability
- Durability ensures that once transaction changes are done (committed), they cannot be
undone or lost, even in the event of a system failure.
• Serializability
- Serializability ensures that the schedule for the concurrent execution of the transactions
produces consistent results.
- This property is important in multiuser and distributed databases, where multiple
transactions are likely to be executed concurrently.

Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 2

By: Chirag SIR
 SELFSTUDYPRO
❖ Concurrency Control
- The coordination of the simultaneous execution of transactions in a multiuser database
system is known as concurrency control.
- The objective of concurrency control is to ensure the serializability of transactions in a
multiuser database environment.
- Concurrency control is important because the simultaneous execution of transactions over
a shared database can create several data integrity and consistency problems.
- The three main problems are lost updates, uncommitted data, and inconsistent retrievals
which are as follows:
Lost Updates
- The lost update problem occurs when two concurrent transactions, T1 and T2, are
updating the same data element and one of the updates is lost (overwritten by the other
transaction).

Example
Consider this normal execution of two transactions T1 and T2.
T1: Purchase 100 units and add the PROD_QOH (Product Quantity on Hand)
T2: Sell 30 units and subtract from PROD_QOH (Product Quantity on Hand)
Time Transaction Step Stored Value
1 T1 Read PROD_QOH 35
2 T1 PROD_QOH = 35+100
3 T1 Write PROD_QPH 135
4 T2 Read PROD_QOH 135
5 T2 PROD_QOH = 135-30
6 T2 Write PROD_QOH 105

- The first transaction (T1) has not yet been committed when the second transaction (T2) is
executed.
- Therefore, T2 still operates on the value 35, and its subtraction yields 5 in memory.
- In the meantime, T1 writes the value 135 to disk, which is promptly overwritten by T2.
- In short, the addition of 100 units is “lost” during the process.
Time Transaction Step Stored Value
1 T1 Read PROD_QOH 35
2 T2 Read PROD_QOH 35
3 T1 PROD_QOH = 35 + 100
4 T2 PROD_QOH = 35 - 30
5 T1 Write PROD_QOH (Lost Update) 135
6 T2 Write PROD_QOH 5

Uncommitted Data
- The phenomenon of uncommitted data occurs when two transactions, T1 and T2, are
executed parallel and the first transaction (T1) is rolled back after the second transaction

Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 3

By: Chirag SIR
 SELFSTUDYPRO
(T2) has already read the uncommitted data - thus violating the isolation property of
transactions.
Example
Consider this normal execution of two transactions T1 and T2.
T1: Purchase 100 units and add the PROD_QOH (Product Quantity on Hand) - Rollback
T2: Sell 30 units and subtract from PROD_QOH (Product Quantity on Hand)

- Under normal circumstances, the serial execution of those transactions produces the
correct answer.
Time Transaction Step Stored Value
1 T1 Read PROD_QOH 35
2 T1 PROD_QOH = 35+100
3 T1 Write PROD_QPH 135
4 T1 ROLLBACK 35
5 T2 Read PROD_QOH 35
6 T2 PROD_QOH = 35-30
7 T2 Write PROD_QOH 5

- Below table shows how the uncommitted data problem can arise when the ROLLBACK
is completed after T2 has begun its execution.
Time Transaction Step Stored Value
1 T1 Read PROD_QOH 35
2 T1 PROD_QOH = 35+100
3 T1 Write PROD_QPH 135
4 T2 Read PROD_QOH 135
(Read Uncommitted Data)
5 T2 PROD_QOH = 135-30
6 T1 ROLLBACK 35
7 T2 Write PROD_QOH 105

Inconsistent Retrievals
- Inconsistent retrievals occur when a transaction accesses data before and after another
transaction(s) finish working with such data.
- For example, an inconsistent retrieval would occur if transaction T1 calculated some
summary (aggregate) function over a set of data while another transaction (T2) was
updating the same data.
- The problem is that the transaction might read some data before they are changed and
other data after they are changed, thereby producing inconsistent results.
Example
1. T1 calculates the total quantity on hand of the products stored in the PRODUCT table.
2. At the same time, T2 updates the quantity on hand (PROD_QOH) for two of the
PRODUCT table’s products.
Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 4
By: Chirag SIR
 SELFSTUDYPRO
PROD_CODE PROD_QOH PROD_QOH
Before After
P1 100 100
P2 120 120
P3 70 70+10 = 80
P4 35 35-10 = 25
P5 100 100
P6 30 30

TIME TRANSACTION STEP VALUE TOTAL

1 T1 Read PROD_QOH 100 100
For Prod_Code= ‘P1’
2 T1 Read PROD_QOH 120 220
For Prod_Code= ‘P2’
3 T2 Read PROD_QOH 70
For Prod_Code= ‘P3’
4 T2 PROD_QOH = 70+10
5 T2 Write PROD_QOH 80
For Prod_Code= ‘P3’
6 T1 Read PROD_QOH 80 300
For Prod_Code= ‘P3’
7 T1 Read PROD_QOH 35 335
For Prod_Code= ‘P4’
8 T2 Read PROD_QOH 35
For Prod_Code= ‘P4’
9 T2 PROD_QOH = 35-10
10 T2 Write PROD_QOH 25
For Prod_Code= ‘P4’
11 T2 COMMIT
12 T1 Read PROD_QOH 100 435
For Prod_Code= ‘P5’
13 T1 Read PROD_QOH 30 465
For Prod_Code= ‘P6’

Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 5

By: Chirag SIR
 SELFSTUDYPRO
- The total 465 is wrong and the correct answer is 455, such problem of concurrency
control can result in inconsistency in information retrieved from the database.

❖ Concurrency Control with Locking Methods

Lock
- A lock guarantees exclusive use of a data item to a current transaction.
- In other words, transaction T2 does not have access to a data item that is currently being
used by transaction T1.
- A transaction gets a lock prior to data access; the lock is unlocked when the transaction is
completed so that another transaction can lock the data item for its exclusive use.

Lock Manager
- Most multiuser DBMSs automatically initiate and enforce locking procedures.
- All lock information is managed by a lock manager, which is responsible for assigning
and policing the locks used by the transactions.

Lock Granularity
- Lock granularity indicates the level of lock use.
- Locking can take place at the following levels: database, table, page, row, or even field
(attribute).

• Database-level Lock
- In database-level lock entire database is locked by the transaction.
- For example: if transaction T1 has locked the database, then transaction T2 will wait until
T1 unlocks the database.
- T2 cannot access any other tables within the database. Thus, two transaction cannot
access the same database at the same time.

• Table-level Lock
- In table-level lock entire table is locked and rest of the database can be locked by other
transaction.
- For example: Transaction T1 has locked the table product, the simultaneous transaction
T2 can lock another table customer.

• Page-level Lock
- In a page-level lock, the DBMS will lock an entire disk page. A disk page, or page, is the
equivalent of a disk block, which can be described as a directly addressable section of a
disk.
- A page has a fixed size, such as 4K, 8K, or 16K.
- A table can span several pages, and a page can contain several rows of one or more
tables.
- Page-level locks are currently the most frequently used multiuser DBMS locking method.

• Row-level Lock
- A row-level lock is much less restrictive than the other locks.
- The DBMS allows concurrent transactions to access different rows of the same table even
when the rows are located on the same page.
- Although the row-level locking approach improves the availability of data, its
management requires high overhead because a lock exists for each row in a table of the
database involved in a conflicting transaction.

Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 6

By: Chirag SIR
 SELFSTUDYPRO
• Field-level Lock
- The field-level lock allows concurrent transactions to access the same row as long as they
require the use of different fields (attributes) within that row.

Types of Locks
- Regardless of the level of locking, the DBMS may use different lock types which are as
follows: Binary or Shared/Exclusive.
• Binary Lock
- A binary lock has only two states: locked (1) or unlocked (0).
- If an object - that is, a database, table, page, or row – is locked by a transaction, no other
transaction can use that object.
- If an object is unlocked, any transaction can lock the object for its use.
- Every database operation requires that the affected object be locked.
- As a rule, a transaction must unlock the object after its termination.
- Therefore, every transaction requires a lock and unlock operation for each data item that
is accessed.
- Such operations are automatically managed and scheduled by the DBMS; the user does
not need to be concerned about locking or unlocking data items.
Time Transaction Step Stored Value
1 T1 Lock Product
2 T1 Read PROD_QOH 35
3 T1 PROD_QOH = 35+100
4 T1 Write PROD_QOH 135
5 T1 Unlock Product
6 T2 Lock Product
7 T2 Read PROD_QOH 135
8 T2 PROD_QOH = 135-30
9 T2 Write PROD_QOH 105
10 T2 Unlock Product

• Shared and Exclusive Lock

- The labels “shared” and “exclusive” indicate the nature of the lock.
o Exclusive Lock
- An exclusive lock exists when access is reserved specifically for the transaction that
locked the object.
- The exclusive lock must be used when the likely for conflict exists.
- It is used when the transactions are updating or inserting new values to the database.

o Shared Lock
- A shared lock exists when parallel transactions are granted read access on the basis of a
common lock.
- A shared lock produces no conflict as long as all the concurrent transactions are read-
only.

Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 7

By: Chirag SIR
 SELFSTUDYPRO
Two Phase Locking Protocol (2PL)
- Two-phase locking defines how transactions get and surrender locks. Two-phase locking
guarantees serializability, but it does not prevent deadlocks.
- The two phases are:
1. A growing phase, in which a transaction acquires all required locks without
unlocking any data. Once all locks have been acquired, the transaction is in its locked
point.
2. A shrinking phase, in which a transaction releases all locks and cannot obtain any
new lock.

- The two-phase locking protocol is governed by the following rules:

✓ Two transactions cannot have conflicting locks.
✓ No unlock operation can lead a lock operation in the same transaction.
✓ No data are affected until all locks are obtained - that is, until the transaction is in its
locked point.

❖ Concurrency Control with Stamping Methods

- The time stamping method is scheduling concurrent/parallel transactions assigns a global,
unique time stamp to each transaction.
- The time stamp value produces an order in which transactions are submitted to the
DBMS.
- Time stamps must have two properties: uniqueness and monotonicity.
✓ Uniqueness ensures that no equal time stamp values can exist.
✓ Monotonicity ensures that time stamp values always increase.
- All database operations (Read and Write) within the same transaction must have the same
time stamp.
- The DBMS executes conflicting operations in time stamp order, thereby ensuring
serializability of the transactions.
- If two transactions conflict, one is stopped, rolled back, rescheduled, and assigned a new
time stamp value.

Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 8

By: Chirag SIR
 SELFSTUDYPRO
Wait/Die and Wound/Wait Schemes
- Wait/die scheme, the older transaction waits for the younger to complete and release its
locks.
- Wound/wait scheme, the older transaction rolls back the younger transaction and
reschedules it.

❖ Concurrency Control with Optimistic Methods

- The optimistic approach is based on the assumption that the majority of the database
operations do not conflict.
- The optimistic approach requires neither locking nor time stamping techniques.
- Instead, a transaction is executed without restrictions until it is committed.
- Using an optimistic approach, each transaction moves through two or three phases,
referred to as read, validation, and write.
Read Phase
- During the read phase, the transaction reads the database, executes the needed
computations, and makes the updates to a private copy of the database values.
- All update operations of the transaction are recorded in a temporary update file, which is
not accessed by the remaining transactions.

Validation Phase
- During the validation phase, the transaction is validated to ensure that the changes made
will not affect the integrity and consistency of the database.
- If the validation test is positive, the transaction goes to the write phase.
- If the validation test is negative, the transaction is restarted and the changes are discarded.

Write Phase
- During the write phase, the changes are permanently applied to the database.

❖ Database Recovery Management

- Database recovery restores a database from a given state (usually inconsistent) to a
previously consistent state.
- Recovery techniques are based on the atomic transaction property: all portions of the
transaction must be treated as a single, logical unit of work in which all operations are
applied and completed to produce a consistent database.
- If, for some reason, any transaction operation cannot be completed, the transaction must
be aborted and any changes to the database must be rolled back (undone).
- In short, transaction recovery reverses all of the changes that the transaction made to the
database before the transaction was aborted.
Types of Backups
- DBMS provides backup facilities for recovery of the database.
• Full Backup
- In full back up the copy of entire database is taken as a backup.

• Differential Backup
- Instead of copying the entire database again and again, only last modifications done to the
database are considered for backup.

• Transaction Log Backup

- The copy of transaction log file is considered for backup. This backup also takes into
consideration of last modifications done to the database.

Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 9

By: Chirag SIR
 SELFSTUDYPRO
- These backups are kept at a secure place as they are required in case of database failure to
restore the database.

- The recovery of transactions, recovery techniques also apply to the database and to the
system after some type of critical error has occurred.
- Critical events can cause a database to become nonoperational and compromise the
integrity of the data.
- Examples of critical events are:
• Hardware/Software Failure
- A failure of this type could be a hard disk media failure, a bad capacitor on a
motherboard, or a failing memory bank.
- Other causes of errors under this category include application program or operating
system errors that cause data to be overwritten, deleted, or lost.

• Human-Caused Incident
- This type of event can be categorized as unintentional or intentional.
- An unintentional failure is caused by carelessness by end users. Such errors include
deleting the wrong rows from a table, pressing the wrong key on the keyboard, or shutting
down the main database server by accident.
- Intentional events are of a more severe nature and normally indicate that the company
data are at serious risk. Under this category are security threats caused by hackers trying
to gain unauthorized access to data resources and virus attacks caused by dissatisfied
employees trying to compromise the database operation and damage the company.

• Natural Disasters
- This category includes fires, earthquakes, floods, and power failures.

Transaction Recovery
- Database transaction recovery uses data in the transaction log to recover a database from
an inconsistent state to a consistent state.
- The database recovery process involves bringing the database to a consistent state after a
failure.
- Transaction recovery procedures generally make use of deferred-write techniques and
write-through techniques.
• Deferred-Write Techniques
- The transaction operations do not immediately update the physical database. Instead, only
the transaction log is updated.
- The database is physically updated only after the transaction reaches its commit point,
using information from the transaction log.

• Write-Through Techniques
- The database is immediately updated by transaction operations during the transaction’s
execution, even before the transaction reaches its commit point.

Sub: Database Management System – II (BCA-Sem-4) Unit-2 Page 10

By: Chirag SIR