/hotel-reservation-service

A basic hotel reservation system with 2PC using pessimistic and optimistic locks in Go

Primary LanguageGo

You can check the blog post for details of this project.

Creating Reservation Table

create table reservation
(
    reservation_id uuid
        constraint reservation_pk
            primary key,
    hotel_id       integer,
    room_type_id   integer,
    start_date     date,
    end_date       date,
    status         text
);

Status can be in one of these states: pending, paid, refunded, canceled, rejected.

Creating Room Type Inventory Table

create table room_type_inventory
(
    hotel_id        integer,
    room_type_id    integer,
    date            date,
    total_inventory integer,
    total_reserved  integer
);

alter table room_type_inventory
    add constraint room_type_inventory_pk
        primary key (hotel_id, room_type_id, date);

INSERT INTO room_type_inventory (hotel_id, room_type_id, date, total_inventory, total_reserved)
VALUES
    (100, 1, '2023-10-12', 2, 0),
    (100, 1, '2023-10-13', 2, 0);

Altering Room Type Inventory Table For Optimistic Locking

alter table room_type_inventory
add version integer default 0;

Adding reservation db

create database reservation;
create table reservation
(
    reservation_id uuid
        constraint reservation_pk
            primary key,
    hotel_id       integer,
    room_type_id   integer,
    start_date     date,
    end_date       date,
    status         text
);

Adding inventory db

create database inventory;
create table room_type_inventory
(
    hotel_id        integer,
    room_type_id    integer,
    date            date,
    total_inventory integer,
    total_reserved  integer
);