Database Systems: Design, Implementation, and Management

1
Database Systems Design, Implementation, and
Management

• CHAPTER 9
• Transaction Management and
• Concurrency Control

2
Chapter Overview

• What is a transaction
• Concurrency control
• Locks
• Two-Phase Locking protocol

3
What Is a Transaction?

• A transaction is a logical unit of work that must
  be either entirely completed or aborted no
  intermediate states are acceptable.
• 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.
• A transaction that changes the contents of the
  database must alter the database from one
  consistent database state to another.

4
What Is a Transaction?
Figure 9.1 Example of a Transaction
5
What Is a Transaction?

• Evaluating Transaction Results
• Examining the current balance for an account
• SELECT ACC_NUM, ACC_BALANCEFROM CHECKACCWHERE
  ACC_NUM 0908110638
• The database remains in a consistent state after
  the transaction.

6
What Is a Transaction?

• Evaluating Transaction Results
• An accountant wishes to register the credit sale
  of 100 units of product X to customer Y in the
  amount of 500.00
• Reducing product Xs Quantity on hand by 100.
• Adding 500.00 to customer Ys accounts
  receivable.
• UPDATE PRODUCTSET PROD_QOH PROD_QOH -
  100WHERE PROD_CODE X
• UPDATE ACCRECSET AR_BALANCE AR_BALANCE
  500WHERE AR_NUM Y
• If the above two requests are not completely
  executed, the transaction yields an inconsistent
  database.

7
What Is a Transaction?

• Transaction Properties
• Atomicity requires that all operations of a
  transaction be completed if not, the transaction
  is aborted.
• Durability indicates the permanence of the
  databases consistent state.
• Serializability describes the result of the
  concurrent execution of several transactions.
  This property is important in multi-user and
  distributed databases.
• 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. This is also relevant in a multi-user
  database.
• What about single-user systems with respect to
  each of these?

8
What Is a Transaction?

• Transaction Management with SQL
• Transaction support is provided by two SQL
  statements COMMIT and ROLLBACK.
• When a transaction sequence is initiated, it must
  continue through all succeeding SQL statements
  until one of the following four events occurs
• A COMMIT statement is reached.
• A ROLLBACK statement is reached.
• The end of a program is successfully reached
  (COMMIT).
• The program is abnormally terminated (ROLLBACK).

9
What Is a Transaction?

• Transaction Management with SQL
• Example
• UPDATE PRODUCTSET PROD_QOH PROD_QOH -
  100WHERE PROD_CODE 345TYX
• UPDATE ACCRECSET AR_BALANCE AR_BALANCE
  3500WHERE AR_NUM 60120010
• COMMIT

10
What Is a Transaction?

• The Transaction Log
• A transaction log keeps track of all transactions
  that update the database.
• The transaction log stores before-and-after data
  about the database and any of the tables, rows,
  and attribute values that participated in the
  transaction.
• The information stored in the log is used by the
  DBMS for a recovery requirement triggered by a
  ROLLBACK statement or a system failure.
• Roll back (for uncommitted) and Roll forward (for
  committed but not written to disk).
• It is desirable to store the log file and the
  database files on separate disks to reduce the
  probability of simultaneous loss of both data.

11
What Is a Transaction?
Table 9.1 Transaction Log
12
Concurrency Control

• Concurrency control coordinates simultaneous
  execution of transactions in a multiprocessing
  database.
• The objective of concurrency control is to ensure
  the serializability of transactions in a
  multi-user database environment.
• Simultaneous execution of transactions over a
  shared database can create data integrity and
  consistency problems

13
Concurrency Control

• Example
• Two concurrent transactions update PROD_QOH
• Transaction Computation
• T1 Purchase 100 units ? PROD_QOH PROD_QOH
  100
• T2 Sell 30 units ? PROD_QOH PROD_QOH - 30
• See Table 9.2 for the serial execution under
  normal circumstances.
• See Table 9.3 for the lost update problems
  resulting from the execution of the second
  transaction before the first transaction is
  committed. Which property is violated?

14
Lost Updates, Uncommitted Data, Inconsistent
Retrieval
Lost Updates
Table 9.2
Table 9.3
15
Lost Updates, Uncommitted Data, Inconsistent
Retrieval
Uncommitted Data

• T1 reads the data and modifies it and writes.
• T2 reads the data not yet committed and modifies
  it
• T1 rollsback
• T2 writes

Table 9.4
Table 9.5
16
Lost Updates, Uncommitted Data, Inconsistent
Retrieval

• Inconsistent retrieval
• Occurs when a transaction calculates some
  summary(aggregate) functions over a set of data
  while other transactions are updating it.

Table 9.6
Table 9.7
17
Inconsistent Retrievals (cont.)
Table 9.8
18
Concurrency Control

• The Scheduler
• The scheduler establishes the order in which the
  operations within concurrent transactions are
  executed.
• The scheduler sequences the execution of database
  operations to ensure serializability.
• To determine the appropriate order, the scheduler
  bases its actions on concurrency control
  algorithms.

When two transactions access the same data and at
least one involves a write operation, then there
is potential for conflict.
19
LOCKS

• Concurrency Control with Locking Methods
• A lock guarantees exclusive use of a data item to
  a current transaction.
• All lock information is managed by a lock
  manager.
• Lock granularity indicates the level of lock use.
• Database level
• Table level
• Page level
• Row level
• Field level

20
LOCKS

• Database level Lock
• Entire database is locked by a transaction
• Good for batch processing but inefficient for
  on-line processing
• When transaction T1 is accessing table A,
  transaction T2 has to wait for the lock to be
  released even if wants to access table B.
• Table level Lock
• Entire table is locked by a transaction
• If a transaction requires access to several
  tables, each table may be locked
• Less restrictive compared to database locks
• Can cause unnecessary delays when two
  transactions need to access different parts of
  the same table
• Not suitable for multi-user DBMS

21
Database-Level Locking Sequence
Figure 9.2
22
Table-Level Lock Example
Figure 9.3
23
LOCKS

• Page Level Lock
• A diskpage is locked by a transaction (4K or 8K
  or 16K)
• A table may span many pages
• A page contains many rows
• This is most popular level of locking in
  multiuser databases
• Row Level Lock
• A row is locked by a transaction
• Multiple transactions can access different rows
  in the same table concurrently
• Less restrictive than page level locking
• High overhead in lock management
• Field Level Lock
• Different transactions can lock different fields
  in the same row concurrently
• Most flexible for mult-user data access
• Requires very high overhead in managing locks

24
Page-Level Lock Example
Figure 9.4
25
Row-Level Lock Example
Figure 9.5
26
LOCKS

• Binary Locks
• A binary lock has only two states locked (1) or
  unlocked (0).
• If an object 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.
• A transaction must unlock the object after its
  termination.
• Every transaction requires a lock and unlock
  operation for each data item that is accessed.

27
Example of Binary Lock Table
Table 9.10
28
LOCKS

• Exclusive Locks
• An exclusive lock exists when access is specially
  reserved for the transaction that locked the
  object.
• The exclusive lock must be used when the
  potential for conflict exists.
• An exclusive lock is issued when a transaction
  wants to write a data item and no locks are
  currently held on that data item.
• Shared Locks
• A shared lock exists when concurrent transactions
  are granted READ access on the basis of a common
  lock.
• A shared lock produces no conflict as long as the
  concurrent transactions are read only.
• A shared lock is issued when a transaction wants
  to read data from the database and no exclusive
  lock is held on that data item.

29
Two-Phase Locking

• The two-phase locking protocol defines how
  transactions acquire and relinquish locks.
• In a growing phase, a transaction acquires all
  the required locks without unlocking any data.
• Once all locks have been acquired, the
  transaction is in its locked point. All
  operations are executed at this time.
• In a shrinking phase, a transaction releases all
  locks and cannot obtain any new locks.
• This protocol guarantees serializability, but it
  does not prevent deadlocks.
• 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.

30
Two-Phase Locking
Figure 9.6 Two-Phase Locking Protocol
31
Two-Phase Locking

• Deadlocks (Deadly Embrace)
• Deadlocks exist when two transactions T1 and T2
  exist in the following mode
• T1 access data items X and Y
• T2 access data items Y and X
• If T1 has not unlocked data item Y, T2 cannot
  begin and, if T2 has not unlocked data item X,
  T1 cannot continue. (See Table 9.11)
• Several strategies have been developed to control
  deadlock situations.

32
Two-Phase Locking
Table 9.11 How a Deadlock Condition Is Created
33
Two-Phase Locking

• Deadlock Prevention
• A transaction requesting for a new lock is
  aborted if there is a possibility of deadlock.
  Rollback and release acquired locks.
• Deadlock Detection
• DBMS periodically tests for existence of
  deadlock. If found kills one of the transactions.
  Rollback and release acquired locks.
• Deadlock Avoidance
• Must obtain all locks before it starts execution.
  It increases the response time.
• So, What is the best method?