How to use a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day?
It was suggested to me that it will different for every isolation level. Could you add an example for every one of them? (
repeatable read and
serializable). I would like to understand every one of them.
Here are tables and test data:
CREATE TABLE place ( place_id INT PRIMARY KEY, Name CHARACTER VARYING(50) NOT NULL, Type CHARACTER VARYING(50) NOT NULL ); CREATE TABLE visit ( visit_id SERIAL PRIMARY KEY, place_id INT NOT NULL, place_dt TIMESTAMP NOT NULL, FOREIGN KEY (place_id) REFERENCES place(place_id) ); INSERT INTO place(place_id, Name, Type ) VALUES (1, 'Denali', 'mountain'), (2, 'Brindley', 'mountain'), (3, 'St. Louis Cathedral', 'church') ; INSERT INTO visit(place_id, place_dt ) VALUES (1, '2019-01-02 10:00'), (2, '2019-01-02 11:00'), (3, '2019-01-03 14:09') ;
What I want is that for each isolation level, look for a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day.
Given that it could be the case where a strategy could be optimal for various levels of isolation.