=====
CS 325 - Week 14 Lecture 1 - 2021-11-29
=====

=====
TODAY WE WILL
=====
*   announcements
*   start discussion of Transaction Management - part 2
*   prep for next class

*   reading:
    *   DB Reading packet 10 - Transaction Management - part 2
    *   SQL Reading Packet 8 - Simple reports, part 1 and 2

*   Should be working on Homework 10, due Friday, December 3
*   Should be working on Project Population Milestone, due 11:59 pm on
    SUNDAY, December 5th

=====
three broad categories amongst those for concurrency control:
*   locks
*   time stamping
*   optimistic methods

-----
LOCKS and locking approaches
-----
*   PREVENTING undesired inconsistencies during concurrent transactions
    by limiting sharing of data;

*   transactions must obtain a LOCK for data before they are permitted
    to use it (read it OR write it)

    and the transaction RELEASES the lock when it is done, so another
    transaction can obtain a lock on that data;

*   (we are assuming IMPLICIT locks -- managed by the DBMS -- not explicit
    locks handled by applications...)

*   lock granularity - how much is being locked at once
    *   make sure you can consider the trade-offs of "larger" versus "smaller"
        lock granularity

*   amongst the family of locking algorithms,
    we'll discuss two classics:
    *   binary locks
    *   shared/exclusive locks (also called read/write locks)

-----
Binary locks
-----
*   binary lock on a "chunk" has only 2 states, 1 (locked) and 0 (unlocked)

    *   if a "chunk" has a binary lock whose state is 1, it is locked
        for all but the transaction with that lock;
	(no other transaction can use that chunk, only the transaction
	    with the binary lock can do so)

    *   typically, any other transaction that wants to use that
        chunk has to wait

    *   once the lock is released, another transaction can obtain that lock

    *   relatively simple, but also relatively restrictive (no concurrent
        reads allowed, for example)

-----
Shared/Exclusive locks (read/write locks)
-----
*   what if your locks had THREE states instead of two?
    What if it could be UNLOCKED,
        or SHARED-locked (read-locked),
	or EXCLUSIVE-locked (write-locked)

   if a "chunk" is UNLOCKED, a transaction can obtain
       EITHER a shared-lock or an exclusive-lock;

   if a "chunk" is SHARED-locked, a transaction that
       wants to read can ALSO get a SHARED-lock on that
       chunk,
       BUT a transaction that wants an EXCLUSIVE lock
       has to WAIT

   if a "chunk" is EXCUSIVE-locked, all other transactions
       that want either shared- or exclusive- locks have to
       wait.

-----
Two-phased locking protocol
-----
*   Locking does help achieve the Isolation property.
    BUT! locking BY ITSELF does not ensure the Serializability property!

    *   you need additional protocols added to locking to
        also achieve serializability;

    *   a classic way: the two-phased locking protocol

        *   a transaction can request the different locks it needs,
	    BUT! as soon as it releases ANY of those locks,
	    it cannot obtain any more locks!

            the name "two-phased" is because you can imagine the
	    life-cycle of a transaction thus having 2 "phases",
	    a period when it can obtain locks -- a "growing" phase,
	    a period when it cannot obtain locks, but can release them -
	        a "shrinking" phase

        *   a very-restrictive (and easier-to-implement) variation
	    of two-phased locking is for the DBMS to assume the
	    growing phase is until the transaction issues a COMMIT
	    or a ROLLBACK command...!
	    (so none of a transaction's locks are released until
	        that transaction is either done or about to be rolled
		back...!)

-----
a few words about DEADLOCK...
-----
*   two (or more) transactions
    are each waiting for a resource
    the other has locked

-----
strategies for deadlock management
-----
these include:
*   timeouts
    *   there's a system-defined amount
        of time a transaction can wait
	for a lock -- after that, the
	system assumed deadlock MAY have
	occurred and it rolls back and
	restarts the transaction
*   detection
    *   allow it to occur, detect it,
        and rollback one of involved
	transactions (and restart it) to
	break it
*   prevention
    *   prevents deadlock from occurring
        at all
*   avoidance
    *   ...just require a transaction
        to obtain ALL its at once!
*   ...or just notice something
    has gone awry and rollback
    a transaction...!

-----
time stamping - an ALTERNATIVE to locking approaches for
    concurrency control...!
-----
*   when a transaction starts, it is assigned a TIME STAMP
    - a value that is unique, it is global, and it is
    MONOTONIC (it is always increasing) - a transaction
    thst starts later gets a larger time stamp

    *   all the actions of that transaction are considered
        to have that transaction's time stamp;

*   here, the DBMS ensures that CONFLICTING operations be
    executed in TIME STAMP order

    *   if the actions of 2 transactions conflict such that
        this would NOT be the case, one is rolled back and
	restarted (thus receiving  a new, larger time stamp)

*   MORE on time stamps in Week 14 Asynchronous Material!