In this lecture we look at...
[Section notes PDF 86Kb]

• Transactions
• Unpredictable failure
• Commit and rollback
• Stored procedures
• Brief PL overview
• Cursors

• Real world database actions
• Rarely single step
• Flight reservation example
• Add passenger details to roster
• Charge passenger credit card
• Update seats available
• Order extra vegetarian meal

ACID test

• Atomicity
• transaction as smallest unit of processing
• transactions complete entirely or not at all
• consequences of partial completion in flight example
• Consistency
• complete execution preserves database constrained state/integrity
• e.g. Should a transaction create an entity with a foreign key then the reference entity must exist (see 4 constraints)

• Isolation
• not interfered with by any other concurrent transactions
• Durable (permanency)
• commited changes persist in the database, not vulernable to failure

• Notion of Commit (durability)
• Transaction failures
• From flight reservation example
• Add passenger details to roster
• Charge passenger credit card
• Update seats available: No seats remaining
• Order extra vegetarian meal
• Rollback

• Language format
• Declarations
• Execution
• Exceptions
• Handling I/O
• Functions
• Cursors

• Blocks broken into three parts
• Declaration
• Variables declared and initialised
• Execution
• Variables manipulated/actioned
• Exception
• Error raised and handled during exec
• 
  DECLARE
      ---declarations
  BEGIN
      ---statements
  EXCEPTION
      ---handlers
  END ;

• DECLARE
• age NUMBER;
• name VARCHAR(20);
• surname employee.fname%TYPE;
• addr student.termAddress%TYPE;

• BEGIN (not in order)
• /* sql_statements */
• UPDATE employee SET salary = salary+1;
• /* conditionals */
• IF (age < 0) THEN
• age: = 0;
• ELSE
• age: = age + 1;
• END IF;
• /* transaction processing */
• COMMIT; ROLLBACK;
• /* loops */ /* cursors */
• [END;] (if no exception handling)

• Beginnings of PL I/O
• CREATE TABLE temp (logmessage varchar(80));
• Can create transfer/bridge relation outside




• Within block (e.g. within exception handler)
• WHEN invalid_age THEN
• INSERT INTO temp VALUES( ‘Cannot have negative ages’);
• END;




• SELECT * FROM temp;
• To review error messages

• DECLARE
• invalid_age exception;
• BEGIN
• IF (age < 0) THEN
• RAISE invalid_age
• END IF;
• EXCEPTION
• WHEN invalid_age THEN
• INSERT INTO temp VALUES( ‘Cannot have negative ages’);
• END;

• Cursors
• Tuple by tuple processing of relations
• Three phases (two)
• Declare
• Use
• Exception (as per normal raise)

• PL blocks coherently change database state
• No runtime I/O
• Difficult to debug
• SQL tested independently

• DECLARE
• name_attr EMPLOYEE.NAME%TYPE;
• ssn_attr EMPLOYEE.SSN%TYPE;
• /* cursor declaration */
• CURSOR myEmployeeCursor IS
• SELECT NAME,SSN FROM EMPLOYEE
• WHERE DNO=1
• FOR UPDATE;
• emp_tuple myEmployeeCursor%ROWTYPE;

• BEGIN
• /* open cursor */
• OPEN myEmployeeCursor;
• /* can pull a tuple attributes into variables */
• FETCH myEmployeeCursor INTO name_attr,ssn_attr;
• /* or pull tuple into tuple variable */
• FETCH myEmployeeCursor INTO emp_tuple;
• CLOSE myEmployeeCursor;






• [LOOP…END LOOP example on handout]

• Concurrent transactions
• Distributed databases (DDB)
• Fragmentation
• Desirable transaction properties
• Concurrency control techniques
• Locking
• Timestamps

• Language
• PL too complex/long-winded
• Simplified database model
• Database as collection of named items
• Granularity, or size of data item
• Disk block based, each block X
• Basic transaction language (BTL)
• read_item(X);
• write_item(X);
• Basic algebra, X=X+N;

• DBMS Multiuser system
• Multiple terminals/clients
• Single processor, client side execution
• Single centralised database
• Multiprocessor, server
• Resolving many transactions simultaneously
• Concurrency issue
• Coverage by previous courses (e.g. COMS12100)
• PL/SQL scripts (Transactions) as processes
• Interleaved execution

• Two transactions, T₁ and T₂
• Overlapping read-sets and write-sets
• Interleaved execution
• Concurrency control required
• PL/SQL example
• Commit; and rollback;

• Three potential problems
• Lost update
• Dirty read
• Incorrect summary
• All exemplified using BTL
• Transaction diagrams to make clearer
• C-like syntax for familiarity
• Many possible examples of each problem

• T₁
• read_item(X);
• X=X-N;




• write_item(X);
• read_item(Y);




• Y=Y+N;
• write_item(Y);

• T₂




• read_item(X);
• X=X+M;




• write_item(X);

• T₁ X update overwritten

• T₁
• read_item(X);
• X=X-N;
• write_item(X);




• <T₁ fails>
• <T₁ rollback>




• read_item(X);
• X=X+N;
• write_item(X);

• T₂




• read_item(X);
• X=X+M;
• write_item(X);

• T₂ reads temporary incorrect value of X

• T₁




• read_item(X);
• X=X-N;
• write_item(X);




• read_item(Y);
• Y=Y-N;
• write_item(Y);

• T₂
• sum=0;
• read_item(A)
• sum=sum+A;




• read_item(X);
• sum=sum+X;
• read_item(Y);
• sum=sum+Y;

• T₂ sums after X-N and before Y-N

• Schedule S is a collection of transactions (T_i)
• Serial schedule S₁
• Transactions executed one after the other
• Performed in a serial order
• No interleaving
• Commit or abort of active transaction (T_i) triggers
  execution of the next (T_i+1)
• If transactions are independent
• all serial schedules are correct

• Serial schedules/histories
• No concurrency
• Unfair timeslicing
• Non-serial schedule S₂ of n transactions
• Serializable if
• equivalent to some serial schedule of the same n transactions
• correct
• n! serial schedules, more non-serial

• DDB, collection of
• multiple logically interrelated databases
• distributed over a computer network
• DDBMS
• Multiprocessor environments
• Shared memory
• Shared disk
• Shared nothing

• Distribution transparency
• Multiple transparency levels
• Network
• Location/dept autonomy
• Naming
• Replication
• Fragmentation
• Reliability and availability
• Performance, data localisation
• Expansion

• Breaking the database into
• logical units
• for distribution (DDB design)
• Global directory to keep track/abstract
• Fragmentation schema/allocation schema
• Relational
• Horizontal
• Derived (referential), complete (by union)
• Vertical
• Hybrid

• Multiple copies
• Failure of individual sites (hosts/servers)
• Failure of network/links
• Transaction processing
• Distributed commit
• Deadlock
• Primary/coordinator site - voting

• Coordinator elected
• Coordinator prepares
• writes log to disk, open sockets, sends out queries
• Process
• Coordinator sends ‘Ready-commit’ message
• Peers send back ‘Ready-OK’
• Coordinator sends ‘Commit’ message
• Peers send back ‘Commit-OK’ message

• Data transfer costs of query processing
• Local bias
• High remote access cost
• Vast data quantities to build intermediate relations
• Decomposition
• Subqueries resolved locally

• Must avoid 3+ problems
• Lost update, dirty read, incorrect summary
• Deadlock/livelock - dining example
• Data item granularity
• Solutions
• Protocols, validation
• Locking
• Timestamps

• Binary (two-state) locks
• locked, unlocked associated with item X
• Mutual exclusion
• Four requirements
• Must lock before access
• Must unlock after all access
• No relocking of already locked
• No unlocking of already unlocked

• Multiple mode locking
• Read/write locks
• aka. shared/exclusive locks
• Less restrictive (CREW)
• read_lock(X), write_lock(X), unlock(X)
• e.g. acquire read/write_lock
• not reading or writing the lock state

• Must hold read/write_lock to read
• Must hold write_lock to write
• Must unlock after all access
• Cannot upgrade/downgrade locks
• Cannot request new lock while holding one
• Upgrading permissable (read lock to write)
• if currently holding sole read access
• Downgrading permissable (write lock to read)
• if currently holding write lock