Memgraph Stream Cannot resolve conflicting transactions

I am using a locally hosted Memgraph instance and streaming CDC data from a postgres through Kafka Connect and Kafka. I have been experiencing a transaction error recently, and I haven't found a solution that worked yet.

The error I am getting is:

Error happened in consumer XX while processing a batch: Cannot resolve conflicting transactions. You can retry this transaction when the conflicting transaction is finished!

This is my compose object:

memgraph:
    image: memgraph/memgraph-mage:1.16-memgraph-2.16
    container_name: memgraph-mage
    restart: always
    depends_on:
      kafka:
        condition: service_healthy
    environment:
      - vm.max_map_count=262144
    ports:
      - "7687:7687"
      - "7444:7444"
    command: ["--log-level=INFO", "--also-log-to-stderr=true", "--telemetry-enabled=false", "--stream-transaction-conflict-retries=10", "--stream-transaction-retry-interval=500"]
    volumes:
      - mg-data:/var/lib/memgraph
      - mg-log:/var/log/memgraph
      - mg-etc:/etc/memgraph
      - mg-usr:/usr/lib/memgraph
    networks:
      - internal_net

I have two stream running at the same time, where they could be interfering, I.e adding edges to the same node at the same time.

Currently, I have to restart the stream every 2-3 minutes or so. I don't have exceptionally high volume of data being processed, around 500 events/minute. I suspect it is because some nodes are being updated at the same time, but I am not sure how to debug it and fix it.

Any help is appreciated.

Thanks.

Solution

If you modify any relationship or node at the same time or even add a relationship to the same node, you will get conflicting transactions, as you have properly noticed.

If a batch of messages in the transaction modifies a super node (which has many relationships), other batches (streams) often try to update the same super node, and you will get a lot of conflicting transaction.

This all comes from the fact that Memgraph has high ACID consistency: https://memgraph.com/docs/fundamentals/storage-memory-usage

This is an expected error that you can address.

The stream configuration flags should be configured based on your workload: https://memgraph.com/docs/database-management/configuration#streams

I would expect this to be a higher value than 10:

--stream-transaction-conflict-retries=

How high? The answer should depend on how many things your batches are modifying and how your data is structured. Bigger batches and changes increase the possibility of a conflict.

Smaller batches, more retries with smaller timeouts.

If you opt for bigger batches, then you should probably have smaller retries but with bigger timeouts.

You should not be overly concerned with retries if Memgraph is able to keep pace regarding the occurring events.

I would expect this flag to be dependent on the actual batch duration.

--stream-transaction-retry-interval=500

It is pointless to retry every 500ms if the batch of messages/events takes around 3-4 seconds.