STRUCTURE OF PRESENTATION 1 Basic database concepts Entracte

STRUCTURE OF PRESENTATION : 1. Basic database concepts ** Entr’acte 2. Relations 8. SQL tables 3. Keys and foreign keys 9. SQL operators I 4. Relational operators I 10. SQL operators II 5. Relational operators II 11. SQL constraints 6. Constraints and predicates 12. 7. The relational model Copyright C. J. Date 2013 A. SQL vs. the relational model References page 169

ENTR’ACTE : Transactions Database design Copyright C. J. Date 2013 page 170

TRANSACTIONS : This concept is of only tangential relevance to most users, but you do need a basic appreciation of it in order to understand what databases in general, and relational databases in particular, are all about Not a specifically relational topic … … though it’s interesting to note that the original research that put transaction management on a sound scientific footing (by Eswaran, Gray, Lorie, and Traiger) was all done in a specifically relational context Copyright C. J. Date 2013 page 171

A transaction is [ part of ] a program execution: a logical unit of work /* “all or nothing” */ Begins by executing a BEGIN TRANSACTION statement /* normally explicit */. . . Ends by executing either a COMMIT statement or a ROLLBACK statement COMMIT = successful termination /* normally explicit */ ROLLBACK = unsuccessful termination /* might be implicit */ Copyright C. J. Date 2013 page 172

Program execution = sequence of transactions: time begin transaction … commit or rollback etc. All database updates (actually database reads too) must be done within the context of some transaction Copyright C. J. Date 2013 page 173

STANDARD EXAMPLE : Transfer $100 from account 123 to account 321: BEGIN TRANSACTION ; UPDATE ACC 123 : { BALANCE : = BALANCE - $100 } ; IF error THEN ROLLBACK ; UPDATE ACC 321 : { BALANCE : = BALANCE + $100 } ; IF error THEN ROLLBACK ; COMMIT ; Aside: Tutorial D could do both UPDATEs in one statement Copyright C. J. Date 2013 page 174

EXPLANATION : COMMIT “commits” the transaction’s updates (i. e. , installs them in the database) and terminates the transaction ROLLBACK “rolls the database back”—undoing the transaction’s updates—to the state it was in at the most recent BEGIN TRANSACTION (“commit point”) and terminates the transaction The system will automatically issue an implicit ROLLBACK on a given transaction’s behalf if that transaction fails to reach its planned termination (either COMMIT or explicit ROLLBACK) Copyright C. J. Date 2013 page 175

HOW IT’S (typically) DONE : System maintains recovery log in persistent storage (typically disk) “Write ahead log rule”: Log writes are done before database writes COMMIT forces log records out ROLLBACK uses log records to recover prior state Copyright C. J. Date 2013 page 176

THE ACID PROPERTIES : Atomicity: Transactions are all or nothing Consistency: Transactions transform a consistent state of the DB into another consistent state, without necessarily preserving consistency at all intermediate points /* ? ? ? . . . to be discussed! */ Isolation: Any given transaction’s updates are concealed from all other transactions until the given transaction commits /* also to be discussed */ Durability: Once a transaction commits, its updates survive in the DB, even if there’s a subsequent system crash Copyright C. J. Date 2013 page 177

IN OTHER WORDS : Atomicity: Logical unit of work Consistency: Logical unit of integrity (? ) Isolation: Logical unit of concurrency Durability: Logical unit of recovery Copyright C. J. Date 2013 page 178

CONSISTENCY : This one is a little suspect … Assumes integrity constraint checking is done at (successful) end of transaction (i. e. , at commit time) … which is in fact the case with some constraints in some DB products today* … … but is logically incorrect: The relational model requires all constraint checking to be IMMEDIATE. . . implying that the logical unit of integrity is really the statement /* not to mention the fact that “the C property” is often said to stand for correctness, anyway !!! */ * As well as in the SQL standard Copyright C. J. Date 2013 page 179

ISOLATION : Consider the following scenario: Transaction TX 1 … update p … … … ROLLBACK TX 2 now dependent on an update that “never occurred” Copyright C. J. Date 2013 Transaction TX 2 … … … retrieve p … … time page 180

ISOLATION : Another (worse? ) scenario: Transaction TX 1 … retrieve p … … … update p … Transaction TX 2 … … … retrieve p … … update p time Copyright C. J. Date 2013 TX 1’s update is lost page 181

In both scenarios, one transaction has “interfered” with another concurrent transaction, producing an overall incorrect result Note: Dependence on uncommitted updates and lost updates aren’t the only problems that can occur (absent suitable controls), but they’re the easiest to understand But the point is: We need those “suitable controls”! Typical and commonest such control (not the only one possible) is locking Copyright C. J. Date 2013 page 182

LOCKING : Before operating on a sharable object (e. g. , a tuple in the DB), the transaction must acquire a lock on that object /* typically done implicitly */ Effect of successful lock acquisition is to “lock other transactions out” of the object in question (in a manner to be explained) until the lock is released Typically two kinds of locks are supported, “shared” (S locks) and “exclusive” (X locks) … S locks are mutually compatible, but X locks aren’t compatible with anything /* as the names are meant to suggest */ Copyright C. J. Date 2013 page 183

LOCKING (cont. ) : Typical protocol: A retrieval request is an implicit request for an S lock on the target object An update request is an implicit request for an X lock on the target object In both cases, if the lock request can’t currently be granted (because it clashes with an existing lock), the requesting transaction waits COMMIT and ROLLBACK release all locks held by the terminating transaction Copyright C. J. Date 2013 page 184

FIRST EXAMPLE REVISITED : Transaction TX 1 … update p (X lock) … … … ROLLBACK Transaction TX 2 … … … retrieve p (request S lock: wait) … /* resume: acquire S lock */ TX 2 now doesn’t see “the update that never occurred” time Copyright C. J. Date 2013 page 185

ASIDE : FIRST EXAMPLE bis Transaction TX 1 … update p (X lock) … … … COMMIT time Copyright C. J. Date 2013 Transaction TX 2 … … … retrieve p (request S lock: wait) … /* resume: acquire S lock */ But TX 2 does see the update after it has been committed … Hence the “I” property page 186

SECOND EXAMPLE REVISITED : Transaction TX 1 … retrieve p (S lock) … … … update p (request X lock: … wait) … Transaction TX 2 … … … retrieve p (S lock) … … … update p (request X lock: wait) time Copyright C. J. Date 2013 deadlock! page 187

DEADLOCK : So we’ve “solved” the lost update problem by reducing it to another problem … !!! Typical solution to the deadlock problem: Choose one of the deadlocked transactions (typically the “youngest”) as the victim Roll that victim back, thereby releasing its locks Start the victim again as a new transaction Copyright C. J. Date 2013 page 188

A FINAL REMARK : Everything I’ve said regarding recovery and concurrency applies to SQL in particular. . . More or less! Note, however, that SQL has no explicit reliance on locking as such /* neither does Tutorial D */ But SQL does have all kinds of additional wrinkles, over and above what’s been said so far. . . Further details beyond the scope of this presentation Copyright C. J. Date 2013 page 189