Why transaction will time out in stored procedure
In installable cloud apps, network connection problems can cause commands to fail, and if the transaction timeout value is not set properly, it can lead to deadlocks of concurrent calls. Therefore, it is very important to set a proper value for transaction timeout in PowerServer. However, there is no such concept as transaction timeout in PowerBuilder traditional C/S applications because there is no network connection issues in C/S architecture.
Therefore, you may encounter the transaction timeout error in PowerServer, especially in stored procedures and cursors.
In PowerServer, when AutoCommit = false:
-
When querying the database (such as running DataWindow Retrieve or embedded SQL Select), PowerServer automatically starts and commits/rolls back transactions (implicit management).
-
When updating the database (such as running DataWindow Update or embedded SQL Update/Delete), PowerServer starts a transaction automatically and will not close the transaction until a Commit/Rollback is explicitly executed in the code or the transaction timeout is reached.
The challenge arises because both PowerBuilder and PowerServer have no way of knowing if the stored procedure (or cursor) being invoked is used for querying purpose only or if it also involves database updates; so to ensure transaction integrity, the execution of a stored procedure or cursor is considered an update by default.
This is where the problem comes in PowerServer: when a stored procedure is executed only for querying purposes and the transaction is started automatically, and if the transaction is not explicitly committed or rolled back, then when the transaction timed out in PowerServer, PowerServer will automatically recycle the transaction; resulting in a "Transaction is timeout" or "Transaction does not exist" error when the same transaction object is used again for other database operations.
How to resolve the timeout issue in stored procedure
To resolve the timeout issue in stored procedure, you can utilize the ProcedureInTransaction parameter of Transaction.DBParm based on how stored procedures and cursors are used in your project:
-
If the stored procedure is only used for querying purpose and does not involve any updates, it is not necessary to start a transaction (set ProcedureInTransaction to 0 to not start a transaction).
-
If a small amount of code uses the stored procedure for database updates, set ProcedureInTransaction to 1 during the update and then set it to 0 after the transaction is committed. This ensure the transaction is properly managed for the specific code segment. View code examples here.
-
If a large amount of code uses the stored procedure for database updates, and substantial code changes need to make, it is recommended to keep the default value (=1) of ProcedureInTransaction and increase the transaction timeout value to 3600 or higher (to be the same as the session timeout value) so as to accommodate the potentially longer transaction duration. For how to configure the timeout value, refer to Configure the timeout settings.
Note: It is not advisable to use long connections as a solution to this problem. Even with a long connection, the error will still occur after exceeding the Transaction Timeout. Therefore, adjusting the ProcedureInTransaction parameter and Transaction Timeout value are the recommended approaches to address the timeout issue in stored procedures.
