Blog › ICP guides

Actian PSQL developer on retainer: Btrieve record lock not released on failure, Pervasive PSQL developer, Btrieve developer on monthly retainer

October 10, 2026 · ~15 min read

An Actian PSQL developer was maintaining a medical billing application built on Delphi 11 and Actian PSQL 13 at a regional medical group with fifteen billing workstations, all sharing the same Btrieve data files on a Windows Server 2019 file share through the Actian PSQL Workgroup engine. The application — originally written in the mid-1990s when the billing software vendor shipped it on Pervasive PSQL v7, then upgraded through successive Pervasive versions before being handed to the in-house development team for customization — managed the full lifecycle of medical insurance claims: intake, coverage validation, submission to payer, remittance posting, and denial management. A procedure named ProcessClaim in ClaimProcessing.pas handled the workflow for advancing a claim from “Received” to “Submitted” status. At line 118, the procedure called BtrieveFile.GetRecord(Rec, BTRV_SINGLEWAIT) on the CLAIMS.MKD Btrieve file to acquire an exclusive record lock on the current claim record, using the BTRV_SINGLEWAIT lock bias so the call would wait briefly for the lock if another session held it but acquire it exclusively once available. After acquiring the lock, the procedure executed a coverage validation step at line 134: it retrieved the patient’s insurance coverage record, checked whether the specific procedure code on the claim was covered under the patient’s plan, and if the coverage type was flagged as “EXCLUDED,” the branch executed ShowMessage('Coverage type not eligible for this claim') to display an alert to the billing specialist, then called ClearRecord(Rec) and Exit to abort the claim processing. The developer who wrote the coverage exclusion failure branch had correctly cleared the record buffer before exiting. What was not accounted for was that Exit in a Delphi procedure exits the enclosing procedure and returns to the caller — but it does not release any Btrieve record locks that were acquired by GetRecord with a lock bias during the procedure’s execution. The BTRV_SINGLEWAIT lock acquired at line 118 remained active after Exit at line 134. The Btrieve engine held the exclusive lock on the CLAIMS.MKD record for claim C-2024-09847 for the billing specialist’s client session lifetime — until the billing specialist closed the application or the session timed out. Other billing workstations calling GetRecord on the same claim record received Btrieve status code 84 (“Record lock error”) and the application displayed “This record is currently in use by another user.” Lock errors per billing session: 4–6; 0 after BtrieveFile.UnlockRecord was added before every Exit within the GetRecord lock scope.

The root cause was the relationship between the Btrieve record locking model and the procedural flow of the application code that calls the Btrieve API. In the Actian PSQL Btrieve API, when a client application calls GetRecord with a lock bias of BTRV_SINGLEWAIT or BTRV_WAIT, the MicroKernel Database Engine (MKDE) that manages the Btrieve data files acquires an exclusive record lock on the record at the engine level. This lock is associated with the client session — the connection between the billing workstation’s Btrieve Requestor and the PSQL engine on the server — not with a code block, a Delphi try/finally region, or a transaction scope. The lock is released only by one of four events: a GetRecord call with BTRV_NOLOCK on the same record (which re-reads the record without holding the lock), an UnlockRecord call for that specific record, an Abort call that rolls back any pending transaction and releases all locks held by the session, or the termination of the Btrieve client session. A Delphi Exit or Return exits the current procedure’s call frame and returns control to the caller — but the Btrieve client session continues running normally, the PSQL Workgroup engine continues holding the lock for that session, and every subsequent attempt by any other workstation’s session to acquire a lock on the same record will receive status code 84 until the holding session explicitly releases the lock or terminates.

The single-workstation test invisibility follows the standard multi-user lock bug pattern. The developer who wrote the coverage exclusion failure branch in ProcessClaim tested it on a development workstation with a single Actian PSQL client session connecting to a development copy of the PSQL Workgroup engine. They called ProcessClaim with a claim that had a coverage exclusion, confirmed that the “Coverage type not eligible” message appeared and that the procedure exited cleanly without advancing the claim status, and verified that the claim record showed no unexpected changes. Since only one workstation was connecting to the PSQL engine during the test, no other session attempted GetRecord with a lock bias on the same claim record, and no Btrieve status code 84 occurred. The CLAIMS.MKD record remained locked in the development session after the Exit, but with no other session attempting to access it, the lock was invisible. In production with fifteen billing workstations sharing the same PSQL Workgroup engine, the collision became visible when multiple billing specialists were processing claims for the same high-volume payer simultaneously: one billing specialist at workstation WS04 called ProcessClaim for a claim with a coverage exclusion, held the lock on the CLAIMS.MKD record after the ShowMessage dialog, and other billing specialists at workstations WS08 and WS11 attempting to work on related claims in the same coverage batch received Btrieve status code 84 when their sessions tried to acquire a lock on the same record. The lock was resolved only when the billing specialist at WS04 closed the billing application, terminating the Btrieve client session and releasing all held locks.

Btrieve, Pervasive PSQL, and Actian PSQL: the transactional engine embedded in a generation of vertical-market Windows software

Btrieve is a transactional key-value database engine originally developed by SoftCraft, Inc. in Austin, Texas in 1982. The Btrieve engine stores data in a proprietary B-tree sequential file format — the “.MKD” (MicroKernel Database) file extension on modern Actian PSQL — and exposes a record-oriented navigational API (GetFirst, GetNext, GetPrevious, GetRecord, Insert, Update, Delete, and their variants) rather than the SQL query language. This navigational API made Btrieve extremely fast for the transactional workloads of 1980s and 1990s business software: sequential record scans for invoice aging reports, keyed lookups for patient record retrieval, and high-throughput insert operations for order line items all performed well because the Btrieve API allowed application code to navigate the B-tree directly without a query optimizer or SQL parser in the way. Vertical-market ISVs — software vendors who built and sold industry-specific applications to insurance companies, medical groups, property management firms, churches, schools, and retailers — adopted Btrieve as the embedded database engine for their DOS and Windows applications throughout the late 1980s and 1990s because it was fast, it required no database administration (the engine managed its own files without a DBA-maintained server), and Novell bundled it with NetWare, the dominant network operating system of the era, making it effectively free for any organization already running a Novell network.

Novell acquired SoftCraft in 1994 and renamed the product “Novell Btrieve” before spinning it out as a separate company called Pervasive Software in 1994. Pervasive Software added a relational SQL layer on top of the Btrieve engine in the mid-1990s — the Pervasive.SQL product that later became Pervasive PSQL — allowing new development on the SQL layer while keeping all existing Btrieve API applications running unchanged against the same data files. Actian Corporation acquired Pervasive Software in 2013 and rebranded the product as Actian PSQL, continuing development of both the Btrieve API layer (now called the MicroKernel API) and the SQL layer (now called PSQL SQL or PSQL ODBC). The installed base of Btrieve and Pervasive PSQL applications in vertical markets is substantial: tens of thousands of organizations — medical billing companies, property management firms, independent insurance agencies, healthcare clinics, school business offices, and churches — run day-to-day operations on Windows applications built in the 1990s or early 2000s on Btrieve/Pervasive PSQL, many of them still using the MicroKernel API layer exclusively rather than the SQL layer. For these organizations, an Actian PSQL developer on retainer is the specialist who keeps the PSQL engine version current, fixes MicroKernel API data access bugs in the application code, and advises on SQL layer migration when the organization needs capabilities that the navigational Btrieve API does not provide.

The Actian PSQL engine is available in two architectural editions that matter for retainer engagements. Actian PSQL Workgroup is the peer-to-peer edition: each Windows server or workstation runs a local PSQL Workgroup engine that manages data files stored on that machine’s local disk or a mapped network drive, and client workstations connect to the Workgroup engine on the file server via the PSQL Requester (a client-side component that intercepts Btrieve API calls and routes them to the appropriate engine). Actian PSQL Client-Server is the dedicated-server edition: a PSQL Server engine runs as a Windows Service on a dedicated server, and client workstations connect to it via a network protocol (TCP/IP). The locking model differs between editions: in Workgroup, locks are managed by the engine on the machine that owns the data file; in Client-Server, locks are managed by the dedicated server engine. Both editions exhibit the same behavior with respect to GetRecord lock-bias locks: the lock is held at the engine level for the client session lifetime until explicitly released by the application code.

Btrieve record locking API: GetRecord lock biases, UnlockRecord, Abort, and status code 84

The Btrieve API’s record locking model differs from SQL transactional locking in an important way: Btrieve lock biases are parameters to read operations rather than explicit lock-request calls. There is no LockRecord() function in the Btrieve API. Instead, record locks are acquired implicitly as a side effect of read operations when a lock bias is specified. The principal lock-acquiring read operations and their lock semantics are as follows. GetRecord with BTRV_SINGLEWAIT reads the record at the current position and acquires an exclusive lock on it. If another session holds an exclusive or shared lock on the record, the calling session waits for approximately two seconds (the single-wait interval) and retries once; if the lock is still held after the retry, the call returns Btrieve status code 84. If the lock is acquired successfully, the call returns status code 0 and the record buffer is populated with the record data. GetRecord with BTRV_WAIT acquires an exclusive lock and waits indefinitely for the lock to become available rather than timing out after a single retry. This is appropriate for operations where lock contention is expected and the calling session should queue behind the current lock holder rather than failing immediately. GetRecord with BTRV_NOLOCK reads the record without acquiring a lock — safe for read-only operations where the application does not need to prevent concurrent modification. UnlockRecord releases the exclusive lock on the current record without modifying the record data. It must be called on the same client session that acquired the lock. It is the correct call to make on every failure path within a GetRecord lock-bias scope when the procedure decides not to update the record. Abort rolls back any pending Btrieve transaction and releases all locks held by the current client session. Abort is the correct call at a higher scope when the application detects an unrecoverable error condition and wants to release all locks in one operation rather than calling UnlockRecord for each individual record. Btrieve status code 84 (“Record lock error”) is the standard error returned when a GetRecord call with BTRV_SINGLEWAIT cannot acquire a lock because another session holds the lock and the wait interval expired. The application must handle status 84 explicitly — typically by displaying a user-facing error message such as “This record is in use by another user” — and retry or cancel the operation.

The ProcessClaim procedure in the medical billing incident had two Exit statements within the GetRecord lock-bias scope at line 118, not one. The developer who identified the coverage exclusion failure path at line 134 as the lock-hold bug fixed it by adding BtrieveFile.UnlockRecord before the Exit at line 134. A thorough audit of the full procedure revealed one additional Exit path that also lacked UnlockRecord: the duplicate claim check at line 149, which searched the Btrieve claim index for a prior submission with the same claim number and payer combination, and on finding a duplicate called ShowMessage('Duplicate claim detected') followed by ClearRecord(Rec) and Exit — again without UnlockRecord. The duplicate-check failure was actually more impactful in production than the coverage exclusion failure: claims often entered the duplicate-check branch in the afternoon resubmission cycle when multiple billing specialists were processing denial remittances, and the held locks from the duplicate-check failures were blocking the entire payer batch. Both failure paths were fixed by adding BtrieveFile.UnlockRecord immediately before every Exit within the GetRecord lock scope. The preferred structural pattern for procedures with many exit paths is a single Delphi try/finally block: acquire the Btrieve record lock in the try body, execute all validation and update logic, and call UnlockRecord (or Abort if a transaction is active) in the finally block, which executes on every exit path including exceptions. This pattern makes it structurally impossible to exit the procedure with a held lock.

Diagnosing a Btrieve record lock hold in production requires the Pervasive PSQL Monitor (or Actian PSQL Monitor in current versions), accessible on the server running the PSQL Workgroup or Client-Server engine. The Monitor provides a Sessions view listing all currently connected client sessions with their workstation names, session IDs, connected Btrieve files, and current operation state. The Files view lists all open Btrieve data files with the count of locks held for each file. The Locks view — accessible by selecting a file in the Files view — lists each individual record lock by session ID, lock type (exclusive or shared), and record offset (the byte position of the locked record within the MKD file, which can be mapped back to a logical record key using the Btrieve GetPosition operation). Matching the session ID in the Locks view with the workstation name in the Sessions view identifies which billing workstation holds the stuck lock and at which record position in CLAIMS.MKD. Correlating the record offset with the claim record data (using a Btrieve API utility to retrieve the record at that offset) identifies the specific claim number and patient being blocked. This diagnostic chain — Actian PSQL Monitor → Locks view for CLAIMS.MKD → record offset → session ID → workstation name → claim record data — is the standard diagnostic workflow for Btrieve status code 84 incidents in medical billing applications.

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

Btrieve record lock not released on Exit or Return failure branch is the canonical Actian PSQL invisible production lock bug for any procedure that uses GetRecord with a lock bias and multi-path validation logic. It appears in the work logs of PSQL retainer engagements for medical billing systems, insurance claim processing systems, property management applications, and retail POS systems that were originally built as vertical-market ISV products on Pervasive PSQL in the late 1990s or early 2000s. The work log entry that makes this diagnosable: “Actian PSQL v13.30 Workgroup; Delphi 11; procedure ProcessClaim (ClaimProcessing.pas); Btrieve file CLAIMS.MKD (\\\\SERVER\\MedBill); GetRecord BTRV_SINGLEWAIT at line 118; coverage exclusion failure Exit at line 134 (ClearRecord + Exit without UnlockRecord); duplicate claim failure Exit at line 149 (same — Exit without UnlockRecord); Pervasive PSQL Monitor: session 1047 (BILLING-WS04), CLAIMS.MKD locked, record offset 0x00A4C000 (Claim ID C-2024-09847); sessions 1062 (BILLING-WS08) and 1075 (BILLING-WS11) receiving status 84 on GetRecord SINGLEWAIT for same offset; status 84 frequency before fix: 4–6 per billing session; fix: UnlockRecord added before Exit at lines 134 and 149; full ProcessClaim audited for additional lock-scope Exits — none found; status 84 frequency after fix: 0; hours: 1.75h.”

Actian PSQL version upgrade is the most common planned Actian PSQL retainer engagement for organizations maintaining vertical-market applications that have been running on the same PSQL version for several years. The typical scenario: an organization is running a medical billing application on Pervasive PSQL v11 on a Windows Server 2008 R2 machine, and a new HIPAA EDI compliance requirement necessitates upgrading both the operating system and the PSQL engine. The retainer developer tests the application against Actian PSQL v13 on a Windows Server 2019 test environment, identifies any Btrieve API compatibility issues introduced by the MicroKernel engine changes between v11 and v13 (most commonly around file format differences, maximum file size limits, and Btrieve status code mappings for certain error conditions), patches the application code where necessary, and oversees the production upgrade. Work log: “Actian PSQL v11 to v13.30 upgrade assessment; medical billing application (Delphi 11, PSQL Workgroup); CLAIMS.MKD, PATIENTS.MKD, PROVIDERS.MKD, 12 additional Btrieve files; Btrieve API compatibility test: all GetRecord, Insert, Update, Delete operations return correct status codes on v13; 1 file format issue: CLAIMS.MKD internal format upgraded from v11 format to v13 format by PSQL v13 engine on first open — upgrade is one-way, backup taken; no API changes required in Delphi source; v13 engine performance on GetNext sequential scan of CLAIMS.MKD (2.1M records): 3.2s (v11: 4.8s) — 33% improvement; upgrade plan approved; production upgrade scheduled; 4h assessment + 2h upgrade.”

Btrieve API to PSQL SQL migration is the highest-investment Actian PSQL retainer engagement for organizations whose applications use only the Btrieve navigational API and need SQL query capabilities — typically to support reporting, analytics, or integration with other systems that speak ODBC or SQL. The migration path is typically to add the PSQL SQL layer on top of the existing Btrieve files (defining SQL table schemas that map to the existing MKD file structures using the PSQL SQL DDL), then add ODBC or PSQL SQL client connectivity alongside the existing Btrieve API code, and eventually migrate the high-volume transaction paths from the Btrieve API to SQL stored procedures or parameterized ODBC calls. Work log: “Actian PSQL SQL layer migration scope; medical billing application; existing Btrieve files: CLAIMS.MKD (4.2M records), PATIENTS.MKD (380k records), PROVIDERS.MKD (12k records), 12 additional files; SQL schema defined for all 15 Btrieve files using PSQL SQL DDL (Btrieve key layouts mapped to PRIMARY KEY and INDEX definitions); ODBC DSN configured on billing server; test: SELECT COUNT(*) FROM CLAIMS returns 4,200,341 rows in 2.1s; reporting query for AR aging by payer: 12-table JOIN, 8.4s; Btrieve API code for claim submission path retained (2,400 lines); ODBC added for denial reporting (340 lines new); mixed-mode operation confirmed stable over 2-week test; full API-to-SQL migration of claim submission path deferred to Phase 2; hours: 18h assessment and Phase 1 implementation.”

How HourTab tracks Actian PSQL developer retainer hours

Btrieve record lock hold bugs caused by UnlockRecord missing on Exit or Return failure paths within a GetRecord lock-bias scope are invisible in single-workstation development testing by the same mechanism that makes all single-user lock bugs invisible when only one client session accesses the PSQL engine at a time. The developer who writes ProcessClaim tests the coverage exclusion failure path on a development workstation with a single PSQL client session: they call the procedure with a claim that has a coverage exclusion, confirm that the “Coverage type not eligible” message appears and the procedure exits without advancing the claim status, and verify that the claim record data is unchanged. The development workstation’s Btrieve client session holds the CLAIMS.MKD record lock from GetRecord(BTRV_SINGLEWAIT) after the test, but since no other workstation is connecting to the PSQL engine during development testing, no other session calls GetRecord on the same claim record, and no status code 84 occurs. The Pervasive PSQL Monitor on the development server would show the locked record if consulted, but the developer has no visible symptom to prompt a Monitor check. In production with fifteen billing workstations sharing the same PSQL Workgroup engine, the collision is visible only when two billing specialists attempt to work on claims in the same coverage batch simultaneously: one specialist holding the lock after a coverage exclusion failure, and another specialist attempting a GetRecord(BTRV_SINGLEWAIT) lock on the same claim record.

The work log entry that makes Actian PSQL lock work auditable must name every element of the causal chain: the PSQL version and edition (Workgroup vs. Client-Server), the application language and procedure name, the Btrieve file and its network path, the record offset and logical key that identifies the blocked record, the GetRecord lock-bias call site line number, the validation failure path that lacked UnlockRecord, the Pervasive PSQL Monitor session and lock evidence, the Btrieve status 84 frequency before and after the fix, and the audit result for additional unprotected Exit paths within the same procedure. A log that says “fixed Btrieve locking issue, 1.75h” is not auditable to a medical billing manager reviewing a HIPAA compliance incident log. A log that names the procedure, the Btrieve file, the record offset, the specific Exit that lacked UnlockRecord, and the before/after status 84 frequency is auditable, defensible, and demonstrates the value of a long-term Actian PSQL retainer engagement for a medical group that depends on their billing application for daily revenue cycle management. HourTab gives Actian PSQL developers a public retainer-hours URL they send to clients — medical groups, insurance agencies, property management firms, and retail businesses that run core revenue cycle and administrative operations on Btrieve and Pervasive PSQL applications built in the 1990s and maintained by an Actian PSQL specialist on monthly retainer.

The broader context for Actian PSQL retainer billing is that the GetRecord lock-bias without UnlockRecord pattern is the Btrieve instantiation of the universal principle that every platform requiring explicit lock management demands pairing every lock acquisition with a lock release on every exit path. Harbour developer retainers cover the equivalent in Harbour: DBRLOCK() acquiring a byte-range lock on a DBF record that is held for the workstation session lifetime, with DBRUNLOCK() missing on the validation failure branch — same invisible single-workstation test, same multi-user shared-file lock contention in production. DataFlex developer retainers cover the equivalent in DataFlex: an implicit lock acquired by FIND in a write mode that creates a record lock not released on the failure path — same invisible single-user development pattern, same multi-user lock contention. Progress OpenEdge ABL developer retainers cover the equivalent in Progress: FIND FIRST EXCLUSIVE-LOCK acquiring a record lock that persists until explicit RELEASE or transaction end, with RELEASE missing on the failure path — same class of invisible explicit-lock management bug.

FAQ: Actian PSQL developer retainers