Principles of Database Systems With Internet and Java Applications

1
Principles of Database SystemsWith Internet and
Java Applications
Todays TopicChapter 14 Transaction Processing

• Instructors name and information goes here
• Please see the notes pages for more information.

2
COP 4710 DatabasesSpring, 2000Todays
TopicChapter 14 Transaction Processing

• Gregory A. Riccardi
• April 4, 2000
• Department of Computer Science

3
ACID Transactions

• Atomicity the property of a transaction that all
  of the updates are successful, or there is no
  update at all.
• Consistency each transaction should leave the
  database in a consistent state. Properties such
  as referential integrity must be preserved.
• Isolation each transaction when executed
  concurrently with other transactions should have
  the same affect as if it had been executed by
  itself.
• Durability once a transaction has completed
  successfully, its changes to the database should
  be permanent. Even serious failures should not
  affect the permanence of a transaction.

4
Atomicity in a single user system

• Operations
• Begin
• Commit
• Rollback
• Read
• Write

5
Rollback segments in Oracle8

• An RBS entry is a set of before images of rows
  that have been modified by the transaction
• The transaction reads the table, other
  transactions read the RBS

6
Example of transaction

• open transaction
• videoId video1 select id of a copy of "Star
  Wars"
• if (video1 null) rollback transaction
• insert row into Reservation for video1
• videoId video2 select id of a copy of "Return
  of the Jedi"
• if (video2 null) rollback transaction
• insert row into Reservation for video2
• videoId video3 select id of a copy of "The
  Empire Strikes Back"
• if (video3 null) rollback transaction
• insert row into Reservation for video3
• commit transaction

7
Transaction isolation

• Consider these transactions
• Actions of T1
• A balance1 (select balance from Customer
  where accountId 101) balance1 5.00
• B update Customer set balance ?balance1 where
  accountId 101
• Actions of T2
• A balance2 (select balance from Customer where
  accountId 101) balance2 10.00
• B update Customer set balance ?balance1 where
  accountId 101
• Problems
• Lost update
• T1.a, T2.a, T1.b, T2.b
• Dirty read
• T1.a, T1.b, T2.a, T1.rollback, T2.b, and T2
  commit
• Incorrect Summary example in class

8
Using locks to control transactions

• Types of locks
• Read (shared) locks
• Write (exclusive) locks
• Explicit vs. implicit locks
• Implicit is lock as required by transaction ops
• Consider locks in previous transactions

9
Locking database objects

• Allow transaction operations to lock objects
• Read (shared) locks
• Write (exclusive) locks
• Lock granularity
• What size object to lock?
• Table, row, field, column
• Effect on concurrency
• T1Select sum(balance) from Customers
• T2 Update Customers set firstNameJoe where
  accountId101
• Effect on size and cost
• Smaller objects more locks

10
How to lock

• Explicit request
• T1 request read on X
• Implicit request
• T2 Select sum(balance) from Customers
• Explicit release
• T1 release read on X
• Implicit release
• T2 commit

11
Two phase locking (2PL)

• Locks granted and released in two phases
• Growing phase
• Request and upgrade locks
• Request read on X
• Request write on X
• Shrinking phase
• Release and downgrade locks
• Request read on X (downgrade from write)
• Release read on X
• 2PL guarantees serializability
• Any conflicting operation is blocked

12
Implementing 2PL

• No explicit request or release
• Release at commit or rollback
• Discuss why this works and why it accomplishes
  serializability