Resolving SQLite Database Locking Issues in Lazarus with Zeos Components

Issue Overview: Database Locking During Sequential Transactions in Lazarus

The core issue revolves around encountering a "Database Locked" error when performing sequential database operations (specifically, a SELECT followed by an INSERT) in a Lazarus application using the Zeos library with SQLite. The error occurs despite the absence of multithreading, network storage (NAS), or concurrent processes. The problem is isolated to the second table (T2), while operations on the first table (T1) work without issues. Key observations include:

1. Transactional Boundaries: The code explicitly starts and commits transactions around both the SELECT and INSERT operations.
2. Query Closure: Queries are closed after execution, which should release associated locks.
3. IDE Configuration: The TZConnection component is left open in the Lazarus IDE designer, potentially holding a persistent connection.
4. Zeos Library Behavior: The library’s internal handling of transactions and locks might differ from SQLite’s native behavior, leading to unexpected locking.

The resolution involved adding an explicit Commit after closing the query, suggesting that implicit transaction management by Zeos was insufficient in this scenario. The error message ("Database Locked") is inconsistent with SQLite’s native "Database Busy" error, implying that Zeos or the Lazarus IDE might be introducing additional locking mechanisms.

Possible Causes: Transaction Management, Component State, and Journal Mode

1. Implicit Transaction Retention by Zeos

• Uncommitted Read Transactions: Even after closing a query, Zeos might retain a read transaction if not explicitly committed. SQLite’s read transactions acquire a SHARED lock, which blocks writers until released.
• Auto-Commit Discrepancies: Zeos may default to manual transaction control, requiring explicit Commit calls even for read operations. Unlike SQLite’s default auto-commit mode, where each statement is a transaction, Zeos might bundle operations into larger transactions.

2. IDE-Designer Connection Leaks

• Persistent TZConnection in Designer: Leaving TZConnection.Active := True in the Lazarus IDE designer can create a persistent connection to the database. This connection may hold a SHARED lock indefinitely, blocking writes from the runtime application.
• Design-Time Queries: Metadata queries executed by the IDE (e.g., to populate table/column lists) might not release locks promptly, especially if DesignConnection is misconfigured.

3. SQLite Journal Mode and Locking Semantics

• Rollback Journal (DELETE Mode): In the default journal mode, SQLite uses a RESERVED lock during writes, which blocks other writers but allows readers. However, if a read transaction is still active (due to uncommitted transactions), the RESERVED lock cannot upgrade to an EXCLUSIVE lock, causing "Database Busy" errors.
• WAL Mode Differences: If the database uses Write-Ahead Logging (WAL), concurrent reads and writes are allowed, but Zeos might not fully support WAL’s locking semantics.

4. Component Lifecycle and Resource Leaks

• Unclosed Dataset Handles: While the code explicitly closes ZQ_getmax, other components (e.g., ZQ_insert_img) might retain prepared statements or cursors, keeping the database locked.
• Transaction Isolation Levels: Zeos’ default isolation level (tiNone) might conflict with SQLite’s locking model, especially when nested transactions are involved.

Troubleshooting Steps, Solutions & Fixes

Step 1: Validate Transaction Boundaries and Commit Semantics

1. Explicit Commit After Reads:

   • Problem: The SELECT query acquires a SHARED lock. If Zeos does not auto-commit read transactions, the lock persists until the connection is closed or the transaction is committed.
   • Solution:

     datamod.dmHuffin.ZConnection1.StartTransaction;
     try
       datamod.dmHuffin.ZQ_getmax.Open;
       // ... process results ...
     finally
       datamod.dmHuffin.ZQ_getmax.Close;
       datamod.dmHuffin.ZConnection1.Commit;
     end;
     

   • Rationale: Ensures the read transaction is committed immediately, releasing the SHARED lock.

2. Avoid Implicit Transactions:

   • Problem: Zeos might implicitly start transactions for operations that do not require them.
   • Solution: Set ZConnection1.AutoCommit := True for read-only operations or single-statement writes.

Step 2: Diagnose and Mitigate IDE-Related Locking

1. Disable Design-Time Connections:

   • Problem: The IDE’s design-time connection holds a SHARED lock.
   • Solution:
     • Set TZConnection.DesignConnection := False.
     • Set TZConnection.Active := False at design time.
   • Verification: Close and reopen the Lazarus IDE to ensure no lingering connections.

2. Separate Design-Time and Runtime Connections:

   • Problem: Metadata queries (e.g., fetching table schemas) might lock the database.
   • Solution: Use a separate SQLite database file for design-time exploration.

Step 3: Configure SQLite Journal Mode and Locking Parameters

1. Switch to WAL Mode:

   • Problem: The default rollback journal mode (DELETE) creates writer-blocking scenarios.
   • Solution:

     PRAGMA journal_mode = WAL;
     

   • Effect: Allows concurrent reads and writes, reducing contention.

2. Set Busy Timeout:

   • Problem: SQLite immediately returns "Database Busy" instead of retrying.
   • Solution:

     datamod.dmHuffin.ZConnection1.ExecSQL('PRAGMA busy_timeout = 5000;');
     

   • Rationale: Instructs SQLite to retry locked operations for up to 5 seconds.

Step 4: Audit Component State and Resource Management

1. Force-Close All Queries:

   • Problem: Cached prepared statements or unclosed result sets may retain locks.
   • Solution:

     datamod.dmHuffin.ZQ_getmax.Close;
     datamod.dmHuffin.ZQ_insert_img.Close;
     datamod.dmHuffin.ZConnection1.Disconnect;
     

   • Verification: Use SQLite’s sqlite3_temp_directory or utilities like lsof (Linux) or Process Explorer (Windows) to check for open handles.

2. Enable Zeos Debug Logging:

   • Problem: Lack of visibility into Zeos’ internal operations.
   • Solution:

     ZConnection1.LoginPrompt := False;
     ZConnection1.Protocol := 'sqlite-3';
     ZConnection1.LogEvents := [logExec, logError, logStmt];
     ZConnection1.LogFile := 'zeos.log';
     

   • Analysis: Check the log for uncommitted transactions or unexpected lock acquisitions.

Step 5: Refactor Code to Use Native Lazarus SQLite Components

1. Replace Zeos with TSQLite3Connection:

   • Problem: Zeos’ abstraction layer might introduce locking quirks.
   • Solution:

     uses SQLite3Conn, SQLDB;
     var
       Conn: TSQLite3Connection;
       Tran: TSQLTransaction;
       Query: TSQLQuery;
     begin
       Conn := TSQLite3Connection.Create(nil);
       Tran := TSQLTransaction.Create(nil);
       Query := TSQLQuery.Create(nil);
       try
         Conn.DatabaseName := 'mydb.sqlite';
         Conn.Transaction := Tran;
         Query.Database := Conn;
         Query.SQL.Text := 'INSERT INTO image (...) VALUES (...)';
         Query.ExecSQL;
         Tran.Commit;
       finally
         Query.Free;
         Tran.Free;
         Conn.Free;
       end;
     end;
     

   • Advantage: Native components adhere closer to SQLite’s default behavior.

2. Implement Busy Retry Logic:

   • Problem: "Database Busy" errors require manual retries.
   • Solution:

     var
       Retries: Integer;
     begin
       Retries := 0;
       while Retries < 3 do
       try
         Query.ExecSQL;
         Tran.Commit;
         Break;
       except
         on E: ESQLDatabaseError do
           if E.ErrorCode = 5 then // SQLITE_BUSY
           begin
             Sleep(100);
             Inc(Retries);
           end
           else
             Raise;
       end;
     

Step 6: Validate File System Permissions and Antivirus Interference

1. Check File Write Permissions:

   • Problem: The SQLite database or its journal/WAL files might have restrictive permissions.
   • Solution: Grant full read/write access to the database directory for the application’s user account.

2. Exclude Database Files from Antivirus Scans:

   • Problem: Real-time antivirus scanning can lock files intermittently.
   • Solution: Add the database directory to the antivirus exclusion list.

By systematically addressing transaction boundaries, IDE configuration, journal modes, and component lifecycle management, developers can resolve SQLite locking issues in Lazarus-Zeos applications. The key takeaway is that ORM layers like Zeos often abstract database interactions in ways that conflict with SQLite’s lightweight locking model, necessitating explicit transaction control and diagnostic logging.