Lets say I am developing a timetable system whereby for each time slot, there can only be a maximum of ONE booking.

I have set a UNIQUE constraint in my table to ensure that inserting fails when there are multiple entries with the same time slot. (This is based on the date field, and time field)

However, on the API level, I need to show an error message whenever someone tries to book a slot already taken.

There are two approaches:

1. I first query the slot to check whether there is an entry: if a slot exists, inform the user the slot is taken
2. I simply insert, and if there is a DUPLICATE error: if mysql throws a duplicate error, inform the user the slot is taken

My question is whether using approach 2) is sufficient or should I first use 1) to check?

Are there any pros and cons?

Thank you

I've tried both approaches, and they work. Just looking at which is more efficient, or any potential short comings of approach 2.

CodePudding user response：

Doing 1) is pointless because a transaction can run right after you run your query for 1) and insert an item with the same ID as the one you are about to insert.

Doing 2) should be sufficient, assuming you are using a decent engine in your Mysql (e.g. InnoDB, which is default in MySQL 8.0)

Note that you could run a more graceful query using ON DUPLICATE KEY. See "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"