Understanding Apache Derby Architecture

Embedded vs. Network Mode

Derby can operate in two modes:

• Embedded mode: Runs inside the application JVM, tightly coupled to the lifecycle of the application.
• Network Server mode: Allows remote connections via JDBC over TCP, similar to traditional RDBMS systems.

Each mode has distinct behavior around locking, connection reuse, and multi-threading.

Locking and Transaction Semantics

Derby uses table- or row-level locks to manage concurrency. Poor transaction handling or unclosed connections can cause lock contention and table-wide blocks.

Common Issues in Production

1. "Database is in use by another process" Errors

This occurs in embedded mode when multiple JVMs or threads attempt to open the same database without proper coordination.

2. Unreleased Locks and Deadlocks

Improper connection or statement closure can leave dangling locks that block subsequent operations.

3. Corrupted Database Files

Unexpected crashes or improper shutdowns (like JVM kill -9) can result in log file corruption, especially in write-heavy apps.

4. Connection Leaks in Embedded Mode

Applications with long uptimes and no connection pool can easily leak connections, leading to out-of-memory errors or stale transactions.

Diagnostics and Monitoring

Enable Derby Logging

Set the following system properties:

-Dderby.stream.error.file=derby.log
-Dderby.language.logStatementText=true

Review derby.log for stack traces, lock waits, and failed transactions.

Use SYSCS_DIAG Diagnostic Tables

These built-in system procedures provide insights into lock states and transactions:

SELECT * FROM SYSCS_DIAG.LOCK_TABLE;
SELECT * FROM SYSCS_DIAG.TRANSACTION_TABLE;

Check for Open Connections

Use JMX or profiling tools to detect JDBC connections that remain open for extended durations.

Step-by-Step Fixes

1. Resolve Lock Contention

Explicitly commit or rollback transactions. Example:

Connection conn = DriverManager.getConnection(url);
conn.setAutoCommit(false);
// execute queries
conn.commit();
conn.close();

Always close ResultSets and PreparedStatements.

2. Restart Database in Safe Mode

If derby.log reports corruption, run in recovery mode:

java -Dderby.system.durability=test org.apache.derby.tools.ij

This disables flushing but allows inspection. Use with caution.

3. Migrate to Network Server Mode

In shared environments, start Derby as a server:

java -jar derbyrun.jar server start

Connect using:

jdbc:derby://localhost:1527/mydb;create=true

This isolates app lifecycle from the database.

4. Use Connection Pooling

Wrap Derby connections in a pool like Apache DBCP or HikariCP. This prevents leaks and improves resource reuse.

Best Practices

• Use shutdown=true in JDBC URL to cleanly shut down the database.
• Never share an embedded database across JVMs or threads without synchronization.
• Monitor connection lifetimes via logs or JMX.
• Schedule regular backups using SYSCS_UTIL.SYSCS_BACKUP_DATABASE.
• Enable foreign keys and constraints explicitly—they are not enforced by default.

Conclusion

Apache Derby offers a simple and embeddable database engine for Java applications, but demands care in production scenarios. Locking issues, file corruption, and connection leaks can undermine data integrity and uptime. By proactively configuring diagnostics, using pooled connections, and respecting Derby's operational boundaries, teams can safely embed Derby into robust, long-lived applications.

FAQs

1. Why does Derby fail with "Database already in use"?

This usually happens in embedded mode when another JVM has already opened the database. Use Network Server mode to avoid this limitation.

2. How can I recover a corrupted Derby database?

Inspect derby.log for clues, run in recovery mode, or restore from a backup. Forensic inspection using SYSCS_DIAG tables may help isolate damaged tables.

3. Can Derby be used with connection pools?

Yes. Use standard JDBC pooling libraries, but ensure proper shutdown and cleanup since Derby handles file locks tightly.

4. What is the best isolation level to avoid deadlocks?

Read Committed is the default and safest. Avoid Serializable unless absolutely necessary—it increases lock contention in embedded environments.

5. Is Derby suitable for multi-user applications?

Only in Network Server mode. In embedded mode, concurrent access must be tightly controlled to avoid corruption and conflicts.