Consider the following example:
A MySQL table (table) with structure id (Primary Key), value, unique_id exists.
Whenever a user clicks on Button A, PHP executes the following query:
SELECT `id` FROM `table` WHERE `unique_id` = x; //where x is a `unique_id`
If the query returns nothing (I.E. x does not yet exist in the unique_id column), the new row is inserted into table (with x in the unique_id column).
... Continuing with this logic:
A query is executed when viewing Page A:
SELECT `id` FROM `table` WHERE `unique_id` = x;
Today I received the following error:
SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row
This is the first time I have experienced this issue (with a table in excess of 20k rows).
Is is possible that if two separate users both clicked on Button A at precisely the same moment in time (down to the millisecond), that two rows could be written with duplicate values in the unique_id column?
If so, how can I avoid this happening again in future? (Am I taking the wrong approach here?).
With the approach you are using, the answer is most likely that two records can be created. The first thing to do is to actually create UNIQUE INDEX on your unique_id column. According to your question it does not seem to have one.
That raises another question. Do you really need both an id and unique_id in your table? It may be possible to use only one or the other.
If you dropped the unique_id and relied merely on the primary key and converted that to an auto increment field, this problem would not exist.
If you want to continue with your current approach, add the unique index and then do the INSERT first.