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

3 minute read
0

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.

Short description

SQL statements that manipulate data or create database objects don't persist until the transaction is committed. This doesn't 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 that your transaction can't be committed, check the STL system tables.

Resolution

To confirm whether a transaction has been committed or rolled back, use the output of the following query on SVL_STATEMENTTEXT system table. Then, 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;

The output on the query displays an "Undoing 1 transactions" statement for the rolled back transaction.

If a transaction starts with a BEGIN statement, then the statement was explicitly opened by the user or application. The statement has to also be explicitly committed. Transactions that are not initiated with a BEGIN statement are usually auto-committed by the SQL client or driver's AUTO COMMIT option. If the option is disabled, the user has to explicitly send a COMMIT.

When a transaction is properly committed, transaction's changes are durable (persisted) and can be seen by other XIDs that have been initiated after the COMMIT statement. For more information, see Serializable isolation.

When there is no END, COMMIT, or "Undoing 1 transactions" message seen on the SVL_STATEMENTTEXT system table, the XID might still be open. Use the SVV_TRANSACTIONS view to identify open transactions and LOCK contention.

The systems tables STL_COMMIT_STATS and STL_UNDONE can also be used to confirm whether a transaction has ended with a COMMIT or a ROLLBACK.

Run the following query to find out if the changes have been 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];

Run the following query to find out if the changes have been rolled back:

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

Transaction changes aren't persisted due to an explicit ROLLBACK command or if it doesn't run until completion. Explicit ROLLBACKS can't occur when there is a serializable isolation violation. They also can't occur when an administrator TERMINATES a session or CANCELS a query. Timeouts in a network connection can also keep transaction changes from persisting.

If a rollback occurs, then the client receives an error message with more details. It's best practice to configure your client to log errors. For more information, see Configure logging (JDBC) or LogLevel (ODBC).

Related information

STL_DDLTEXT

AWS OFFICIAL
AWS OFFICIALUpdated a year ago