0

I'm working on a web application which uses a PostgreSQL database for storage. There are many logical entities in this application which are related to each other like hotel --> hotel_floor --> hotel_room (the hotels example used throughout the question is purely hypothetical). Each table stores the IDs of all its parents in the hierarchy together with its own ID which is unique in scope of its parents. The DB structure looks something like this:

table hotel (
    hotel_id int,
    ...

    CONSTRAINT hotel_pk PRIMARY KEY (hotel_id)
)


table floor (
    hotel_id int,
    floor_id int,
    ...

    CONSTRAINT floor_pk PRIMARY KEY (hotel_id, floor_id),
    CONSTRAINT floor_hotel_fk FOREIGN KEY (hotel_id) REFERENCES hotel (hotel_id)
)


table room (
    hotel_id int,
    floor_id int,
    room_id int,
    ...

    CONSTRAINT room_pk PRIMARY KEY (hotel_id, floor_id, room_id),
    CONSTRAINT room_hotel_fk FOREIGN KEY (hotel_id) REFERENCES hotel (hotel_id),
    CONSTRAINT room_floor_fk FOREIGN KEY (hotel_id, floor_id) REFERENCES floor (hotel_id, floor_id),
)

And the records that are stored in there have IDs which look like this:

  hotel
--------
hotel_id
--------
   1
   2
   3


        floor
-------------------
hotel_id | floor_id
-------------------
    1    |    1
    1    |    2
         |
    2    |    1
    2    |    2


             room
-----------------------------
hotel_id | floor_id | room_id
-----------------------------
    1    |    1     |    1
    1    |    1     |    2
    1    |    1     |    3

    1    |    2     |    1
    1    |    2     |    2
    1    |    2     |    3
    1    |    2     |    4

Obviously, because of such DB structure (which was inherited by our team and is not easy to refactor) all the IDs have to be generated manually. For this reason we have a before-insert trigger on each table which all boil down to something similar to this:

CREATE OR REPLACE FUNCTION room_set_new_id() RETURNS trigger LANGUAGE plpgsql AS $
BEGIN
    LOCK TABLE room IN SHARE ROW EXCLUSIVE MODE;

    NEW.room_id = (
        SELECT COALESCE(MAX(room_id), 0) + 1
        FROM room
        WHERE hotel_id = NEW.hotel_id AND floor_id = NEW.floor_id
    );

    RETURN NEW;
END
$;

CREATE TRIGGER room_set_new_id
BEFORE INSERT ON room
FOR EACH ROW EXECUTE PROCEDURE room_set_new_id();

The problem lies in the LOCK TABLE statement. Until recently there hasn't been any, but we started getting duplicate-key errors when 2 insert operations happened to execute at the same time, so I decided to add those locks hoping to fix the issue. Unfortunately, this led to a new problem: simultaneous inserts now result in a deadlock. I've experimented with the server code and found out that the problem lies exclusively in the INSERT's themselves, e.g. two or more INSERT INTO room (hotel_id, floor_id) VALUES (1, 1) queries issued at the same time - there're no other read/write operations happening with the hotel/floor/room tables at that moment (actually, each request to the web server comes from a logged in user, and there's a connection between the users table and the hotels table which is used for authentication purposes, but I don't think a simultaneous "check if the current user is related to the hotel in question" query should affect the insert operation).

I've been trying to figure out how to fix this but to no avail. Of course, I can add deadlock-error handling on the web server by catching the error and retrying the query execution but I'd rather do that only if there's really no way to resolve this problem on the database level, i.e. if my table locking strategy is wrong and there's a different way to avoid duplicate IDs generation, I'd like to do that instead. (Remember, I'm stuck with this database structure and it will take a huge amount of time and effort to rewrite all the DB and web server logic to use proper autogenerated columns instead.)

2
  • Why don't you use a sequence? CREATE SEQUENCE ... and it will give you a number. No locks needed and no duplicate id's. (unless someone tries to be smart) Aug 10, 2023 at 15:35
  • Well the problem is the schema design in the first place which was made without concurrency in mind. And in part caused by tight coupling between application and database layer, I assume because it's some big framework like spring/django. Don't fight the tide and and hack the solution on the level of your app instead, because that's how the stack is designed in the first place. And from the application level just run these inserts in a task queue which ensures sequential inserts and proper generated IDs. Aug 11, 2023 at 4:37

1 Answer 1

0

After some more experimenting I've found the source of the problem - lock being acquired during trigger execution. I don't know what's the reason here but, apparently, this leads to conflicts between whatever operations the database is executing during those simultaneous transactions. Therefore, executing LOCK TABLE before INSERT fixes the problem entirely.

Fixed trigger:

CREATE OR REPLACE FUNCTION room_set_new_id() RETURNS trigger LANGUAGE plpgsql AS $
BEGIN
    NEW.room_id = (
        SELECT COALESCE(MAX(room_id), 0) + 1
        FROM room
        WHERE hotel_id = NEW.hotel_id AND floor_id = NEW.floor_id
    );

    RETURN NEW;
END
$;

CREATE TRIGGER room_set_new_id
BEFORE INSERT ON room
FOR EACH ROW EXECUTE PROCEDURE room_set_new_id();

And sample transaction:

BEGIN;
LOCK TABLE room IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO room (hotel_id, floor_id) VALUES (1, 1);
COMMIT;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.