Hi,

I have a challenge with transactions in Postgresql when they have the isolation level SERIALIZABLE.

This is the transaction table and data:
Code:
CREATE TABLE "test_transaction"
(
  "id" bigserial PRIMARY KEY,
  "other_id" bigint NOT NULL,
  "created_time" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_time" timestamp with time zone NOT NULL DEFAULT now()
);

INSERT INTO "test_transaction"
	("id", "other_id")
	VALUES
	(1, 1),
	(2, 2),
	(3, 1),
	(4, 2),
	(5, 1),
	(6, 2);;
Start transaction A:
Code:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;
start transaction B:
Code:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;
Select the last transaction with "other_id"=1 in transaction A:
Code:
select 
  "test_transaction"."id", 
  "test_transaction"."other_id", 
  "test_transaction"."created_time", 
  "test_transaction"."updated_time" 
from 
  "test_transaction" 
where 
  "test_transaction"."other_id" = 1 
order by 
  "test_transaction"."updated_time" desc limit 1;
Select the last transaction with "other_id"=2 in transaction B:
Code:
select 
  "test_transaction"."id", 
  "test_transaction"."other_id", 
  "test_transaction"."created_time", 
  "test_transaction"."updated_time" 
from 
  "test_transaction" 
where 
  "test_transaction"."other_id" = 2 
order by 
  "test_transaction"."updated_time" desc limit 1;
Update "updated_time" where id = 1 in transaction A:
Code:
update 
  "test_transaction" 
set 
  "updated_time" = now()
where 
  "test_transaction"."id" = 5
returning 
  "test_transaction"."id", 
  "test_transaction"."other_id", 
  "test_transaction"."created_time", 
  "test_transaction"."updated_time";
Update "update_time" where id = 2 in transaction B:
Code:
update 
  "test_transaction" 
set 
  "updated_time" = now()
where 
  "test_transaction"."id" = 6
returning 
  "test_transaction"."id", 
  "test_transaction"."other_id", 
  "test_transaction"."created_time", 
  "test_transaction"."updated_time";
Commit transaction A:
Code:
COMMIT;
Commit transaction B:
Code:
COMMIT;
Recieve an error in transaction B
Code:
ERROR: could not serialize access due to read/write dependencies among transactions
SQL state: 40001
Detail: Reason code: Canceled on identification as a pivot, during commit attempt.
Hint: The transaction might succeed if retried.
Not sure what goes wrong in transaction B as I think that transaction A and B should work on each set and only lock sets where other_id equals 1 nad 2 reprectively. But it seems like the entire table is protected?

I have tested the same with "Repeatable Read" isolation level and which works perfectly. I can see that it should be "Phantom Read" that is allowed here.

Can someone shred some light on what goes on here as I expected it would work without any issues?