0

I have two tables

flats

   id - primary key
   title - text

flats_reservations

   id - primary key
   flat_id - foreign key to flats(id)
   creator_id - foreign key to users(id)
   is_reserved - boolean
   created_at - current_timestamp

If flats_reservations has these rows

id=1, flat_id=12, creator_id=5, is_reserved=1, created_at=2016-10-10 00:00:00
id=2, flat_id=12, creator_id=5, is_reserved=0, created_at=2016-10-10 00:00:05
id=3, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:10

Then only user with id=4 can remove reservation (e.g. insert following row (is_reserved=0)

id=4, flat_id=12, creator_id=4, is_reserved=0, created_at=2016-10-10 00:00:10

No other user can make this. At any point of time only one user can reserve flat.

Can I use mysql transactions to ensure that two users at the same time can not reserve flat?

I want to be sure, that following rows can not be inserted at once

id=5, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:15
id=6, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:15

In application I use following queries in transactions

# transaction start
select is_reserved from flat_reservations where flat_id=12 order by created_at desc limit 1;
# if is_reserved == 0 or query returned 0 rows then
insert into `flat_reservations` (`flat_id`, `creator_id`, `is_reserved`) values (12, 4, 1)
# commit

Is there some way to lock table during execution of these two queries and only when flat_id=12?

Users must be able to reserve other flats (e.g., where id is not 12).

If it is not possible then should I lock whole table for writing?

  • Please provide SHOW CREATE TABLE for both tables. – Rick James Oct 23 '16 at 20:32
0

Plan A: Add FOR UPDATE to the SELECT.

Plan B: Add UNIQUE(flat_id, creator_id, is_reserved)

If neither of those work, then I am confused by your business logic; please explain further.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.