Look into transaction conflicts

[stephenh]

It looks like Joist's "only do one UPDATE" maybe be too complicated for Postgres's transaction conflict detection:

UPDATE units SET
  "updated_at" = data."updated_at",
  "duration" = data."duration",
  "task_status_rollup_id" = data."task_status_rollup_id"
FROM ( SELECT
  unnest($1::int[]) as "id",
  unnest($2::timestamp with time zone[]) as "updated_at",
  unnest($3::int[]) as "duration",
  unnest($4::int[]) as "task_status_rollup_id",
  unnest($5::timestamptz[]) as original_updated_at
) as data
WHERE
  units.id = data.id
 AND date_trunc('milliseconds', units.updated_at) = data.original_updated_at
RETURNING units.id
- could not serialize access due to read/write dependencies among transactions

Maybe this is only triggered by doing one UPDATE as well as doing oplocks? Unclear.

[stephenh]

@chr1sjf0x / @zgavin I formatted the ^ SQL to be more readable, but basically I feel pretty confident that what is happening is:

• We put our transaction into isolation mode, which tries to detect others transactions "touching the rows I'm updating" but looking at those transaction's where clauses.
• I assume that when UPDATE authors SET ... WHERE id = 1 and UPDATE authors SET ... WHERE id = 2 are running simultaneously, pg can see the WHERE clauses are obviously updating separate rows
• However, the "single UPDATE w/unnest approach that Joist uses pipes in multiple row ids as a single data[0] = [1, 2, ...] array, and so WHERE units.id = data.id might be telling the transaction detection code "hey I could be touching any row"

Hence musing about maybe the best fix for this is:

• Move back to one-UPDATE-per-row
• Look into transaction pipelining

Which looks unsupported in node pg:

brianc/node-postgres#2646
brianc/node-pg-native#100

But is available in https://github.com/porsager/postgres

Granted, I'm not sure if we'd want to switch everything over to postgres, or try to use knex for qureies + em.finds, and just start out using postgres for em.flush specifically for the pipeline support.