Overview of Transaction Management

Document Sample
Overview of Transaction Management Powered By Docstoc
					       Transaction Management Overview

                                        Chapter 18




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   1
        Objects, transections

        v   Database `objects' are the units in which
            programs read or write information
             § Pages, records …
        v   A transaction is seen by the DBMS as a series,
            or list of actions. The actions that can be
            executed by a transaction include reads and
            writes of database objects



Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   2
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   3
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   4
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   5
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   6
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   7
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   8
        Transactions
v   Concurrent execution of user programs is essential for
    good DBMS performance.
    §   Because disk accesses are frequent, and relatively slow, it is
        important to keep the cpu humming by working on several
        user programs concurrently.
v A user’s program may carry out many operations on
  the data retrieved from the database, but the DBMS is
  only concerned about what data is read/written
  from/to the database.
v A transaction is the DBMS’s abstract view of a user
  program: a sequence of reads and writes.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke           9
        Concurrency in a DBMS
v   Users submit transactions, and can think of each
    transaction as executing by itself.
    §   Concurrency is achieved by the DBMS, which interleaves
        actions (reads/writes of DB objects) of various transactions.
    §   Each transaction must leave the database in a consistent
        state if the DB is consistent when the transaction begins.
         • DBMS will enforce some ICs, depending on the ICs
           declared in CREATE TABLE statements.
         • Beyond this, the DBMS does not really understand the
           semantics of the data. (e.g., it does not understand how
           the interest on a bank account is computed).
v   Issues: Effect of interleaving transactions, and crashes.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke      10
        ACID four important properties of
        transactions
        v   Users should be able to regard the execution of
            each transaction as Atomic
        v   must preserve the consistency of the database
        v   even if the DBMS interleaves the actions of
            several transactions for performance reason
            without considering the effect of other
            concurrently executing transactions: isolation:
        v   Once the DBMS informs the user that a
            transaction has been successfully completed, its
            effects should persist even if the system crashes
            before all its changes are reflected on disk.
            durability.


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   11
        Consistency and Isolation
        v   Users are responsible for ensuring transaction
            consistency
             § Transection will leave DB in Consistent state
                • 100 depit acount A
                • 99 credit acount B, 1 difference ins users logic
                  problem
        v   isolation ensured by guaranteeing that even
            though actions of several transactions might be
            interleaved, the net effect is identical to
            executing all transactions one after the other in
            some serial order.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       12
        Atomicity of Transactions
        v   A transeciton can be incomplete for
             § Being ABORTED, or terminated due to some kind
               of anomaly DBMS
                • İf terminated by DBMS it is restarted
             § System crash
             § Unexpected stuation (access some disk )
        v    a transaction that is interrupted in the middle
            may leave the database in an inconsistent
            state .
             § either all of a transaction's actions are carried out,
               or none are.DMBS undo actions from logs
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke          13
        Atomicity of Transactions
v A transaction might commit after completing all its
  actions, or it could abort (or be aborted by the DBMS)
  after executing some actions.
v A very important property guaranteed by the DBMS
  for all transactions is that they are atomic. That is, a
  user can think of a Xact as always executing all its
  actions in one step, or not executing any actions at all.
    §   DBMS logs all actions so that it can undo the actions of
        aborted transactions.



Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke     14
        v   http://www.vbdotnetheaven.com/blogs/4995/transaction-processing-concept-
            in-ado-net


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                         15
        Example
v   Consider two transactions (Xacts):
        T1:       BEGIN A=A+100, B=B-100 END
        T2:       BEGIN A=1.06*A, B=1.06*B END

v Intuitively, the first transaction is transferring $100
  from B’s account to A’s account. The second is
  crediting both accounts with a 6% interest payment.
v There is no guarantee that T1 will execute before T2
  or vice-versa, if both are submitted together.
  However, the net effect must be equivalent to these
  two transactions running serially in some order.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   16
        Example (Contd.)
v   Consider a possible interleaving (schedule):
        T1:        A=A+100,                           B=B-100
        T2:                          A=1.06*A,                   B=1.06*B

v   This is OK. But what about:
        T1:        A=A+100,                                      B=B-100
        T2:                          A=1.06*A, B=1.06*B

v   The DBMS’s view of the second schedule:
        T1:        R(A), W(A),                                      R(B), W(B)
        T2:                            R(A), W(A), R(B), W(B)

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                   17
        Scheduling Transactions
        v a schedule represents an actual or potential
          execution sequence.
        v DBMS interleaves the actions of different
          transactions to improve performance




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   18
        Scheduling Transactions
        v Ensuring transaction isolation while
          permitting such concurrent execution is
          difficult, but is necessary for performance
          reasons
        v I/O activity can be done in parallel with
          CPU activity in a computer.
        v Over-lapping I/O and CPU activity,
             § Reduce
                • i/o , cpu idle time
             § Increase
                • system throughput

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   19
        Scheduling Transactions
v Serial schedule: Schedule that does not interleave the
  actions of different transactions.
v Equivalent schedules: For any database state, the effect
  (on the set of objects in the database) of executing the
  first schedule is identical to the effect of executing the
  second schedule.
v Serializable schedule: A schedule that is equivalent to
  some serial execution of the transactions.
(Note: If each transaction preserves consistency, every
  serializable schedule preserves consistency. )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   20
        Anomalies with Interleaved
        Execution
        v   Two actions on the same data object conflict if
            at least one of them is a write
             § Write-Read WR conflict T1 àT2 «dirty read»
             § Read-Write RW conflict
             § Write-Write WW conflict




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   21
    Anomalies with Interleaved Execution

        v   Reading Uncommitted Data (WR Conflicts,
            “dirty reads”):
    T1:       R(A), W(A),                                  R(B), W(B), Abort
    T2:                            R(A), W(A), C

        v   Unrepeatable Reads (RW Conflicts):
    T1:       R(A),                             R(A), W(A), C
    T2:                  R(A), W(A), C


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                 22
        Anomalies (Continued)

        v   Overwriting Uncommitted Data (WW
            Conflicts):
    T1:       W(A),                              W(B), C
    T2:                  W(A), W(B), C
        v   if transaction does not reads object before
            writing it such a write is called a blind write




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   23
        Schedules Involving Aborted
        Transactions
        v   Intuitively, all actions of aborted transactions
            are to be undone.
             § if T2 had not been committed,
               cascading abort of T1 and T2,
               also releated transection
             § But T2 is committed, thus it is
               Unrecoverable Schedule



             § recoverable schedule is one in which transactions
               commit only after (and if !) all transactions whose
               changes they read commit.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       24
        avoid cascading aborts schedule

        v   If transactions read only the changes of
            committed transactions, not only is the
            schedule recoverable, but also aborting a
            transaction can be accomplished without
            cascading the abort to other transactions.
            Such a schedule is said to avoid cascading
            aborts




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   25
        Aborting a Transaction
v If a transaction Ti is aborted, all its actions have to be
  undone. Not only that, if Tj reads an object last
  written by Ti, Tj must be aborted as well!
v Most systems avoid such cascading aborts by releasing
  a transaction’s locks only at commit time.
    §   If Ti writes an object, Tj can read this only after Ti commits.
v   In order to undo the actions of an aborted transaction,
    the DBMS maintains a log in which every write is
    recorded. This mechanism is also used to recover
    from system crashes: all active Xacts at the time of the
    crash are aborted when the system comes back up.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        26
    Lock-Based Concurrency Control
v   Strict Two-phase Locking (Strict 2PL) Protocol:
     §   Each Xact must obtain a S (shared) lock on object before
         reading, and an X (exclusive) lock on object before writing.
     §   All locks held by a transaction are released when the
         transaction completes
           • (Non-strict) 2PL Variant: Release locks anytime, but
             cannot acquire locks after releasing any lock.
     §    If an Xact holds an X lock on an object, no other Xact can
         get a lock (S or X) on that object.
v   Strict 2PL allows only serializable schedules.
     § Additionally, it simplifies transaction aborts
     § (Non-strict) 2PL also allows only serializable schedules,
       but involves more complex abort processing
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke          27
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   28
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   29
        Deadlock
        v   Consider the following example:
             § transaction T1 gets an exclusive lock on object A,
               T2 gets an exclusive lock on B, T1 requests an
               exclusive lock on B and is queued, and T2 requests
               an exclusive lock on A and is queued. Now, T1 is
               waiting for T2 to release its lock and T2 is waiting
               for T1 to release its lock! Such a cycle of
               transactions waiting for locks to be released is
               called a deadlock




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        30
        Deadlock Prevention
        v giving each transaction a priority and
          ensuring that lower priority transactions are
          not allowed to wait for higher priority
          transactions (or vice versa).
        v Timestamp priority.
             § Ti requests a lock and transaction Tj holds a
               conflicting lock
                • Wait –die
                        • lower priority transactions can never wait for higher
                          priority transactions.
                  • Wound-wait
                        • higher priority transactions never wait for lower priority
                          transactions
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                         31
        Deadlock Detection
        v Deadlocks tend to be rare and typically
          involve very few transactions
        v The lock manager maintains a structure
          called a waits-for graph to detect deadlock
          cycle
             § The nodes correspond to active transactions, and
               there is an arc from Ti to Tj if (and only if) Ti is
               waiting for Tj to release a lock. The lock manager
               adds edges to this graph when it queues lock
               requests and removes edges when it grants lock
               requests

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        32
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   33
         CRASH RECOVERY

        v   recovery manager of a DBMS is responsible
            for ensuring transaction atomicity and
            durability
             § atomicity by undoing the actions of transactions
               that do not commit
             § durability by making sure that all actions of
               committed transactions survive system crashes
               and media failures



Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke    34
        after crashes

        v    recovery manager is given control
             § responsible
                • for bringing the database to a consistent state
                • for undoing the actions of an aborted
                  transaction.




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke      35
        The Log
v   The following actions are recorded in the log:
    §   Ti writes an object: the old value and the new value.
         • Log record must go to disk before the changed page!
    §   Ti commits/aborts: a log record indicating this action.
v Log records are chained together by Xact id, so it’s
  easy to undo a specific Xact.
v Log is often duplexed and archived on stable storage.
v All log related activities (and in fact, all CC related
  activities such as lock/unlock, dealing with deadlocks
  etc.) are handled transparently by the DBMS.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke    36
        Recovering From a Crash
v   There are 3 phases in the Aries recovery algorithm:
    §   Analysis: Scan the log forward (from the most recent
        checkpoint) to identify all Xacts that were active, and all dirty
        pages in the buffer pool at the time of the crash.
    §   Redo: Redoes all updates to dirty pages in the buffer pool,
        as needed, to ensure that all logged updates are in fact
        carried out and written to disk.
    §   Undo: The writes of all Xacts that were active at the crash
        are undone (by restoring the before value of the update,
        which is in the log record for the update), working
        backwards in the log. (Some care must be taken to handle
        the case of a crash occurring during the recovery process!)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke         37
        Summary
v Concurrency control and recovery are among the
  most important functions provided by a DBMS.
v Users need not worry about concurrency.
    §   System automatically inserts lock/unlock requests and
        schedules actions of different Xacts in such a way as to
        ensure that the resulting execution is equivalent to
        executing the Xacts one after the other in some order.
v   Write-ahead logging (WAL) is used to undo the
    actions of aborted transactions and to restore the
    system to a consistent state after a crash.
    §   Consistent state: Only the effects of commited Xacts seen.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke       38
        Sql server Transection isolation
        v   Dirty Reads occur when one transaction reads data
            written by another, uncommitted, transaction. The danger
            with dirty reads is that the other transaction might never
            commit, leaving the original transaction with "dirty" data.
        v   Non-repeatable Reads occur when one transaction
            attempts to access the same data twice and a second
            transaction modifies the data between the first
            transaction's read attempts. This may cause the first
            transaction to read two different values for the same data,
            causing the original read to be non-repeatable
        v   Phantom Reads occur when one transaction accesses a
            range of data more than once and a second transaction
            inserts or deletes rows that fall within that range between
            the first transaction's read attempts. This can cause
            "phantom" rows to appear or disappear from the first
            transaction's perspective.


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke            39
        v   SQL Server's isolation models each attempt to
            conquer a subset of these problems, providing
            database administrators with a way to balance
            transaction isolation and business requirements.
            The five SQL Server isolation models are:
        v   The Read Committed Isolation Model is SQL
            Server’s default behavior. In this model, the
            database does not allow transactions to read data
            written to a table by an uncommitted transaction.
            This model protects against dirty reads, but
            provides no protection against phantom reads or
            non-repeatable reads.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   40
        v   The Read Uncommitted Isolation Model offers
            essentially no isolation between transactions. Any
            transaction can read data written by an uncommitted
            transaction. This leaves the transactions vulnerable to
            dirty reads, phantom reads and non-repeatable reads.
        v   The Repeatable Read Isolation Model goes a step
            further than the Read Committed model by
            preventing transactions from writing data that was
            read by another transaction until the reading
            transaction completes. This isolation model protect
            against both dirty reads and non-repeatable reads.


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke        41
        v   The Serializable Isolation Model uses range locks to
            prevent transactions from inserting or deleting rows
            in a range being read by another transaction. The
            Serializable model protects against all three
            concurrency problems.
        v   The Snapshot Isolation Model also protects against
            all three concurrency problems, but does so in a
            different manner. It provides each transaction with a
            "snapshot" of the data it requests. The transaction
            may then access that snapshot for all future
            references, eliminating the need to return to the
            source table for potentially dirty data.


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke      42
        v   If you need to change the isolation model in use
            by SQL Server, simply issue the command:
        v   SET TRANSACTION ISOLATION LEVEL
            <level>
        v   where <level> is replaced with any of the
            following keywords:
             §   READ COMMITTED
             §   READ UNCOMMITTED
             §   REPEATABLE READ
             §   SERIALIZABLE
             §   SNAPSHOT


Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   43
         BEGIN TRAN

         UPDATE    authors
         SET au_fname = 'John'
         WHERE     au_id = '172-32-1176'

         UPDATE    authors
         SET au_fname = 'Marg'
         WHERE     au_id = '213-46-8915'

         COMMIT TRAN

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   44
        BEGIN TRAN

        UPDATE    authors
        SET au_fname = 'John'
        WHERE     au_id = '172-32-1176'

        UPDATE    authors
        SET au_fname = 'JohnY'
        WHERE     city = 'Lawrence'

        IF @@ROWCOUNT = 5
         COMMIT TRAN
        ELSE
         ROLLBACK TRAN
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   45
Create Proc TranTest1
AS
BEGIN TRAN

INSERT INTO [authors]([au_id],
      [au_lname],
      [au_fname],
      [phone],
      [contract])
VALUES         ('172-32-1176',
      'Gates',
      'Bill',
      ' 800-BUY-MSFT',
      1)

UPDATE        authors
SET  au_fname = 'Johnzzz'
WHEREau_id = '172-32-1176'

COMMIT TRAN
GO
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   46
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   47
USE pubs
DECLARE @intErrorCode INT

BEGIN TRAN
  UPDATE Authors
  SET Phone = '415 354-9866'
  WHERE au_id = '724-80-9391'

  SELECT @intErrorCode = @@ERROR
  IF (@intErrorCode <> 0) GOTO PROBLEM

  UPDATE Publishers
  SET city = 'Calcutta', country = 'India'
  WHERE pub_id = '9999'

 SELECT @intErrorCode = @@ERROR
 IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
  ROLLBACK TRAN
END
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   48
        Nested trasnection
                                                 Figure 1: A COMMIT always
                                                 balances a BEGIN
                                                 TRANSACTION by reducing the
                                                 transaction count by one.



                                                 Figure 2: A single ROLLBACK
                                                 always rolls back the entire
                                                 transaction




Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                  49
USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0
BEGIN TRAN
  SELECT 'After BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 1
  DELETE sales
  BEGIN TRAN nested
    SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
         -- The value of @@TRANCOUNT is 2
    DELETE titleauthor
  COMMIT TRAN nested
         -- Does nothing except decrement the value of @@TRANCOUNT

 SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
       -- The value of @@TRANCOUNT is 1
ROLLBACK TRAN

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is
0
-- because ROLLBACK TRAN always rolls back all transactions and sets
-- @@TRANCOUNT to 0.

SELECT TOP 5 au_id FROM titleauthor
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke          50
USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0
BEGIN TRAN
  SELECT 'After BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 1
  DELETE sales
  BEGIN TRAN nested
    SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
       -- The value of @@TRANCOUNT is 2
    DELETE titleauthor
  ROLLBACK TRAN

  SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
  -- The value of @@TRANCOUNT is 0 because
  -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT
  -- to 0.

IF (@@TRANCOUNT > 0) BEGIN
  COMMIT TRAN -- Never makes it here cause of the ROLLBACK
  SELECT 'After COMMIT TRAN', @@TRANCOUNT
END

SELECT TOP 5 au_id FROM titleauthor
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke               51
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   52
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
           @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
  INSERT titles(title_id, title, type)
  VALUES (@title_id, @title, @title_type)

  IF (@@ERROR <> 0) BEGIN
    PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
                                                    IF @@ERROR <> 0
    RETURN 1                                            BEGIN
  END
                                                           RAISERROR('error occured
  INSERT titleauthor(au_id, title_id)
  VALUES (@au_id, @title_id)                        while recording purchase', 16, 1)
  IF (@@ERROR <> 0) BEGIN
                                                           ROLLBACK
    PRINT 'Unexpected error occurred!'                  END
    ROLLBACK TRAN
    RETURN 1
  END

COMMIT TRAN

RETURN 0


 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke                    53
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   54
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   55
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke   56

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:2/7/2014
language:English
pages:56