Transactions

All Cypher queries are run within transactions, which means that all modification made by a single query are held in memory by the transaction until the query is successfully executed. The changes are then committed and visible to all other transactions, users and systems. In the case of an error, the transaction is rolled back and no changes are committed.

These automatic transactions are also called implicit transactions.

You can also create explicit transactions to execute multiple Cypher queries in sequence, then commit them or roll them back.

If you want to switch between storage modes, there can't be any active transactions. Memgraph will log a warning message if it finds any active transactions, so set the log level to WARNING to see them. No other transactions will take place during the switch between modes.

During transaction execution, an important property of a database is the isolation level that defines how or when the changes made by one operation become visible to others.

Explicit transactions

To start a transaction, run the BEGIN; query.

All the following queries will be executed as a part of a single transaction.

If any of the queries fails during execution, further queries will no longer be successfully executed and it won't be possible to commit the transaction. As active constraints are checked upon commit, multi-query transactions will be committed as long as the database adheres to the constraints upon the execution of the final query.

Commit successful transactions by executing the COMMIT; query.
Roll back unsuccessful transactions by executing the ROLLBACK; query.

The following query will find to nodes and adjust a certain relationship property in order to check the results of a max_flow algorithm, then rollback the change as this query was a part of an what-if analysis:

BEGIN;
 
MATCH (e:E)-[r:CONNECTED_TO]->(f:F)
SET r.flow = 17;
 
MATCH (a:A), (f:F)
CALL max_flow.get_flow(a, f, “flow”) YIELD max_flow
RETURN max_flow;
 
ROLLBACK;

Optimistic vs. pessimistic approaches

In database management systems, there are two approaches to handling potential conflicts. The optimistic approach checks for conflicts only during the commit. If a conflict occurs, the transaction is rolled back. On the other hand, the pessimistic approach entails making additional checks to prevent conflicts from occuring in the first place.

The difference between the two approaches is relevant in several areas of Memgraph’s operation: concurrency control and constraint checking.

In constraint checking, Memgraph takes the optimistic approach. Multi-query transactions will be committed as long as the database adheres to the constraints upon the execution of the final query in the transaction.

Manage transactions

Memgraph can return information about running transactions and allow you to terminate them.

Show running transactions

To get information about running transaction execute the following query:

SHOW TRANSACTIONS;

 
memgraph> SHOW TRANSACTIONS;
+---------------+-----------------------------+-------------------------------------------+----------------+
| username      | transaction_id              | query                                     | metadata       |
+---------------+-----------------------------+-------------------------------------------+----------------+
| ""            | "9223372036854794885"       | ["CALL infinite.get() YIELD * RETURN *;"] | {}             |
| ""            | "9223372036854794896"       | ["SHOW TRANSACTIONS"]                     | {}             |
+---------------+-----------------------------+-------------------------------------------+----------------+

By default, the users can see and terminate only the transactions they have started. For all other transactions, the user must have the TRANSACTION_MANAGEMENT privilege which the admin assigns with the following query:

GRANT TRANSACTION_MANAGEMENT TO user;

The privilege to manage all the transactions running in Memgraph is revoked using the following query:

REVOKE TRANSACTION_MANAGEMENT FROM user;

If you are connecting to Memgraph using a client, you can pass additional metadata when starting a transaction (if the client supports additional metadata) which will be visible when running the SHOW TRANSACTIONS; query, thus allowing you to identify each transaction precisely.

The Python example below demonstrates how to pass metadata for both an implicit and explicit transaction:

import neo4j
 
def main():
  driver = neo4j.GraphDatabase.driver("bolt://localhost:7687", auth=("user","pass"))
 
  s1 = driver.session()
  tx = s1.begin_transaction(metadata={"where":"in explicit tx", "my_uuid":1})
  tx.run("MATCH (n) RETURN n LIMIT 1")
 
  s2 = driver.session()
  query=neo4j.Query("SHOW TRANSACTIONS", metadata={"where":"in implicit tx", "my_uuid":2})
  print(s2.run(query).values())
 
  tx.close()
  s1.close()
  s2.close()
 
if __name__ == '__main__':
  main()

Terminate transactions

To terminate one or more transactions, you need to open a new session and use the following query:

TERMINATE TRANSACTIONS "tid", "<tid2>", "<tid3>", ... ;

The tid is the transactional ID that can be seen using the SHOW TRANSACTIONS; query.

The TERMINATE TRANSACTIONS query signalizes to the thread executing the transaction that it should stop the execution. No violent interruption will happen, and the whole system will stay in a consistent state.

Terminate custom procedures

If you want to be able to terminate custom procedures, crucial parts of the code, such as while and until loops, or similar points where the procedure might become costly, need to be preceded with CheckMustAbort() function.

Example

Managing transactions is done by establishing a new connection to the database.

Show and terminate transactions

The output of the SHOW TRANSACTIONS command shows that a query is currently being run as part of the transaction ID "9223372036854794885".

 
memgraph> SHOW TRANSACTIONS;
+---------------+-----------------------------+-------------------------------------------+----------------+
| username      | transaction_id              | query                                     | metadata       |
+---------------+-----------------------------+-------------------------------------------+----------------+
| ""            | "9223372036854794885"       | ["CALL infinite.get() YIELD * RETURN *;"] | {}             |
| ""            | "9223372036854794896"       | ["SHOW TRANSACTIONS"]                     | {}             |
+---------------+-----------------------------+-------------------------------------------+----------------+

To terminate the transaction, run the following query:

TERMINATE TRANSACTIONS "9223372036854794885";

Upon the transaction termination, the following confirmation will appear:

memgraph> TERMINATE TRANSACTIONS "9223372036854794885";
+-----------------------+-----------------------+
| transaction_id        | killed                |
+-----------------------+-----------------------+
| "9223372036854794885" | true                  |
+-----------------------+-----------------------+
1 row in set (round trip in 0.000 sec)

The following message will appear in the session in which the infinite query was being run:

memgraph> CALL infinite.get() YIELD * RETURN *; 
Client received exception: Transactions was asked to abort either because it was executing longer than time
specified or another user asked to abort it.

Isolation levels

In database systems, isolation determines how transaction integrity is visible to other users and systems.

A lower isolation level allows many users to access the same data at the same time but increases the number of concurrency effects (such as dirty reads or lost updates). A higher isolation level secures data consistency but requires more system resources and increases the chances that one transaction will block another.

Memgraph currently supports three isolation levels, from the highest to the lowest:

• SNAPSHOT_ISOLATION (default) - guarantees that all reads made in a transaction will see a consistent snapshot of the database, and the transaction itself will successfully commit only if no updates it has made conflict with any concurrent updates made since that snapshot. Protects users from observing Dirty Read, Non-repeatable Read and Phantom phenomena as described in ANSI/ISO SQL-92 standard.
• READ_COMMITTED - guarantees that any data read was committed at the moment it is read. It protects users from observing Dirty Read phenomenon as described in ANSI/ISO SQL-92 standard.
• READ_UNCOMMITTED - one transaction may read not yet committed changes made by other transactions. Doesn't protect users from any of the three phenomena described in ANSI/ISO SQL-92 standard. In order to not mess up data consistency, this isolation level should only be used in the read-only access mode.

Based on Adya's classification of phenomena (opens in a new tab), we can further write:

In terms of Adya's isolation levels, Memgraph supports: PL-1, PL-MSR (Monotonic Snapshot Reads), PL-2, PL-2', PL-2'', PL-2L, PL-CS (Cursor Stability) and PL-2+ (consistent view).

You can find tests for these phenomena here (opens in a new tab).

To check the current isolation level run the following query:

SHOW STORAGE INFO;

IN_MEMORY_ANALYTICAL storage modes offers no isolation levels and no ACID guarantees. Multiple transactions can write data to Memgraph simultaneously. One transaction can therefore see all the changes from other transactions.

ON_DISK_TRANSACTIONAL storage mode uses only snapshot isolation.

Set the isolation level

Memgraph can work in IN_MEMORY_ANALYTICAL, IN_MEMORY_TRANSACTIONAL or ON_DISK_TRANSACTIONAL storage mode. IN_MEMORY_TRANSACTIONAL is the default mode in which Memgraph runs on startup.

When Memgraph is running in the IN_MEMORY_TRANSACTIONAL mode you can change the isolation level, change the --isolation-level configuration flag to any of the supported values.

You can also change the initially set isolation level when Memgraph is running using the following query:

SET <scope> TRANSACTION ISOLATION LEVEL <isolation_level>

<scope> defines the scope to which the isolation level change should apply:

• GLOBAL - apply the new isolation level globally
• SESSION - apply the new isolation level only for the current session
• NEXT - apply the new isolation level only for the next transaction in the current session

<isolation_level> defines the isolation level:

• SNAPSHOT ISOLATION
• READ COMMITTED
• READ UNCOMMITTED