While waiting for a long running transaction, it is a smart idea to keep an eye on it. The following queries can give you information about what it is doing.

Check Transactions

Check for long running transactions on your engine.

SELECT id,
       state,
       abort_reason,
       CASE
         WHEN duration IS NULL THEN Timestampdiff(s, created_on,
                                    CURRENT_TIMESTAMP(6))
         ELSE duration
       END AS DURATION,
       finished_at
FROM   relationalai.api.transactions
WHERE  engine_name = 'genevieve_lalonde'
ORDER  BY created_on DESC;

The duration isn't recorded until the transaction finishes, so the case statement here calculates the current runtime of unfinished transactions.

Example result

-- ID	STATE	ABORT_REASON	DURATION	CREATED_ON	FINISHED_AT
-- 01c07652-0207-0aec-0002-28df05e0325a	RUNNING			2025-11-17 16:18:41.540 -0800	
-- 01c07651-0207-0aec-0002-28df05e0319a	COMPLETED		4605	2025-11-17 16:17:41.867 -0800	2025-11-17 16:17:46.472 -0800

In the result, note the transaction ID. If it has finished with an issue, note the aborted reason.

Cancel Unhealthy Transactions

If the transaction is taking too long or making other transactions queue, cancel the transaction with the command below. Enter the transaction ID where indicated.

CALL relationalai.api.cancel_transaction('TRANSACTION_ID_HERE');

If it was a very expensive transaction, then it can take a few minutes to cancel. This is because it must interrupt and stop all the in-flight materializations and calculations.

Cancel Your Own Transaction Only

That function requires a role with the all_resource_admin application role granted to it, such as the rai_admin. If you have insufficient privileges to interact with all transactions, use the limited function below to only cancel your own transactions.

CALL relationalai.api.cancel_own_transaction('TRANSACTION_ID_HERE');

Heavyweight Solution

If the transaction takes 10+ minutes and still hasn't canceled, then suspend the engine. Next resume the engine. For both, insert the engine name where indicated in the commands below.

CALL relationalai.api.suspend_engine('ENGINE_NAME_HERE');
CALL relationalai.api.resume_engine('ENGINE_NAME_HERE');

Restarting the engine like this will abort all transactions on it. This effectively stops the current transaction. However, it also clears out any other workload that was sent on the same engine. If transactions are queued, this action drops the whole queue. Transactions are not auto resumed when the engine resumes, so you will need to resend any queries that you want to run.

After Transaction Failure

If the transaction failed with an error, find out why so you can prevent recurrence.

Check for Errors

Check for any problems with a completed transaction using the command below, entering the transaction ID where indicated. This checks for any errors associated with the transaction.

SELECT * FROM TABLE(relationalai.api.get_transaction_problems('TRANSACTION_ID_HERE'));

That function get_transaction_problems() requires a role with the all_resource_admin application role granted to it, such as the rai_admin. If you have insufficient privileges to see all transactions, use the limited function below to only see problems with your own transactions.

SELECT * FROM TABLE(relationalai.api.get_own_transaction_problems('TRANSACTION_ID_HERE'));

Both of these functions will only have results if the transaction has finished. Otherwise they will return a warning like transaction hasn't finished yet, such as the one below:

Request failed for external function GET_TRANSACTION_PROBLEMS with remote service error: 400 '{"data":[[0,{"status":"Not Found","message":"error getting problems for transaction \"01c07652-0207-0aec-0002-28df05e0325a\": transaction \"01c07652-0207-0aec-0002-28df05e0325a\" hasn't finished (\"RUNNING\"): transaction hasn't finished yet"} ]]}'

Resolve Errors and Optimize Query

The error message returned should have more information on the failure and allow you to troubleshoot further. If the next steps are unclear from the error message, send us a message with the transaction ID, error message, and a description of the problem at support@relational.ai. We'll work with you to determine the cause of any problems, or help you rewrite the query to improve performance.

Query Timeout

By default, any transaction that runs for 24 hours will be automatically cancelled. This is configured by the parameter query_timeout_mins either in your raiconfig.toml file, or in your code in a config object. If you frequently have transactions that become long running on your account, control them by reducing this value.

For example if most transactions on your account complete in 30 minutes and only sometimes take 1-2 hours to complete, then reduce the query_timeout_mins to 3 hours. This is slightly above the longest expected transaction runtime.

Example of reducing this in your code in a config object:

cfg = rai.Config({
    "query_timeout_mins": "180", # 3 hours
})
model = rai.Model("my_model", config=cfg)

Further Information

For more information on these topics, please see our documentation:

Related to