Database System

Tags

soft-eng

Is the idea of a large store of see page 3, data.

While programs in memory have rich data-types (arbitrary classes), with dynamically expandable data (linked lists or heap allocation) and support for object references through memory pointers, programs in databases have a slue of other concerns. The data has to be simply typed and stored into a pre-defined set of tables. These tables can grow to accommodate new records but cannot support new fields. The database must support quick lookups through the database using unique keys or indexes to quickly navigate to specific records.

Concurrent Availability

Is the common situation where multiple users access the same database [see page 21, simultaneously]. The sort of conflicts this can lead to include:

• Dirty Reads: user1 reads data as user2 is updating it but has not committed it. The data is read out of date.
• Non-repeatable Read: user1 runs a query twice as user2 is modifying the data. The query returns inconsistent data.
• Phantom Reads: user1 runs query twice as user2 is adding to the data. The query returns extra phantom data.
• Lost Update: user1 and user2 make simultaneous updates to the same data. The last commit is saved so the first update is lost.

Ways to mitigate this involve:

• Serialise all transactions All transactions are forced to execute in sequential order but the serial bottleneck reduces performance.
• Row/Page Locking Other transactions locked out while row is being updated. This can lead to a possible deadlock if transactions wait for each other.
• Deadlock Avoidance Aborts transactions if all locks cannot be obtained, but may abort and restart too many valid transactions.
• Deadlock Detection Uses a wait for graph to detect and break circular deadlocks by aborting an arbitrary transaction. Results in fewer aborts.
• Shared and Exclusive Locks Shared read-lock allows multi-user access for reading. Exclusive write-lock prevents further access while updating.
• Phantom and Intent Locks Phantom lock reserves an empty row for later insertion. Intent locks declares an intention to commit changes which become exclusive only during the commit phase. This may co-exist with shared locks.
• Time-stamping Each transaction is given a unique start-time and all rows are marked with their last-read and last-write timestamps. Transaction freely write any rows with earlier read/write stamps and read rows with later read stamps. But otherwise the transaction is aborted.