Blog › ICP guides

Xojo developer on retainer: SQLite SQLITE_BUSY silent failure, REALbasic developer, REAL Studio developer on monthly retainer

October 9, 2026 · ~15 min read

A Xojo developer was maintaining a point-of-sale application for a small retail chain with three locations, each with two POS terminals — six terminals total. The application was built in Xojo 2017r3 using the old REALSQLiteDatabase API (now called SQLiteDatabase). All six terminals connected to the same SQLite database file (\\nasserver\pos\sales.db) on a Synology NAS over SMB. The developer had tested the app with two terminals running simultaneously in the office and never observed a problem. During peak retail hours on Saturday afternoons, the weekly sales reconciliation consistently showed 3–5 missing sale records per week: a cashier at Terminal 2 would complete a transaction, the receipt would print, the post-sale screen would return to the idle state, but when the manager pulled the end-of-day report those sales were absent from the database totals. The Xojo app showed no error dialog. The receipt printed normally. The cashier had no indication whatsoever that the record had not been saved to the database. Silent sale records lost per busy week: 3–5 → 0 after adding db.Execute("PRAGMA busy_timeout = 3000") immediately after db.Connect and adding explicit db.Error checking after every db.Execute write call.

The root cause was a two-part failure in Xojo’s old database API combined with SQLite’s default concurrency behavior. SQLite’s default busy_timeout is 0 milliseconds — when two Xojo terminals simultaneously attempt a write to the same .db file, the second terminal’s SQLite connection immediately receives SQLITE_BUSY (error code 5) without retrying. SQLite returns SQLITE_BUSY when the database file is locked by another writer; with busy_timeout = 0, there is zero retry time, and the write fails instantly. The critical second part of the failure: Xojo’s old REALSQLiteDatabase / Database API (pre-2019 Xojo framework) does not raise an exception when db.Execute(insertSQL) encounters a SQLite error. Instead it silently sets db.Error = True and populates db.ErrorMessage = "database is locked". The developer had never checked db.Error after db.Execute calls because during development testing — single machine, no concurrency — db.Error was always False. The INSERT was not retried. The sale record was never written to sales.db. The cashier saw the normal post-sale screen. The receipt printer fired because receipt printing was triggered before the database write was confirmed. The missing record surfaced only in the weekly sales reconciliation.

The concurrency window that triggers the collision is narrow but reliably hit during busy retail periods. When Terminal 1 is in the middle of writing a sale record (a single SQLite BEGIN IMMEDIATE / INSERT INTO sales ... / COMMIT transaction), and Terminal 2 attempts its own BEGIN IMMEDIATE within that window, SQLite’s file-level write lock is held by Terminal 1’s connection. Terminal 2’s sqlite3_step() call returns SQLITE_BUSY immediately because the default busy_timeout of 0ms means SQLite does not loop-sleep waiting for the lock to clear. On a Synology NAS over SMB, the SQLite locking protocol relies on POSIX-style byte-range locks on the .db file, which adds SMB round-trip latency to every lock acquire and release cycle — a Terminal 1 commit that takes 8ms on a local SSD can take 40–80ms over SMB, dramatically widening the collision window during which Terminal 2’s concurrent write attempt will encounter SQLITE_BUSY. The developer’s office test with two terminals on a local network switch did not reproduce the issue because the local network latency was an order of magnitude lower than the NAS SMB latency in the retail store.

Setting PRAGMA busy_timeout = 3000 changes SQLite’s behavior from “fail immediately on SQLITE_BUSY” to “retry for up to 3000 milliseconds before returning SQLITE_BUSY.” SQLite’s busy handler uses an exponential back-off sleep: 1ms, 2ms, 5ms, 10ms, 15ms, 25ms, … up to 100ms per sleep cycle, retrying until the timeout is exhausted. For a typical retail POS write transaction that completes in 40–80ms over SMB, a 3000ms busy timeout gives 37–75 retry cycles — more than sufficient to wait out any realistic lock contention between two terminals writing simultaneously. After setting the timeout, the second terminal’s write succeeds on the first or second retry. The sale record is written. Both terminals confirm the sale. Zero silent losses during peak Saturday hours. The second part of the fix — adding explicit db.Error checking after every db.Execute call — ensures that any future SQLite error (including a SQLITE_BUSY that exceeds the 3000ms timeout under extreme sustained load) is caught, logged to a local error file, and surfaced as a dialog to the cashier rather than silently discarded.

REALbasic, REAL Studio, and the Xojo platform lineage

Xojo’s history spans nearly three decades under three names. REALbasic 1.0 shipped for Mac OS in 1998, founded by Andrew Barry in Austin, Texas. REALbasic was a Visual Basic-inspired RAD (rapid application development) environment for the Mac, addressing a market gap left by Apple’s discontinuation of HyperCard development: it offered a drag-and-drop UI designer, an event-driven BASIC-derived programming language, and compiled Mac applications without requiring knowledge of C or the Mac Toolbox. REALbasic 3.0 (2001) added Windows compilation from the same Mac project file, making it the first cross-platform RAD tool to generate native Mac and Windows executables from a single codebase without code changes for the majority of applications. REALbasic 5.0 (2003) added Linux as a compile target. Database access in early REALbasic used the REALSQLiteDatabase class (wrapping SQLite 2.x), REALbasicDatabase (the proprietary REALbasic flat-file database), and plugin-based ODBC, MySQL, and PostgreSQL connectors.

In 2011, Real Software (the company behind REALbasic) rebranded the product as REAL Studio, reflecting the expansion to a full development suite (IDE + debugger + profiler + build server). The language and framework remained backward-compatible with REALbasic projects. REAL Studio added iOS as a deployment target (REAL Studio 2012) and improved the SQLite integration by upgrading the bundled SQLite library and adding the SQLiteDatabase class alongside the legacy REALSQLiteDatabase class. In 2013, Real Software rebranded again: the product became Xojo, the company became Xojo Inc., and the IDE was redesigned with a new project navigator and code editor. The Xojo name reflected the company’s ambition to position the product as a modern cross-platform development environment beyond its BASIC-language RAD tool origins. Current Xojo (2024) supports macOS (.app), Windows (.exe), Linux (ELF binary), iOS (via Xojo iOS SDK, compiled through Xcode), Android (via Xojo Android SDK), Web (Xojo Web applications running on a Xojo web server), and Console (command-line applications). The cross-platform parity — a single Xojo project compiled to six target platforms without platform-specific code for the majority of business logic — is the primary driver of Xojo adoption for independent software vendors and internal IT departments with small development teams.

The database API transition within Xojo itself is the critical version boundary for retainer developers diagnosing legacy deployments. The classic Xojo framework (REALbasic through Xojo 2018) uses REALSQLiteDatabase (deprecated name) or SQLiteDatabase with the silent-error model: db.Execute(sql) sets db.Error = True and db.ErrorMessage on failure without raising an exception. db.SQLSelect(sql) returns Nil on failure (with db.Error = True) instead of raising an exception. This API design originated in the REALbasic era when exception-based error handling was not idiomatic in BASIC-derived languages; the consequence is that every call site must explicitly check db.Error after every database operation, and applications written by developers who did not know to do this — especially those who tested only in single-user scenarios — contain silent error paths throughout their database interaction code. The new Xojo framework (Xojo 2019r2+, also called the “Xojo framework” as opposed to the “MBS framework” naming convention used in the community) changed the database API to raise DatabaseException on error: db.ExecuteSQL(sql) raises DatabaseException if the SQL fails, and db.SelectSQL(sql) raises DatabaseException on failure. The new API makes it significantly harder to accidentally ignore database errors because the exception propagates up the call stack if no Try/Catch DatabaseException is present.

SQLite itself has been the primary embedded database in Xojo applications since REALbasic 5. The bundled SQLite library in Xojo tracks the upstream SQLite releases: Xojo 2017r3 bundled SQLite 3.18.0. SQLite’s SQLITE_BUSY semantics have been consistent since SQLite 3.0: when a connection attempts to acquire a write lock and the lock is held by another connection, SQLite calls the registered busy handler. If no busy handler is registered and no PRAGMA busy_timeout has been set (or if it has been set to 0), SQLite returns SQLITE_BUSY immediately from the blocking API call. The PRAGMA busy_timeout = N statement registers SQLite’s built-in busy handler, which sleeps and retries for up to N milliseconds. The sqlite3_busy_timeout(db, ms) C API call does the same. In the Xojo SQLite integration, PRAGMA busy_timeout is the correct mechanism because Xojo does not expose the sqlite3_busy_handler callback registration API. Setting it requires executing the PRAGMA as a SQL statement via db.Execute("PRAGMA busy_timeout = 3000") (old API) or db.ExecuteSQL("PRAGMA busy_timeout = 3000") (new API) immediately after db.Connect.

SQLite on network drives: WAL mode, SMB locking, and write serialization

SQLite’s use on network file systems (SMB, NFS, AFP) is a well-documented limitation. SQLite’s locking protocol uses byte-range file locks on the .db file to coordinate concurrent access between multiple processes. On a local NTFS or HFS+ volume, byte-range lock acquisition and release round-trips complete in under 1ms. On an SMB share over a 1 Gbps LAN, the same byte-range lock round-trip involves a network packet exchange with the SMB server; on a Synology NAS with multiple simultaneous clients, this round-trip is 5–20ms under low load and 40–80ms under concurrent write load from multiple terminals. This latency amplifies the SQLITE_BUSY collision window: a write transaction that on a local disk would hold the write lock for 2ms holds it for 50ms over SMB, making simultaneous write attempts from two terminals far more likely to overlap. SQLite’s FAQ explicitly warns against using SQLite on NFS and SMB network shares for concurrent write workloads; however, for small retail deployments where a centralized database server is unavailable or the client cannot justify SQL Server or PostgreSQL licensing, the shared-SMB-SQLite pattern is extremely common in Xojo applications built by independent consultants. The correct advisory for these deployments is: set busy_timeout = 3000 (minimum), check db.Error after every write, and consider migrating to WAL mode (PRAGMA journal_mode = WAL) to improve concurrent read performance.

WAL mode (Write-Ahead Logging) is SQLite’s alternative journal mode introduced in SQLite 3.7.0. In the default DELETE journal mode, SQLite uses a rollback journal and exclusive write locks that block all concurrent readers and writers for the duration of a write transaction. In WAL mode, writers write to a separate WAL file rather than modifying the main database file in place; readers read from the main database file (and optionally the WAL) without blocking writers, and writers proceed without blocking readers. The critical point: WAL mode improves read concurrency (readers never block on a writer), but it does not improve write concurrency — SQLite WAL mode still allows only one writer at a time, and concurrent write attempts still encounter SQLITE_BUSY. Setting PRAGMA journal_mode = WAL in a Xojo POS application reduces the frequency of read/write collisions (cashier queries during a concurrent write no longer block) but does not eliminate write/write SQLITE_BUSY collisions. WAL mode also has implications for network file systems: the WAL file (sales.db-wal) and the shared-memory file (sales.db-shm) must be accessible to all connecting processes, which requires that the NAS share expose all three files and that the SMB client correctly implements the shared-memory mapping protocol. On Synology NAS, WAL mode over SMB works reliably with the NAS firmware updated to DSM 7.0+ and the SMB 3.0 protocol enabled. The correct full configuration for a Xojo POS on a Synology NAS: immediately after db.Connect, execute PRAGMA busy_timeout = 3000 followed by PRAGMA journal_mode = WAL, and check db.Error after each.

The alternative architectural fix — recommended for deployments that grow beyond six terminals or that experience sustained high-volume concurrent writes — is to migrate from SQLite to a client-server database. Xojo supports MySQL, MariaDB, PostgreSQL, Microsoft SQL Server, and Oracle through native database plugins bundled with Xojo or available through third-party Xojo plugins (notably the MBS Xojo SQL Plugin and the Valentina DB plugin). A client-server database handles concurrent write serialization at the server layer with proper transaction isolation, connection pooling, and row-level locking that eliminates the file-lock contention that makes SQLite-on-SMB unreliable for concurrent multi-terminal write workloads. For a six-terminal retail POS, a PostgreSQL instance running on the Synology NAS itself (available via the Synology Package Center) would handle the write concurrency without any application-level busy_timeout or WAL mode configuration. The migration path in Xojo from SQLiteDatabase to a PostgreSQLDatabase or MySQLCommunityServer connection requires changing the database class instantiation, updating the connection parameters (db.Host, db.Port, db.DatabaseName, db.UserName, db.Password), and reviewing SQL syntax differences between SQLite and the target database (SQLite’s type affinity system differs from PostgreSQL’s strict typing; AUTOINCREMENT syntax differs; PRAGMA statements are SQLite-specific and must be replaced with server-side configuration).

Typical Xojo developer retainer work and what it looks like in a work log

SQLite busy_timeout and db.Error audit for a multi-terminal POS application is the canonical Xojo invisible production data-loss bug. The pattern is consistent across Xojo POS deployments: the application opens a REALSQLiteDatabase (old API) connection to a .db file on a NAS share; the SaleComplete event handler (or equivalent) calls db.Execute(insertSQL) to write the sale record; neither PRAGMA busy_timeout nor db.Error checking was added because development testing used a single terminal with no concurrent writes; in production with 2–6 terminals during Saturday peak hours, concurrent write attempts trigger SQLITE_BUSY; db.Execute silently sets db.Error = True; the INSERT is not retried; the sale record is lost. Fix: add db.Execute("PRAGMA busy_timeout = 3000") immediately after db.Connect; add if db.Error then MsgBox "Database error: " + db.ErrorMessage; end if after every db.Execute write call. Sale records lost per busy week: 3–5 → 0. Work log entry: “POSApp.xojo_project; Xojo 2017r3, old REALSQLiteDatabase API; db.Execute INSERT INTO sales on SaleComplete event; db.Error not checked; no busy_timeout set; \\nasserver\pos\sales.db on Synology NAS via SMB; 6 terminals, 2 per location; SQLITE_BUSY on concurrent Saturday writes; 3–5 sale records silently lost per week; fix: PRAGMA busy_timeout = 3000 after db.Connect; if db.Error checks after every db.Execute write; WAL mode enabled; sale records lost after fix: 0; 3h.”

Threading and UI-thread database access race is the second most common Xojo retainer pattern in desktop applications with background data processing. A Xojo laboratory instrument control application (built for a clinical diagnostics company in REAL Studio 2012r2) uses a Timer with a 500ms interval to poll the instrument serial port and INSERT new measurement readings into a local SQLiteDatabase. The application crashes intermittently with App.UnhandledException during high-frequency instrument output bursts, typically when the user is simultaneously navigating the results grid (which triggers a db.SQLSelect query to the same SQLiteDatabase connection). Root cause: Xojo’s SQLiteDatabase connection object is not thread-safe; the Timer event handler fires on the main UI thread, but the instrument polling also fires on the main thread via the Timer; however, the results grid’s Sort event handler fires a db.SQLSelect on the main thread while the Timer’s db.Execute INSERT is mid-transaction. The collision is a re-entrant call on the same SQLite connection handle from within the Xojo event loop. Fix: move the instrument polling db.Execute calls to a dedicated Thread class instance; protect all database access through a CriticalSection that the Thread acquires before any db.Execute or db.SQLSelect call and releases after. The App.UnhandledException crashes disappear. Work log entry: “InstrumentControl.xojo_project; REAL Studio 2012r2; Timer 500ms polling db.Execute INSERT; results grid Sort event db.SQLSelect on same connection; re-entrant SQLite call on main UI thread; App.UnhandledException crash during high-frequency instrument burst + user sort interaction; fix: instrument polling moved to Thread class; CriticalSection protecting all db.Execute and db.SQLSelect calls; crashes: eliminated; 4h.”

Report generation memory leak from unreleased RecordSet and Picture objects is the third common Xojo retainer pattern in long-running retail or operations applications. A Xojo Windows desktop application (built for a wholesale distributor in Xojo 2019r1) generates PDF inventory reports on demand using a Xojo Report object and Graphics drawing. After 40–50 report generation runs in a single application session (the sales team generates updated inventory PDFs throughout the day as stock changes), the application’s working set memory has grown from 85 MB at launch to 1.4 GB; Windows begins paging; the application becomes unresponsive; the cashier restarts the application to recover. Root cause: the report generation routine allocates a RecordSet from db.SQLSelect to pull inventory rows, iterates through all rows to build the report, but never calls rs.Close on the RecordSet. In Xojo’s classic framework, RecordSet is a reference-counted object; it is released when the last reference goes out of scope. However, the report generation routine stores the RecordSet in a module-level variable (Shared Properties on a module) to allow access from multiple report subroutines; the module-level variable is never set to Nil after the report completes, so the RecordSet’s reference count never drops to zero and it is never released. Each report run accumulates another unreleased RecordSet (holding its full result set in memory) in the module-level variable chain. Fix: explicitly call rs.Close on the RecordSet after the report completes and set the module-level variable to Nil. Additionally, review the Picture objects used for chart generation in the report: each chart allocates a New Picture(width, height, depth); the same module-level reference retention pattern applies. After the fix, memory usage stays flat at 90–100 MB through 200 consecutive report runs. Work log entry: “InventoryApp.xojo_project; Xojo 2019r1; report generation module; RecordSet from db.SQLSelect stored in module-level variable; rs.Close never called; variable never set to Nil; RecordSet held in memory for application lifetime; 40–50 report runs → 1.4 GB working set; Windows paging; application unresponsive; fix: rs.Close after report complete; module variable set to Nil; Picture chart objects set to Nil after drawing; memory stable at 90–100 MB through 200 report runs; 2.5h.”

How HourTab tracks Xojo developer retainer hours

Xojo SQLite SQLITE_BUSY silent failures are invisible by the same mechanism that makes them impossible to reproduce in single-terminal development testing: when one terminal writes a sale record, db.Execute(insertSQL) acquires the SQLite write lock, completes the INSERT, and returns; db.Error is False; the sale record appears in sales.db; the developer confirms the transaction was recorded and moves on. The only evidence of the problem appears during Saturday peak hours at the retail store when two terminals complete transactions within the same 50–80ms SMB write window — with no error dialog on the cashier’s screen, no Windows Event Log entry from Xojo (the application catches no exception because none is raised), no notification to the manager (who discovers the missing records only in the weekly reconciliation), and no way to identify which specific transactions were lost (since the receipt was printed and the database write failed, there is no database record to audit). Diagnosing the problem requires examining the Xojo source code for missing db.Error checks after write calls, confirming the absence of a PRAGMA busy_timeout call after db.Connect, and reproducing the collision with a test harness that fires two concurrent db.Execute INSERT calls on the same .db file from two separate Xojo application instances.

The work log must name the mechanism to be auditable: which Xojo project file (POSApp.xojo_project), which Xojo version and API (Xojo 2017r3, old REALSQLiteDatabase API), which specific db.Execute call and event handler (INSERT INTO sales in the SaleComplete event of POSWindow), the database file path and share type (\\nasserver\pos\sales.db on Synology NAS via SMB), the concurrent terminal count at time of failure (6 terminals across 3 locations, 2 per location, Saturday peak), the sale records lost per busy week before fix (3–5 silent losses), the records lost after fix (0), and the complete fix (db.Execute("PRAGMA busy_timeout = 3000") immediately after db.Connect; db.Execute("PRAGMA journal_mode = WAL") for concurrent read improvement; if db.Error then check with db.ErrorMessage logging after every db.Execute write call). A log entry that says “fixed database issue in POS app, 3h” is not auditable. A log entry that names the Xojo version and API, the database file path and NAS share, the specific event handler and SQL call, the SQLITE_BUSY mechanism, the missing db.Error check, the missing busy_timeout, and the sale records lost and recovered is auditable and defensible to the retail chain owner and the accountant performing the reconciliation audit. HourTab gives Xojo developers a public retainer-hours URL they send to clients — retail chains, clinical laboratories, industrial automation companies, wholesale distributors, and educational institutions that built cross-platform desktop applications in REALbasic, REAL Studio, or Xojo in the 2000s and 2010s, maintained today by the original developer or a successor retainer consultant.

Comparative context: Xojo SQLite SQLITE_BUSY silent failures are structurally related to resource-lock and silent-failure patterns in other legacy desktop database platforms where a write operation fails silently without surfacing an error to the application layer. DataFlex retainers cover the same class of problem at the skip-branch level: DataFlex’s FIND command sets a skip condition in a branch that fails silently without raising an error, causing implicit record updates or skipped writes that produce data inconsistencies only visible in end-of-day reports — structurally identical to “db.Execute sets db.Error = True; application does not check db.Error; write is silently discarded.” Advantage Database retainers cover AdsTable.Edit without a corresponding AdsTable.Cancel on validation failure — a lock-not-released pattern where the row lock is held after the validation branch exits without canceling the edit, producing subsequent write failures on the same row that appear silent to the application; the same “works in single-user development, fails in concurrent production” diagnostic signature as SQLITE_BUSY with no retry. FileMaker retainers cover $$variable scope leakage where a global FileMaker variable retains a stale value from a previous session and silently overwrites a current calculation result — in the same “works in single-user testing, invisible in concurrent production” invisible bug class where the failure mechanism is completely absent from the developer’s development testing scenario.

FAQ: Xojo developer retainers