Week 10 saw us move away from the more technical SQL and Indexing topics to ‘Database administration, Transaction management and Concurrency Control’.

Responsibilities of DBA:

  • Control of redundancy
    • Use of standards for modelling
    • Maintaining data dictionary
    • Ensuring sufficient normalization
    • Managing sharing of data  between multiple applications
  • Control of Integrity Constraints
    • Defining appropriate integrity constraints
    • Ensuring applications meet with the impose constraints
    • Ensuring applications cannot subvert constraints
  • Control of central data security
    • Provision relevant user views
    • Issuing passwords
    • reviewing physical security
    • design and monitoring of back up procedures

Also important duties are:

  • Query Optimization
  • Clusters, Indexes

Manoj then continued into the nature transactions, crashes and concurrency control. A quick summary below:

  • Transactions
    • A single or number of processes involving database accesses that constitutes one single logical action (i.e. bank transfer). Atomic (indivisible, can’t separate processes), Durable (upon completion, stored permanently).
    • Transactions can be committed or aborted (rollback, utilizing before-image log)
    • Checkpoints are periodic (usually 15-20mins) batch writes of transactions to disk.
  • Soft Crashes
    • I.e. Power failure, where system resets but no physical damage.
    • After soft crash, secondary memory data is loaded, then transaction log is referred to and transaction missing from secondary memory are either:
      • Redone (REDO), if the transaction was completed and can be re-completed this will be done
      • Undone (UNDO) if the transaction was not completed (committed) in the log then it must be undone (transactions are atomic).
source: week 10 lecture notes

  • Hard Crashes
    • I.e. Fire, physical damage to secondary storage devices and complete re installation and importing of backup data required.
  • Check Points
    • Created when transactions are written to secondary memory
  • Commits
    • When a transaction has been completed in entirety
  • Logs
    • Serial files storing transaction information needed for REDO and UNDO operations
  • Concurrency Control
    • Multiple users of centralized data can result in concurrent access to a single file, 2 major problems:
      • The lost Update – 2 concurrent updates can result in one of the updates being ‘lost’
      • Dirty Read – In the case of concurrent access to a file and rollback occurring. The rollback can be lost if a concurrent user updates based on the pre-rollback data.
  • Solution: Serialization
    • Transactions affecting the same file must be executed on after the other, not concurrently. Two methods of enforcing serialization:
      • Locking of records
      • Time-stamping transactions
  • Locking
    • Granularity (size) of locks can vary:
      • File
      • Record
      • Field
    • Shared Locks (for reads) and Exclusive locks (for updates)
  • Dealing with deadlocks
    • Deadlock prevention protocol
      • Processes must obtain all locks prior to execution
      • Detection (DBMS lock manager)
      • Resolution (Lock manager wait table)

Have been working on the SQL assignment for this subject and although I am able to get an Oracle XE server working on my home network, I am unable to connect to Monash’s zebra.its.monash.edu.au server using VPN. I had better just do the assignment on my home network first as I am wasting a lot of time trying to connect through the VPN.