Transaction Management and Concurrency Control

1
Chapter 9

• Transaction Management and Concurrency Control

2
In this chapter, you will learn

• What a database transaction is and what its
  properties are
• How database transactions are managed
• What concurrency control is and what role it
  plays in maintaining the databases integrity
• What locking methods are and how they work
• How database recovery management is used to
  maintain database integrity

3
What is a Transaction?

• Any action that reads from and/or writes to a
  database, may consist of
• Simple SELECT statement to generate a list of
  table contents
• A series of related UPDATE statements to change
  the values of attributes in various tables
• A series of INSERT statements to add rows to one
  or more tables
• A combination of SELECT, UPDATE, and INSERT
  statements

4
What is a Transaction? (continued)

• A logical unit of work that must be either
  entirely completed or aborted (multicomponent
  transaction must be fully completed)
• Successful transaction changes the database from
  one consistent state to another
• consistent state ? state in which data integrity
  constraints are satisfied

5
The Relational Schema for the Ch09_SaleCo Database
6
Transaction Sample

• Sell a product to customer
• Write new invoice (ADD/INSERT INVOICE and
  INVOICE_LINE)
• Reduce quantity on hand (UPDATE PRODUCT)
• Update account receivable (UPDATE
  CUS_ACCT_TRANSACTION)
• Update customer balance (UPDATE CUSTOMER)
• COMMIT? To end successful transaction
• NOTE if not successful inconsistent state,
  DBMS transaction management will roll back
  database to previous consistent state

7
Evaluating Transaction Results

• Not all transactions update the database
• Improper or incomplete transactions can have a
  devastating effect on database integrity
• Some DBMSs allow user to define enforceable
  constraints based on business rules
• Other integrity rules are enforced automatically
  by the DBMS when table structures are properly
  defined (referential integrity) thereby letting
  the DBMS validate some transactions
• DBMS cannot check semantic meaning of real-world
  eventEG)) QOH QOH 10NOTE cannot check
  update to wrong product id due to user wrong
  input of product id

8
Transaction Properties

• Atomicity
• Requires that all operations (SQL requests) of a
  transaction be completed or abortedtransaction
  is viewed as a single unit
• Durability
• Indicates permanence of databases consistent
  state (once a transaction is completed, db
  reaches a consistent state, that state cannot be
  lost even in event of systems failure)

9
Transaction Properties (continued)

• Serializability
• Ensures that the concurrent execution of several
  transactions yields consistent results (that is
  concurrent execution of T1, T2, T3 give results
  that appear to be executed in serial
  order)IMPORTANT in multiuser and distributed
  database
• Isolation
• Data used during execution of a transaction
  cannot be used by second transaction until first
  one is completed
• NOTE single-user db automatically ensure
  serializability and isolation(database
  consistency and integrity)

10
Transaction Management with SQL

• Transaction support is provided by two SQL
  statements COMMIT and ROLLBACK
• when a transaction sequence is initiated by a
  user or an application program, it must continue
  through all succeeding SQL statements until one
  of four events occurs
• COMMIT reached at end of SQL transaction, all
  changes permanently recorded
• ROLLBACK reached, when changes are aborted and
  roll back to previous consistent state
• End of program, all changes permanently recorded
• Program abnormally terminated, changes are
  aborted and roll back to previous consistent
  state

11
Transaction Management with SQL

• EG when a customer buy 2 units of product
  1558-QW1 priced at 40 per unit
• UPDATE PRODUCT
• SET PROD_QOH PROD_QOH 2
• WHERE PROD_CODE 1558-QW1
• UPDATE CUSTOMER
• SET CUST_BALANCE CUST_BALANCE 80
• WHERE CUST_CODE 10011
• COMMIT
• NOTE Some DBMS has a syntax to indicate start of
  transactionEG BEGIN TRANSACTION

12
The Transaction Log

• Special database table to keep track of all
  transactions that update database
• Used for recovery requirement triggered by
  rollback, abnormal program termination, system
  failure (network/ disk crash)
• To recover database
• Roll Back ? uncommitted transactions
• Roll Forward ? transactions that are committed
  but not yet written to physical database
• Increase processing overhead but help to restore
  a system therefore worth the price

13
Database Recovery

• Roll Back
• Black out/ undo unwanted changes to database
• Apply before-images of records that have been
  changed
• Move database back to earlier state
• For reversing changes of transactions that have
  been aborted or terminated abnormally
• Roll Forward
• Start with earlier copy of database
• Apply after-images (result of good transactions)
• Move database forward to a later state

14
undo
redo
15
The Transaction Log

• Stores records for
• the beginning of transaction
• each transaction component (SQL statement)
• 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 transaction
• the ending (COMMIT) of the transaction

16
A Transaction Log
Store pointer to previous and next transaction
log of SAME TRANSACTION

• Transaction log itself is database, contain most
  critical data

17
Concurrency Control

• Activities to coordinate simultaneous transaction
  execution (multiprocessing system)
• Objective is to ensure transaction
  serializability (multi-user system)
• no proper control can lead to data integrity and
  consistency problems
• lost updates
• uncommitted data
• inconsistent retrievals

18

• TRANSACTION COMPUTATION
• T1 purchase 100 units QOH QOH 100
• T2 sell 30 units QOH QOH - 30

19
Normal Execution of Two Transactions
20
Lost Updates

• When two concurrent transactions update same
  data. T1 has not committed but T2 has started.

21
Lost Updates
T1 write value of 135, which is promptly
overwritten by T2
Suppose a transaction can read QOH value before
previous transaction has been committed

22
Uncommitted data

• When two transactions, are executed concurrently
  and the first transaction is rolled back after
  second transaction has already accessed the
  uncommitted data (violating isolation)NOTE
  isolation data used during execution of a
  transaction cannot be used by a second
  transaction until first one complete

23
Correct Execution of Two Transactions
24
An Uncommitted Data Problem
25
Inconsistent Retrievals

• When a transaction calculates some summary
  (aggregate) functions over a set of data while
  other transactions are updating the data
• Problem transaction might read some data before
  they are changed and other data after they are
  changed

26
Retrieval During Update
27
Transaction Results Data Entry Correction
28
Inconsistent Retrievals
29
The Scheduler

• Special DBMS program to establish order of
  operations within which concurrent transactions
  are executed
• to ensure serializability and isolation of
  transactions
• Significant when T1 and T2 operate on
  same/related data, the order of operated
  transactions can yield undesirable consequences

30
The Scheduler (continued)

• Appropriate orders are based on its actions on
  concurrency control algorithms (locking and time
  stamping)
• Ensures computers central processing unit (CPU)
  is used efficiently (if first come, first serve
  lost CPU cycle)
• Facilitates data isolation to ensure that two
  transactions do not update the same data element
  at the same time
• Conflict occur when 2 operations access same data
  and at least one is WRITE operation (solve by
  concurrency controls locking or time stamping)

31
Read/Write Conflict Scenarios Conflicting
Database Operations Matrix
32
Concurrency Control Methods

• Lock
• Guarantees exclusive use of a data item to a
  current transaction, other transactions cant use
• Transaction acquire lock before accessing data,
  release lock after transaction complete
• Required to prevent another transaction from
  reading inconsistent data
• Lock manager
• Responsible for assigning and policing the locks
  used by the transactions

33
Lock Granularity

• Indicates the level of lock use
• Locking can take place at the following levels
• Database
• Table
• Page
• Row
• Field (attribute)

34
Lock Granularity (continued)

• Database-level lock
• Entire database is locked
• For batch processing system OR during entire
  database backup process
• Table-level lock
• Entire table is locked (can cause traffic jam /
  delay ?not for multiuser)
• For bulk update OR update to whole table (eg.
  Increase every employee salary by 5)
• Page-level lock
• Entire diskpage is locked (most commonly
  implemented)
• Page size depend on system (used in multiuser)

35
Lock Granularity (continued)

• Row-level lock
• Only requested record is locked
• Improve availability of data but high computer
  overhead cost (to lock and unlock)
• Field-level lock
• Only requested field(s)/column(s) is(are) locked
• high computer overhead cost
• For system in which most update affect only
  one/two fields in a record (eg. Inventory control
  ? most update are on only Q_ONHAND field)

36
A Database-Level Locking Sequence
37
An Example of a Table-Level Lock
38
Example of a Page-Level Lock
Page can contain several rows of one or more
tables A table can span over many pages
39
An Example of a Row-Level Lock
Both transactions are executed concurrently
40
Lock Types

• Binary lock
• Has only two states locked (1) or unlocked (0)
• Eliminate lost update problem
• Too restrictive (if 2 transactions read same
  data, need to wait)

41
An Example of a Binary Lock
42
Lock Types

• Shared/Exclusive lock
• Shared lock /Read lock
• Concurrent transactions are granted Read access
• When read lock on, write lock is not allow
• Exclusive lock / Write lock
• Access is specifically reserved for the
  transaction that locked the object (no read/
  update allow until unlock)
• 3 stages unlocked, shared(Read), exclusive
  (Write)
• Mutual Exclusive Rule
• Only one transaction at a time can own exclusive
  lock on the same object
• Lock can prevent data inconsistency but not
  serializability and deadlock
• Deadlock ? solved by deadlock detection and
  prevention
• Serializability ? solved by Two-phased locking

43
Deadlock
44
Deadlocks

• when two transactions wait for each other to
  unlock data
• Possible only if one of the transactions wants to
  obtain an exclusive lock on a data item
• No deadlock condition can exist among shared
  locks
• T1 access data items X and Y
• T2 access data items Y and X

45
How a Deadlock Condition Is Created
46
Deadlocks Controls

• Prevention
• A transaction requesting a new lock is aborted if
  there is the possibility that deadlock can occur.
  If aborted, changes made to transaction are
  rolled back and all locks are released
• Used when probability of deadlocks is high
• Detection
• DBMS periodically test database for deadlock. (if
  found, one of the transaction will be aborted,
  rolled back, restart), the other continue
• Used when probability of deadlocks is low
• Avoidance
• Transaction must obtain all locks it needs before
  it can be executed, avoid Roll back
• Used when response time is not significant
  (serial lock assignment, increase action response
  time slow)

47
Two-Phase Locking to Ensure Serializability

• Defines how transactions acquire and relinquish
  locks
• Guarantees serializability, but it does not
  prevent deadlocks
• 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

48
Two-Phase Lockingto Ensure Serializability
(continued)

• Governed by the following rules
• Two transactions cannot have conflicting locks
• No unlock operation can precede a lock operation
  in the same transaction
• No data are affected until all locks are
  obtainedthat is, until the transaction is in its
  locked point

49
Two-Phase Locking Protocol
50
Concurrency Control

• Time Stamping Methods
• Assigns a global unique time stamp to each
  transaction
• Produces an explicit order in which transactions
  are submitted to the DBMS
• Uniqueness
• Ensures that no equal time stamp values can exist
• Monotonicity
• Ensures that time stamp values always increase

51
Concurrency Control with Time Stamping Methods

• Disadvantage
• Each value stored in database requires 2
  additional time stamp fields
• One for the last time the field was read
• One for the last update
• Thus increase memory needs and processing overhead

52
Concurrency Controlwith Optimistic Methods

• Optimistic approach
• assume that the majority of database operations
  do not conflict
• Does not require locking or time stamping
  techniques
• Transaction is executed without restrictions
  until it is committed
• Phases are read, validation, and write (a
  transaction moves through 2 or 3 phases)
• Acceptable for mostly read or query database
  system (with few updates transactions)

53
Concurrency Controlwith Optimistic Methods

• Read
• transaction reads the database
• execute needed computations
• Update to private copy of database value
  (temporary update file)
• Validate
• Transaction is validated to ensure changes will
  not effect integrity and consistency of database
  (if test is positive,move to write phase)
  otherwise, the transaction is restarted, changes
  are discarded
• Write
• changes are permanently applied to database

54
Database Recovery Management

• Database recovery
• Restores database from a given state, usually
  inconsistent, to a previously consistent state
• Based on the atomic transaction property
• All portions of the transaction must be treated
  as a single logical unit of work, in which all
  operations must be applied and completed to
  produce a consistent database
• Used when a transaction cannot be completed, so
  aborted, and any changes to the database must be
  rolled back (undo)
• And also used if the system fails

55
Level of backup

• Full backup
• dump of database
• Differential backup
• only modifications not in previous backup copy
• Transaction log backup
• only the transaction log operations not in
  previous backup copy

56
Database and system failure induced by

• Software
• O/S, DBMS Software, application program, viruses
• Hardware
• Memory chip error, dish crashes, bad disk sector,
  disk full
• Programming Exemption
• application program or end user roll back
  transaction that meet condition defined by
  business rulesEG)) CTRLC, withdraw from 0
  balance

57
Database and system failure induced by

• Transaction
• System detect deadlock and abort
• External
• Fire, earthquake, flood..
• Recovery is impossible without backup
• Roll backward
• When committed part of the database is still
  usable, use log, undo only uncommitted
  transactions
• Roll forward
• recent backup, log, redo
• When entire database needs to be recovered to a
  consistent state

58
Transaction Recovery

• Write-ahead-log protocol
• ensures that log are always written before any
  database data are actually updated
• If failure occur, database can later be recovered
  using transaction log
• Redundant transaction logs
• keep several copies of transaction log to protect
  against physical disk failure

59
Transaction Recovery

• Database buffer
• temporary storage in primary memory to speed up
  processing time (faster than accessing physical
  disk every time)
• data from physical disk is stored in buffer, any
  update will be made to this copy. Later on, all
  buffers are written to physical disk in a single
  operation (saving processing time)
• Database checkpoint
• Operation in which DBMS writes all of its updated
  buffer to disk and also the checkpoint operation
  is registered in transaction log
• Physical database and transaction log will be
  in-sync

60
Transaction Recovery Techniques

• Deferred write /Deferred update
• Transaction operations do not immediately update
  the physical database
• Only the transaction log is updated
• Physical updates is made only after the
  transaction reaches its commit point using the
  transaction log information
• If aborted before COMMIT (no change), no need to
  ROLL BACK

61
Transaction Recovery Techniques

• To recover committed transactions
• Identify last checkpoint (last time when physical
  update to disk is made)
• Transaction COMMIT before last checkpoint,
  nothing to be done
• Transaction that COMMIT after last checkpoint,
  use log to redo and update to database , using
  after value (from oldest to newest)
• Transaction that ROLL BACK after last checkpoint,
  before failure, nothing to be done caused no
  update made yet

62
Transaction Recovery (continued)

• Write-through / Immediate update
• Database is immediately updated by transaction
  operations during the transactions execution,
  even before the transaction reaches its commit
  point
• If transaction is aborted before COMMIT, need to
  ROLL BACK using before values

63
Transaction Recovery Techniques

• To recover committed transactions
• Identify last checkpoint (last time when physical
  update to disk is made)
• Transaction COMMIT before last checkpoint,
  nothing to be done
• Transaction that COMMIT after last checkpoint,
  use log to redo and update to database , using
  after value (from oldest to newest)
• Transaction that ROLL BACK after last checkpoint,
  before failure,use log to undo, using before
  values ( newest to oldest)

64
A Transaction Log for Transaction Recovery
Examples (Using Deferred Write)
65
Summary

• Transaction
• Sequence of database operations that access the
  database
• Represents real-world events
• Must be a logical unit of work
• No portion of the transaction can exist by itself
• Takes a database from one consistent state to
  another
• One in which all data integrity constraints are
  satisfied

66
Summary (continued)

• SQL provides support for transactions through the
  use of two statements COMMIT and ROLLBACK
• Concurrency control coordinates the simultaneous
  execution of transactions
• Scheduler is responsible for establishing order
  in which concurrent transaction operations are
  executed

67
Summary (continued)

• Lock guarantees unique access to a data item by a
  transaction
• Database recovery restores the database from a
  given state to a previous consistent state