Transaction Read Conflict During CREATE Operation

[SergioKingOne]

Transaction Read Conflict During CREATE Operation

First, I want to express my appreciation for the excellent work on SurrealKV.

Issue Description

I'm encountering intermittent transaction read conflicts when executing a creation query using the Rust SDK with SurrealKV. Specifically, the CREATE operation fails while the subsequent RELATE operation succeeds.

Query

-- This operation fails with transaction read conflict
CREATE $reply_id CONTENT $reply;

-- This operation succeeds
RELATE $msg_id->message_replies->$reply_id;

Context

• The reply record is intended to be unique and created for the first time
• The issue occurs sporadically with no clear pattern
• We're not using any ANALYZER in this query or related tables (though we do have one defined on a separate, unrelated table)

Record Structure

The reply record follows this structure:

CREATE $reply_id CONTENT {
    name: "actors::chat_actor::ChatResponse",
    tx: actor:⟨/chat_actor/telegram⟩,
    rx: actor:⟨/relay/telegram⟩,
    msg: {
        "content": "A friendly message.",
        "error": null
    },
    err: null
};

Error Output

The CREATE operation fails with a transaction read conflict:

{
    // ... client configuration omitted for brevity ...
    results: {
        0: (
            Stats {
                execution_time: Some(413.578745ms),
            },
            Err(
                Api(
                    Query(
                        "The query was not executed due to a failed transaction. There was a problem with a datastore transaction: Transaction read conflict",
                    ),
                ),
            ),
        ),
        1: (
            // ... successful relation creation omitted ...
        ),
    },
    live_queries: {},
}

Related Issues

There's a similar issue (#4898) in the SurrealDB repository, but it specifically mentions ANALYZER-related conflicts, which doesn't appear to be relevant in this case.

Questions

1. What might be causing these sporadic transaction read conflicts during the CREATE operation?
2. Are there any recommended patterns for handling this type of create operation more reliably?
3. Could the presence of an ANALYZER in an unrelated table affect this operation?

Any insights or suggestions would be greatly appreciated. Thank you!

[arriqaaq]

Hey @SergioKingOne

Thank you for using surrealkv. SurrealKV by default works in a Snapshot Isolation mode, so the conflict error means that if two concurrent transactions (modifying common set of keys) are being executed at the same time, it would lead to conflict. You can find a simple example of that here.

Could you confirm if these queries are being executed concurrently?

[SergioKingOne]

I have thoroughly reviewed our codebase to identify any instances of concurrent modifications to the same key, specifically within the reply table using the unique ID. However, I have not found any evidence of such concurrent transactions. This behavior aligns with our expectations, as the reply record is being created for the first time, which should prevent other transactions from reading or modifying it simultaneously. Therefore, these queries are not being executed concurrently based on our current findings.

That said, it's possible that concurrent modifications are occurring in areas we haven't yet identified. We will continue investigating to uncover any hidden instances of concurrent transactions.

[arriqaaq]

Noted @SergioKingOne

In case you have a test case through which you can reproduce it deterministically, would be easier to discuss around it.

[johansmitsnl]

In my case I get the same error but from live queries. So I'm not making transactions but I perform multiple live query on single item or different items.

I guess it is because live queries are stored in a table and that causes a transaction that fails?

The error message I get is:

2024-11-30T09:36:02.136966Z ERROR backend::models::db_api::traits: Unable to activate the live query for query (("site", "xtn7bep2z66gq83hc4tw")) due to error: The query was not executed due to a failed transaction. There was a problem with a datastore transaction: Transaction read conflict
2024-11-30T09:36:02.138653Z ERROR backend::models::db_api::traits: Unable to activate the live query for query (("site_page_section", "98bow4nauv8culs7826a")) due to error: The query was not executed due to a failed transaction. There was a problem with a datastore transaction: Transaction read conflict
2024-11-30T09:36:02.138727Z ERROR backend::models::db_api::traits: Unable to activate the live query for query (("site", "xtn7bep2z66gq83hc4tw")) due to error: The query was not executed due to a failed transaction. There was a problem with a datastore transaction: Transaction read conflict
2024-11-30T09:36:02.138726Z ERROR backend::models::db_api::traits: Unable to activate the live query for query (("site", "xtn7bep2z66gq83hc4tw")) due to error: The query was not executed due to a failed transaction. There was a problem with a datastore transaction: Transaction read conflict
2024-11-30T09:36:02.138771Z ERROR backend::models::db_api::traits: Unable to activate the live query for query (("site_page_section", "00000000000000000000")) due to error: The query was not executed due to a failed transaction. There was a problem with a datastore transaction: Transaction read conflict
2024-11-30T09:36:02.139011Z ERROR backend::models::db_api::traits: Unable to activate the live query for query (("site_page_section", "w2ysl5kll7321ol45o0w")) due to error: The query was not executed due to a failed transaction. There was a problem with a datastore transaction: Transaction read conflict

This is also a problem witch rockdb but the error message is different, but same issue.

[johansmitsnl]

I have created a minimal example to reproduce the issue here: surrealdb-live-resource-panic

[arriqaaq]

Thanks @johansmitsnl, is the error in rocksdb "resource busy error"?

[johansmitsnl]

@arriqaaq yes, that is the error with rocksdb.

[SergioKingOne]

Happened to me as well recently with one of my LIVE QUERYs. In my case is just one live query for one table with a WHERE clause.

The live query looks like this: LIVE SELECT * FROM message WHERE rx = receiver

And I get the error:

Live query response: Response { client: Surreal { router: OnceLock(Router { sender: Sender { .. }, last_id: 20, features: {LiveQueries} }), engine: PhantomData<surrealdb::api::engine::any::Any> }, results: {0: (Stats { execution_time: Some(60.891µs) }, Err(Api(Query("The query was not executed due to a failed transaction. There was a problem with a datastore transaction: Transaction read conflict"))))}, live_queries: {0: Err(Api(NotLiveQuery(0)))} }

[arriqaaq]

Just to keep you updated, we are checking on this internally. Recent PR #146 on surrealkv makes the transaction guarantees for SI less strict. However, since this behaviour also occurs in RocksDB, the issue arises when a live query is created, as common keys are accessed, leading to this conflict. I'll keep you updated as this issue is related to surrealdb internals

[johansmitsnl]

@arriqaaq the PR #146 fixes it for the surrealkv backend? Or are other changes needed to surrealdb itself?