Why did my Amazon Redshift query status change from "Completed" to "Aborted" with no updates?

Short description

SQL statements that change data or create database objects persist only after you commit the transaction. TRUNCATE statements are an exception because they automatically commit.

The query status for a SQL statement that remains in an open transaction shows "Completed" on the Amazon Redshift console. The status changes to "Aborted" when you roll back the transaction. When the "Aborted" column equals 0, the STL_QUERY system table shows a successful statement completion.

After you commit the transaction, your changes appear. If Amazon Redshift can't commit the transaction, then the console displays an "Aborted" status. You can review the STL system tables to determine why Amazon Redshift didn't commit your transaction.

Resolution

Check transaction details

To view all SQL statements in an Amazon Redshift transaction, including commits and rollbacks, run the following query:

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

Note: Replace Query ID with your query ID. For Amazon Redshift Serverless queries, replace SVL_STATEMENTTEXT and STL_QUERY with SYS_QUERY_HISTORY.

The output shows an "Undoing 1 transactions" message for a rolled back transaction.

If the SVL_STATEMENTTEXT table doesn't show a COMMIT, ROLLBACK, or "Undoing 1 transactions" message, then the transaction ID (xid) might remain open. Use the SVV_TRANSACTIONS view to identify open transactions and LOCK conflicts.

For more information, see How do I detect and release locks in Amazon Redshift?

Check transaction commit status

Check the transaction commit status for transactions that involve DML or DDL statements.

To check successful commits with exact timestamps and node-level status, run the following STL_COMMIT_STATS query:

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

Note: Replace QUERY ID with your query ID.

A successful COMMIT makes transaction changes permanent and durable. All transaction IDs that you create after the COMMIT can view the changes. For more information, see Serializable isolation.

Check for rollbacks

To check only the transactions that rolled back, run the STL_UNDONE query:

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

Note: Replace QUERY ID with your query ID.

Transactions fail to save changes because of ROLLBACK commands or incomplete execution.

Serializable isolation violations, administrator session terminations, query cancellations, and network connection timeouts also prevent transaction completion. A rollback initiates a detailed error message to the client. It's a best practice to configure your client to log errors.

For more information, see LogLevel.

Related information

STL_DDLTEXT