DERBY-1000 java resource_error ai_generated partial

java.sql.SQLException: Lock time out. The lock request timed out because another transaction holds a conflicting lock.

ID: java/derby-lock-timeout

Also available as: JSON · Markdown · 中文
82%Fix Rate
85%Confidence
1Evidence
2024-01-20First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Apache Derby 10.14 active — — —
Apache Derby 10.15 active — — —
Apache Derby 10.16 active — — —

Root Cause

A database transaction is waiting for a lock held by another transaction and exceeds the configured lock timeout, typically due to long-running transactions or deadlocks in embedded Derby databases.

generic

中文

数据库事务正在等待另一个事务持有的锁，并超过了配置的锁超时时间，通常是由于嵌入式 Derby 数据库中的长时间运行事务或死锁。

Official Documentation

https://db.apache.org/derby/docs/10.15/ref/rrefexceptlocktimeout.html

Workarounds

  1. 80% success Set a shorter lock timeout using Derby system property derby.locks.waitTimeout=30 (in seconds) to fail fast and retry the transaction. 
    Set a shorter lock timeout using Derby system property derby.locks.waitTimeout=30 (in seconds) to fail fast and retry the transaction.
  2. 85% success Optimize transaction scope: commit or rollback transactions as early as possible to release locks. For example, in JDBC code, use try-with-resources to ensure auto-close and commit. 
    Optimize transaction scope: commit or rollback transactions as early as possible to release locks. For example, in JDBC code, use try-with-resources to ensure auto-close and commit.
  3. 75% success Use isolation level READ_COMMITTED instead of REPEATABLE_READ to reduce lock contention. Set connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED). 
    Use isolation level READ_COMMITTED instead of REPEATABLE_READ to reduce lock contention. Set connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED).

中文步骤

  1. Set a shorter lock timeout using Derby system property derby.locks.waitTimeout=30 (in seconds) to fail fast and retry the transaction.
  2. Optimize transaction scope: commit or rollback transactions as early as possible to release locks. For example, in JDBC code, use try-with-resources to ensure auto-close and commit.
  3. Use isolation level READ_COMMITTED instead of REPEATABLE_READ to reduce lock contention. Set connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED).

Dead Ends

Common approaches that don't work:

  1. Restart the database server or application 90% fail

    Restarting clears all locks but does not fix the underlying transaction logic that causes contention; the problem will recur.

  2. Increase the lock timeout to a very high value (e.g., 600 seconds) 70% fail

    A higher timeout delays the error but does not resolve the conflict; the transaction may still time out or cause performance degradation.

  3. Use a different database driver version 85% fail

    The lock timeout is a database-level behavior, not a driver issue; changing drivers does not affect locking mechanisms.