Class 17: midterm

 

Class 18: hw4 is available

 

 

Section 5.4, Programming For Transactions

 

Idea of concurrency, bank tellers have simultaneous access to data. 

 

Problem:  Example 5.4.1, pg. 288. Inconsistent view of data.  Table A of accounts: A.balance where A.aid = A2 will be called A2. balance.  One process wants to move $400.00 from one account to another.

 

S1:   A1.balance == $900.00  A2.balance == $100.00  -- sequence of states

 

S2:   A1.balance == $500.00  A2.balance == $100.00  -- We can only update

 

S3:   A1.balance == $500.00  A2.balance == $500.00  -- one row at a time in

                                                                                                                SQL

If a different process wants to do a credit check, adds A1.balance and A2.balance, shouldn't see state S2 or may fail credit check.

 

Create an idea called transactions.  Programming with transactions makes guarantees to programmer, one of which is Isolation.  Means every transaction acts as if all data accesses it makes come in serial order with no intervening accesses from other transactions.  E.g.:

 

    T1:  R1(A1) W1(A1) R1(A2) W1(A2)  (explain notation)

    T2:  R2(A1) R2(A2)

 

Turn Update (after R) into W.  Turn Select into R only.  Problem of S2 comes because of schedule: 

 

   R1(A1) W1(A1) R2(A1) R2(A2) R1(A2) W1(A2)

 

But this is not allowed to happen with transactions, must act like:

 

    T1 then T2 (T2 sees state S3) or T2 then T1  (T2 sees S1).

 

Don't care which.  Called Serializability.

 

Why do we want to have concurrency?  Why not true serial execution? Class?  Idea of keeping CPU busy.  More next term.

 

Two new SQL statements control transactions.

 

EXEC SQL COMMIT [WORK];  Successful transaction so commit, rows up­dated, locks released, updates become visible to other transactional reads.

 

EXEC SQL ROLLBACK;  Unsuccessful transaction so rollback (also called abort); row value updates rolled back, rows become visible to others.

 

Java case: conn.commit(), conn.rollback()

 

Transactions START when first access is made to table (select, update, open cursor, etc.) after connect or prior commit or abort.  Ends with next commit work or rollback statement or system abort for other reason.

 

Recall idea that we hold locks, user has SQL statements to say:  logical task is complete:  you can make updates permanent and drop locks now.

 

Typically, applications loop around in logic, with user interaction, transaction extends from one interaction to the next.  But might have multiple transactions between interactions.

 

DON't typically hold transaction during user interaction!

 

User interaction may set off a lot of account activity:  say that each Tx subtracts money from n-1 acct balances, adds money to a joint Christmas Club account.  Rule that Tx should abort if one acct is under balance.

 

It should be clear that a database system can't predict when one set of accts has been balanced off, given that the logic might start immediately dealing with another set.  Programmer must tell system WHEN to commit.

 

Value of rollback (abort) is: needn't write application code to reverse prior changes.

 

We speak of the ACID guarantees of transactions made to programmers.

 

Atomicity.  The set of updates performed by a Tx are atomic, that is, indivisible.  If something bad happens (terminal goes down) will abort.

 

Consistency.  E.g., money is neither created nor destroyed by transac­tional logic, then won't happen even under conditions of concurrency.

 

Isolation. Interleaved history will act as if if we were presented with a serial set of Txs, each set of operations isolated from all the other Txs.

 

Durability.  Even if lose power & memory (crash), lose place in what doing, will RECOVER from the crash and guarantee atomicity.

 

LOCKING.  Simple version here.  (a) When a Tx accesses a row R, first must get lock.  (b)  Locks are held until Tx ends (Commit).  (3 & 4)  Locks are exclusive, so second locker will wait if it can.

 

Example 5.4.2.  Recall problem of inconsistent view.

 

   R1(A1) W1(A1) R2(A1) R2(A2) C2 R1(A2) W1(A2) C1

 

where add Ci for commit by Ti.  Now add locks. L1(A1) means T1 is requesting a lock for data item A1. When it gets the lock, it is allowed to read or write A1. If some other transaction has the lock, this transaction has to wait, i.e., the lock request call doesn't return right away, but possibly seconds later after some other transaction releases the lock.

 

L1(A1) R1(A1) W1(A1) L2(A1) (conflict, must wait) L1(A2) R1(A2) W1(A2) C1 (Releases T1 locks including L1(A); Now lock request L2(A1) can succeed) R2(A1) R2(A2) C2

 

Result is T2 sees State S3.  No matter what arrangement of attempts, T2 will see either S1 or S3.  This sort of thing is happening all the time in the database, to keep one transaction from interfering with another.

 

New idea:  DEADLOCK.  What if rearrange:

 

    R1(A1) W1(A1) R2(A2) R2(A1) C2 R1(A2) W1(A2) C1

 

Now locking proceeds as follows (Careful, Slow):

 

    L1(A1) R1(A1) W1(A1) L2(A2) R2(A2) L2(A1) (prior L1(A1), so T2 must wait) L1(A2) (prior L2(A2) so T1 must wait:  DEADLOCK!  illustrate)

 

(System chooses one "victim" Tx to abort, say T2; now L1(A2) can be successfully completed, so:) R1(A2) W1(A2) C2 (Now T2 audit attempt retries as T3) L3(A2) R3(A2) L3(A1) R3(A1) C3

 

And state seen by T2 (retried as T3) is S3.  If aborted T1 instead of T2, state seen by T2 would be S1. 

 

This means that programmer must watch out for deadlock error return, attempt a retry: LOOK AT Example 5.4.4, pg 298.

 

Note can't cause deadlock abort until already HOLD a lock and try to get another one. Of course this could happen in a single statement in (e.g.) Searched Update for multiple rows, but not if only a single row is accessed.

 

Note that in general, locks held are much more sophisticated than the simple exclusive locks explained here, but the idea is equivalent.

 

No user interaction during transaction. Problem if Tx retrieves data of quantity available for an order and then user confers with customer.  LOOK AT Example 5.4.5, program pg. 300.

 

To avoid this, typically commit transaction after retrieving data, then confer with customer, the start new transaction for update based on customer wishes.  But new problem: can't trust previous value.  Example 5.4.6, program pg 301.

 

Can deal with this by rechecking after new transaction starts. "Oops, I see someone got that window seat I was going to assign you."

 

Next time: look at Java for these examples.