ClickHouse DB::Exception: Invalid transaction

ClickHouse raises the INVALID_TRANSACTION error when a transaction-related operation is attempted but the transaction is in an invalid or unexpected state. This can happen when you try to commit or roll back a transaction that has already been aborted, or when you issue transaction commands in the wrong order. ClickHouse's transaction support (available for specific engines) has strict state machine rules, and violating them triggers this error.

Impact

The current transaction cannot proceed and must be rolled back (if it has not been already). Any pending writes within the transaction are discarded. Applications that depend on transactional guarantees will need to restart the entire transaction from scratch. If the error occurs in automated pipelines, it can stall data ingestion until the transaction state is cleared.

Common Causes

1. Issuing COMMIT after the transaction was already aborted -- If a query within a transaction fails, the transaction enters a failed state. Attempting to COMMIT instead of ROLLBACK triggers this error.
2. Nested BEGIN statements -- ClickHouse does not support nested transactions. Issuing BEGIN while already inside a transaction results in an invalid state.
3. Using transaction commands without session context -- Transactions require a session. Issuing BEGIN over a stateless HTTP connection without a session ID leads to errors.
4. Transaction timeout -- If a transaction exceeds the configured timeout, it may be automatically aborted. Subsequent operations on that transaction will fail.
5. Mixing DDL and DML in a transaction -- DDL statements (like ALTER TABLE) are generally not supported inside transactions, and attempting them can corrupt the transaction state.

Troubleshooting and Resolution Steps

1. Roll back the invalid transaction: If a transaction is in a bad state, explicitly roll it back before starting a new one:

   ROLLBACK;
   

2. Follow the correct transaction lifecycle: Ensure your code follows the proper sequence:

   BEGIN TRANSACTION;
   INSERT INTO my_table VALUES (1, 'data');
   INSERT INTO my_table VALUES (2, 'more_data');
   COMMIT;
   

   If any statement fails between BEGIN and COMMIT, issue ROLLBACK instead.

3. Check for error handling gaps: Ensure your application catches errors within a transaction and rolls back properly:

   try:
       client.execute("BEGIN TRANSACTION")
       client.execute("INSERT INTO my_table VALUES (1, 'data')")
       client.execute("INSERT INTO my_table VALUES (2, 'more_data')")
       client.execute("COMMIT")
   except Exception:
       client.execute("ROLLBACK")
       raise
   

4. Verify session and transaction compatibility: Transactions require a session. When using the HTTP interface, make sure you have a session_id:

   curl "http://localhost:8123/?session_id=txn_session" \
     --data-binary "BEGIN TRANSACTION"
   curl "http://localhost:8123/?session_id=txn_session" \
     --data-binary "INSERT INTO my_table VALUES (1, 'data')"
   curl "http://localhost:8123/?session_id=txn_session" \
     --data-binary "COMMIT"
   

5. Avoid DDL inside transactions: Keep DDL statements outside of transactions. Only DML operations (INSERT, DELETE for lightweight deletes) are safe within a transaction.

6. Check transaction timeout settings: If transactions are timing out, review and adjust relevant settings:

   SELECT name, value FROM system.settings WHERE name LIKE '%transaction%';
   

Best Practices

• Always wrap transaction logic in try/catch blocks with explicit ROLLBACK in the error path.
• Keep transactions short -- long-running transactions are more likely to time out or conflict.
• Avoid DDL operations inside transactions entirely.
• Use a single session per transaction and do not share sessions across concurrent transactions.
• Test transaction error handling paths explicitly, not just the happy path.

Frequently Asked Questions

Q: Which ClickHouse table engines support transactions?
A: Transactions are primarily supported by the MergeTree family of engines when used with specific settings. Support was introduced as an experimental feature and has been expanded over time. Check your ClickHouse version's documentation for the current scope of transaction support.

Q: Can I retry a transaction after getting INVALID_TRANSACTION?
A: Yes, but you must first ROLLBACK the failed transaction and then start a completely new one with BEGIN TRANSACTION. You cannot resume or repair the invalid transaction.

Q: Does ClickHouse support savepoints?
A: No. ClickHouse does not currently support savepoints or nested transactions. A transaction is all-or-nothing.

Q: Why did my transaction become invalid even though all my queries succeeded?
A: The transaction may have timed out due to inactivity, or a concurrent operation may have interfered. Check the server logs for timeout or conflict messages associated with your transaction.