In my web application (java+mySQL) I have the following server side (pseudo)code:
select count(*) c form table where condition; if c>0 then update... else insert...
In some situations (heavy load, multiple click, slow connections) it seems that different requests execute the select at once, generating thus multiple records instead of only one.
I'm looking for the best and safest way to avoid this. It will be enough to put the whole block in a transaction, and which level, serializable? Should I be careful with locking conditions, considering that other parts of the application could write/read on the same table for other reasons?
INSERT ... ON DUPLICATE KEY UPDATE is not an option as I don't have an usable unique key.
CREATE TABLE start ( id int(10) unsigned NOT NULL AUTO_INCREMENT, startDateTime datetime DEFAULT NULL, status varchar(10), PRIMARY KEY (id))
And the code should look like
query=select count(*) c, id from start where date(StartDateTime)=curdate() if c>0 then update start set code="updated" where id=query.id else insert start (startDate,code) values (curtime(),"start")
This code is heavily simplified. I could solve it with insert...on duplicate key if I could define a primary key as the function date(startDateTime). But mysql 5.6 doesn't support it.
I could also add a column with date(startDateTime), but this is not easy done code-side.