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).
- 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.
- Multiple users of centralized data can result in concurrent access to a single file, 2 major problems:
- 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
- Transactions affecting the same file must be executed on after the other, not concurrently. Two methods of enforcing serialization:
- Locking
- Granularity (size) of locks can vary:
- File
- Record
- Field
- Shared Locks (for reads) and Exclusive locks (for updates)
- Granularity (size) of locks can vary:
- Dealing with deadlocks
- Deadlock prevention protocol
- Processes must obtain all locks prior to execution
- Detection (DBMS lock manager)
- Resolution (Lock manager wait table)
- Deadlock prevention protocol
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.