I'm currently PHP building a script that has to update statistics when it finishes it's purpose. The script is accessed by web browser and depending on the traffic it may be executed simultaneously. I have to guarantee the statistics are right.
To give you the picture let's say we have a table:
CREATE TABLE statistics(
user_id integer NOT NULL,
date integer NOT NULL, -- for unix time
stat1 integer NOT NULL DEFAULT 0,
stat2 integer NOT NULL DEFAULT 0,
stat3 integer NOT NULL DEFAULT 0 -- and so on...
);
-- Let's insert some testing data for a couple of users and days...
-- Day one
INSERT INTO statistics(1, 1303520820, 1, 1, 1);
INSERT INTO statistics(2, 1303520820, 1, 1, 1);
-- Day two
INSERT INTO statistics(1, 1303603200, 1, 1, 1);
INSERT INTO statistics(2, 1303603200, 1, 1, 1);
-- Day three
INSERT INTO statistics(1, 1303689600, 1, 1, 1);
INSERT INTO statistics(2, 1303689600, 1, 1, 1);
Every day a new row is inserted in the table so we could have a daily, weekly, monthly, yearly statistics. I have to be sure only one row is inserted per user_id per day. Also whenever an UPDATE query is executed it would increment the columns stat1,stat2,stat3 appropriately.
This script is expected to have quite some traffic and I want to figure out how to make things work when the script is executed and has a couple of instances working simultaneously. What method/technique do you find best for such tasks ?
The simplest solution is to add a unique constraint
CREATE TABLE statistics(
user_id integer NOT NULL,
date integer NOT NULL, -- for unix time
stat1 integer NOT NULL DEFAULT 0,
stat2 integer NOT NULL DEFAULT 0,
stat3 integer NOT NULL DEFAULT 0, -- and so on...
UNIQUE(user_id,date)
);
You should definitely do this regardless of what other measures you take.