should you got 100 000 customers, is mysql performing one sql query at that time?
cause during my php code i see if a particular row is available. whether it does not it produces one. whether it does, it simply update the row counter.
it entered my thoughts that possibly 100 customers are checking when the row is available simultaneously, so when it does not all of them create one row each.
when the mysql is handling them sequentially i understand it will not be an problem. the other user will see if it is available, otherwise, create it. and also the other user will see if it is available, and also, since this is the situation, it simply update the counter.
but when all of them see if it is available simultaneously and let us say it does not, they all create one row and also the whole table structure will fail.
could be great if a person could shed an easy about this subject.
Make use of a UNIQUE constraint or, if viable, result in the primary key your data products and also the SQL server may prevent duplicate rows from being produced. You may also make use of the "ON DUPLICATE KEY UPDATE ..." syntax to specify the alternate operation when the row already is available.
Out of your comments, it may sound like you could utilize the consumer_id as the primary key, by which situation, you'd have the ability to use something similar to this:
INSERT INTO usercounts (user_id,usercount) VALUES (id-goes-here,1) ON DUPLICATE KEY UPDATE usercount=usercount+1;
Should you place the check and place right into a transaction then this can be avoided problem. By doing this, the check and make is going to be run as you one query there should not be any confusion