Blog › ICP guides

Sybase developer on retainer: transaction ROLLBACK missing on failure branch, SAP Sybase ASE developer, PowerBuilder Sybase developer on monthly retainer

October 9, 2026 · ~15 min read

A Sybase ASE developer was maintaining a trade order management system at a mid-size financial services firm running Sybase ASE 15.7 on a Sun SPARC server. The database served a PowerBuilder application used by 40 traders across two trading desks. A stored procedure named sp_ProcessTrade processed new trade orders: it opened a transaction with BEGIN TRANSACTION, updated the TradeOrders table to record the new trade, then ran a risk limit check against the trader’s current position and the firm’s exposure limits. If the risk check failed, the procedure called RAISERROR 20011 'Trade exceeds risk limit, contact risk desk' WITH NOWAIT followed by RETURN to signal the error to the calling PowerBuilder DataWindow transaction. The developer assumed that RAISERROR would roll back the open transaction — analogous to how exceptions in modern application frameworks automatically unwind transaction scope. In Sybase ASE, RAISERROR does not roll back the current transaction. The RETURN statement exits the stored procedure, returning control to the calling application with an error status, but the BEGIN TRANSACTION opened by sp_ProcessTrade remains open with its exclusive row-level lock on the TradeOrders row held at the database connection level. Other traders submitting trades for the same instrument received Sybase ASE deadlock victim error 1205 when their transactions attempted to lock the same rows, or waited indefinitely for the lock. Deadlock errors per trading session: 3–5 before fix; 0 after ROLLBACK TRANSACTION was added before every RAISERROR + RETURN on the failure branches.

The root cause was the difference between Sybase ASE’s transaction model and the developer’s assumption about RAISERROR behavior. In Sybase ASE’s T-SQL implementation, RAISERROR raises a user-defined error message to the client application but has no effect on the current transaction scope. Unlike SQL Server’s THROW statement (which can terminate a transaction if used inside a TRY/CATCH block that rolls back on catch), Sybase ASE’s RAISERROR is a notification mechanism only — it does not commit, rollback, or otherwise affect any open transaction. The RETURN statement that follows RAISERROR exits the stored procedure, decrementing the @@nestlevel counter for stored procedure call depth. But exiting a stored procedure does not alter the transaction’s commit/rollback state: if sp_ProcessTrade opened a transaction with BEGIN TRANSACTION, and the procedure exits via RETURN without issuing COMMIT TRANSACTION or ROLLBACK TRANSACTION, the transaction remains open in the calling database session. The PowerBuilder application’s transaction object (the SQLCA) continued operating with an open transaction after receiving the error from sp_ProcessTrade — subsequent DataWindow SQL operations issued by that trader’s session operated within the lingering open transaction, accumulating additional locks without the developer realizing it.

The invisibility of the bug during development has a structural cause. The developer who wrote and tested sp_ProcessTrade tested the risk limit failure path by submitting a trade that exceeded the risk limit from their own trading desk workstation. They verified that the PowerBuilder DataWindow displayed the “Trade exceeds risk limit, contact risk desk” error message as expected. From the developer’s perspective, the procedure worked correctly: the trade was not committed to the database (because COMMIT TRANSACTION was never called on the failure path), and the error was surfaced to the operator. The developer did not check sp_lock output after the failure to verify that the TradeOrders row locks had been released — there was no immediate indication that they had not been. The locks held by the open transaction became visible only when a second trader attempted to access the same instrument rows: the second trader’s transaction either waited indefinitely (if Sybase ASE’s lock wait parameter was set to −1 for indefinite wait) or was killed as a deadlock victim (error 1205) if the deadlock detection cycle identified the circular wait. The first trader’s terminal showed no error; the second trader’s terminal received a deadlock error with no indication of what caused it. Both the operations desk and the IT team initially diagnosed these as “random deadlocks” and escalated to the Sybase DBA only after the frequency reached 3–5 per session.

Sybase ASE: from Epstein and Hoffman’s Sybase Inc. to SAP’s Adaptive Server Enterprise

Sybase Inc. was founded in Berkeley, California in 1984 by Bob Epstein and Mark Hoffman, two former Relational Technology Inc. (Ingres) engineers, with the goal of building a high-performance RDBMS optimized for transaction-intensive financial and telecommunications workloads. The original Sybase SQL Server (version 1.0, 1987) introduced several architectural innovations that distinguished it from the competing IBM DB2 and Oracle RDBMS products of the era: a multi-threaded server architecture (rather than multi-process), client-server communication via the Tabular Data Stream (TDS) protocol over TCP/IP, stored procedures compiled and cached in the procedure cache for fast re-execution, and row-level locking (rather than page-level locking) as an option for high-concurrency tables. The TDS protocol and the stored procedure architecture became the template for the relational database industry: Microsoft licensed Sybase’s technology in 1988 to build its own SQL Server product, and the two companies co-developed Sybase SQL Server 4.2 before Microsoft and Sybase diverged on separate development paths in 1994. SQL Server 6.0 (1995) was Microsoft’s first independently developed release; Sybase’s concurrent development track became Sybase SQL Server 11.0, later renamed Adaptive Server Enterprise (ASE).

Sybase ASE achieved dominant market share in the financial services sector throughout the 1990s and 2000s. The combination of high-throughput transaction processing, the TDS wire protocol’s low latency for stored procedure calls, and Sybase’s aggressive marketing to Wall Street firms and European banks produced a concentration of Sybase ASE deployments in trading systems, banking core systems, and insurance policy management systems that is still visible today. J.P. Morgan, Deutsche Bank, Barclays, Credit Suisse, and many other major financial institutions built core banking and trading systems on Sybase ASE infrastructure in the 1990s. Sybase’s companion product PowerBuilder — a 4GL rapid application development tool for building client-server Windows applications with a DataWindow drag-and-drop query builder — integrated tightly with Sybase ASE via the native CT-Lib connection, making it the tool of choice for Sybase-backed financial application development. The Sybase ASE / PowerBuilder stack is the financial services equivalent of the Oracle Forms / Oracle Database stack: deeply embedded in institutional financial IT infrastructure, maintained by specialists with deep institutional knowledge, and extremely difficult to migrate off without business disruption and multi-year re-implementation projects.

SAP acquired Sybase in 2010 for $5.8 billion and renamed the product SAP Adaptive Server Enterprise (SAP ASE). SAP ASE 16.0 (2014) and SAP ASE 16.3 (2019) added enterprise features including in-memory computing acceleration, compression enhancements, and expanded cloud deployment support, while maintaining backward compatibility with the T-SQL stored procedure language and the TDS wire protocol. SAP ASE 16.3 introduced TRY/CATCH error handling to Sybase T-SQL — a significant change from the classic Sybase error handling model of checking @@error after each statement and branching conditionally. The TRY/CATCH addition creates an important migration opportunity for stored procedures with the open-transaction-on-RAISERROR pattern: the correct SAP ASE 16.3 pattern for stored procedures with transactional error handling is a TRY block containing the BEGIN TRANSACTION and all data modification statements, with a CATCH block that issues ROLLBACK TRANSACTION before re-raising the error. Retainer developers migrating legacy Sybase ASE 12.x or 15.x stored procedures to SAP ASE 16.3 must audit every stored procedure that uses BEGIN TRANSACTION for the open-transaction-on-RAISERROR pattern before converting to TRY/CATCH — the migration is the correct moment to fix all such procedures.

Sybase ASE’s transaction model has precise semantics around transaction nesting, the @@trancount system function, and the behavior of ROLLBACK TRANSACTION in nested procedures. @@trancount tracks the current transaction nesting depth: each BEGIN TRANSACTION increments @@trancount by 1; each COMMIT TRANSACTION decrements it by 1 (the transaction only commits to disk when @@trancount reaches 0); each ROLLBACK TRANSACTION sets @@trancount to 0 and rolls back all work since the outermost BEGIN TRANSACTION. A ROLLBACK TRANSACTION inside a stored procedure called from a calling procedure that opened the transaction does not just roll back the inner procedure’s work — it rolls back the entire transaction stack and sets @@trancount to 0 in the calling session. This means that if a calling PowerBuilder DataWindow opened a transaction before calling sp_ProcessTrade, and sp_ProcessTrade issues ROLLBACK TRANSACTION on the failure path, the entire caller’s transaction is also rolled back. Understanding this interaction between @@trancount, nested stored procedure calls, and ROLLBACK TRANSACTION scope is one of the most nuanced aspects of Sybase ASE retainer work, and it is the reason that adding ROLLBACK TRANSACTION to a stored procedure failure branch is not always a simple one-line fix: the retainer developer must understand whether the stored procedure is always called within a caller-opened transaction, whether @@trancount is checked after the rollback to handle the @@trancount = 0 case in the caller, and whether the stored procedure needs a savepoint (SAVE TRANSACTION savepoint_name) to roll back only to the savepoint rather than the full outer transaction.

Sybase ASE transaction model: RAISERROR, @@trancount, and open connection lock hold

The Sybase ASE transaction model’s interaction with RAISERROR and RETURN is the source of the most common category of Sybase production lock bugs. In Sybase ASE T-SQL, a transaction opened with BEGIN TRANSACTION in a stored procedure remains open in the database session after the stored procedure exits via RETURN, unless either COMMIT TRANSACTION or ROLLBACK TRANSACTION has been explicitly called. RAISERROR sets @@error to the error number and sends the error message to the client, but it has no effect on @@trancount or the state of any active transaction. When sp_ProcessTrade executes RAISERROR 20011 ... WITH NOWAIT followed by RETURN, the following chain of events occurs in the Sybase ASE session: (1) The RAISERROR sends the error message to the PowerBuilder application’s transaction object; (2) @@error is set to 20011 in the stored procedure’s scope; (3) RETURN exits the stored procedure and decrements @@nestlevel; (4) The PowerBuilder SQLCA transaction object receives the error in its SQLErrText property and sets SQLCode to -1; (5) The database session remains in an open transaction state: @@trancount = 1 in the session’s connection-level scope; (6) The exclusive row-level lock on the TradeOrders row acquired by the UPDATE statement inside sp_ProcessTrade remains held by the session. If the PowerBuilder application’s error handler does not issue an explicit ROLLBACK (or call a stored procedure that does) after receiving SQLCode = -1, the connection remains in the open-transaction state holding the row lock until the connection is closed, the session times out, or a DBA explicitly kills the session with KILL spid.

Diagnosing the open-transaction lock hold in Sybase ASE requires using the system stored procedures designed for lock and session inspection. The primary diagnostic query is: sp_lock — calling sp_lock (with no arguments, or with a specific SPID argument) returns a table of all current locks held in the Sybase ASE server, including the lock type (shared, exclusive, update, intent), the table and row being locked, and the SPID (server process ID) of the holding session. The output of sp_lock during the sp_ProcessTrade incident shows an exclusive lock (locktype = “Ex_row” for exclusive row-level lock on a table configured with lock datarows) on the TradeOrders table, held by a SPID corresponding to the first trader’s PowerBuilder session. Cross-referencing with sp_who2 (which shows all active sessions with their login names, host names, and current commands) identifies the specific trader’s workstation and the PowerBuilder application holding the lock. DBCC sqltext(spid) retrieves the text of the last SQL statement executed by the SPID, which shows the sp_ProcessTrade stored procedure call or the last DataWindow statement issued after the error — confirming that the SPID is in an open transaction state. To identify the open transaction directly: SELECT @@trancount executed in the context of the holding session (using Sybase Central or isql with the holding SPID’s connection) returns 1, confirming an open transaction with no matching commit or rollback.

The correct fix for sp_ProcessTrade requires adding ROLLBACK TRANSACTION before every RAISERROR + RETURN on any failure branch within the BEGIN TRANSACTION scope, and understanding the @@trancount interaction for nested transactions. The corrected stored procedure pattern for the risk limit check failure: BEGIN TRANSACTION at the procedure entry; UPDATE TradeOrders ...; IF @risk_check_failed BEGIN ROLLBACK TRANSACTION; RAISERROR 20011 'Trade exceeds risk limit' WITH NOWAIT; RETURN END; COMMIT TRANSACTION at the success exit. If the stored procedure is called from a PowerBuilder DataWindow that has already opened its own transaction, the ROLLBACK TRANSACTION inside sp_ProcessTrade will roll back the outer transaction as well (setting @@trancount to 0). In this case, the correct pattern is to use SAVE TRANSACTION sp_ProcessTrade_savepoint at the start of sp_ProcessTrade and ROLLBACK TRANSACTION sp_ProcessTrade_savepoint on the failure path — rolling back only to the savepoint without affecting the caller’s outer transaction. The savepoint approach preserves any work done by the outer transaction before calling sp_ProcessTrade, while ensuring that the inner procedure’s partial work is rolled back on the failure path. The retainer developer must determine which approach is appropriate by reviewing the PowerBuilder DataWindow’s transaction management code (the BeginTran() / CommitTrans() calls in the DataWindow event scripts) and the full call chain from the PowerBuilder application to the stored procedure.

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

Open transaction lock hold caused by ROLLBACK missing on RAISERROR + RETURN is the canonical Sybase ASE invisible production lock bug, and it appears in the work logs of nearly every Sybase retainer engagement for financial trading, banking, or insurance core system applications with stored procedures containing validation logic. The pattern is consistent across Sybase ASE 12.x, 15.x, and SAP ASE 16.x: a stored procedure opens a transaction, performs data modifications, encounters a validation failure, and raises an error without rolling back; the open transaction holds locks on the modified rows; concurrent connections attempting to modify the same rows receive deadlock errors or lock-wait timeouts; the incident is attributed to “random deadlocks” and resolved by killing the holding session. The work log entry that makes this diagnosable and auditable: “TradeDB (Sybase ASE 15.7); stored procedure sp_ProcessTrade; BEGIN TRANSACTION at line 12; UPDATE TradeOrders SET ... at line 18 (acquires exclusive row-level lock on TradeOrders row for trade_id); risk limit check failure branch at line 34: RAISERROR 20011 'Trade exceeds risk limit' WITH NOWAIT + RETURN without ROLLBACK TRANSACTION; @@trancount = 1 in the holding session after RETURN (confirmed via sp_lock during incident); exclusive lock type: Ex_row on TradeOrders (table configured with lock datarows); holding SPID identified via sp_lock: SPID 47, login name TRADER01, host name DESK-A-01 (confirmed via sp_who2); concurrent connections receiving deadlock victim error 1205: 3–5 per trading session before fix; fix: ROLLBACK TRANSACTION added before RAISERROR 20011 + RETURN on risk limit failure branch; @@trancount confirmed 0 after fix on failure path; deadlock errors per trading session after fix: 0; 4h.”

Sybase ASE stored procedure maintenance and T-SQL tuning is the routine day-to-day Sybase retainer task for financial system databases with large stored procedure libraries. The typical engagement: the trading desk reports that a batch reconciliation stored procedure sp_EOD_Reconcile takes 45 minutes to complete end-of-day, up from 12 minutes six months ago when the TradeOrders table was a tenth of its current size. The retainer developer runs the reconciliation procedure with Sybase ASE’s procedure trace enabled (SET SHOWPLAN ON and SET STATISTICS IO ON before calling the procedure) to capture the query plan and I/O statistics for each statement in the procedure. The trace output reveals that a SELECT joining TradeOrders to InstrumentMaster and FXRates is performing a table scan on TradeOrders (1.2 million rows) because the query’s WHERE clause includes a function call on the trade_date column (WHERE DATEPART(year, trade_date) = @year AND DATEPART(month, trade_date) = @month) that prevents index use. The fix is rewriting the predicate as a range condition on the indexed column (WHERE trade_date >= @start_date AND trade_date < @end_date) and updating the query optimizer’s statistics (UPDATE STATISTICS TradeOrders) to reflect the current row distribution. Work log: “TradeDB (Sybase ASE 15.7); sp_EOD_Reconcile; SHOWPLAN trace identified table scan on TradeOrders (1.2M rows) due to DATEPART function on trade_date in WHERE clause; fix: rewrote DATEPART predicate as range condition on trade_date using @start_date and @end_date parameters; UPDATE STATISTICS TradeOrders; execution time: 45 minutes → 8 minutes; 3h.”

SAP ASE 16.3 migration work — TRY/CATCH error handling conversion is the highest-investment Sybase retainer engagement category for clients running Sybase ASE 12.x or 15.x who need the security patches, in-memory acceleration features, and official SAP support available only in SAP ASE 16.x. The migration begins with a stored procedure inventory: count the stored procedures that use BEGIN TRANSACTION, identify those that use the classic IF @@error != 0 ... RAISERROR ... RETURN error handling pattern, and flag those with open-transaction-on-RAISERROR bugs as immediate pre-migration fix targets. The retainer developer converts the flagged procedures to the SAP ASE 16.3 TRY/CATCH pattern: the BEGIN TRANSACTION and all data modification statements move inside the TRY block; the CATCH block contains IF @@trancount > 0 ROLLBACK TRANSACTION followed by a re-raise using RAISERROR with the error information from the caught exception context. The TRY/CATCH conversion automatically handles the open-transaction-on-error pattern by centralizing rollback logic in the CATCH block, replacing the scattered IF @@error != 0 ... RAISERROR ... RETURN checks throughout the procedure body. Each migrated procedure is regression-tested against the production transaction patterns using Sybase ASE’s DBCC traceon(3604) trace flags to verify lock acquisition and release behavior. Work log: “TradeDB ASE 15.7 → SAP ASE 16.3 migration; stored procedure inventory: 184 stored procedures; 47 using BEGIN TRANSACTION; 12 identified with RAISERROR + RETURN without ROLLBACK TRANSACTION (open-transaction-lock pattern); 12 procedures migrated to TRY/CATCH with @@trancount > 0 ROLLBACK TRANSACTION in CATCH block; sp_ProcessTrade updated (the incident procedure from last quarter); regression tested 12 procedures for lock acquire/release behavior via sp_lock monitoring; hours: 16h across 2 sessions.”

How HourTab tracks Sybase developer retainer hours

Sybase ASE open-transaction lock bugs caused by ROLLBACK TRANSACTION missing on RAISERROR + RETURN paths are invisible in development testing by exactly the mechanism that makes all open-transaction lock bugs invisible when only one developer tests a procedure. The developer who writes and tests sp_ProcessTrade tests the risk limit failure path by submitting a trade that violates the limit from their own PowerBuilder session. They verify that the error message appears in the DataWindow and that the trade record is not committed to the database. From the developer’s perspective, the feature works: the error is surfaced and the transaction is not committed. The developer does not check sp_lock output after the failure to verify that the TradeOrders row lock has been released — there is no visible consequence in a single-session test because no other session is competing for the locked row. The open transaction is entirely invisible to the developer at test time. In production, the lock contention pattern emerges only when two conditions are true simultaneously: a trader’s session holds an open transaction lock on a TradeOrders row from a risk limit failure, and another trader’s session attempts to modify a row that is locked or create a deadlock cycle through the open transaction. Both conditions require concurrent sessions, which cannot occur in single-developer testing.

The work log entry that makes Sybase ASE open-transaction lock work auditable must name every element of the causal chain. The database name and ASE version (TradeDB, Sybase ASE 15.7). The stored procedure name (sp_ProcessTrade). The BEGIN TRANSACTION call site (line 12). The data modification statement and the lock it acquired (UPDATE TradeOrders SET ... at line 18, exclusive row-level lock on the TradeOrders row for the specific trade_id). The failure branch that lacked ROLLBACK TRANSACTION (risk limit check failure at line 34: RAISERROR 20011 'Trade exceeds risk limit' WITH NOWAIT + RETURN). The @@trancount state after the failure (1, confirmed via sp_lock during incident). The lock type (Ex_row on TradeOrders, table configured with lock datarows). The holding SPID and trader identity (SPID 47, login TRADER01, host DESK-A-01). The impact (3–5 deadlock victim error 1205 reports per trading session, other traders receiving errors on trade submissions for the same instrument). The fix (ROLLBACK TRANSACTION added before RAISERROR 20011 + RETURN on risk limit failure branch). The post-fix state (@@trancount = 0 on failure path, confirmed; 0 deadlock errors per trading session). Hours (4h). A log that says “fixed Sybase deadlock, 4h” is not defensible to a trading desk operations manager or a banking IT risk committee. A log that names the procedure, the transaction state, the sp_lock evidence, and the before/after deadlock rate is auditable and builds the trust that sustains a long-term Sybase retainer relationship. HourTab gives Sybase developers a public retainer-hours URL they send to clients — financial institutions, trading firms, banks, and insurance companies that built core transaction processing systems on Sybase ASE infrastructure in the 1990s and 2000s and maintain them today with a Sybase specialist on retainer.

The broader context for Sybase ASE retainer billing is that the open-transaction-ROLLBACK-missing pattern is not unique to Sybase — it is the universal consequence of explicit transaction management in stored procedure languages where the developer controls every commit and rollback boundary. Progress OpenEdge ABL developer retainers cover the Progress ABL FIND FIRST EXCLUSIVE-LOCK without RELEASE pattern — the same lock acquired during a validation operation and not released on the failure branch, the same production concurrency errors invisible in single-user development testing, structurally identical to Sybase ASE’s open-transaction-on-RAISERROR-without-ROLLBACK. InterSystems Caché developer retainers cover the Caché ObjectScript global lock not released on failure — the same lock-not-released-on-failure-branch class, where LOCK +^GlobalName acquires a global lock that persists until an explicit LOCK -^GlobalName releases it, structurally analogous to Sybase ASE’s open transaction holding row-level locks until an explicit ROLLBACK TRANSACTION. Magic xpa developer retainers cover the Magic xpa implicit transaction model and the “Quit task without Rollback” lock-hold pattern — the same implicit transaction lock accumulation where the platform-managed transaction holds all row-level locks until task exit, with the developer responsible for inserting the explicit rollback call on every failure path. In each case, the retainer developer’s value is the same: understanding the platform’s transaction semantics, diagnosing open-transaction lock accumulation in production via the platform’s system views, and adding the correct explicit rollback call on every failure path in the stored procedure or application code.

FAQ: Sybase ASE developer retainers