How to block table in booking?

Question

How to block table to booking? My booking table has from_datetime and to_datetime fields. I would like to avoid situation, when two users book something in the same time.

My attempts:

TERMINAL 1: - user1

LOCK TABLE booking READ;
INSERT INTO booking VALUES ('2019-01-01 12:00:00', '2019-01-01 14:00:00');

Now table booking should be locked, and other users should not be able to read from its, but...

TERMINAL 2: - user2

SELECT * from booking

I have results... Why? This should be blocked until I do in TERMINAL 1:

unlock tables;

Barmar · Accepted Answer · 2019-06-28T22:27:19.293

3

You need to use LOCK TABLE booking WRITE;. That indicates that you're going to be writing to the table, and you need to block everyone else from reading and writing the table.

A READ lock means that you're only going to read the table. It blocks other users from writing to the table, so that all your reads access consistent table contents. But it doesn't block other users from reading the table.

However, if you're using InnoDB you should use transactions rather than table locks. This is more fine-grained, and figures out what to lock automatically. Your code would look something like this:

START TRANSACTION;
SELECT COUNT(*) FROM booking
WHERE from_datetime = '2019-01-01 12:00:00' AND to_datetime = '2019-01-01 14:00:00'
FOR UPDATE;
-- If this returns zero count:
INSERT INTO booking VALUES ('2019-01-01 12:00:00', '2019-01-01 14:00:00');
COMMIT;

The FOR UPDATE option in the SELECT causes MySQL to lock the appropriate part of the table. If another user executes the same code, they'll be blocked if they try to select the same times.

edited Jun 28 '19 at 22:27

answered Jun 28 '19 at 22:07

Barmar

741,623
53
500
612

Thanks, but what if two user make reservation in the same time? Same transaction won't help. Lock table yes. – ruyawo Jun 28 '19 at 22:14
Two users are in different transactions. – Barmar Jun 28 '19 at 22:16
1

Yes, but I can't check in transaction whether the dates of the reservation coincide. I can't set unique keys for my example. – ruyawo Jun 28 '19 at 22:20
I've added more details to the answer. – Barmar Jun 28 '19 at 22:27
But whether it will block dates from '2019-01-02 12:00:00' to '2019-01-04 14:00:00'? These dates shouldn't be in booking, because they overlap with my dates. – ruyawo Jun 28 '19 at 22:33
No, it won't. For that you may need to lock the whole table. – Barmar Jun 28 '19 at 22:34
So should I do "LOCK TABLE booking WRITE;" or is better way? – ruyawo Jun 28 '19 at 22:42
You might want to read https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html – Barmar Jun 28 '19 at 22:45

René Höhle · Answer 2 · 2019-06-28T22:26:11.660

0

Locking tables is not such a good idea in some cases.

You should use a Transaction and check in your transaction if the ranges overlap.

Check overlap of date ranges in MySQL

https://dev.mysql.com/doc/refman/8.0/en/commit.html

edited Jun 28 '19 at 22:26

answered Jun 28 '19 at 22:06

René Höhle

26,716
22
73
82

But first user can made reservation from '2019-01-01 12:00:00' to '2019-01-03 14:00:00' and secons user can: '2019-01-02 12:00:00' to '2019-01-04 14:00:00'. I don't have idea how to set index for this case... – ruyawo Jun 28 '19 at 22:15

How to block table in booking?

2 Answers2