CIS 315 - Week 12 Lecture 
Transaction Management and Concurrency Control - part 1

what is a transaction? ...part 1;
  *   it STARTS with the idea of a transaction being
      a logical unit of work (luw)

  *   represents a real world event that we consider
      "atomic", or indivisible --
      but in reality, it does consist of a number
      of steps;

  *   we want to ensure that, in terms of our databases,
      that a transaction is either COMPLETED,
      all of its steps done, or is
      (as if it were) NOT started at all;

*   this leads us to our definition of a transaction:

    a transaction is a logical unit of work that must
    be either entirely completed or entirely aborted;
    no intermediate states are acceptable;

*   and, oh yes -- what if such transactions are
    going on at the same time? CONCURRENTLY?
    ... that's why we'll be discussing 
        concurrency control in conjunction
        with transaction management;

    *   indeed, defining a series of steps as a
        transaction is an important aspect of
	concurrency control;

*   As it relates to transactions,
    we'll also be discussing consistent
    database state;

    *   a consistent database state is one in which
        all data integrity constraints are satisfied;

    ...we expect transactions to change the contents
    of the database from one consistent state to
    another;

    ...in general, all transactions are controlled
    and executed by the DBMS to guarantee database
    integrity;

ASIDE - levels of support for database integrity

*   these can vary for different DBMS's;

*   you should be aware of these 4 levels:
    *   entity integrity
        *   if the DBMS requires "legal", unique
	    (and non-null) primary keys, then you
	    have this

        *   (the idea: every relation has a set
	    of attributes that uniquely determines
	    a row...)

    *   domain integrity
        *   a DBMS supports this if you can specify that
	    an attribute must be one of a particular
	    set of values (its domain) and that
	    restriction is enforced by the DBMS;

        *   can you restrict its type?
	    ...specify it as not null?
	    ...limit its value more specifically
	       than just a type (e.g., via check
	       clauses?)
            ...specify user-defined types?

        *   the more detail you can specify about
	    an attribute's domain (with DBMS support),
            the more domain integrity is supported;

    *   referential integrity
        *   a DBMS supports this if you can specify
	    a foreign key relationship, and
	    that referential constraint is subsequently
	    enforced by the DBMS

    *   transaction integrity
        *   a DBMS supports this if it supports
	    atomicity of transactions
	    (completely done, or as if never
	    started/not even begun)

        *   SQL provides the most basic support
            for this; some DBMS's may provide
	    additional support;

*   So, continuing with transactions...
    there are five (OK, 4 in some textbooks...)
    main database transaction properties --
    they make up the acronym ACIDS:

    *   atomicity
    *   consistency
    *   isolation
    *   durability
    *   serializability

    *   atomicity - just what we've already
        mentioned -- ALL the operations/parts/steps
	of a transaction must be completed; if
	not, the transaction is aborted;

    *   consistency
        (Connaly & Begg, pp. 553-554)
	"A transaction must transform the database
	from one consistent state to another
	consistent state"

        *   DBMS and application developers may
	    have to cooperate on this;
	*   DBMS, for its part, helps ensure
	    consistency by enforcing all of the
	    constraints that have been specified
	    on the database schema;

    *   isolation
        *   means that the data used during the
	    execution of one transaction cannot
	    be used by a second transaction until
	    the first is completed (at least, the
	    effect must be as if this was the
	    case...)

        *   if transactions are executed concurrently
	    (at the same time), then the PARTIAL
            effects of one transaction should not be
	    visible to other transactions;

        *   there are a number of kinds of "bad"
	    occurrences that can result when transactions
	    don't have this property...
	    dirty read/uncommitted dependency
	    nonrepeatable reads
	    phantom reads
	    etc...

    *   Durability
        *   indicates the permanence of the database's
	    consistent state;

        *   consistent stipulates that a transaction
	    transforms the database from one consistent
	    state to another;

	    durabililty stipulates that the database
	    maintains its consistent state BETWEEN
	    transactions;

            (won't lose them due to system failure,
	    power outage, natural disaster, etc.)

        *   It is the responsibility of a DBMS's
	    recovery subsystem to ensure durability;

    *   serializability
        *   you have this property when the concurrent
            execution of transactions is equivalent
	    to the case where the transactions 
	    executed serially in some arbitrary order

        *   ...we don't want anomalies due JUST to
	    the fact that the transactions were executed
	    concurrently;

	    ...we should get a result the same as
	    if they HAD executed serially... (in any
	    of the possible serial orderings...)

        *  another definition: a scheme for processing
	   concurrent transactions where this is 
	   achieved is said to be serializable

SQL's support for transaction atomicity:
the commit and rollback commands...!
*   commit;
    *   (a SQL command, note!)
    *   should permanently record all changes since
        the previous commit to your database;

*   rollback;
    *   (also a SQL command)
    *   should "roll" the database "back" to its
        previous consistent state -- to how it was
	at the time of that latest commit

*   (note: Oracle SQL*Plus does issue some
    commits on the user's behalf -- when you
    exit SQL*Plus, an auto-commit is done,
    and when you change the structure of the
    database -- dropping or creating tables,
    for example -- you also get an auto-commit

*   so -- if you do a commit before starting a
    transaction,
    then if something goes awry during the transaction,
       you can rollback, and it is as if the transaction
       never started -- just we want for atomicity!
    but if all goes well, you commit, and the transaction
       is "completely" done;

*   a few words on transaction logs,
    an example of one way to support durability

    *   a transaction log keeps track of all
        transactions that update the database,

        and can then be used for database recovery
	if some failure occurs;

    *  one approach:
       do a complete database save
       log each change that a transaction makes to
           the database, in chronological order
           (log the change, THEN make the change)
       FAILURE OCCURS!!
       if you recover using ROLLFORWARD
       *   the database is restored to the saved
           version
      *    roll forward only committed transactions --
           that is, redo the changes in the transaction
	   log, but only for COMMITTED transactions;
           
           (you don't redo actions of rolled back
	   transactions, OR of transactions that
	   were still in-progress, and hadn't 
	   been committed yet...)

       *   if you recover using ROLLBACK
           ...you would just UNDO changes made by
           incomplete transactions;
...continuing next Monday (more Oracle SQL stuff on
   Wednesday)