SHOW TRANSACTIONS

The SHOW TRANSACTIONS statement lists details about currently active transactions, including:

The node running the transaction
The application that initiated the transaction
The number of statements that have been executed on the transaction
The number of times the transaction was retried (both manually and by the SQL executor)

These details let you monitor the overall state of transactions and identify those that may need further investigation or adjustment.

Required privileges

No privileges are required to execute this statement. However, note that non-admin users see only their own currently active transactions, whereas the admin users see all users’ currently active transactions.

Syntax

SHOW [ALL] [CLUSTER | LOCAL] TRANSACTIONS

To list the active transactions across all nodes of the cluster, use SHOW TRANSACTIONS or SHOW CLUSTER TRANSACTIONS.
To list the active transactions just on the gateway node, use SHOW LOCAL TRANSACTIONS.
To list internal transactions (that are issued by the database itself), use SHOW ALL.. TRANSACTIONS.

Response

The following fields are returned for each transaction:

Field	Description
`node\_id`	The ID of the node running the transaction.
`txn\_id`	The ID of the transaction.
`application\_name`	The application name specified by the client, if any. For transactions from the built-in SQL client, this will be `$ cockroach sql`.
`num\_stmts`	The number of statements that have been executed on the transaction.
`num\_retries`	The number of times that the transaction was retried.
`num\_auto\_retries`	The number of times that the transaction was automatically retried by the SQL executor.

Examples

List active transactions across the cluster

> SHOW CLUSTER TRANSACTIONS;

  node_id |                txn_id                | application_name | num_stmts | num_retries | num_auto_retries
----------+--------------------------------------+------------------+-----------+-------------+-------------------
        1 | 1f02ca1c-46c8-491b-8048-daa6b38fc587 | $ cockroach sql  |         1 |           0 |                0
        2 | 3af3f2e3-d0ed-4fd4-886b-1f5fc67b397d | movr             |         1 |           0 |                0
(2 rows)

Alternatively, you can use SHOW TRANSACTIONS to receive the same response.

List active transactions on the gateway node

> SHOW LOCAL TRANSACTIONS;

  node_id |                txn_id                | application_name | num_stmts | num_retries | num_auto_retries
----------+--------------------------------------+------------------+-----------+-------------+-------------------
        1 | 1f02ca1c-46c8-491b-8048-daa6b38fc587 | $ cockroach sql  |         1 |           0 |                0
(1 row)

Filter for specific transactions

You can use a SELECT statement to filter the list of currently active transactions by one or more of the response fields.

Show transactions associated with a specific application

> WITH x as (SHOW CLUSTER TRANSACTIONS) SELECT * FROM x WHERE application_name = 'movr';

  node_id |                txn_id                | application_name | num_stmts | num_retries | num_auto_retries
----------+--------------------------------------+------------------+-----------+-------------+-------------------
        1 | 59891000-260a-44f6-abf9-59c94fefc081 | movr             |         1 |           0 |                0
(1 row)

Exclude transactions from the built-in SQL client

To exclude transactions from the built-in SQL client, filter for transactions that do not show $ cockroach sql as the application_name:

> WITH x AS (SHOW CLUSTER TRANSACTIONS) SELECT * FROM x
      WHERE application_name != '$ cockroach sql';

  node_id |                txn_id                | application_name | num_stmts | num_retries | num_auto_retries
----------+--------------------------------------+------------------+-----------+-------------+-------------------
        1 | 8851060b-5e7c-4402-a8cd-487baa63e02e | movr             |         1 |           0 |                0
(1 row)

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Syntax

Response

Examples

List active transactions across the cluster

List active transactions on the gateway node

Filter for specific transactions

Show transactions associated with a specific application

Exclude transactions from the built-in SQL client

See also

​Required privileges

​Syntax

​Response

​Examples

​List active transactions across the cluster

​List active transactions on the gateway node

​Filter for specific transactions

​Show transactions associated with a specific application

​Exclude transactions from the built-in SQL client

​See also

Required privileges

Syntax

Response

Examples

List active transactions across the cluster

List active transactions on the gateway node

Filter for specific transactions

Show transactions associated with a specific application

Exclude transactions from the built-in SQL client

See also