there is nothing wrong postgres transactions, they work exactly as intended, but they aren't magic. this is pretty easy to test:

Session 1:

    psql (16.3 (Debian 16.3-1.pgdg120+1))
    Type "help" for help.

    postgres=# CREATE TABLE tags (
      id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      name VARCHAR(50) NOT NULL
    );
    CREATE TABLE
    postgres=# ALTER TABLE tags ADD CONSTRAINT tags_name_unique UNIQUE(name);
    ALTER TABLE
    postgres=# INSERT INTO tags (name) VALUES ('A'), ('B') RETURNING *;
     id | name
    ----+------
      1 | A
      2 | B
    (2 rows)

    INSERT 0 2
    postgres=# BEGIN;
    BEGIN
    postgres=*# INSERT INTO tags (name) VALUES ('B') ON CONFLICT DO NOTHING RETURNING *;
     id | name
    ----+------
    (0 rows)

    INSERT 0 0

Session 2:

    psql (16.3 (Debian 16.3-1.pgdg120+1))
    Type "help" for help.

    postgres=# DELETE FROM tags WHERE name = 'B';
    DELETE 1

Session 1 continues:

    postgres=*# SELECT * FROM tags WHERE name = 'B';
     id | name
    ----+------
    (0 rows)

    postgres=*# END;
    COMMIT

(yes, I added `ON CONFLICT DO NOTHING` to avoid aborting the transaction prematurely)