Query to kill transactions that take longer than X seconds and doesn't contain certain keywords

Neo4j Graph Platform Operations
, ,
1

In Neo4j we currently have the configuration property referred to as execution guard:

dbms.transaction.timeout=30s

that can be set automatically to kill transactions that take more than “x” seconds (x is equal to what is assigned to dbms.transaction.timeout, in this case 30s).
However this is at global level and can’t be controlled for specific User or Query type.

So in order to implement this, a small script can be written and scheduled to run and kill the queries that take more than 30 seconds. This script can be triggered via cypher-shell.
The query to kill the transaction that are not part of LOAD CSV and taking more than 30 seconds can be written as:

call dbms.listQueries() yield query, elapsedTimeMillis, queryId, username
where  NOT query contains toLower(“LOAD")
and elapsedTimeMillis >30000
with query, collect(queryId) as q
call dbms.killQueries(q) yield queryId
return query, queryId

The query to kill the transaction where the user executing the query is not "neo4j" and taking more than 30 seconds:

call dbms.listQueries() yield query, elapsedTimeMillis, queryId, username
where  NOT username contains toLower("neo4j")
and elapsedTimeMillis >30000
with query, collect(queryId) as q
call dbms.killQueries(q) yield queryId
return query, queryId

You can modify the above query based on either certain parameters for queries or for certain users that should not be killed.

Note: This applies to Neo4j 3.1 and newer only!