Docstoc

concurrency

Document Sample
concurrency Powered By Docstoc
					                       Module 7

Database Concurrency
                            Objectives

At the end of this module, you will be able to:
     Identify factors that influence locking
     List the objects that locks can be obtained on
     Identify scope of different types of DB2 locks
     Identify factors affecting amount of locks that are used
     Identify factors affecting the type of locks obtained
     Identify the isolation levels that should be used for a given a
     situation




                                                                       7-2
           Understanding Data Consistency

DB2 Universal Database uses the following data consistency support
mechanisms:
    Transactions
    Locking
    Logging




                                                                 7-3
      Transactions and Transaction Boundaries

A transaction, (otherwise known as a unit of work) is a recoverable
sequence of one or more SQL operations grouped together as a
single unit, usually within an application process.
     The initiation and termination of a transaction defines the points
     of database consistency
     Either:
     • The effects of all SQL operations performed within a
        transaction are applied to the database (COMMIT)
     or
     • The effects of all SQL operations performed are completely
        "undone" and thrown away (ROLLBACK)

                                                                      7-4
  Effects of COMMIT and ROLLBACK Operations

A commit or rollback operation only affects changes that are made
within the transaction that ends the COMMIT or ROLLBACK
operation.
     As long as data changes remain uncommitted, other users and
     applications are usually unable to see them
     • There are exceptions which we will look at later
     They can be backed out with a ROLLBACK operation
Once data changes are committed, they become accessible to other
users and applications and can no longer be removed by a
ROLLBACK operation.



                                                                7-5
 Effects of an Unsuccessful Transaction
                           An Unsuccessful Transaction
START TRANSACTION
                                    SQL Operation
                                    SQL Operation
                                   ERROR Condition
                                      Commit




                  END TRANSACTION
Locks are acquired at      When an error condition      Locks are released when
   the start of, and      occurs, the DB2 Database      the error condition occrs;
throughout the life of   Manager removes all changes   no connections are allowed
   the transaction.        made by the transaction.    until consistency is restored.

                                       TIME

                                                                                        7-7
Effects of a Successful Transaction


                         A Successful Transaction

START TRANSACTION                                END TRANSACTION

                             SQL Operation
                             SQL Operation
                             SQL Operation
                               Commit

Locks are acquired at     When the COMMIT          Locks are released
   the start of, and     statement is executed   when the transaction is
throughout the life of   all changes are made      terminated (by the
   the transaction.            permanent.         COMMIT statement).

                                        TIME



                                                                           7-8
         Multiple Users Accessing a Database

When transactions are not isolated from each other in multi-user
environments, four types of events (or phenomenon) can occur:
     Lost updates
     Dirty reads
     Non-repeatable reads
     Phantoms
DB2 UDB uses the following isolation levels to enforce concurrency:
    Repeatable Read
    Read Stability
    Cursor Stability
    Uncommitted Read
                                                                   7-9
         The Repeatable Read Isolation Level

For Repeatable Read, all rows scanned by a single transaction are
locked for the duration of that transaction.
     Any SELECT statement that is issued more than once within the
     same transaction always yields the same results
     • Lost updates, dirty reads, non-repeatable reads, and
        phantoms cannot occur
Each row referenced by the isolating transaction is locked—not just
the rows that are actually retrieved and/or modified.




                                                                  7-11
            The Read Stability Isolation Level

For Read Stability, all rows that are retrieved by a single transaction
are locked for the duration of that transaction.
      When this isolation level is used, no row read by the isolating
      transaction can be changed by other transactions until the
      isolating transaction terminates
      SELECT statements that are issued more than once within the
      same transaction may not always yield the same results
      • Lost updates, dirty reads, and non-repeatable reads cannot
         occur
      • Phantoms, on the other hand, can and may be seen
Only the rows that are actually retrieved and/or modified by the
isolating transaction are locked.
                                                                      7-12
           The Cursor Stability Isolation Level

For Cursor Stability, each row referenced by a cursor that is being
used by the isolating transaction is locked as long as the cursor is
positioned on that row.
      The lock acquired remains in effect until either the cursor is
      repositioned or until the isolating transaction terminates
      SELECT statements that are issued more than once within the
      same transaction may not always yield the same results
      Lost updates and dirty reads cannot occur
      • Non-repeatable reads and phantoms, however, can and may
        be seen
By default, Cursor Stability is the isolation level used by most
transactions.
                                                                   7-13
        The Uncommitted Read Isolation Level

For Uncommitted Read, rows that are retrieved by a single
transaction are only locked for the duration of that transaction.
     Lost updates, dirty reads, non-repeatable reads, and phantoms
     can occur
     Such transactions can neither see nor access tables, views, or
     indexes that have been created by other transactions until they
     are committed
This isolation level is commonly used for transactions that access
read-only tables and/or transactions that execute SELECT
statements for which uncommitted data from other transactions have
no adverse affect.


                                                                  7-14
               Specifying the Isolation Level

Isolation levels are specified at the application level.
      For embedded SQL applications, the isolation level to be used
      is specified at precompile time or when the application is bound
      to a database
When no isolation level is specified, the Cursor Stability isolation
level is used by default.
Isolation level used by commands and scripts run from the
Command Line Processor can also be set by executing the
CHANGE ISOLATION command.




                                                                       7-15
         Choosing the Proper Isolation Level

When more restrictive isolation levels are used, less concurrency
support is provided and overall performance may be decreased
because more resources are required.
    Uncommitted Read: Read-only transactions needed—high data
    stability not required
    Cursor Stability: Read/write transactions needed—high data
    stability not required
    Read Stability: Read-only or read/write transactions needed—
    high data stability required
    Repeatable Read: Read-only transactions needed—extremely
    high data stability required


                                                               7-16
                      How Locking Works

A lock:
     Is a mechanism that is used to associate a data resource with a
     single transaction
     Controls how other transactions interact with that resource while
     it is associated with the owning transaction
     Is used to prohibit transactions from accessing uncommitted
     data written by other transactions
Once a lock is acquired, it is held until the owning transaction is
terminated.
     The lock is then released and the data resource is made
     available to other transactions

                                                                      7-18
                        Lock Attributes

All locks have the following basic attributes:
      Object — identifies the data resource that is being locked
      Size — specifies the physical size of the portion of the data
      resource that is being locked
      Duration — specifies the length of time a lock is held
      Mode — specifies the type of access allowed for the lock owner
      as well as the type of access permitted for concurrent users of
      the locked data resource




                                                                   7-19
               Lock States — Types of Locks

The lock state determines the type of access:
     Allowed for the lock owner
     Permitted for concurrent users of a locked data resource
States (mode) of locks:
     Intent None (IN)        Intent Share (IS)
     Next Key Share (NS)     Share (S)
     Intent Exclusive (IX)   Share With Intent Exclusive (SIX)
     Update (U)              Next Key Exclusive (NX)
     Exclusive (X)           Next Key Weak Exclusive (NW)
     Weak Exclusive (WE)     Super Exclusive (Z)




                                                                 7-20
                       Lock Compatibility

Locks are compatible if the state of one lock placed on a data
resource enables another lock to be placed on the same resource.
     Application A holds a lock on a table that application B also
     wants to access
     Application B requests a lock of some particular mode
If the mode of the lock held by A permits the lock requested by B, the
two locks (or modes) are said to be compatible.
If the lock mode requested for application B is not compatible with
the lock held by application A, application B cannot continue.
      It must wait until application A releases its lock, and all other
      existing incompatible locks are released

                                                                          7-23
                       Lock Conversion

Lock conversion:
     Is the operation of changing the state of a lock already held to a
     more restrictive state
     Occurs because a transaction can hold only one lock on a data
     resource at a time
     Is mostly performed for row-level locks
     Only occurs if a held lock can increase its restriction
Once a lock's state has been converted, the lock stays at the highest
state obtained until the transaction holding the lock is terminated.




                                                                     7-25
                         Lock Escalation

Lock escalation is performed automatically whenever too many locks
(of any type) have been acquired.
     Lock escalation is the conversion of several individual row-level
     locks within the same table to a single table-level lock
When a lock is requested and the lock storage space is full:
   One of the tables associated with the transaction is selected
   A table-level lock is acquired
   All row-level locks for that table are released
   The table-level lock is added to the lock list
If lock space is still unavailable, the transaction is asked to either
COMMIT or ROLLBACK and the transaction is terminated.

                                                                         7-26
                           Deadlocks

A deadlock can occur when there is contention for locks by two or
more transactions.




                          X            want X
                                  T1

                                  T2
                         want X         X
               Table A                          Table B




                                                                    7-27
                         Lock Timeouts

A transaction might wait indefinitely for a lock to be released.
     A lock timeout value can be specified to avoid stalling other
     applications
This value controls the amount of time any transaction waits to obtain
a requested lock.
If the desired lock is not acquired before the timeout elapses, the
waiting application receives an error and is rolled back.
By using lock timeouts, global deadlock situations can be avoided,
especially in distributed transaction application environments.




                                                                      7-28
                        How Locks are Acquired

                                       SQL OPERATION
                   Index Scan                                         Table Scan


         No                      Yes                        No                      Yes

              Update intended?                                   Update intended?


    Acquire                      Acquire               Acquire                    Acquire
Intent-Share(IS)           Intent-Exclusive(IX)       Share(S)                  Exclusive(X)
Table-level Lock             Table-level Lock      Table-level Lock           Table-level Lock



   Acquire                     Acquire                                  No
  Share(S)              Share(S) or Exclusive(X)                  Row-level Locks
Row-level Lock              Row-level Lock                           Acquired
 On All Rows                 On All Rows
 Referenced                   Referenced

                                                                                                 7-29
               Concurrency and Granularity
To optimize for maximum concurrency, row-level locks are usually better
than table-level locks because they limit access to a much smaller
resource.
      A single table-level lock requires less overhead than several
      individual row-level locks
      Row-level locks are acquired by default
Global approach:
     Use the ALTER TABLE ... LOCKSIZE TABLE statement to acquire
     table-level locks for a particular table
Individual transaction approach:
      Use the LOCK TABLE statement to allow table-level locks to be
      acquired at an individual transaction level

                                                                      7-31
                   Transaction Processing

For locking, all transactions typically fall under one of the following
categories:
     Read-Only — use Intent Share (IS) and/or Share (S) locks
     Intent-To-Change — use Update (U), Intent Exclusive (IX) and
     Exclusive (X) locks for tables; Share (S), Update (U), and
     Exclusive (X) locks for rows
     Change — use Intent Exclusive (IX) and/or Exclusive (X) locks
     Cursor-Controlled — use Intent Exclusive (IX) and/or Exclusive
     (X) locks




                                                                     7-32
                     Data Access Paths

The access plan:
    Specifies the operations required and the order those
    operations are to be performed to resolve an SQL request
    Can use either of two ways to access data in a table:
    • By directly reading the table
    • By reading an index on that table and then retrieving the row
       in the table referred by the index
The access path chosen by the DB2 Optimizer can have a significant
effect on the amount of locks acquired and the lock states used.




                                                                 7-33
                             Summary

You should now be able to:
     Identify factors that influence locking
     List the objects that locks can be obtained on
     Identify scope of different types of DB2 locks
     Identify factors affecting amount of locks that are used
     Identify factors affecting the type of locks obtained
     Identify the isolation levels that should be used for a given a
     situation




                                                                       7-34

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:8/26/2011
language:English
pages:27