Cloudera Docs

Options to monitor transactions

As a Hive administrator, you can view the list of all currently open and aborted transactions using the SHOW TRANSACTIONS statement or by querying the TRANSACTIONS view within the SYS database.

The query statements display the following details about transactions:

  • TXN_ID: Unique internal transaction ID
  • STATE: Transaction state
  • STARTED: Timestamp when the transaction was started
  • LAST_HEARTBEAT: Timestamp of the latest heartbeat
  • USER: Hive user who initiated the transaction
  • HOST: Host machine or virtual machine where the transaction was initiated
  • HEARTBEAT_COUNT: Total number of heartbeats
  • TYPE: Transaction type
    • DEFAULT
    • REPL_CREATED
    • READ_ONLY
    • COMPACTION
  • TC_DATABASE: Hive database name
  • TC_TABLE: Table name
  • TC_PARTITION: Partition name (if the table is partitioned)
  • TC_OPERATION_TYPE:
    • SELECT
    • INSERT
    • UPDATE
    • COMPACT
  • TC_WRITEID: Unique internal write ID

The following sections describe the various options that you can use to monitor transactions.

SHOW TRANSACTIONS

You can run the SHOW TRANSACTIONS statement to view details about all open and aborted transactions.

SHOW TRANSACTIONS;

This statement lists all the transactions and you cannot filter or limit the results as required. Alternatively, you can use the SYS database to query and filter specific transactions.

Querying the SYS database

You can query the TRANSACTIONS view within the SYS database to filter and view specific transactions.

For example, you can run the following query to view transactions in a particular state:
SELECT *
FROM SYS.TRANSACTIONS
WHERE STATE='aborted';