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, degree VARCHAR(150), 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; UPDATE orders SET loc_id = 1 -- 2 or 3 WHERE o_id IN ( SELECT o2.id FROM orders AS o2 WHERE o2.loc_id IS NULL AND CURRENT_TIME != loc_dt ) COMMIT;
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.