» Database/SQL Illustrations
By ivc at 23:24, March 31, 2010
Databases and SQL can be hard to grasp by just reading text, as I learned. To visualize two concepts I made a quick vector illustration for both; log based recovery and two-phase locking. A quick glaze:
Log based recovery – To ensure consistency after a crash or interruption of a transaction (bunch of queries), log files are used to check what made it from the cachemanager to the physical disk before the interruption. All committed translations (in the log but not database/disk) are normally redone, while transactions with only a start marker is reverted/undo. This is the basis for 4 concepts of log based recovery; undo/redo, no-undo/redo, undo/no-redo, and no-undo/no-redo.
Two-Phase Locking – For consistency during a transaction the order of which reads and writes are made to a row/table/database is essential. Locking makes it possible to restrict access to (lock) a row/table/database and have exclusive right to (exclusive lock) write a value, excluding everyone else until the restricting is lifted (unlock). Likewise when a exclusive lock is active, no-one can read the row/table/database. Lastly, if only a read is made a simple shared lock is used, other transactions can also read the same row/table/database at the same time, placing more shared locks. Once a transaction wants to write, an exclusive lock is requested.
For structure and order all transactions need to lock in the beginning and unlock before finishing, this creates a phase system. Phavse 1 is the start and phase 2 finishing the transation. A transaction changes phase once a lock is released or lock is downgraded from exclusive to shared. In phase 2 new locks can not be requested and shared locks can not be upgraded, thus the transaction is in the unlock/falling phase. The opposite is phase 1 where locks can be made and shared can be upgraded to exclusive.
The illustrations: