Why did my Amazon Redshift query status change from "Completed" to "Aborted" when no changes were made?

Last updated: 2020-09-18

The Amazon Redshift console shows that the query status is "Completed", but the status then changes to "Aborted". However, no updates were made to the table when I queried the results from a previous session or transaction. Why is this happening?

Short description

SQL statements that manipulate data or create database objects don't persist until the transaction is committed. This does not apply to TRUNCATE statements, which implicitly perform a COMMIT.

The Amazon Redshift console shows that the query status is "Completed" for a SQL statement if it's still in an open transaction. The status changes to "Aborted" if the transaction is rolled back. The STL_QUERY system table also shows that the SQL statement is successfully completed when the aborted column value is 0.

If the transaction is later committed, then the changes will appear. However, if the transaction can't be committed, the Amazon Redshift console shows that the query is aborted. To identify the reason your transaction can't be committed, check the STL system tables.

Resolution

Run the following query to check the SVL_STATEMENTTEXT system table and filter by the transaction ID (xid) of the SQL statement:

SELECT * FROM SVL_STATEMENTTEXT WHERE xid IN (SELECT xid FROM STL_QUERY WHERE query = [QUERY ID]) ORDER BY starttime, sequence;

If the result is a BEGIN statement with no corresponding END or COMMIT statement, the SQL client or driver's AUTO COMMIT setting is disabled. Depending on the SQL client or driver, you can enable the AUTO COMMIT setting. Alternatively, you can manually issue an explicit COMMIT or END statement when the transaction is complete.

When a SQL statement commits its changes, a corresponding entry is added to the STL_COMMIT_STATS system table. Run the following query to confirm that the changes are committed:

SELECT q.query, q.xid, NVL2(cs.endtime, cs.endtime::text, 'NO COMMIT') AS commit_endtime
FROM STL_QUERY q LEFT JOIN STL_COMMIT_STATS cs ON q.xid = cs.xid AND cs.node = -1
WHERE q.query = [QUERY ID];

If a SQL statement is unable to commit the changes and the transaction ends, an entry appears in the STL_UNDONE system table for the rollback. Run the following query to find out if the changes are rolled back:

SELECT *
FROM STL_UNDONE
WHERE xact_id_undone IN (SELECT xid from STL_QUERY where query = [QUERY ID]);

This query returns information about transactions that are rolled back, which means the transaction didn't run until completion and the changes weren't applied. Rollbacks occur when there is a serializable isolation violation, or when an administrator TERMINATES a session or CANCELS a query. Rollbacks can also be caused by any timeouts in network connection. If a rollback occurs, the client receives an error message with more details. Therefore, be sure that your client is configured to log errors.


Did this article help?


Do you need billing or technical support?