I have a web app that does an addition to a field based on a user input.

So say for example User-A inputs 7, my web app would first query the contents of the field for the current number in there, which is say '10' in this case, then adds 7 to it and updates the filed with the total. In this case 17. Then User-B inputs 3, the same routine happens and now the filed contains 20 etc...

My concern is that if 2 or 2000 users query the table at the same time and get the same number back, for example 10, then it is going to go all sorts of wrong when it comes to the addition and writing the new total back.

Should I be explicitly locking the table / row on the SELECT statement? Is that normal practice in this situation and will it solve this issue.

This is basically a aggregate table for a survey system so I can quickly run and display reports on the daily totals etc...

  • Does the user have to see the existing value (10) before he provides the input (7) or not? – ypercubeᵀᴹ Jun 16 '14 at 10:01
  • No the user does not need to know the value before submitting his own. – superphonic Jun 16 '14 at 10:03

I think that a simple update statement will work just fine:

UPDATE TableName
SET col = col + @user_input 
WHERE key_col = @key ;

The update will take the necessarily lock (probably on one row only) and any other update (by other users) will either wait for the lock to be released or fail.

If you want the user to see the result of the update, you can enclose in an transaction, something like this:

    UPDATE table_name
    SET col = col + @user_input 
    WHERE key_col = @key ;

    SET @r = 
        ( SELECT col
          FROM table_name
          WHERE key_col = @key
        ) ;
SELECT @r AS col_updated ;
  • Thank you for this. How would you suggest I check for the existence of a record, and if it's not there create it? The same problem applies in that if multiple people are checking at the same time, more than one might try and create a new record. – superphonic Jun 16 '14 at 11:40
  • Check INSERT ... ON DUPLICATE KEY UPDATE. It tries to insert and if there is an existing row, it updates. – ypercubeᵀᴹ Jun 16 '14 at 11:49

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.