Storage Model

N-ary Storage Model (Row/Record)

Derby implements row-based storage model. Rows corresponds to records in data pages.

Foreign Keys

Supported

Foreign key is implemented as one of the CONSTAINT clauses. There are two levels of CONSTAINTS, column level and table level. Foreign key constraint in a column level enforces that the values in the column must corresponds to the values in the referenced column marked as primary key or unique key. Table level constraint works similarly, but it is for multiple columns.

Insert, update or delete instructions will be rejected with a statement exception if the foreign key constraint is violated. The constraint check can be at statement execution or commit depending on the constraint mode. (IMMEDIATE or DEFERRED).

Isolation Levels

Read Uncommitted Read Committed Serializable Repeatable Read

It supports all four level of isolations. Isolation levels only differ for SELECT statements. They behave the same for other operations.

Concurrency Control

Two-Phase Locking (Deadlock Detection)

There are two scopes of locking (table-level and row-level), three types of locks (exclusive, shared and update) and four different types of transaction isolation levels. The locking strategies for different combination of scopes, lock types and isolation levels are different. In general, Derby implements strict two-phase locking. Exclusive locks will be held until a transaction aborts or commits; shared lock, instead, will be released after the reading of the rows finish (except for specific isolation levels) Derby also supports deadlock detection. When a deadlock is detected, the transaction that holds the least number of locks will be aborted.

Views

Virtual Views

Query Compilation

Code Generation JIT Compilation

Derby parses the prepared statement using Javacc and generates the Java binary code directly. JIT complier is supported, so that after several executions, JIT compiler will compile it to native code for performance improvement.

Indexes

B+Tree

Derby implements standard B+ Tree algorithms. It stores keys in leaf pages only. The B+ Tree supports page-level latching. Derby uses only exclusive latches, not shared latches in B+ tree implementation.

Checkpoints

Non-Blocking Fuzzy

Derby supports fuzzy checkpointing, with slight variances from the ARIES implementation. Instead of storing active transaction table and dirty page table in checkpoint, it instead stores a few timestamps, which include the checkpoint start time and the earliest start time of ongoing transaction when the checkpoint starts. For example, if transaction T1, T2 and T3 are not finished when the checkpoint starts, then the earliest start time of the three will be recorded in the checkpoint.

When it comes to recovery, the system will first find the nearest checkpoint. Using the earliest start time of ongoing transactions, it will iterate through the log and find all the active transactions and dirty pages at the checkpoint, and redo or undo accordingly.

Storage Architecture

Hybrid

Derby mainly support on-disk database. It also provides in-memory database for testing and developing applications.

Logging

Physiological Logging

Derby implements a combination of physical and logical logging. For actions on the same page, it uses physical logging. For BTree operations, which might affect several pages, it uses physical redo and logical undo.

Joins

Nested Loop Join Hash Join

Derby provides two types of join strategies -- nested loop and hash join. Nested loop join is more preferable in most cases. Hash join is preferred when inner table values are unique and outer table have many qualifying rows. Also, when the system estimates that the amount of memory required for hash join exceeds the amount available, nested loop will be used.

Data Model

Relational

Query Interface

SQL

Query Execution

Materialized Model

Subqueries can only be materialized if they not correlate with outer queries, and return one row. For subqueries that cannot be flattened (DISTINCT), optimization can be made on subqueries such as using Hash Join.

Stored Procedures

Supported

Derby support Java stored procedures.