The Amazon Redshift console shows that the query status is COMPLETED, but the status then changes to ABORTED. The SQL client response indicates that one or more commands are complete, but no data modifications were made to the table when querying the results from a previous session or transaction. What can I do to troubleshoot this issue?

SQL statements that manipulate data or create database objects, including but not limited to INSERT, DELETE, UPDATE, or COPY, do not persist until its transaction is committed.

Note: TRUNCATE implicitly performs a COMMIT.

The Amazon Redshift console shows that the query status is COMPLETED for a SQL statement if it is 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 completed successfully (aborted column = 0).

If the transaction is later committed, the changes will appear. However, if the transaction cannot be committed, the Amazon Redshift console shows that the query status is ABORTED for the SQL statement with the following transaction rollback log entry: Undoing x transactions on table table_oid.

This behavior is by design. You can use the following steps to identify the source of the issue.

1.    Check the SVL_STATEMENTTEXT system table and filter by the transaction ID (xid) of the SQL statement by running the following command:

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

The result is a BEGIN statement with no corresponding END or COMMIT statement. In this state, the data changes do not appear until a COMMIT or END statement is performed. This issue occurs when the SQL client or driver's AUTO COMMIT setting is disabled. Depending on the SQL client or driver, you can enable this setting, or you can manually issue an explicit COMMIT or END statement when the transaction is complete.

2.    When a SQL statement commits its changes, a corresponding entry is added to the STL_COMMIT_STATS system table. Confirm that the changes are committed by running the following command:

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];

3.    If a SQL statement is unable to COMMIT the changes and the transaction ends, there is an entry in the STL_UNDONE system table for the ROLLBACK. Confirm that the changes are rolled back by running the following command:

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

Note: This query shows information about transactions that are rolled back, which means the transaction did not run until completion and therefore the changes were not applied. This can happen when there is a serializable isolation violation, when an administrator TERMINATES a session or CANCELS a query, or when the network connection has timed out. In this example, the client receives an error message with more details, so be sure that your client is configured to log errors.


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2018-03-27