Database Components:, Lecture notes of Data Warehousing

Download Database Components: and more Lecture notes Data Warehousing in PDF only on Docsity!

ISBS204 Database

Management Systems

Lecture 10

Transaction Management and Concurrency

Control

Textbook Reading:

Chapter 10

After completing this lecture, you will be able to:

• Describe the database transaction management process
• Identify the four properties of a database transaction
• Explain concurrency control and its role in maintaining database integrity
• Describe how locking methods are used for concurrency control
• Describe how stamping methods are used for concurrency control
• Describe how optimistic methods are used for concurrency control
• List and explain the ANSI levels of transaction isolation
• Describe the role of database recovery management in maintaining database integrity Learning Objectives

• Consistent database state
  • All data integrity constraints are satisfied
  • Must begin with the database in a known consistent state to ensure consistency
  • Most real-world database transactions are formed by two or more database requests - Database requests: equivalent of a single SQL statement in an application program or transaction - Example: if a transaction is composed of two UPDATE statements and one INSERT statement, the transaction uses three database requests

What is a Transaction?

• Not all transactions update the database
  • SQL code represents a transaction because it accesses

the database

• Improper or incomplete transactions can have devastating

effect on database integrity

• Users can define enforceable constraints based on

business rules

• Other integrity rules(referential/entity) are automatically

enforced by the DBMS

• Example: if a transaction inserts a new customer number into a customer table and the number already exists, the DBMS will end the transaction with an error code to indicate a violation of the primary key integrity rule. Evaluating Transaction Results

• Isolation
  • Data used during transaction cannot be used by second transaction until the first is completed
  • Example: if transaction T1 is being executed and is using the data item X, that data item cannot be accessed by any other transaction (T2 … Tn) until T1 ends.
  • This property is particularly useful in multiuser database environments because several users can access and update the database at the same time.
• Durability
  • Ensures that once transactions are committed they cannot be undone or lost
• Serializability
  • Ensures that the schedule for the concurrent execution of several transactions should yield consistent results

Transaction Properties

• Example: if several concurrent transactions are executed

over the same data set and the second transaction

updates the database before the first transaction is

finished, the isolation property is violated and the

database is no longer consistent.

Transaction Properties

• Keeps track of all transactions that update the database
  • DBMS uses the information stored in a log for (a):
    • Recovery requirement triggered by a ROLLBACK statement
    • Program’s abnormal termination
    • System failure (i.e. disk crash)
• While the DBMS executes transactions that modify the database, it also automatically updates the transaction log.
• The transaction log stores the following :
• A record for the beginning of the transaction.
• For each transaction component (SQL statement): –– The type of operation being performed (INSERT, UPDATE, DELETE). –– The names of the objects affected by the transaction (the name of the table). –– The “before” and “after” values for the fields being updated. –– Pointers to the previous and next transaction log entries for the same transaction.
• The ending (COMMIT) of the transaction. The Transaction Log

• Table 10.1: simplified transaction log that reflects a basic transaction composed of two SQL UPDATE statements.

The Transaction Log

• Coordination of the simultaneous transactions execution

in a multiuser database system

• Objective: ensures serializability of transactions in a

multiuser database environment

• Important because the simultaneous execution of

transactions over a shared database can create several

data integrity and consistency problems

• Three main problems are lost updates, uncommitted

data, and inconsistent retrievals

Concurrency Control

• Lost update
  • Occurs in two concurrent transactions when:
    • Same data element is updated
    • One of the updates is lost Problems in Concurrency Control

• Uncommitted data
  • Occurs when:
    • Two transactions are executed concurrently
    • First transaction is rolled back after the second transaction has already accessed uncommitted data

Problems in Concurrency Control

(Rolled back)

Problems in Concurrency Control

Inconsistent retrievals

• The “After” summation shown in Table 10.10 reflects that the value of 25 for product 1546-QQ2 was read after the WRITE statement was completed.
• Therefore, the “After” total is 40 + 25 = 65.
• The “Before” total reflects that the value of 23 for product 1558-QW1 was read before the next WRITE statement was completed to reflect the corrected update of 13.
• Therefore, the “Before” total is 65 + 23 = 88.

Inconsistent retrievals