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. How can I troubleshoot this issue?

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 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 can't be committed, the Amazon Redshift console shows that the query status is ABORTED. Check the STL system tables to find out why the transaction could not be committed.

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 either enable this setting, or 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, there is an entry 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. 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 each of these situations, 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: 2019-03-25