Transactions and the Internet
Week 12-13
Schedule
Week Date Lecture Topic
1 Jan 9 Introduction to Data Management 2 Jan 16 The Relational Model
3 Jan. 23 Constraints and SQL DDL
4 Jan. 30 SQL DML, DB Applications, JDBC 5 Feb 6 JDBC, DDL (Views, Access Control) 6 Feb 13 Relational Algebra, Advanced SQL - Feb 20 [Reading Week]
7 Feb 27 Review and Midterm (Mar 1)
8 Mar 5 OLAP
9 Mar 12 ER Conceptual Modelling 10 Mar 19 Normalization
11 Mar 26 XML and Data Integration
12 Apr 2 Transactions and the Internet, Query Processing
This week’s reading:
Chapters 18,20,22
Transaction Processing in the WWW
Browser requests information from an HTTP server
Server sends to the browser an HTML page (and possibly scripts)
User interacts with the page (and scripts) and sends information back to the server
Application on HTTP server (CGI, servlet, JSP, ASP.NET) reads information from the browser, processes it (accesses a
Three-Tiered Model of TPS
DBMS
database server machine
presentation server
• • •
client machines
communication presentation
server
application server
application server machine
Interconnection of Servers
Sessions on the Internet
HTTP sessions do not maintain context between interactions
Context information is stored in file on Web server that is accessed through a session number
Cookies: servlet places session number in a cookie file in browser; subsequent servlets can access cookie
Hidden fields in HTML: servlet places session number in hidden field in HTML document it sends to
browser; hidden field is not displayed but is returned with HTML document
Appended field to URL (HTTP return address):
session number is appended to URL return address and can be accessed by the next servlet
ACID Properties of Transactions
Transaction execution must maintain the correctness of the database model
Therefore additional requirements are placed on the execution of transactions beyond those placed on ordinary programs
Atomicity (all or nothing)
Consistency (w.r.t. Integrity Constraints)
Isolation Durability
Isolation
Serial execution of a set of (consistent) transactions is correct, but performance might be inadequate
Concurrent (interleaved) execution of a set of transactions offers performance benefits, but might not be correct
Interleaved Execution
Incorrect Interleaved Schedule
Example for course registrations: c is the number of current registrants, transaction reads and adds 1 to this number
T1: r1(c: 29) w1 (c: 30) T2: r2 (c: 29) w2 (c: 30)
Schedule not equivalent to T1, T2 or T2, T1 Database state no longer corresponds to
real-world state, integrity constraints are
Serializable Schedules
The concurrent schedule S is serializable
S: r1(a:4) r2(b:5) w2(a:5*2) r1(b:5) w1(c:4+5)
because it is equivalent to the serial schedule
T1, T2: r1(a:4) r1(b:5) w1(c:4+5) r2(b:5) w2(a:5*2)
read operations of distinct transactions on the same data item commute
S is not equivalent to T2, T1 since read and write
Commutativity
Two operations commute if, when executed in either order:
The values returned by both are the same and
The database is left in the same final state
Two schedules are equivalent if one can be derived from the other by a series of simple interchanges of commutative operations
A schedule is serializable if it is equivalent to a serial schedule
Implementing Serializability:
Two-Phase Locking
Locks are associated with each data item
A transaction must acquire a read (shared) or write (exclusive) lock on an item in order to read or write it
A write lock on an item conflicts with all other locks on the item; a read lock conflicts with a write lock
If T1 requests a lock on x and T2 holds a conflicting lock on x, T1 must wait
Atomicity and Durability
Atomicity deals with transaction aborts:
User aborts transaction (e.g., cancel button)
System aborts transaction (e.g., deadlock)
Transaction aborts itself (e.g., unexpected db state)
System crashes
Durability deals with failure:
System and Media failures
Log
Log:
Append-only sequence of records used to restore database to a consistent state after a failure.
Stored on non-volatile device distinct from mass storage device that contains database
Survives processor crash and media failure
To execute an Update:
Appended Update Record to the log when a transaction modifies an item
Contains before image: value of item prior to update
Used to restore item when transaction is aborted
Aborting a Transaction
Transaction abort:
Scan log backward; apply before image in each of the transaction’s update records to database items to restore them to their original state.
Begin Record terminates scan
B T1
U T1
B T2
U T1
U T2
U T1
U T2 B - begin
A T2
Recovery From Crash
Transaction is not committed until its Commit Record is in the log
A crash at any time before that causes transaction to be rolled back
Active transactions must be identified and aborted when system recovers
B T1
U T1
B T2
U T1
U T2
U T1
B T3
A T2
U T3
C T3
B T4
End of scan for crash recovery
U T4
End of log when crash occurs;
roll back T1 and B - begin
U - update C - commit
Write-Ahead Log
Both the log and database must be updated when a transaction modifies an item. If a crash occurs between updates, abort the transaction
Database updated first - On recovery, item is in the new state but there is no before image to roll it back.
Transaction cannot be aborted.
Log updated first - On recovery, item in old state and before image in log. Use of before image has no effect, but transaction can be aborted
Update record in log must be written-ahead of
Summary: Transactions
Architectures for TP on the Internet
Two and Three Tiers
Sessions
ACID Properties
Isolation
TP Abstraction: Schedule
Serializability, Anomalies, Commutativity
Two-phase Locking
Atomicity and Durability
Logs, Recovery
