I have two tables
id - primary key title - text
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
Can I use mysql transactions to ensure that two users at the same time can not
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?