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?

Other solutions?


INSERT ... ON DUPLICATE KEY UPDATE is not an option as I don't have an usable unique key.

As requested

id int(10) unsigned NOT NULL AUTO_INCREMENT,
startDateTime datetime DEFAULT NULL,
status varchar(10),

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.

  • Have you try use procedure instead the code
    – Krismorte
    Feb 18 '16 at 11:49
  • 1
    INSERT ... ON DUPLICATE KEY UPDATE is not an option as I don't have an usable unique key. The problem is right there. In other words, if there is not a unique key, I dont see a problem with different connections inserting multiple rows. What's wrong with that? You (or whoever designed this) obviously didn't care if there are multiple identical rows. Feb 18 '16 at 11:51
  • the condition is a function of 2 columns, so I don't insert multiple identical rows. Actually, this block of code is exactly to prevent that. The conditions is something like: date(operationDate)=curdate and operationType="xxx"
    – Glasnhost
    Feb 19 '16 at 12:17
  • @Glasnhost Then you can probably define some meaningful multicolumn unique key to take care of that.
    – jkavalik
    Feb 19 '16 at 14:18
  • 1
    Too much hand-waving; let's see the actual SQL and SHOW CREATE TABLE.
    – Rick James
    Feb 19 '16 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.