Blog › ICP guides
Watcom SQL developer on retainer: transaction not rolled back on failure, SQL Anywhere developer, SAP SQL Anywhere developer on monthly retainer
October 9, 2026 · ~15 min read
A SQL Anywhere developer was maintaining an insurance claims processing system at a regional insurance company running SAP SQL Anywhere 17 on a Windows Server 2019 host. The SQL Anywhere database served as the central data store for a claims management application used by 25 concurrent claims adjusters across two office floors. A stored procedure named sp_ProcessClaim handled the initial intake stage for new insurance claims: it opened a transaction with BEGIN TRANSACTION, updated the claim record’s status to indicate it was being processed with UPDATE Claims SET Status = 'Processing' WHERE ClaimId = @ClaimId, called a coverage eligibility validation function dbo.udf_CheckCoverage(@PolicyType, @ClaimType) to verify that the submitted claim type was eligible under the policy, and on successful validation committed the transaction and updated additional claim attributes. When the coverage validation returned zero, indicating an ineligible claim type, the validation check branch executed RAISERROR 'Coverage type not eligible' and immediately exited the procedure with RETURN. The developer who wrote sp_ProcessClaim understood that RAISERROR would send the error message to the calling application, which would then display it to the adjuster. What the developer did not account for was that SQL Anywhere’s RAISERROR statement sends a message to the client application but does not automatically roll back an open transaction. The BEGIN TRANSACTION opened at the start of sp_ProcessClaim remained open after RAISERROR + RETURN, and the exclusive row-level lock on the Claims row acquired by the UPDATE statement remained held by the connection for its lifetime. Other claims adjusters attempting to update or access the same claim record received SQL Anywhere error −306 “Lock request time out period exceeded.” Lock timeout errors per day: 5–8 at peak processing hours; 0 after ROLLBACK TRANSACTION was added before RAISERROR + RETURN on every failure path in sp_ProcessClaim.
The root cause was the semantics of RAISERROR in SQL Anywhere’s stored procedure language, which differ from developer expectations shaped by SQL Server experience. In Microsoft SQL Server, RAISERROR with a high severity (17–25) combined with SET XACT_ABORT ON automatically terminates and rolls back the current transaction. SQL Anywhere’s RAISERROR is a signaling mechanism only: it sets the SQL exception state for the procedure (visible as @@error or via a SQL exception handler) and sends the error message to the client connection, but it takes no action on any open transaction. The open transaction from BEGIN TRANSACTION continues in full force after RAISERROR executes. The RETURN statement that follows exits the stored procedure and returns control to the calling application connection, but RETURN in SQL Anywhere stored procedures also does not commit or roll back an open transaction. The connection’s transaction state at the time sp_ProcessClaim exited was the same as it was when BEGIN TRANSACTION was issued: an open transaction with an uncommitted UPDATE that held an exclusive row lock on Claims WHERE ClaimId = @ClaimId. SQL Anywhere’s row-level locking model holds exclusive write locks for the duration of the owning transaction. Until the transaction is explicitly committed or rolled back, the exclusive lock is retained by the connection. The calling claims application received the RAISERROR message, displayed “Coverage type not eligible” to the adjuster, and returned to the claim entry form — with no indication that the database connection it was using was now holding an uncommitted transaction and a live row-level lock that would prevent any other adjuster from modifying that claim.
The invisibility in development testing followed the standard multi-user lock bug pattern. The developer tested sp_ProcessClaim by submitting a claim with an ineligible coverage type through the development instance of the claims application. The application displayed the “Coverage type not eligible” error message correctly. The claim record appeared unmodified in the database (because the UPDATE had not been committed). There was no visible indication that the database connection retained an open transaction — the development instance ran with a single active connection, so no other connection attempted to lock the same Claims row, and the idle-connection timeout on the development server eventually rolled back the transaction without any visible incident. In production with 25 concurrent adjusters processing a mix of new and resubmitted claims, the contention became visible only when two conditions occurred simultaneously: one adjuster’s connection held an open transaction after a coverage validation failure, leaving an exclusive lock on a specific claim row, and another adjuster’s workflow brought them to an operation involving the same claim — a supervisor reviewing the claim, a resubmission attempt, or a status update from the claims management system. The −306 error appeared to the second adjuster with no explanation of why the claim was locked or which connection held the lock.
Watcom SQL to SAP SQL Anywhere: the embedded database that powered a generation of vertical-market applications
Watcom SQL was developed by Watcom International Corporation in Waterloo, Ontario, Canada, beginning in the late 1980s. Watcom was already well known in the PC software development community for its Watcom C/C++ compiler — a high-performance optimizing compiler for DOS and Windows that was widely used in game development and systems programming in the early 1990s. The Watcom SQL database engine was designed from the ground up as a small-footprint, high-reliability embedded SQL database for use within client-server desktop applications: it could be shipped as a component of a commercial application, administered with zero DBA overhead, and accessed via ODBC or native APIs from programming environments like PowerBuilder, Delphi, Visual Basic, and C/C++. Watcom SQL’s architecture was particularly well-suited to the vertical-market ISV (independent software vendor) market of the 1990s: a single developer or small team could build a complete client-server database application and ship it with an embedded SQL Anywhere database that required no separate installation or administration step from the customer.
The ownership history of Watcom SQL traces a path through the 1990s consolidation of the database and development tools market. Powersoft Corporation, makers of the PowerBuilder 4GL RAD tool for building Windows client-server applications, acquired Watcom International in 1994 primarily to obtain the Watcom SQL engine — which they bundled with PowerBuilder as “Watcom SQL” and later “PowerSoft SQL Anywhere.” Sybase, Inc. acquired Powersoft in 1995, adding both PowerBuilder and SQL Anywhere to its product family. Under Sybase ownership, the product was developed and marketed as Sybase SQL Anywhere Studio, with dedicated releases targeting the embedded database market (SQL Anywhere) and the mobile and occasionally-connected database market (UltraLite, for Palm and Windows CE devices). iAnywhere Solutions, a Sybase subsidiary formed in 2000, took over the SQL Anywhere and UltraLite product lines for several years. SAP acquired Sybase in 2010, and SQL Anywhere was eventually renamed SAP SQL Anywhere — the current product name as of SQL Anywhere 16 and 17. Despite the ownership changes, the SQL Anywhere engine maintained strong backward compatibility: stored procedures written for Watcom SQL in 1994 generally run on SAP SQL Anywhere 17 without modification, which is both a testament to the engine’s design stability and a reason why organizations that built core business applications on early Watcom SQL still run them today with minimal changes to the stored procedure layer.
The installed base of SQL Anywhere in production is largest in vertical markets where ISVs built and sold complete application packages in the 1990s and 2000s: insurance claims management (the industry the claims processing system above operates in), medical practice management, dental practice software, regional retail point-of-sale, school student information systems, legal matter management, and municipal government administrative systems. In each of these verticals, the customer purchased a complete application from an ISV — a practice management system, a claims processing system, a student information system — with SQL Anywhere embedded as the database engine. The customer never directly interacts with SQL Anywhere; the ISV’s application is the only interface. Retainer developers who specialize in SQL Anywhere for these vertical markets are typically former employees of the ISV, developers who inherited maintenance of an ISV application after the ISV dissolved or was acquired, or systems integrators who built custom integrations into the ISV application’s SQL Anywhere database layer. The specialty is narrow enough that SQL Anywhere retainer developers command rates comparable to other niche legacy database specialists.
SQL Anywhere transaction model: RAISERROR semantics and row-level lock lifetime
SQL Anywhere’s transaction model closely follows the SQL standard but has specific behaviors around error handling and lock lifetime that developers familiar with SQL Server or Oracle may not expect. BEGIN TRANSACTION in SQL Anywhere (or the implicit transaction started by the first DML statement if AUTOCOMMIT is off) opens a transaction context that holds all acquired row-level locks until an explicit COMMIT or ROLLBACK. SQL Anywhere uses row-level locking by default (unlike some older systems that use page-level or table-level locking as the default unit): an UPDATE statement acquires an exclusive write lock on each row it modifies, held for the transaction duration. Exclusive write locks prevent any other connection from reading (in READ COMMITTED isolation) or modifying the locked row until the lock is released by commit or rollback.
RAISERROR in SQL Anywhere stored procedures sends a user-defined error message to the client connection and sets the connection’s error state (accessible as @@error or via a WHENEVER SQLERROR handler). It is a signaling mechanism for error communication between the stored procedure and the calling application — it has no effect on the current transaction state. This contrasts with SQL Server’s behavior when SET XACT_ABORT ON is configured, which automatically terminates and rolls back a transaction when a runtime error occurs. SQL Anywhere has no equivalent of SET XACT_ABORT ON: every transaction commit or rollback in SQL Anywhere requires an explicit COMMIT or ROLLBACK statement. The correct pattern for a stored procedure that acquires locks and may hit validation failures is: issue ROLLBACK (or ROLLBACK TO SAVEPOINT savepoint_name for a partial rollback) before any RAISERROR + RETURN on a failure path, before any unconditional RETURN that exits without committing, and in any exception handler that catches a SQL error. The ROLLBACK releases all locks acquired since the matching BEGIN TRANSACTION and returns the connection to a clean state.
Diagnosing a SQL Anywhere open transaction lock hold requires access to the SQL Anywhere system procedures and the database administration tools. The primary diagnostic is the sa_locks() system procedure: called from any connection with DBA privilege in the SQL Anywhere database (via ISQL, Sybase Central, or SAP SQL Anywhere Studio), it returns a result set with one row per current lock, including the connection ID of the lock holder, the table name, the row ID of the locked row, and the lock type (Exclusive Write for the lock class acquired by an uncommitted UPDATE). To identify the application connection holding the lock, cross-reference the connection ID with sa_conn_info(), which returns connection ID, user name, application name, host name, and login time for all active connections. In the sp_ProcessClaim incident, sa_locks() during a lock timeout incident shows: connection 47, table Claims, row 12843, lock type ExclusiveWrite. sa_conn_info() for connection 47 shows: user ADJUSTER09, application ClaimsApp.exe, host CLAIMS-WS-09, login time 08:14:22. The diagnosis: ADJUSTER09 at workstation CLAIMS-WS-09 called sp_ProcessClaim, hit the coverage validation failure path, and the procedure exited via RAISERROR + RETURN without ROLLBACK TRANSACTION, leaving connection 47’s transaction open with an exclusive write lock on Claims row 12843. The fix: ROLLBACK TRANSACTION added immediately before RAISERROR 'Coverage type not eligible' on the validation failure branch, and the remaining stored procedure audited for any other RAISERROR + RETURN paths that also lacked ROLLBACK TRANSACTION.
Typical SQL Anywhere developer retainer work and what it looks like in a work log
Transaction not rolled back on RAISERROR + RETURN is the canonical SQL Anywhere invisible production lock bug for stored procedures that handle validation failures with error signaling followed by early return. The pattern appears consistently in stored procedures built on the assumption (carried over from SQL Server experience with SET XACT_ABORT ON) that RAISERROR terminates the transaction. The work log entry that makes this diagnosable: “SQL Anywhere 17, database claims.db (\\\\server\\claims\\); stored procedure sp_ProcessClaim; transaction opened at line 4 with BEGIN TRANSACTION; UPDATE Claims SET Status = 'Processing' WHERE ClaimId = @ClaimId at line 7 (exclusive write lock on Claims row @ClaimId); coverage validation failure at line 12: IF dbo.udf_CheckCoverage(@PolicyType, @ClaimType) = 0 then RAISERROR 'Coverage type not eligible' + RETURN (no ROLLBACK TRANSACTION); lock holder: connection 47 (sa_locks(): table Claims, row 12843, ExclusiveWrite; sa_conn_info(): connection 47, user ADJUSTER09, application ClaimsApp.exe, host CLAIMS-WS-09, login 08:14:22); blocked connections: 51 and 53 receiving error -306 'Lock request time out period exceeded' on UPDATE Claims WHERE ClaimId = 12843; lock timeout errors per day before fix: 5–8 at peak processing hours (10:00–14:00); fix: ROLLBACK TRANSACTION added before RAISERROR 'Coverage type not eligible' at line 12; full stored procedure audited for additional RAISERROR + RETURN paths — 2 additional paths found and fixed (duplicate claim check at line 18, policy cancellation check at line 25); lock timeout errors per day after fix: 0; 2.5h.”
SQL Anywhere schema and stored procedure maintenance is the routine day-to-day SQL Anywhere retainer task for vertical-market ISV applications that use SQL Anywhere as their embedded database engine. A common engagement for a regional insurance claims system: the ISV’s annual maintenance contract has lapsed, the in-house IT team cannot modify the SQL Anywhere stored procedures (the ISV used obfuscated procedure definitions), and the insurance company needs a custom report added to the claims analytics dashboard. The retainer developer connects to the SQL Anywhere database using the ISQL command-line tool (dbisql -c "DSN=ClaimsDB;UID=dba;PWD=xxxxx") or Sybase Central (the SQL Anywhere GUI administration tool), identifies the relevant base tables, and writes a new SQL Anywhere stored procedure for the report query. SQL Anywhere’s T-SQL-compatible procedure syntax supports CREATE PROCEDURE, BEGIN/END blocks, IF/ELSEIF/ELSE conditional logic, LOOP/WHILE iteration, DECLARE CURSOR for result set iteration, and CREATE TEMPORARY TABLE for intermediate result sets within a procedure. The work log: “SQL Anywhere 17; database claims.db; new stored procedure sp_ClaimsAnalyticsReport(IN @StartDate DATE, IN @EndDate DATE, IN @AdjusterCode CHAR(8)); implemented with temporary table #claim_summary for intermediate aggregation; tested against 6 months of production data snapshot; execution time: 3.2s for 90-day range on 1.2M claim rows; delivered to IT for integration with Crystal Reports; 4h.”
SQL Anywhere database file administration is a required retainer task for SQL Anywhere deployments where the ISV no longer provides DBA support and the in-house IT team has limited database administration experience. SQL Anywhere uses a two-file database structure: the main database file (claims.db) stores the schema and data; the transaction log file (claims.log) records every committed transaction for point-in-time recovery. Left unmanaged, the transaction log file grows without bound. The retainer developer configures a scheduled backup using SQL Anywhere’s dbbackup command-line utility (dbbackup -c "DSN=ClaimsDB" -t -x backup_dir\ to back up the database and truncate the transaction log after a successful backup). For databases showing performance degradation from index fragmentation, the retainer developer runs sa_defrag_index() on the high-update tables (typically the Claims and ClaimEvents tables) to rebuild fragmented index pages. Work log: “SQL Anywhere 17; database claims.db; transaction log claims.log grown to 47 GB (last truncation: unknown — no scheduled backup); implemented daily dbbackup scheduled task via Windows Task Scheduler; backup directory configured on NAS \\\\fileserver\\db-backups\\claims\\; transaction log truncated after first successful backup; log size: 47 GB → 0 GB (freshly truncated); scheduled backup validated over 5 days; sa_defrag_index('Claims') and sa_defrag_index('ClaimEvents') run; index scan performance improvement verified; 3h.”
Track SQL Anywhere developer retainer hours without the status emails
When a 2.5-hour investigation traces 5–8 daily lock timeout errors in a claims processing system to an sp_ProcessClaim stored procedure that calls RAISERROR 'Coverage type not eligible' and RETURN on the validation failure path without first issuing ROLLBACK TRANSACTION — leaving the BEGIN TRANSACTION open with an exclusive write lock on the Claims row for the connection lifetime, blocking concurrent adjusters with SQL Anywhere error −306 — the work log must name the procedure, the UPDATE table, the RAISERROR path that lacked ROLLBACK, the sa_locks() evidence, and the lock timeout rate before and after the fix. HourTab gives your SQL Anywhere retainer client a public dashboard URL they can bookmark: hours used, hours remaining, and a work log that names the missing ROLLBACK on the failure branch. No client login. No status emails. CSV in, URL out.
How HourTab tracks SQL Anywhere developer retainer hours
SQL Anywhere transaction hold bugs caused by ROLLBACK TRANSACTION missing on RAISERROR + RETURN validation failure paths are invisible in single-connection development testing by the same mechanism that makes all multi-user lock bugs invisible in single-user test environments. The developer who writes sp_ProcessClaim tests the coverage validation failure path by calling the procedure from a development connection with an ineligible claim type. The application displays the “Coverage type not eligible” error correctly. The claim record is unchanged in the database (the uncommitted UPDATE was neither visible nor committed). The developer’s connection is the only active connection in the development instance, so no other connection attempts to lock Claims row 12843. The SQL Anywhere connection idle timeout eventually rolls back the open transaction without any visible indication. The lock is never observed because there is no competing connection. In production, the contention becomes visible only when two adjusters’ workflows intersect on the same claim: one connection holding an open transaction on a claim after a coverage validation failure, and another connection attempting an UPDATE on the same claim row within the lock hold window. The second connection receives error −306 with no diagnostic information about which connection holds the lock or which procedure left the transaction open.
The work log entry that makes SQL Anywhere lock work auditable must name every element of the causal chain: the SQL Anywhere version, the database file name, the stored procedure name, the UPDATE table and WHERE clause, the validation failure path that lacked ROLLBACK, the connection holding the lock (from sa_locks(): connection ID, table, row, lock type; from sa_conn_info(): user, application, host, login time), the blocked connections and error they received, the lock timeout error frequency before and after the fix, and the fix itself (ROLLBACK TRANSACTION added before RAISERROR + RETURN, plus the procedure audit for additional unprotected paths). A log that says “fixed lock timeout issue in claims system, 2.5h” is not auditable to an insurance claims director, an IT operations manager, or a compliance team reviewing change records. A log that names the procedure, the sa_locks() evidence, the RAISERROR path missing ROLLBACK, and the before/after error rate is auditable, defensible, and builds the trust that sustains a long-term SQL Anywhere retainer engagement. HourTab gives SQL Anywhere developers a public retainer-hours URL they send to clients — insurance companies, healthcare organizations, retailers, law firms, and educational institutions that run core business operations on vertical-market applications backed by SQL Anywhere and maintain them with a SQL Anywhere specialist on monthly retainer.
The broader context for SQL Anywhere retainer billing is that the RAISERROR-without-ROLLBACK pattern is the SQL Anywhere instantiation of the universal principle that explicit transaction management requires matching every transaction-open on every code path. Sybase ASE developer retainers cover the structurally identical pattern in Sybase ASE stored procedures: RAISERROR + RETURN without ROLLBACK TRANSACTION leaving an open transaction with row-level locks, discovered via sp_lock rather than sa_locks(). Progress OpenEdge ABL developer retainers cover the Progress ABL equivalent: FIND FIRST EXCLUSIVE-LOCK without RELEASE leaving a record lock on the failure branch — same class of invisible single-user-test/multi-user-production bug, same discovery mechanism (concurrent users receiving lock-wait errors). PowerBuilder developer retainers often cover the companion bug: PowerBuilder DataWindow in UPDATE mode holding a database cursor open with locked rows while the operator attends to something else — SQL Anywhere was PowerBuilder’s default embedded database through much of the 1990s and 2000s, so PowerBuilder retainer developers and SQL Anywhere retainer developers frequently encounter the same application.
FAQ: SQL Anywhere developer retainers
What does a SQL Anywhere developer on retainer typically do?
A SQL Anywhere developer on monthly retainer covers transaction rollback audits (reviewing all stored procedures containing BEGIN TRANSACTION to confirm that every RAISERROR + RETURN path, every validation failure branch, and every error handler issues ROLLBACK TRANSACTION before exiting); lock timeout diagnosis (identifying the connection holding an exclusive write lock using sa_locks() and sa_conn_info(), naming the stored procedure and failure path that left the transaction open); SQL Anywhere stored procedure development and maintenance (writing and modifying procedures, functions, triggers, and events in SQL Anywhere T-SQL syntax); SQL Anywhere version upgrade work (migrating SQL Anywhere 9 or 12 databases to version 16 or 17, updating deprecated syntax, validating stored procedure behavior); and vertical-market ISV application support (maintaining the database layer for insurance, healthcare, retail, or legal applications built on PowerBuilder, Delphi, or Visual Basic with SQL Anywhere as the embedded backend).
What SQL Anywhere transaction debugging work is most commonly underlogged?
Open transaction lock hold bugs caused by ROLLBACK TRANSACTION missing on RAISERROR + RETURN paths are the most systematically underlogged SQL Anywhere retainer work. The pattern: a stored procedure issues BEGIN TRANSACTION and an UPDATE (acquiring an exclusive write lock); developer tests single-connection; production hit a validation failure causing RAISERROR + RETURN without ROLLBACK; SQL Anywhere does not auto-rollback on RAISERROR; open transaction with row lock persists for connection lifetime; other connections receive SQL Anywhere error −306 “Lock request time out period exceeded.” The work log must name the stored procedure, the UPDATE table, the validation failure branch lacking ROLLBACK, the sa_locks() evidence (connection ID, table, row, lock type), and the lock timeout error rate before and after the fix.
What are typical SQL Anywhere developer retainer rates?
Entry-level SQL Anywhere developers with experience in basic stored procedure development, BEGIN TRANSACTION / COMMIT / ROLLBACK management, and SQL Anywhere schema management typically bill at $60 to $100 per hour. Mid-level SQL Anywhere developers with experience in SQL Anywhere transaction isolation, row-level locking, sa_locks() and sa_conn_info() diagnostics, SQL Anywhere event scheduling, and vertical-market ISV application integration typically bill at $90 to $155 per hour. Senior SQL Anywhere developers with deep knowledge of SQL Anywhere’s MVCC concurrency model, transaction log management, SQL Anywhere 16/17 features, and vertical-market application architecture typically bill at $135 to $220 per hour. Monthly retainer ranges: $2,000 to $3,500/month for advisory engagements; $3,500 to $6,500/month for active SQL Anywhere stored procedure development and DBA services.
What should a SQL Anywhere developer retainer agreement include?
A SQL Anywhere developer retainer agreement should specify: SQL Anywhere version (9, 11, 12, 16, or 17 — SAP SQL Anywhere 16/17 have updated transaction log behavior vs. older versions); whether the retainer developer has DBA access to run sa_locks(), sa_conn_info(), and the SQL Anywhere Console Utility for live diagnostics; whether the retainer covers a transaction rollback audit (reviewing all procedures with BEGIN TRANSACTION for missing ROLLBACK on failure paths); whether the retainer covers SQL Anywhere database file maintenance (dbbackup configuration, transaction log truncation, dbvalid validation, sa_defrag_index()); the scope of the ISV application layer (PowerBuilder, Delphi, Visual Basic, or other RAD tool client — or SQL Anywhere stored procedures and schema only); and whether the retainer includes on-call response for production lock timeout incidents requiring DBA connection kill.
How should SQL Anywhere developer retainer hours be logged?
Log each SQL Anywhere retainer session with the version, database file name, stored procedure name, specific issue, and before/after metric. For transaction not rolled back on failure: SQL Anywhere version (17), database file (claims.db), stored procedure (sp_ProcessClaim), UPDATE statement and table (UPDATE Claims SET Status = 'Processing' WHERE ClaimId = @ClaimId), validation failure path lacking ROLLBACK (coverage eligibility check: IF dbo.udf_CheckCoverage(@PolicyType, @ClaimType) = 0 RAISERROR 'Coverage type not eligible' RETURN), lock holder from sa_locks() (connection 47, table Claims, row 12843, ExclusiveWrite) and sa_conn_info() (user ADJUSTER09, application ClaimsApp.exe, host CLAIMS-WS-09), blocked connections (51 and 53, error −306), lock timeout errors per day before fix (5–8), fix (ROLLBACK TRANSACTION added before RAISERROR + RETURN on coverage failure and 2 additional failure paths), lock timeout errors per day after fix (0), hours (2.5h). For schema migration: source version, target version, procedures reviewed, deprecated syntax updated, lock behavior validated, hours.