Checkpoints

Non-Blocking Fuzzy

Derby supports fuzzy checkpointing with slight variations from the ARIES implementation. Instead of storing an active transaction table and a dirty page table in checkpoints, Derby instead stores a few timestamps, including 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.

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, although not explicitly stated, 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.

Data Model

Relational

Derby is a relational database that supports SQL syntax.

Foreign Keys

Supported

Foreign key is implemented as one of the CONSTRAINT clauses. There are two levels of CONSTRAINTS, 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 return an error if the foreign key constraint is violated. The constraint check can be at statement execution or commit depending on the constraint mode.

Indexes

B+Tree

Derby implements standard B+ Tree algorithms with a few features:

1. It only uses exclusive latches on pages regardless of reading or modification of the page;

2. Node split is always left to right;

3. The system holds at most 2 latches simultaneously. During insertion, if there is no space for node splitting, all latches will be released, and Derby will do a split pass from top to bottom. After the split pass, Derby will redo the Insertion operation again.

Isolation Levels

Read Uncommitted Read Committed Serializable Repeatable Read

Derby supports four level of isolation: serializable, repeatable read, read committed and read uncommitted for both table-level and row-level locking. The isolation levels can be set by either JDBC methods or SQL statements. One thing to highlight is that under repeatable read isolation level, row-level locking may create repeatable read. However, table-level locking will not, because the entire table will be locked.

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.

Logging

Physical Logging

Derby implements Write Ahead Logging (WAL) similar to the ARIES design. One of the differences is that instead of saving Log Sequence Number (LSN) in the page data, it saves the page version numbers in both the page data and the log records, and compare them during recovery.

Derby implements page-level physical logging. For queries that involves more than one pages, the operation will first be converted to loggable actions for each page involved. Then the loggable actions will be used to generate physical logging on that page.

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.

Query Execution

Tuple-at-a-Time Model

There is no information on the specific execution model used.

Query Interface

SQL

Derby support core subset of SQL-92, and some features of SQL-99.

Storage Architecture

Hybrid

Derby mainly support on-disk database. It also provides in-memory database for testing and developing applications. By following backup procedures, in-memory database can be stored and be used as either an in-memory database or normal on-disk database at a later time.

Storage Model

N-ary Storage Model (Row/Record)

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

Storage Organization

Heaps

Derby stores data and index in containers, which has a one-to-one mapping with files. Within each container, there will be three types of pages -- header page, data page and allocation page. Data pages hold data in row order.

Stored Procedures

Supported

Derby support Java stored procedures using the syntax CREATE PROCEDURE. Several parameters will be passed in, such as parameter style (Java or Derby), data type and Java method name. Note that several data types, such as BLOB, CLOB, LONG and VARCHAR cannot be used in stored procedures.

System Architecture

Embedded

Using JDBC driver, the Derby database can be embedded in the application and runs inside the same JVM.

Views

Virtual Views

Derby provides virtual views which are not updatable. The command to create a view is CREATE VIEW.