deadlock. wtf.

gistfile1.sql

ERROR:  deadlock detected
DETAIL:  Process 36166 waits for ShareLock on transaction 134511; blocked by process 36167.
	Process 36167 waits for ShareLock on transaction 134512; blocked by process 36166.
	Process 36166: DELETE FROM "cart_items" WHERE "cart_items"."id" = $1
	Process 36167: INSERT INTO "sale_items" ("article_id", "cancelled", "created_at", "main_item_id", "properties_hstore", "quantity", "sale_id", "title", "total_price", "updated_at", "variant_id", "vat_rate") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) RETURNING "id"
HINT:  See server log for query details.
CONTEXT:  SQL statement "UPDATE variants SET
	"reserved"   = "reserved" + delta_reserved,
	"remaining"  = "remaining" + delta_remaining
	WHERE "id"=variant_idd"
	PL/pgSQL function "cart_item_change_channel_and_variant_stats" line 40 at SQL statement
STATEMENT:  DELETE FROM "cart_items" WHERE "cart_items"."id" = $1

-- ==========================================================

create table sale_items (
  id                serial NOT NULL PRIMARY KEY,

  sale_id           integer references sales(id) null default null,
  article_id        integer references articles(id) null default null,
  variant_id        integer references variants(id) null default null,
  
  -- ...

  created_at        timestamp without time zone,
  updated_at        timestamp without time zone
);

CREATE OR REPLACE FUNCTION change_channel_and_variant_stats() RETURNS TRIGGER
AS $$
  DECLARE
    delta_sold        INTEGER := 0;
    -- ...
  BEGIN
    -- ... some computations and selects
    UPDATE variants SET
      "sold"      = "sold" + delta_sold,
      "remaining" = "remaining" + delta_remaining,
      "revenue"    = "revenue" + delta_revenue
      WHERE "id"=variant_idd;
    -- ...
  END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER change_channel_and_variant_stats_trigger BEFORE INSERT OR UPDATE OR DELETE ON sale_items
  FOR EACH ROW EXECUTE PROCEDURE change_channel_and_variant_stats();


CREATE TABLE cart_items (
  id                  serial NOT NULL PRIMARY KEY,

  cart_id             integer references carts(id),
  variant_id          integer references variants(id),
  quantity            integer not null default 0,

  -- ...

  created_at          timestamp without time zone,
  updated_at          timestamp without time zone
);


CREATE OR REPLACE FUNCTION cart_item_change_channel_and_variant_stats() RETURNS TRIGGER
AS $$
  DECLARE
    delta_reserved    INTEGER := 0;
    -- ...
  BEGIN
    -- ... some computations and selects
    UPDATE variants SET
      "reserved"   = "reserved" + delta_reserved,
      "remaining"  = "remaining" + delta_remaining
      WHERE "id"=variant_idd;
    -- ...
  END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER cart_item_change_channel_and_variant_stats_trigger BEFORE INSERT OR UPDATE OR DELETE ON cart_items
  FOR EACH ROW EXECUTE PROCEDURE cart_item_change_channel_and_variant_stats();