Transaction Management and Concurrency Control(1) by pptfiles


									Transaction Management and Concurrency Control              Summer 2006

   Transaction Management and Concurrency Control

1. Transaction Management
1.1 Transaction Concept:
 A transaction is a logical unit of work that contains one or more SQL
  statements (unit of program execution) that accesses and possibly
  updates various data items.
 The effects of all the SQL statements in a transaction can be either all
  committed (applied to the database) or all rolled back (undone from the
 A user application is a set of transactions. Examples:
   ‘Create a New Employee’,
   ‘Enter a New Customer Order’,
   ‘Dispatch Goods to a Customer’.
 A transaction must see a consistent database.
 During transaction execution the database may be inconsistent.
 When the transaction is committed, the database must be consistent.
 Successful transaction changes the database from one consistent state
  to another
 Consistent state is one in which all data integrity constraints are
 Two main issues to deal with:
  a. Failures: may occur due to :
           System crashes (hardware and software errors losing memory)
           Media failure (loss of parts of secondary storage)
           Application program failure (transaction failure)
           Natural physical disasters, carelessness and sabotage.
     Database recovery facilities:
        a DBMS should provide the following facilities to assist with
          back-up mechanism: allow generating periodic backup copies of
            the database and log file without having to stop the system;
          logging facilities: keeping track of the current state of
            transactions and database changes
          check point facility: checkpoint is a point of synchronization
            between the database and the transaction log file. At a

Transaction Management and Concurrency Control                Summer 2006

           checkpoint All buffers (log records, database blocks and a
           checkpoint record) are written to secondary storage;
          Recover manager: allow the system to restore the database to
           a consistent state following a failure.

   b. Concurrent execution of multiple transactions – Next Subject

1.2 Transaction Management with SQL:
 Transaction support is provided by two SQL statements: COMMIT and
 A transaction begins with the first executable SQL statement.
 A transaction ends when it is committed or rolled back, either:
        Explicitly with a COMMIT or ROLLBACK statement or
        Implicitly when a DDL statement is issued.

Example 1: Consider a banking database. When a bank customer transfers
money from a savings account to a checking account, the transaction might
consist of three separate operations (Actions):

             Begin Transaction                      Remarks

             End Transaction

Example 2:

        Begin_transaction Reservation
           input(flight_no, date, customer_name);
                       INTO temp1,temp2
                       FROM FLIGHT
                       WHERE FNO = flight_no AND DATE = date;

Transaction Management and Concurrency Control                   Summer 2006

            if temp1 = temp2 then output(``no free seats''); Abort
                        SET STSOLD = STSOLD + 1
                        WHERE FNO = flight_no AND DATE = date;
            EXEC SQL INSERT
                       INTO FC(FNO, DATE, CNAME, SPECIAL);
                       VALUES (flight_no, date, customer_name, null);
           output(``reservation completed'')
           endif end . {Reservation}

Example 3: Consider the following transaction that reserves a seat on an
airplane flight and charges the customer:
       1. Read customer information
       2. Write reservation information
       3. Write charges
    Suppose that after the second step, the database crashes. Or for some
   reason, changes can not be written...
 Transactions can either reach a commit point, where all actions are
   permanently saved in the database or they can abort in which case none
   of the actions are saved.
 Another way to say this is transactions are Atomic. All operations in a
   transaction must be executed as a single unit - Logical Unit of Work.

Example 4:Airline Transaction Example:
             Display greeting
             Get reservation preferences from user
             SELECT departure and return flight records
             If reservation is acceptable then
                    UPDATE seats remaining of departure flight record
                    UPDATE seats remaining of return flight record
                    INSERT reservation record
                    Print ticket if requested
             End If
             On Error: ROLLBACK

1.3 Transaction Types:
a. According to Application area:

Transaction Management and Concurrency Control                  Summer 2006

     - local transaction, data is not distributed;
     - distributed transaction, distributed database,;
b. According to Duration
    Interactive, On-line transactions, E.g. Banking or airline reservation).
    Non-interactive, batch transactions, response time measured in
     minutes, hrs or even days).
c. According to Transaction Structure:
    Flat (single) transaction: A single start point (Begin_transaction) and a
     single termination point (END_transaction).
    Nested transaction: a transaction that include other transactions
     within their own begin and commit points. The embedded transactions
     are called sub-transactions.
    Example of Nested Transaction:             A travel agent who makes
     reservation for hotels, car rentals as well as flights can have all these
     as one transaction. The reservation transaction will thus look like this:
                  Begin_transaction Reservation
                     Begin_transaction Airline
                     end. {Airline}
                     Begin_transaction Hotel
                     end. {Hotel}
                     Begin_transaction Car
                     end. {Car}
1.4 Transaction Properties (ACID):
   To preserve integrity of data, the database Management system
must ensure ACID properties
 Atomicity:
    Requires that either all or none operations are completed (All-or
    Atomicity requires that if a transaction is interrupted by a failure, its
      partial results must be undone.
    The activity of ensuring atomicity in the presence of system crashes
      is called crash recovery.
 Consistency: Ensures the correctness of a database.

Transaction Management and Concurrency Control                Summer 2006

    Transactions must transform the database from one Consistent state
      to another consistent state.
 Isolation : Data used during execution of a transaction cannot be used
   by second transaction until the first one is completed
 Durability:
    Once a transaction commits, its results are permanently stored in the
    Once a transaction commits, the system must guarantee that the
      results of its operations will never be lost, in spite of subsequent
      failures. (Database recovery)
Example of transaction - Fund Transfer:
Transaction to transfer $50 from account A to account B:
       1. read(A)                         4. read(B)
       2.A := A – 50                      5. B := B + 50
       3.write(A)                         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.
    Durability requirement: once the user has been notified that the
      transaction has completed, the updates to the database by the
      transaction must persist despite of future failures.
    Isolation requirement: between steps 1 to 6, another transactions
      should not be allowed to access Accounts A and B.

1.5 The Transaction Log:
 A log or journal that stores information about all updates to the database
   (insert, delete and update SQL statement):
    Transaction identifier
    A record for the beginning of transaction.
    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
    The ending (COMMIT) of the transaction

Transaction Management and Concurrency Control                             Summer 2006

Example of Transaction Log:
       Tr    Prev      Next                                                Before   After
Log                             Operation       Table   Row    Attribute
      Num   Pointer   Pointer                                               value   value
115   210   null      126       Start
126   210   115       130       Update      Product     P504   QOH         40       22
130   210   126       175       Update      Customer    C310   cBalance    100      55
147   210   130       160       Commit

1.6 Transaction State
  The transaction life cycle includes the following states:
 Active: the initial state; the transaction stays in this state while it is
 Partially committed: occurs after the final statement has been executed.
 Failed: after the discovery that normal execution can no longer proceed.
 Aborted: after the transaction has been rolled back and the database
            restored to its state prior to the start of the transaction. Two
            options after it has been aborted: either Restart the
            transaction or kill it.
 Committed: after successful completion.

Transaction Management and Concurrency Control                 Summer 2006

                        Concurrency Control

2. Introduction to Concurrency Control
   Concurrency Control is the process of managing simultaneous
operations on the database without having them interfere with one
2.1 Basic Concepts:
- Concurrency control deals with coordination of simultaneous transaction
  execution in a multiprocessing database system
- Concurrent transactions refer to two or more transactions that appear
  to users as they are being processed against a database at the same time
- In reality, CPU can execute only one instruction at a time
- Transactions are interleaved meaning that the operating system quickly
  switches CPU services among tasks so that some portion of each of them
  is carried out in a given interval
 Objective is to ensure transaction serializability in a multi-user database
 The Scheduler is special DBMS program: establishes order of operations
  within which concurrent transactions are executed:
 Interleaves the execution of database operations to ensure serializability
  and isolation of transactions
 Bases its actions on concurrency control algorithms
 Facilitates data isolation to ensure that two transactions do not update
  the same data element at the same time
 Importance: simultaneous execution of transactions over a shared
  database can create several data integrity and consistency problems.
2.2. Consistency Problems
2.2.1. Lost Updates:
 A successfully completed update by one transaction can be overridden by
   another transaction:
 Example(1):

Transaction Management and Concurrency Control   Summer 2006

Example (2):

Example (3):

Example (4):

Transaction Management and Concurrency Control          Summer 2006

2.2.2 An Uncommitted Data Problem:
    Occurs when one transaction is allowed to read the intermediate
      results of another transaction before it has committed.
Example (1):

Example (2):
           T1                         T2
           --                         begin
           --                         read x (=100)
           --                         x = x + 100
           begin                      write x (=200)
           read x (=200)               --
           x = x - 10                  --
           write x (=190)              --
           commit                      --
            --                        rollback

2.2.3 Inconsistent Retrieval: Retrieval during Update:
    Occurs when a transaction reads several values from the database
      and at the same time a second transaction updates some of them.
Example (1):

Transaction Results: inconsistent retrieval:

Transaction Management and Concurrency Control                    Summer 2006

2.3. Concurrency Control with Locking Methods

   Resource locking prevents multiple applications from obtaining copies of
    the same record when the record is about to be changed
   Lock: Required to prevent another transaction from reading inconsistent
   Locking is a procedure used to control concurrent access to database
    objects. When one transaction is accessing any database object, a lock
    prevents other transactions to access it.
   A lock is a logical flag set by a transaction to alert other transactions the
    data item is in use.
   Locks may be applied to data items in two ways:
        Implicit Locks are locks placed by the DBMS
        Explicit Locks are issued by the application program.
   Lock Types
     Shared lock
        If a transaction has a shared lock on a data item, it can read that
           item but not update it
        allows another transaction to read an item but prevents writing to
           the item
     Exclusive lock
        If a transaction has an exclusive lock on a data item, it can both
           read and update the item

Transaction Management and Concurrency Control                Summer 2006

        Access is specifically reserved for the transaction that locked the
        prevents any other transaction from reading or modifying the
          locked item
        Must be used when the potential for conflict exists
   Lock manager: Responsible for assigning and policing the locks used by
    the transactions

2.3.1 Lock Granularity:
  Lock granularity refers to size of a locked resource: Rows, page, table,
   and database level
 Large granularity is easy to manage but frequently causes conflicts
 Locking can take place at the following levels:
    Database-level lock - Entire database is locked
    Table-level lock - Entire table is locked
    Page-level lock - Entire disk page is locked (memory segment) (many
      rows worth)
    Row-level lock - Allows concurrent transactions to access different
      rows of the same table, even if the rows are located on the same page
    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
An Example of A Database-Level Locking Sequence

Transaction Management and Concurrency Control   Summer 2006

An Example of a Table-Level Lock

Example of a Page-Level Lock

Transaction Management and Concurrency Control                 Summer 2006

An Example of a Row-Level Lock

3.3 Two-Phase Locking Protocol (2PL):
   Objective - to Ensure Serializability
   Serializable transactions refer to two transactions that run concurrently
    and generate results that are consistent with the results that would have
    occurred if they had run separately
   2PL Defines how transactions acquire and relinquish locks

Transaction Management and Concurrency Control               Summer 2006

   2PL protocol consists of 2 phases:
     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
   Governed by the following rules:
     Two transactions cannot have conflicting locks
     No unlock operation can precede a lock operation in the same
     No data are affected until all locks are obtained—that is, until the
       transaction is in its locked point

 Declaring Lock Characteristics:
- Most application programs do not explicitly declare locks due to its
- Instead, they mark transaction boundaries and declare locking behavior
  they want the DBMS to use
- Transaction boundary markers: BEGIN, COMMIT, and ROLLBACK
- Advantage
- If the locking behavior needs to be changed, only the lock declaration
  need be changed, not the application program


Transaction Management and Concurrency Control                Summer 2006

      Deadlock, occurs when two transactions are each waiting a resource
       that the other transaction holds
      Possible only if one of the transactions wants to obtain an exclusive
       lock on the same data item
      Preventing deadlock: protocols ensure that the system will never
       enter into a deadlock state. Some prevention strategies:
        Require that each transaction locks all its data items before it
          begins execution.
        Impose partial ordering of all data items and require that a
          transaction can lock data items only in the order specified by the
          partial order.
      Breaking deadlock
       – Almost every DBMS has algorithms for detecting deadlock
       – When deadlock occurs, DBMS aborts one of the transactions and
          rollbacks partially completed work

Example of a deadlock:
                             T 3            T 4
                    Lock -X(B )
                    Read (B )
                    B :=B -50
                    Write (B )
                                       Lock -S (A)
                                       Read (A)
                                       Lock -S (B )
                    Lock -X(A )

2.4. Concurrency Control with Time Stamping Methods

    Timestamping is a concurrency control protocol that orders
transactions in such a way that older transactions, transactions with
smaller timestamps, get priority in the event of conflict.
    DBMS Assigns a global unique time stamp to each transaction
    Produces an explicit order in which transactions are submitted to the
    The two main characteristics of a timestamp are:
    Uniqueness: Ensures that no equal time stamp values can exist
    Monotonicity: Ensures that time stamp values always increase

Transaction Management and Concurrency Control                      Summer 2006

            Timestamp can be generated by:
                a. Polling the system clock
                b. Use an incremental logical counter every time when a new
                   transaction starts – (preferred method)
            Timestamps applied:
                 On Transactions
                 On Data Items:
                 Both work together to ensure a cohesive system.
            Timestamps on Data Items:
                 Read_timestamp: the timestamp of the last transaction to read
                   the item.
                 Write_timestamp: the timestamp of the last transaction to
                   write/update the item.

2.5          Concurrency Control with Optimistic Methods
           Optimistic algorithm Based on the assumption that the majority of
            database operations do not conflict
           Does not require locking or time stamping techniques
           All transactions are allowed to execute simultaneously without
            restrictions until it is committed
           They write to a local copy of data
           Transaction processed in three phases: read, validation, and write
           DBMS processes a transaction; checks whether conflict occurred
            a. If not, the transaction is finished
            b. If so, the transaction is repeated until there is no conflict
           Optimistic locking is preferred for the Internet and for many intranet

2.5.1 Phases of Optimistic Methods
       The read phase: transaction reads the database, executes the needed
        processing, and makes the updates to a private copy of the database
       The validation phase: the transaction is validated to ensure that the
        changes made will not affect the integrity and consistency of the
            The validation phase checks whether these updates would maintain
               the consistency.
            If validation is passed, the transaction proceeds to the next phase,

Transaction Management and Concurrency Control                                      Summer 2006

         Otherwise the transaction is aborted and restarted.
     The write phase: the changes are permanently applied to the database.

1. Suppose that you are a manufacturer of a product ABC, which is composed
   of parts A, B, and C. Each time a new product ABC is created, it must be
   added to the product inventory, using the PROD_QOH in a table named
   PRODUCT. And each time the product is created, the parts inventory,
   using PART_QOH in a table name PART, must be reduced by one each of
   parts A, B, and C. The sample database contents are shown in Table P9.1
Table name: PRODUCT                                       Table name: PART
PROD_CODE        PROD_QOH                                  PART_CODE               PART_QOH
ABC              1,205                                     A                       567
                                                           B                       498
                                                           C                       549
Given this information, answer questions a-e.
    UPDATE PART                                               Updates the quantity on hand of the part
        SET PART_QOH = PART_QOH – 1                           A
            WHERE PART_CODE = 'A'

      UPDATE PART                                             Updates the quantity on hand of the part
      SET PART_QOH = PART_QOH – 1                             B
               WHERE PART_CODE = 'B'

      UPDATE PART                                             Updates the quantity on hand of the part
         SET PART_QOH = PART_QOH – 1                          C
                WHERE PART_CODE = 'C

   UPDATE PRODUCT                                             Updates the quantity on hand of the
     SET PROD_QOH = PROD_QOH + 1 WHERE                        product ABC


a.    Write the transaction log, using Table 9.1 as your template.
TID     Table          Tuple ID        Attribute                    Before            After
101     Start Transaction
101     PART           A               PART_QOH                     567               566
101     PART           B               PART_QOH                     498               497
101     PART           C               PART_QOH                     549               548
101     PRODUCT        ABC             PROD_QOH                     1,205             1206
101     End Transaction: COMMIT

          Transaction ID automatically assigned by the DBMS


To top