Concurrent PostgreSQL and SQLite3 clients can trigger a deadlock in electric.reorder_main_op pgSQL, Replication.Postgres.Writer restarts, writes being replicated appear to be lost

[nurturenature]

Concurrent PostgreSQL and SQLite3 clients can trigger a deadlock:

• error in electric.reorder_main_op pgSQL
• error in Replication.Postgres.Writer genserver which terminates and restarts
• replication transaction appears to not be retried
• previously OK'd write on a client appears to be dropped and not replicated

Example:

• 2 PostgreSQL clients
• 3 SQLite3 clients
• write by a SQLite3 client confirmed Ok
• values only ever read by that same client(twice)
• values only ever appear in that same client's replication logs
• errors in ElectricSQL and PostgreSQL logs

Error with [key,value] of [23,111]:

Test log:

;; client process 5 writes [23,111] and reads it twice
;; no observations of [23,111] on any other client
 
5 :ok :txn [[:w 29 49] [:w 23 111] [:r 29 49] [:r 29 49]]
...
5 :ok :txn [[:r 23 111]]
...
5 :ok :txn [[:w 22 252] [:r 23 111] [:w 5 192]]

ElectricSQL sync service log:

pid=<0.3300.0> origin=postgres_1 [error] GenServer #PID<0.3300.0> terminating
** (RuntimeError) Postgres.Writer failed to execute statement INSERT INTO "public"."lww_registers"("k","v") VALUES (23,111) with error {:error, {:error, :error, "40P01", :deadlock_detected, "deadlock detected", [detail: "Process 795 waits for ShareLock on transaction 4162; blocked by process 794.\nProcess 794 waits for ShareLock on transaction 4161; blocked by process 795.", file: "deadlock.c", hint: "See server log for query details.", line: "1130", routine: "DeadLockReport", severity: "ERROR", where: "while inserting index tuple (7,19) in relation \"shadow__public__lww_registers\"\nSQL statement \"INSERT INTO electric.shadow__public__lww_registers (_currently_reordering, k, __reordered_v)\n                VALUES (true, NEW.k, NEW.v)\n                ON CONFLICT (k) DO UPDATE SET\n                    _currently_reordering = true, __reordered_v = NEW.v\n                RETURNING *\"\nPL/pgSQL function electric.reorder_main_op___public__lww_registers() line 12 at SQL statement"]}}
    (electric 0.9.0) lib/electric/replication/postgres/writer.ex:93: anonymous fn/2 in Electric.Replication.Postgres.Writer.send_transaction/3
    (elixir 1.15.7) lib/enum.ex:984: Enum."-each/2-lists^foreach/1-0-"/2
    (epgsql 4.7.1) /root/electricsql/components/electric/deps/epgsql/src/epgsql.erl:458: :epgsql.with_transaction/3
    (electric 0.9.0) lib/electric/replication/postgres/writer.ex:86: Electric.Replication.Postgres.Writer.send_transaction/3
    (electric 0.9.0) lib/electric/replication/postgres/writer.ex:67: anonymous fn/2 in Electric.Replication.Postgres.Writer.handle_events/3
    (elixir 1.15.7) lib/enum.ex:2510: Enum."-reduce/3-lists^foldl/2-0-"/3
    (electric 0.9.0) lib/electric/replication/postgres/writer.ex:66: Electric.Replication.Postgres.Writer.handle_events/3
    (gen_stage 1.2.1) lib/gen_stage.ex:2578: GenStage.consumer_dispatch/6

PostgreSQL log:

ERROR: deadlock detected
DETAIL: Process 795 waits for ShareLock on transaction 4162; blocked by process 794.
 Process 794 waits for ShareLock on transaction 4161; blocked by process 795.
 Process 795: INSERT INTO "public"."lww_registers"("k","v") VALUES (23,111)
 Process 794: INSERT INTO lww_registers (k,v) VALUES (29,172) ON CONFLICT(k) DO UPDATE SET v = 172
CONTEXT: while inserting index tuple (7,19) in relation "shadow__public__lww_registers"
 SQL statement "INSERT INTO electric.shadow__public__lww_registers (_currently_reordering, k, __reordered_v)
                 VALUES (true, NEW.k, NEW.v)
                 ON CONFLICT (k) DO UPDATE SET
                     _currently_reordering = true, __reordered_v = NEW.v
                 RETURNING *"
 PL/pgSQL function electric.reorder_main_op___public__lww_registers() line 12 at SQL statement
STATEMENT: INSERT INTO "public"."lww_registers"("k","v") VALUES (23,111)

SQLite3 client log (only appears in log of client that did the write):

[proto] send: ... new: ["23", "111"], old: data: ["23", "110"]
...
[proto] send: ... new: ["23", "119"], old: data: ["23", "111"]

[linear[bot]]

VAX-1618 Concurrent PostgreSQL and SQLite3 clients can trigger a deadlock in electric.reorder_main_op pgSQL, Replication.Postgres.Writer restarts, writes being replicated appear to be lost

[KyleAMathews]

👋 we've been working the last month on a rebuild of the Electric server over at a temporary repo https://github.com/electric-sql/electric-next/

You can read more about why we made the decision at https://next.electric-sql.com/about

We're really excited about all the new possibilities the new server brings and we hope you'll check it out soon and give us your feedback.

We're now moving the temporary repo back here. As part of that migration we're closing all the old issues and PRs. We really appreciate you taking the time to investigate and report this issue!