Query management

This section describes facilities for query management.

1. List all running queries

An administrator is able to view all queries that are currently executing within the instance. Alternatively, the current user may view all of their own currently-executing queries.

Syntax:

CALL dbms.listQueries()

Returns:

Name Type Description

queryId

String

This is the ID of the query.

username

String

This is the username of the user who is executing the query.

metaData

Map

This is any metadata associated with the transaction.

query

String

This is the query itself.

parameters

Map

This is a map containing all the parameters used by the query.

planner

String

Planner used by the query. [1]

runtime

String

Runtime used by the query. [2]

indexes

List

Indexes used by the query.

startTime

String

This is the time at which the query was started.

elapsedTime

String

Deprecated: Use elapsedTimeMillis instead. This is the time that has elapsed since the query was started.

connectionDetails

String

Deprecated: Use requestScheme, clientAddress,requestUri These are the connection details pertaining to the query.

protocol

String

The protocol used by connection issuing the query.

connectionId

String

The ID of the connection issuing the query. This field will be null if the query was issued using embedded API.

clientAddress

String

The client address of the connection issuing the query.

requestUri

String

The request URI used by the client connection issuing the query.

status

String

Status of the executing query.

resourceInformation

Map

Status of the executing query.

activeLockCount

Integer

Count of active locks held by transaction executing the query.

elapsedTimeMillis

Integer

This is the time in milliseconds that has elapsed since the query was started.

cpuTimeMillis

Integer

CPU time in milliseconds that has been actively spent executing the query. This field will be null unless the config parameter dbms.track_query_cpu_time is set to true.

waitTimeMillis

Integer

Wait time in milliseconds that has been spent waiting to acquire locks.

idleTimeMillis

Integer

Idle time in milliseconds. This field will be null unless the config parameter dbms.track_query_cpu_time is set to true.

allocatedBytes

Integer

Estimated bytes allocated for the executing query. For memory-intense or long-running queries the value may be larger than the current memory usage. This field will be null unless the config parameter dbms.track_query_allocation is set to true.

pageHits

Integer

Page hits occurred during the execution.

pageFaults

Integer

Page faults occurred during the execution.

database

String

This is the name of the database the query is executing against.
Example 1. Viewing queries that are currently executing

The following example shows that the user alwood is currently running dbms.listQueries() yielding specific variables, namely queryId, username, query, elapsedTimeMillis, requestUri, status, and database.

CALL dbms.listQueries() YIELD queryId, username, query, elapsedTimeMillis, requestUri, status, database
╒═══════════╤═══════════╤══════════════════════════════╤════════════════════╤════════════════╤══════════╤═══════════╕
│"queryId"  │"username" │"query"                       │"elapsedTimeMillis" │"requestUri"    │"status"  │"database" │
╞═══════════╪═══════════╪══════════════════════════════╪════════════════════╪════════════════╪══════════╪═══════════╡
│"query-33" │"alwood"   │"CALL dbms.listQueries() YIELD│1                   │"127.0.0.1:7687"│"running" │"myDb"     │
│           │           │ queryId, username, query, ela│                    │                │          │           │
│           │           │psedTime, requestUri, status, │                    │                │          │           │
│           │           │database"                     │                    │                │          │           │
└───────────┴───────────┴──────────────────────────────┴────────────────────┴────────────────┴──────────┴───────────┘
1 row

2. List all active locks for a query

An administrator is able to view all active locks held by the transaction executing the query with the queryId.

Syntax:

CALL dbms.listActiveLocks(queryId)

Returns:

Name Type Description

mode

String

Lock mode corresponding to the transaction.

resourceType

String

Resource type of the locked resource

resourceId

Integer

Resource id of the locked resource .
Example 2. Viewing active locks for a query

The following example shows the active locks held by transaction executing query with id query-614

CALL dbms.listActiveLocks( "query-614" )
╒════════╤══════════════╤════════════╕
│"mode"  │"resourceType"│"resourceId"│
╞════════╪══════════════╪════════════╡
│"SHARED"│"SCHEMA"      │0           │
└────────┴──────────────┴────────────┘
1 row

The following example shows the active locks for all currently executing queries by yielding the queryId from dbms.listQueries procedure

CALL dbms.listQueries() YIELD queryId, query, database
CALL dbms.listActiveLocks( queryId ) YIELD resourceType, resourceId, mode
RETURN queryId, query, resourceType, resourceId, mode, database
╒════════════╤══════════════════════════════╤══════════════╤════════════╤════════╤════════════╕
│"queryId"   │"query"                       │"resourceType"│"resourceId"│"mode"  │"database"  │
╞════════════╪══════════════════════════════╪══════════════╪════════════╪════════╪════════════╡
│"query-614" │"match (n), (m), (o), (p), (q)│"SCHEMA"      │0           │"SHARED"│"myDb"      │
│            │ return count(*)"             │              │            │        │            │
├────────────┼──────────────────────────────┼──────────────┼────────────┼────────┼────────────┤
│"query-684" │"CALL dbms.listQueries() YIELD│"SCHEMA"      │0           │"SHARED"│"myOtherDb" │
│            │ .."                          │              │            │        │            │
└────────────┴──────────────────────────────┴──────────────┴────────────┴────────┴────────────┘
2 rows

3. Terminate multiple queries

An administrator is able to terminate within the instance all transactions executing a query with any of the given query IDs. Alternatively, the current user may terminate all of their own transactions executing a query with any of the given query IDs.

Syntax:

CALL dbms.killQueries(queryIds)

Arguments:

Name Type Description

ids

List<String>

This is a list of the IDs of all the queries to be terminated.

Returns:

Name Type Description

queryId

String

This is the ID of the terminated query.

username

String

This is the username of the user who was executing the (now terminated) query.

message

String

A message stating whether the query was successfully found.
Example 3. Terminating multiple queries

The following example shows that the administrator has terminated the queries with IDs query-378 and query-765, started by the users joesmith and annebrown, respectively.

This command can target queries from multiple databases at the same time. In this example, joesmith ran his query against joeDb and annebrown ran hers against anneDb.

CALL dbms.killQueries(['query-378','query-765'])
+-------------------------------------------+
| queryId     | username    | message       |
+-------------------------------------------+
| "query-378" | "joesmith"  | "Query found" |
| "query-765" | "annebrown" | "Query found" |
+-------------------------------------------+
2 rows

4. Terminate a single query

An administrator is able to terminate within the instance any transaction executing the query whose ID is provided. Alternatively, the current user may terminate their own transaction executing the query whose ID is provided.

Syntax:

CALL dbms.killQuery(queryId)

Arguments:

Name Type Description

id

String

This is the ID of the query to be terminated.

Returns:

Name Type Description

queryId

String

This is the ID of the terminated query.

username

String

This is the username of the user who was executing the (now terminated) query.

message

String

A message stating whether the query was successfully found.
Example 4. Terminating a single query

The following example shows that the user joesmith has terminated his query with the ID query-502.

CALL dbms.killQuery('query-502')
+-------------------------------------------+
| queryId     | username    | message       |
+-------------------------------------------+
| "query-502" | "joesmith"  | "Query found" |
+-------------------------------------------+
1 row

The following example shows the output when trying to kill a query with an ID that does not exist.

CALL dbms.killQuery('query-502')
+-----------------------------------------------------------+
| queryId     | username    | message                       |
+-----------------------------------------------------------+
| "query-502" | "n/a"       | "No Query found with this id" |
+-----------------------------------------------------------+
1 row