Alternative to block a table

Question

I have a table called 'messages' (INNODB) where the user can insert their own posts. I want to put a limitation. In my php script when the table gets to 10 records, you can not add more. The logic of the program is more or less as follows.

Step 1. I run a query to count the lines that are in the table.

Step 2. Recovered that value, I decide whether to insert a new post.

My difficulty is in properly managing the possibility of two user who do the same thing simultaneously. If the user A is in step 1 while user B has just finished entering the tenth post, user A will include the eleventh.

How to avoid it?

Answer 1

You can create CHAR(1) NOT NULL field and cover it with UNIQUE INDEX. This will prevent of inserting more than 10 rows.

Other solution that could work would be to create BEFORE INSERT trigger that checks number of rows and raises error if there are more than 10 (look here for sample) (but in this case you can fail with condition races).