FP304

Database System
Chapter 5
DATABASE TRANSACTION MANAGEMENT
(Manage Database)
 Transaction properties: ACID.
 Perform a transaction using SQL statements.
 Use START TRANSACTION, COMMIT, ROLLBACK
statement.
Course Learning Outcome
1. Develop a database using a concurrency control and
data recovery technique to manage the database
system. (CLO2)
Introduction
 Database transactions reflect real-world transaction that are
triggered by events such as buying a product, registering for
a course or making a deposit in bank account.
 Transactions are likely to contain many parts. For example, a
sales transaction may require updating the customer’s
account, adjusting the product inventory and so on.
 All parts of a transaction must be successfully completed to
prevent data integrity problems.
 Therefore executing and managing transactions are
important to database system activities.
 What is a Transaction?
 Any action that reads from and/or writes to a
database may consist of:
Simple SELECT statement to generate a list of table
contents
A series of related UPDATE statements to change the
values of attributes in various tables
A series of INSERT statements to add rows to one or
more tables
A combination of SELECT, UPDATE, and INSERT
statements
 What is a Transaction?
 A transaction is a logical unit of work that must be either
entirely completed or aborted.
 Successful transaction changes the database from one consistent state to
another.
 One in which all data integrity constraints are satisfied
 Most real-world database transactions are formed by two or more
database requests
 The equivalent of a single SQL statement in an application program or
transaction
Evaluating Transaction Results
 Not all transactions update the database.
 SQL code represents a transaction because database was
accessed.
 Improper or incomplete transactions can have a devastating
effect on database integrity
 Some DBMSs provide means by which user can define enforceable
constraints based on business rules
 Other integrity rules are enforced automatically by the DBMS when
table structures are properly defined, thereby letting the DBMS
validate some transactions
The Relational Schema
for SaleCo Database
Transaction Processing
 For example, a transaction may involve:
 The creation of a new invoice
 Insertion of an row in the LINE table
 Decreasing the quantity on hand by 1
 Updating the customer balance
 Creating a new account transaction row
 If the system fails between the first and last step, the database
will no longer be in a consistent state.

Figure 9.2
Transaction Properties
 DBMSs ensure that transactions obey certain properties. The
most important and widely known properties are the ACID
properties (atomic, consistent, isolated, and durable)
as discussed next.
Transaction Properties (cont)
 ATOMIC means that a transaction cannot be subdivided.
Either all the work in the transaction is completed or nothing
is done.
 For example, the transfer of funds between two accounts is a
transaction. If we transfer $20 from account A to account B,
then at the end of the transaction A’s balance will be $20
lower and B’s balance will be $20 higher (if the transaction is
completed) or neither balance will have changed (if the
transaction is aborted).
Transaction Properties (cont)
 CONSISTENT - when the transaction starts the entities are
in a consistent state, and when the transaction ends the
entities are once again in a consistent, albeit different, state.
 The implication is that the referential integrity rules and
applicable business rules still apply after the transaction is
completed.
 For example, if a user's account is balanced before a
transaction, then the account is balanced after the
transaction. Otherwise, the transaction is rejected and no
changes take effect.
Transaction Properties (cont)
 ISOLATED means that transactions do not interfere with
each other except in allowable ways. A transaction should
never overwrite changes made by another transaction. In
addition, a transaction may be restricted from interfering in
other ways such as not viewing the temporary changes made
by other transactions.
 For example, if a transaction T1 is being executed and is
using data item X, that data item cannot be accessed by any
other transaction (T2…Tn) until T1 ends.
Transaction Properties (cont)
 DURABLE means that any changes resulting from a
transaction are permanent. No failure will erase any changes
after a transaction terminates.
 For example, if a bank's computer experiences a failure five
minutes after a transaction completes, the results of the
transaction are still recorded on the bank's database.
Example of Fund Transfer
 Transaction to transfer RM50 from account A to account B:
1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)
 Consistency requirement – the sum of A and B is unchanged by
the execution of the transaction.
 Atomicity requirement — if the transaction fails after step 3
and before step 6, the system should ensure that its updates are
not reflected in the database, else an inconsistency will result.
Example of Fund Transfer (cont)
 Durability requirement — once the user has been
notified that the transaction has completed (i.e., the transfer
of the RM50 has taken place), the updates to the database by
the transaction must persist despite failures.
 Isolation requirement — if between steps 3 and 6,
another transaction is allowed to access the partially updated
database, it will see an inconsistent database (the sum A + B
will be less than it should be).
Transaction Management with SQL
 ANSI has defined standards that govern SQL database
transactions.
 Transaction support is provided by two SQL statements:
COMMIT (saves changes to disk) and ROLLBACK
(restores the previous database state).
 ANSI standards require that, when a transaction sequence is
initiated by a user or an application program, it must
continue through all succeeding SQL statements until one of
four events occurs:
– COMMIT statement is reached
– ROLLBACK statement is reached
– End of program is reached
– Program is abnormally terminated
 Concurrency control objective.
 Interference problems.
 Locks and 2-phase locking.
Course Learning Outcome
1. Develop a database using a concurrency control and
data recovery technique to manage the database
system. (CLO2)
Concurrency Control
 The coordination of the simultaneous execution of
transactions in a multiprocessing database is known as
concurrency control.

 The objective of concurrency control is to ensure the

serializability of transactions in a multiuser database
environment.
Concurrency Control
 Important  simultaneous execution of transactions over a shared
database can create several data integrity and consistency
problems.

Interference Problems
 Three problems that can result because of simultaneous access to
a database:
(1) lost update,
(2) uncommitted dependency / uncommitted data
(3) inconsistent retrieval.
Normal execution of two transactions
Interference Problems : Lost Update
 Lost update is the most serious interference problem because
changes to a database are inadvertently lost.
 In a lost update, one user's update overwrites another user's
update.
Interference Problems: Uncommitted
Dependency
 An uncommitted dependency occurs when one transaction reads
data written by another transaction before the other transaction
commits.
 An uncommitted dependency is also known as a dirty read
because it is caused by one transaction reading dirty
(uncommitted) data.
 Violates the isolation property of transaction.
Correct Execution of Two Transactions
An Uncommitted Dependency Problem
 Inconsistent Retrieval Problem

 Occur when a transaction calculates some aggregate

functions over a set of data while transactions are
updating the data
 Some data may be read after they are changed and some
before they are changed yielding inconsistent results
Retrieval During Update
Transaction Results: Data Entry Correction
Inconsistent Retrievals
Concurrency Control Tools
 In order to prevent the three interference problems, most
DBMS use these two tools:
(1) Lock
(2) Two-phase locking protocol
 Lock
 A lock guarantees exclusive use of data item to a current
transaction.
 The lock prevents one transaction from using the data item
while another transaction is using it.
 Locking is necessary in a concurrent environment to assure
that one process does not retrieve or update a record that is
being updated by another process.
 Failure to use some controls (locking), would result in
inconsistent and corrupt data.
 Example of Lock
Time Anna’s transaction Dean’s transaction
1 Lock account balance
2 Read account balance (balance =
RM1000)
3 Request account balance
(access is denied)
4 Withdraw RM600 (Balance = RM400)
5 Write account balance (balance =
RM400)
6 Unlock account balance
7 Lock account balance
8 Read account balance (Balance =
RM400)
9 Withdraw RM100 (Balance = RM300)
10 Write account balance (Balance =
RM300)
11 Unlock account balance
Lock Granularity
 Locking level:
 Database
 entire database is locked
 used during database updates
 Not suitable for online, multiuser applications
 Table –
 used for bulk updates
 The entire table containing requested record is locked.
 May be used when entire table or when most of the records need to be
updated such as when you want to reduce the price of all items
 Record
 only requested row is locked. All other records available to other user.
High overhead as locks must be applied to each row.
 Field
 system allows concurrent access to the same row as long as they require
the use of different fields in the same row. May cause high overhead
 A Database-Level Locking Sequence

 Good for batch processing but unsuitable for online multi-user DBMSs
 T1 and T2 can not access the same database concurrently even if they use
different tables
 Table-Level Lock

 T1 and T2 can access the same database concurrently as long as they use different
tables
 Can cause bottlenecks when many transactions are trying to access the same table
(even if the transactions want to access different parts of the table and would not
interfere with each other)
 Not suitable for multi-user DBMSs
 Page-Level Lock

 An entire disk page is locked (a table can span several pages and each
page can contain several rows of one or more tables)
 Most frequently used multi-user DBMS locking method
 Row-Level Lock

 Concurrent transactions can access different rows of the same table even if
the rows are located on the same page
 Improves data availability but with high overhead (each row has a lock that
must be read and written to)
 Field-Level Lock
 Allows concurrent transactions to access the
same row as long as they require the use of
different fields with that row
 Most flexible lock buy requires an extremely
high level of overhead
Lock Type
• Regardless of the level of locking, the DBMS may use
different lock types: binary or shared/exclusive
locks.
 Binary Locks
 Has only two states: locked (1) or unlocked (0)
 Eliminates “Lost Update” problem – the lock is not released until the write
statement is completed
 Can not use PROD_QOH until it has been properly updated
 Considered too restrictive to yield optimal concurrency conditions as it locks even
for two READs when no update is being done
 Shared/Exclusive Locks
 Exclusive lock
 Access is specifically reserved for the transaction that locked the object
 Must be used when the potential for conflict exists – when a transaction wants to
update a data item and no locks are currently held on that data item by another
transaction
 Granted if and only if no other locks are held on the data item
 Shared lock
 Concurrent transactions are granted Read access on the basis of a common lock
 Issued when a transaction wants to read data and no exclusive lock is held on that data
item
 Multiple transactions can each have a shared lock on the same data item if they are
all just reading it
 Mutual Exclusive Rule
 Only one transaction at a time can own an exclusive lock in the same object
 Shared/Exclusive Locks
 Increased overhead
 The type of lock held must be known before a lock can be granted
 Three lock operations exist:
 READ_LOCK to check the type of lock
 WRITE_LOCK to issue the lock
 UNLOCK to release the lock
 A lock can be upgraded from share to exclusive and downgraded from exclusive
to share
 Two possible major problems may occur
 The resulting transaction schedule may not be serializable
 The schedule may create deadlocks
Two-Phase Locking to Ensure Serializability
 Defines how transactions acquire and relinquish locks
 Guarantees serializability, but it does not prevent deadlocks
 Growing phase, in which a transaction acquires all the required
locks without unlocking any data
 Shrinking phase, in which a transaction releases all locks and
cannot obtain any new lock
Two-Phase Locking to Ensure Serializability
 Governed by the following rules:

 Two transactions cannot have conflicting locks

 No unlock operation can precede 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
Two-Phase Locking Protocol

• In this example, the transaction

acquires all the locks it needs until it
reaches its locked point. (in this
example, the transaction requires two
locks.)
• When the locked point is reached,
the data are modified to conform to
the transaction requirements.
• Finally, the transaction is completed
as it releases all of the locks it
acquired in the first phase.
Lock: Deadlock
 Using locks to prevent interference problems can lead to
deadlock problem.
 A deadlock is a problem of mutual waiting. One
transaction has a resource that a second transaction needs,
and the second transaction holds a resource that the first
transaction needs.
 Transaction A acquires an
exclusive lock on the first leg (say
from Denver to Chicago) followed
by transaction B acquiring a lock on
the second leg (say from Chicago to
New York).
Transaction A tries to lock the
second leg but is blocked because
 This example depicts a deadlock
transaction B holds an exclusive
among two transactions trying to
lock. Likewise, transaction B must
reserve seats on a flight involving more
wait to obtain a lock on the first
than one leg.
leg.
Deadlocks can involve more than
two transactions, but the pattern is
more complex.
Lock: Deadlock
 To control deadlocks, most enterprise DBMSs perform
deadlock detection.
 Deadlocks can be detected by looking for cyclic patterns of
mutual waiting.
 In practice, most deadlocks involve two or three transactions.
Because deadlock detection can involve significant
computation time, deadlock detection is only performed at
periodic intervals or triggered by waiting transactions.
 In previous example, deadlock detection could be performed
when transaction B is forced to wait. When a deadlock is
detected, the most recent transaction (transaction B) is
usually forced to restart.
Lock: Deadlock
 Most desktop DBMSs use a simpler time-out policy to
control deadlocks.
 In a time-out policy, the concurrency control manager aborts
(with a ROLLBACK statement) any transaction waiting for
more than a specified time.
 Note that a time-out policy may abort transactions that are
not deadlocked. The time-out interval should be set large
enough so that few non deadlocked transactions will wait that
long.
 Recovery tools.
Course Learning Outcome
1. Develop a database using a concurrency control and
data recovery technique to manage the database
system. (CLO2)
Recovery Tools
- These tools are used to restore a database to a consistent state
after a failure:
(1) Database backup
(2) Transaction log
(3) Checkpoint
Database Backup
 A backup is a copy of all or part of a disk. The backup is
used when the disk containing the database or log is
damaged.
 A backup is usually made on magnetic tape because it is less
expensive and more reliable than disk.
 Periodically, a backup should be made for both the database
and the log. To save time, most backup schedules include less
frequent massive backups to copy the entire contents of a
disk and more frequent incremental backups to copy only the
changed part.
Database Backup
 Types of backup:
 Periodic backup (e.g. nightly, weekly)
 Cold backup – database is shut down during backup
 Hot backup – selected portion is shut down and
backed up at a given time
The Transaction Log
 Keeps track of all transactions that update the database. It
contains:
 A record for the beginning of transaction
 For each transaction component (SQL statement)
 Type of operation being performed (update, delete, insert)
 Names of objects affected by the transaction (the name of the table)
 “Before” and “after” values for updated fields
 Pointers to previous and next transaction log entries for the same
transaction
 The ending (COMMIT) of the transaction
 Increases processing overhead but the ability to restore a corrupted
database is worth the price
The Transaction Log
 If a system failure occurs, the DBMS will examine the log for all
uncommitted or incomplete transactions and it will restore the
database to a previous state
 The log it itself a database and to maintain its integrity many
DBMSs will implement it on several different disks to reduce the
risk of system failure
A Transaction Log
Checkpoint Facilities
• A facility by which the DBMS periodically refuses to accept new
transactions. The system is in a quiet state and the database and
transaction logs are synchronized
• All transactions in progress are completed and journal files are
brought up-to-date
• DBMS writes a special record (checkpoint record) to the log file:
snapshot of the state of the database
• Checkpoint record contains information necessary to restart the
system
• Any dirty data blocks (pages of memory that contain changes that
have not yet been written out to disk) are written from memory to
disk storage
• Automatically or response to commands in user application
programs
End Of Lecture