Concurrency Control_3_

Document Sample
Concurrency Control_3_ Powered By Docstoc
					Concurrency Control

 Alexandra Cristea and Steve Russ




                                    1
          Who needs ‘control’?

• large databases are usually shared
  – by many users, and resources


• it is efficient to allow concurrent access.




                                                2
         Relevant Concepts for
          Concurrency Control
• integrity : consistency and correctness
• security : ensuring users only do what they
  are allowed
• recovery : return after an error to a known
  correct state

• ISSUE:
  Concurrent access endangers each of these !!!
                                                  3
               Level of treatment
• Large and complex topic
• Here: logical or conceptual level.
• closely following:
  – C.J. Date Introduction to Database Systems (8th ed.)
    Ch.16. The book by Date has an excellent annotated
    bibliography for each chapter.
  – similar treatments in Silberschatz et al., and Connolly &
    Begg.


                                                           4
        Sources for Learning
There are good chapters on each of the main
topics associated with concurrency control in
the Date book:
   Integrity (Chapter 9)
   Recovery (Chapter 15)
   Concurrency (Chapter 16)
   Security (Chapter 17)



                                                5
               More Sources

• There are similar chapters in the Silberschatz
  et al. recommended book (Ch.5, 15, 16, 17).
• There are some good sets of notes on the web
  on these topics – ‘Transaction processing’ or
  similar.
• Google ‘lost update problem database’ (in UK).

But: better to stick with one source in detail.
                                                  6
Integrity




            7
    Database Integrity

•   Domains
•   Constraints
•   Entity/referential integrity
•   Assertions




                                   8
      Four Types of Integrity Rule
• Domain Rules: Give legal values for domains.
• Attribute Rules: Give legal values for attributes.
• Relation Rules: Rules governing single relations (e.g.,
  Primary keys must be unique and Non-NULL)
• Database Rules: Rules governing interrelationships
  between relations (e.g., Foreign Keys must be
  Primary keys - and therefore Non-NULL).

• SQL provides ways of defining these Rules or
  Constraints.
                                                            9
         Domains and Constraints
• Defining constrains outside the CREATE TABLE data
  definition:
CREATE DOMAIN domain_name [AS] data_type
       [DEFAULT default_option]
       [CHECK (search_condition)]

CREATE DOMAIN sex_type AS CHAR
    CHECK (VALUE IN (‘M’, ‘F’))

Names of types are reusable.
                                                10
                      Entity Integrity
• Entity integrity enforced by naming PRIMARY KEY (i.e., attribute or
  set of attributes which should be NOT NULL and UNIQUE). E.g.:
CREATE TABLE staff(
    staff# number (3),
    name char(30),
    PRIMARY KEY staff#);
• For alternate keys can use NOT NULL and UNIQUE qualifiers after
  column attribute name. E.g.:
CREATE TABLE fruit(
  name char(30) PRIMARY KEY,
  supplier char(20) NOT NULL,
  country char(20) NOT NULL,
  UNIQUE (supplier, country));                                      11
             Referential Integrity

• defined by FOREIGN KEY or REFERENCES.
• Referential action must allow cascading of updates from
  parent to child table (+deletes, NULL value , DEFAULT).
  E.g.:

CREATE TABLE emps(
  emp# number(2),
  name char(30),
  dept# number(2) REFERENCES dept
             ON UPDATE CASCADE);

                                                      12
                         Assertions

CREATE ASSERTION assertion_name
   CHECK (search condition)

CREATE ASSERTION daycheck
     CHECK ( (day>0) and (day<=
                     (select DM.day from
      daysinmonth DM where DM.month=month))

Named rules like this are database/ enterprise wide.


                                                       13
          Oracle SQL Limitations
• Note that Oracle 10g 2 does not implement all
  ISO SQL Integrity Enhancement Features (IEF).
  – Domains cannot be defined outside CREATE TABLE.
  – Referential action restricted to ON DELETE CASCADE
    or NO ACTION.
  – Table check constraints can only refer to attributes
    within a table.
  – Named assertions cannot be defined.


                                                      14
• Note: Integrity is not Security.

• Integrity ensures that the things the users are
  trying to do are correct.
• Security ensures that things users are doing
  are only what they are allowed to do.



                                                15
Security




           16
          Security vs. Integrity
• Integrity: Ensuring what users are trying to do
  is correct.
• Security: Ensuring users are allowed to do
  things they are trying to do.
• Both require rules that users must not violate.




                                                17
   Database Security Approaches

1. Discretionary Control: Named users,
   Privileges or access rights to data objects.
   Distributed control.

2. Mandatory Control: Users have Clearance,
   Objects have classification levels. Central
   control.

                                                  18
         Security Mechanisms

• Security sub-systems which checks IDs against
  security rules.




                                             19
          SQL Syntax for Security Rules
GRANT [privilege-commalist | ALL PRIVILEGES]
ON object-name
TO [authorisation_id_list | PUBLIC]
[WITH GRANT OPTION]
Each privilege is one of the following:
SELECT
DELETE
INSERT [ (attribute-commalist)]
UPDATE [ (attribute-commalist) ]
REFERENCES [ (attribute-commalist) ]

The REFERENCES allows privileges to be granted on named table(s) in
integrity constraints of CREATE TABLE.
The GRANT OPTION allows the named users to pass the privileges on
to other users.
                                                                      20
                       Audit Trails
We can’t assume that security will be perfect, i.e. someone
might gain unauthorised access.
Audit Trails area logs which can track down the infiltrators.
Audit trails will contain entries of the form:
      request (source text)
      location (physical e.g. terminal id)
      user id
      date/ time
      relations affected (base, tuple, attribute)
      old-values
      new-values
Knowing that there is an audit trail may deter security hacks.

                                                                 21
                      Grant and Revoke
If a user A grants privileges to user B, then they can also revoke them e.g.

REVOKE ALL PRIVILEGES ON STATS FROM John;

SQL REVOKE syntax
REVOKE [GRANT OPTION FOR]
[privilege_list | ALL PRIVILEGES]
ON object_name
FROM [authorisation_list|PUBLIC] [RESTRICT|CASCADE]

If RESTRICT option is given then the command is not executed if any
dependent rules exist i.e. those created by other users through the
WITH GRANT OPTION.
CASCADE will force a REVOKE on any dependent rules.



                                                                               22
               Security Summary
• A DBMS security-subsystem enforces security
• Access is checked against security rules
• Discretionary control rules have a users, privileges and
  objects
• Mandatory controls have clearance and classification
  levels
• Audit trails are used to record attempted security
  breaches
• GRANT/ REVOKE syntax in SQL
• We have not dealt with data-encryption, which deals
  with the storing and transmission of sensitive data.
                                                         23
Recovery




           24
                  Recovery
• Restoring a database to a known correct state
  after some failure.

• Database recovery is based on redundancy at
  the physical level.
  – Any piece of information can be recovered from
    some other stored information, somewhere else.



                                                     25
                Transactions
• A transaction is a logical unit of work, as well
  as unit of recovery.
• It is broken down into a sequence of atomic
  operations, which if any fail, the whole
  transaction is undone.
            SELECT | INSERT | …
                   …
                   … work …
                   …
            COMMIT | ROLLBACK                        26
                 Transactions
• series of database commands w clear semantics
  – e.g. transfer of funds from one account to another
• Commit:
  If nothing fails
  commit point where the DB should be consistent.
  All updates are tentative until committed.
• Rollback:
  If any command fails => whole series is undone.

• Any DBMS support these (and lang. e.g. SQL)            27
          Transaction Example
BEGIN TRANSACTION
UPDATE ACC123 {BALANCE := BALANCE - £100}
IF any error occurred THEN GO TO UNDO; END IF;
UPDATE ACC456 {BALANCE := BALANCE + £100}
IF any error occurred THEN GO TO UNDO; END IF;
COMMIT; GO TO FINISH; /*successful end*/
UNDO:
  ROLLBACK;               /*unsuccessful end*/
FINISH:
  RETURN;                                    28
           Transaction Properties
• Atomicity:
 all or nothing
(any error => Rollback, as if nothing happened)
• Consistency:
a consistent state always leads to another consistent
state
• Isolation:
a transaction’s updates are hidden until it Commits
• Durability:
after a Commit, updates persist

These are the ACID properties of transactions.     29
             System Recovery
• How does the system recover after a system
  failure (e.g., power failure) or media failure
  (e.g., disk crash)?
• In the event of a crash …
  – Contents of main memory are lost.
  – Transaction log persists.
  – At failure, certain transactions will be complete
    while others part complete.
• Note that updates are held in memory buffers
  and written out periodically.
                                                        30
                        Recovery
  To recover the state of the database we can use:
• A log file recording every database operation.
• Checkpoints recording the state of all active
  transactions.
  – Then: develop an algorithm for transactions to UNDO,
  – and those that we need to REDO, to effect recovery.
  – at intervals, the system will:
     • Flush its buffers
     • Write out a checkpoint record to log indicating which
       transactions are in progress.


                                                               31
                    Five Transaction categories
               Time               tc                 tf
               T1
Transactions




               T2
               T3
               T4
               T5
                               Checkpoint        System failure
                               (time tc)         (time tf)

         • The most recent check point record was taken at
           time tc.                                         32
Completed             Un-Finished

Cached      Written


T2          T1         T3
T4                     T5



                                    33
• CHECKPOINT RECORD:
  – T3, T5 : undone (rollback possible)
  – T2, T4 : re-done


• DBMS creates REDO/UNDO list from checkpoint
  record + system log.

• isolation =>
  – order of recovery not crucial,
  – only DB should be consistent near tf.
                                            34
Concurrency




              35
                Concurrency
• Many transactions - at the same time.
• Databases shared!

• So: Transactions must be isolated => need of
  concurrency control to ensure no interference.

• We will look at:
  – 3 classic problems on concurrent access
  – Locking mechanism
  – Deadlock resolution                       36
         Three classic problems
PB: two (more) transactions read / write on the
  same part of the db.
  Although transactions execute correctly, results
  may interleave in diff ways =>
          3 classic problems.

• Lost Update
• Uncommitted Dependency
• Inconsistent Analysis
                                                 37
            Lost Update problem
 Time       User 1 (Trans A)        User2 (Trans B)
   1          Retrieve t
   2                                  Retrieve t
   3            Update t
   4                                   Update t
   5
   6
   7

t : tuple in a table. Trans A loses an update at t4.
The update at t3 is lost (overwritten) at t4 by B.     38
          Uncommitted Dependency
    Time       User 1 (Trans A)         User 2 (Trans B)
      1                                    Update t
      2           Retrieve t
      3                                    Rollback
      4
      5
      6                                    Update t
      7            Update t
      8                                    Rollback
2 PBs (T1-3 ; T6-8). One trans is allowed to retrieve/update) a
tuple updated by another, but not yet committed.
Trans A is dependent at time t2 on an uncommitted change
                                                           39
made by Trans B, which is lost on Rollback.
                 Inconsistent Analysis
          Initially:     Acc 1 = 40; Acc2 = 50; Acc3 = 30;
              Time       User 1 (Trans A)    User 2 (Trans B)
            1            Retrieve Acc 1 :
                            Sum = 40
Trans A sees 2           Retrieve Acc2 :
inconsistent                Sum = 90
DB state
after B      3                                Retrieve Acc3 :
updated      4                                 Update Acc3:
Accumulator                                      30 → 20
             5                                Retrieve Acc1:
=> performs
             6                                 Update Acc1:
inconsistent
                                                 40 → 50
analysis.
             7                                   commit
            8            Retrieve Acc3:
                       Sum = 110 (not 120)                      40
         Why these problems?
• Retrieve : ‘read’ (R)
• Update : ‘write’ (W).
• interleaving two transactions => 3 PBS:
      RR – no problem
      WW – lost update
      WR – uncommitted dependency
      RW – inconsistent analysis


                                            41
  How to prevent such problems?
• locking protocol
  – Other approaches : serializability, time-stamping,
    and shadow-paging. See books.
• IF risk of interference = low =>
       two-phase locking ~ common approach
  – although it requires deadlock avoidance!!
• Lock applies to a tuple :
   – exclusive (write; X) or
   – shared(read; S).
                                                         42
             Lost Update ‘solved’
Time          User 1 (Trans A)                 User2 (Trans B)
 1      Retrieve t (get S-lock on t)
 2                                       Retrieve t (get S-lock on t)
 3     Update t (request X-lock on t)
 4                 wait                 Update t (request X-lock on t)
 5                 wait                             wait
 6                 wait                             wait
 7


          No update lost but => deadlock

                                                                 43
  Uncommitted Dependency solved
Time          User 1 (Trans A)                User 2 (Trans B)
 1                                        Update t (get X-lock on t)
 2     Retrieve t (request S-lock on t)               -
 3                   wait                             -
 4                  wait                              -
 5                  wait                      Commit / Rollback
                                            (releases X-lock on t)
 6           Resume: Retrieve t
              (get S-lock on t)
 7                    -
 8
                                                                 44
       Inconsistent Analysis ‘solved’
Time          User 1 (Trans A)                User 2 (Trans B)

 1       Retrieve Acc1 : (get S-lock)
                  Sum = 40
 2       Retrieve Acc2 : (get S-lock)
                  Sum = 90
 3                                       Retrieve Acc3: (get S-lock)

 4                                       Update Acc3: (get X-lock)
                                                  30 → 20
 5                                       Retrieve Acc1: (get S-lock)
 6                                      Update Acc1: (request X-lock)
                                                   wait
 7             Retrieve Acc3:                       wait
              (request S-lock)                      wait
                    wait                            wait         45
                   Deadlock

• Deadlock occurs when 2 or more transaction
  are in a simultaneous wait state.

• It is desirable to conceal deadlocks from the
  user.

                                              46
             Deadlock Resolution
• The system must detect and break deadlocks by:

1. Choosing one trans as a victim and rolling it back.
2. Timing out the trans and returning an error.
3. automatically restarting the transaction hoping
   not to get deadlock again.
4. Return an error code back to the victim and
   leaving it up to program to handle situation.
.
                                                   47
               Topics covered:
• Integrity
  – Domains, Constraints, Entity/referential integrity,
    assertions
• Security
  – Discretionary, mandatory, audit trails
• Recovery
  – Transactions, ACID properties
• Concurrency Control
  – Lost update, uncommitted dependency,
    inconsistent analysis, deadlock                       48
Questions?




             49

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