Create on unique index within transactions in Postgresql

Question

I have a table called Account with a column name. There's a unique index on the column. I have a transaction:

begin
create an account with name("ABC")
publish an event to Kafka
end

I wonder if this situation could happen or not:

Request 1: .....transaction start.......create account name("ABC").......publish Kafka event.......commit transaction
Request 2: ..........transaction start.........create account name("ABC").......publish Kafka event.......commit transaction

Both requests happen at the same time. Because the transaction won't see the data changes in the other transaction, one will fail when committing and one will succeed. Will I receive 2 Kafka events?

you should take a look into concurrent transaction, it will lock a row on a table if a transaction happening postgresql.org/docs/9.1/explicit-locking.html . Like the one suggest above, on success you release the lock and check for duplicate publishs — Linh Nguyen
– Linh Nguyen, Commented Apr 1, 2021 at 7:25
Because if I cannot publish, I don't want the transaction to be successful — Duc Le
– Duc Le, Commented Apr 1, 2021 at 7:26

Julius Tuskenis · Accepted Answer · 2021-04-01 09:34:26Z

You will not receive second Kafka event.

Because of unique index on name attribute the second transaction is forced to wait for the first transaction to complete before it can finish inserting its account with the same name as described in the PostgreSQL documentation:

If a conflicting row has been inserted by an as-yet-uncommitted transaction, the would-be inserter must wait to see if that transaction commits. If it rolls back then there is no conflict. If it commits without deleting the conflicting row again, there is a uniqueness violation. (In practice we just wait for the other transaction to end and then redo the visibility check in toto.)

After the first transaction is commited the second transaction proceeds with insert and raises unique_violation exception like:

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "idx_name" Detail: Key (name)=(ABC) already exists.

The situation would result in such outcome:

Request 1: .....transaction start.......create account name("ABC").......publish Kafka event.......commit transaction
Request 2: ..........transaction start.......create account name("ABC")-----------------------------Error(unique_violation)

(I've added --- to indicate blocking of create account name("ABC") command).

Collectives™ on Stack Overflow

Create on unique index within transactions in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related