postgres-anomalies/spec/vignettes/isolation_serializable/07_read_only_transaction_anomaly_spec.log at ebfd9315668c505fc927df5081a434fba88bef8d · Aerdayne/postgres-anomalies · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
    [observer]	CREATE TABLE events (
    [observer]	  id text NOT NULL,
    [observer]	  available_seats integer NOT NULL CHECK (available_seats >= 0),
    [observer]	  PRIMARY KEY (id)
    [observer]	);	(2.5ms)
    [observer]	CREATE TABLE bookings (
    [observer]	  id integer PRIMARY KEY,
    [observer]	  customer_name text NOT NULL,
    [observer]	  seat_count integer NOT NULL,
    [observer]	  event_id text NOT NULL,
    [observer]	  FOREIGN KEY (event_id) REFERENCES events (id)
    [observer]	);	(3.1ms)
    [observer]	BEGIN ISOLATION LEVEL READ COMMITTED	(0.6ms)
    [observer]	INSERT INTO "events" ("id", "available_seats") VALUES ('event_a', 2) RETURNING "id"	(0.9ms)
    [observer]	INSERT INTO "bookings" ("id", "customer_name", "seat_count", "event_id") VALUES (1, 'Alice', 1, 'event_a') RETURNING "id"	(1.1ms)
    [observer]	INSERT INTO "bookings" ("id", "customer_name", "seat_count", "event_id") VALUES (2, 'Bob', 1, 'event_a') RETURNING "id"	(0.6ms)
    [observer]	COMMIT	(0.7ms)
         [bob]		BEGIN ISOLATION LEVEL SERIALIZABLE	(0.5ms)
         [bob]		SELECT SUM("bookings"."seat_count") FROM "bookings" WHERE "bookings"."customer_name" IN ('Alice', 'Bob') AND "bookings"."event_id" = 'event_a'	(1.0ms)
         [bob]		=> 2
         [bob]		UPDATE "bookings" SET "seat_count" = 2 WHERE "bookings"."customer_name" = 'Bob' AND "bookings"."event_id" = 'event_a'	(0.9ms)
       [alice]			BEGIN ISOLATION LEVEL SERIALIZABLE	(1.0ms)
       [alice]			UPDATE "bookings" SET "seat_count" = 2 WHERE "bookings"."id" = 1	(1.0ms)
       [alice]			COMMIT	(1.1ms)
    [observer]	BEGIN ISOLATION LEVEL SERIALIZABLE	(3.8ms)
    [observer]	SELECT "bookings"."customer_name", "bookings"."seat_count" FROM "bookings" WHERE "bookings"."customer_name" IN ('Alice', 'Bob')	(10.7ms)
    [observer]	=> [["Bob", 1], ["Alice", 2]]
    [observer]	COMMIT	(1.2ms)
         [bob]		COMMIT	(3.3ms)
         [bob]		ROLLBACK	(3.4ms)
         [bob]		-> raised ActiveRecord::SerializationFailure PG::TRSerializationFailure: ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
         [bob]		BEGIN ISOLATION LEVEL SERIALIZABLE	(1.3ms)
         [bob]		SELECT SUM("bookings"."seat_count") FROM "bookings" WHERE "bookings"."customer_name" IN ('Alice', 'Bob') AND "bookings"."event_id" = 'event_a'	(2.4ms)
         [bob]		=> 3
         [bob]		UPDATE "bookings" SET "seat_count" = 0 WHERE "bookings"."customer_name" = 'Bob' AND "bookings"."event_id" = 'event_a'	(1.5ms)
         [bob]		COMMIT	(3.2ms)
    [observer]	SELECT "bookings"."customer_name", "bookings"."seat_count" FROM "bookings" WHERE "bookings"."customer_name" IN ('Alice', 'Bob')	(3.1ms)
    [observer]	=> [["Alice", 2], ["Bob", 0]]
    [observer]	DROP TABLE IF EXISTS bookings;	(10.2ms)
    [observer]	DROP TABLE IF EXISTS events;	(9.0ms)