I have two tables one for venues (3 locations hall1, hall2 and hall3) and one for orders (a row could be 441,2004,50895,’Requested’,’2021-01-02 10:53′,’2021-03-06 11:46′,7,3) as follow:
CREATE TABLE loc (
loc_id INT NOT NULL,
name_loc VARCHAR(150) NOT NULL,
description VARCHAR(150) NOT NULL,
type_loc VARCHAR(150) NOT NULL,
CONSTRAINT pk_loc PRIMARY KEY (loc_id)
CREATE TABLE orders (
o_id INT NOT NULL,
o_code INT NOT NULL,
type_id INT NOT NULL,
creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
status TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
c_user INT NOT NULL,
loc_id INT NULL,
loc_dt TIMESTAMP NULL,
CONSTRAINT pk_orders PRIMARY KEY(o_id),
CONSTRAINT fk_loc FOREIGN KEY ( loc_id ) REFERENCES loc ( loc_id )
I cannot change the table structure with indexes and I have to use transactions to prove my theory.
At first, I was trying to solve the problem as if there’s no concurrency, I was doing something like this:
START TRANSACTION READ COMMITED;
SET loc_id = 1 -- 2 or 3
FROM orders AS o2
WHERE o2.loc_id IS NULL AND CURRENT_TIME != loc_dt
Which SQL statements do I need to ensure that the same location cannot be reserved more than once on the same day? How can I test it with and without concurrency?
I don’t know if my SQL statements are okay or how to continue when there are two transactions executing at the same time.
All suggestions are welcome.