Symptom
The “Transaction does not exist” error pops up when you run a Cloud App and execute a database operation.
Environment
PowerServer 2022 and later
Cause 1
The actual amount of time required to execute the database transaction exceeds the Transaction Timeout setting, which is 120 second by default.
PowerBuilder doesn’t have the Transaction Timeout concept.
Resolution 1
Increase the Transaction Timeout value based on the application’s actual requirement. Note that it is recommended to set Transaction Timeout according to the needs of your application rather than excessively longer.
Please refer to: https://docs.appeon.com/ps2022/Configure_timeout_settings.html
Cause 2
The transaction management in the original PowerBuilder code has a defect in design. When the transaction object has AutoCommit = false, some transactions are not committed or rolled back timely.
Resolution 2
Add the corresponding Commit/Rollback logic to finish the transaction timely.
Cause 3
PowerServer’s n-tier architecture uses the short connection to connect to the database by default, which differs from PowerBuilder. Please refer to https://docs.appeon.com/ps2022/DB_connection_differences.html.
With this architecture, the transaction mechanism of PowerServer has to be redesigned to ensure PowerServer supports high concurrency while minimizing the occurrence of table deadlocks.
When AutoCommit = false, the way PowerServer manages transactions is different from that of PowerBuilder:
When only querying the database (such as running DataWindow retrieve or embedded SQL select), PowerServer uses implicit database transactions, which are automatically committed and rolled back.
When updating the database (such as running DataWindow update or embedded SQL update/delete), PowerServer starts a transaction scope that does not end until Commit/Rollback in the code.
PowerServer has no way to know if the stored procedure (or cursor) being invoked contains a database update operation.
To ensure maximum transaction integrity, the execution of a stored procedure (or cursor) is regarded as an update by default.
If you execute a stored procedure just to execute a query and the transaction is still not committed/rolled back explicitly by any code after exceeding the Transaction Timeout, PowerServer automatically recycles the transaction.
And when the same Transaction object is used again to execute other database operations, the “Transaction does not exist” error occurs.
(Note: When AutoCommit = true, PowerServer handles transactions in the exact same way as PowerBuilder.)
Resolution 3
According to your usage of stored procedure and cursor in your project, decide whether you need to adjust the ProcedureInTransaction parameter of Transaction.DBParm in the code (https://docs.appeon.com/pb2022/connection_reference/ProcedureInTransaction.html):
a) When no stored procedure is used in your project to perform a database update operation, change ProcedureInTransaction to 0.
b) When a small amount of code uses Procedure to perform a database update operation, change ProcedureInTransaction to 0 by default.
Temporarily change ProcedureInTransaction to 1 only in blocks of code that need to update the database using Stored Procedures or Cursors, and change it back to 0 after the transaction is committed.
c) When a large amount of code uses procedures to perform a database update operation, the amount of changes can be large.
In this case, it is recommended that you do not modify the ProcedureInTransaction and leave it at the default value and increase Transaction Timeout to 3600 or longer.
Refer to: https://docs.appeon.com/ps2022/Configure_timeout_settings.html
Important Note: For this kind of problem, it is not recommended to use long connection to solve it. Because with long connection, the error remains after it exceeds Transaction Timeout, so it’s not a resolution.
If you need to learn about long connection, you can refer to: https://docs.appeon.com/pb2022/connection_reference/LongConnection.html