ITEC 3220A Using and Designing Database Systems

1
ITEC 3220AUsing and Designing Database Systems

• Instructor Prof. Z. Yang
• Course Website http//people.yorku.ca/zyang/itec
  3220a.htm
• Office TEL 3049

2
Chapter 10

• Transaction Management and Concurrent Control

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
• Successful transaction changes the database from
  one consistent state to another
• One in which all data integrity constraints are
  satisfied
• Most real-world database transactions are formed
  by two or more database requests
• The equivalent of a single SQL statement in an
  application program or transaction

5
Example Transaction

• Examine current account balance
• Consistent state after transaction
• No changes made to Database

SELECT ACC_NUM, ACC_BALANCEFROM CHECKACCWHERE
ACC_NUM 0908110638
6
Example Transaction

• Register credit sale of 100 units of product X to
  customer Y for 500
• Consistent state only if both transactions are
  fully completed
• DBMS doesnt guarantee transaction represents
  real-world event

UPDATE PRODUCTSET PROD_QOH PROD_QOH -
100WHERE PROD_CODE X UPDATE
ACCT_RECEIVABLE SET ACCT_BALANCE ACCT_BALANCE
500WHERE ACCT_NUM Y
7
Incomplete Transactions

• Reasons
• An anomaly arises during execution (automatically
  restart)
• System crashes
• An unexpected situation during transaction
  execution
• May bring database to inconsistent state

8
Transaction Properties

• Atomicity
• All transaction operations must be completed
• Incomplete transactions aborted
• Durability
• Permanence of consistent database state
• Serializability
• Conducts transactions in serial order
• Important in multi-user and distributed databases
• Isolation
• Transaction data cannot be reused until its
  execution complete

9
Transaction Management with SQL

• Transaction support
• COMMIT
• ROLLBACK
• User initiated transaction sequence must continue
  until
• COMMIT statement is reached
• ROLLBACK statement is reached
• End of a program reached
• Program reaches abnormal termination

10
Transaction Log

• Tracks all transactions that update database
• May be used by ROLLBACK command
• May be used to recover from system failure
• Log stores
• Record for beginning of transaction
• Each SQL statement
• Operation
• Names of objects
• Before and after values for updated fields
• Pointers to previous and next entries
• Commit Statement

11
Transaction LogExample
12
Example

• Suppose that you are a manufacturer of product
  ABC, which is composed of parts A, B, C. Each
  time a new product ABC is created, it must be
  added to the product inventory, using the
  PROD_QOH in PRODUCT table. And each time the
  product is created the parts inventory, using
  PART_QOH in PART table must be reduced by one
  each of parts, A, B, and C.

PART
PRODUCT
PART_CODE PART_QOH
A 567
B 98
C 549
PROD_CODE PROD_QOH
ABC 1205
13
Example (Contd)

• Given the information, answer
• How many database requests can you identify for
  an inventory update for both PRODUCT and PART?
• Using SQL, write each database request you have
  identified above.
• Write the complete transactions.
• Write the transaction log, using the template in
  slide 11.

14
Concurrency Control

• Coordinates simultaneous transaction execution in
  multiprocessing database
• Ensure serializability of transactions in
  multiuser database environment
• Potential problems in multiuser environments
• Lost updates
• Uncommitted data
• Inconsistent retrievals

15
Normal Execution of Two Transactions
16
Lost Updates

17
More Example
18
Correct Execution of Two Transactions
19
An Uncommitted Data Problem
20
Retrieval During Update
21
Transaction Results Data Entry Correction
22
Inconsistent Retrievals
23
Example

• A department store runs a multiuser DBMS on a
  local area network file server which does not
  enforce concurrency control. One customer has a
  balance due of 250 when the following three
  transactions related to this customer were
  processed at the same time
• Payment of 250
• Purchase on credit of 100
• Merchandise return of 50.
• Each transaction reads the customer record
  when the balance was 250. the updated record was
  returned to the database in the order shown
  above.
• What balance will be for the customer after the
  last transaction was completed?

24
The Scheduler

• Establishes order of concurrent transaction
  execution
• Interleaves execution of database operations to
  ensure serializability
• Bases actions on concurrency control algorithms
• Locking
• Time stamping
• Ensures efficient use of computers CPU

25
Read/Write Conflict Scenarios
26
Concurrency Control with Locking Methods

• Lock guarantees current transaction exclusive use
  of data item
• Acquires lock prior to access
• Lock released when transaction is completed
• DBMS automatically initiates and enforces locking
  procedures
• Managed by lock manager
• Lock granularity indicates level of lock use

27
Locking Mechanisms

• Locking level
• Database used during database updates
• Table used for bulk updates
• Block or page very commonly used
• Row only requested row fairly commonly used
• Field requires significant overhead impractical

28
Locking Granularity

• Granularity refers to the level of the database
  item locked.
• A trade-off between overhead and waiting.
• Holding locks at a fine level decreases waiting
  among users but increase the system overhead.
• Holding locks at a coarser level reduces the
  number of locks but increases the amount of
  waiting.

29
A Database-Level Locking Sequence
30
An Example of a Table-Level Lock
31
Example of a Page-Level Lock
32
An Example of a Row-Level Lock
33
Binary Locks

• Two states
• Locked (1)
• Unlocked (0)
• Locked objects unavailable to other objects
• Unlocked objects open to any transaction
• Transaction unlocks object when complete

34
An Example of a Binary Lock
35
Shared/Exclusive Locks

• Shared
• Exists when concurrent transactions granted READ
  access
• Produces no conflict for read-only transactions
• Issued when transaction wants to read and
  exclusive lock not held on item
• Exclusive
• Exists when access reserved for locking
  transaction
• Used when potential for conflict exists
• Issued when transaction wants to update unlocked
  data

36
Shared/Exclusive Locks (Contd)
X S _
X No No Yes
S No Yes Yes
_ Yes Yes Yes
T2
T1
37
Two-Phase Lockingto 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

38
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

39
Two-Phase Locking Protocol
40
Deadlocks

• Condition that occurs 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
• Control through
• Prevention
• Detection
• Avoidance

41
How a Deadlock Condition Is Created
42
Example on Concurrency Control
Given schedule S1 as follows, and the locks wont
be released until commit. Is there any deadlock
in S1 using Shared/Exclusive lock.
T1 T2 T3
R(A)
W(B)
W(A)
Commit A, B
W(B)
Commit B
W(B)
Commit B
43
More Examples

• Let transactions T1, T2, and T3 be defined to
  perform the following operations
• T1 Add one to A
• T2 Double A
• T3 Display A and then set A to one
• Suppose the structure for T1, T2, T3 is indicated
  below. If the transactions execute without any
  locking, please give an example of wrong
  schedules.

44
More Examples (Contd)
T1 T2 T3
T11 Read (A), A ? A1 T12 Update (A) T21 Read (A), A ? A2 T22 Update (A) T31 Read (A), A 1 T32 Update (A)

• Suppose the following schedule
• T11- T31- T12- T32- T21- T22 obeyed the two-phase
  locking algorithm. Explain what could be produced
  by the schedule.