Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

share|improve this question
    
Does the user have to see the existing value (10) before he provides the input (7) or not? –  ypercube Jun 16 at 10:01
    
No the user does not need to know the value before submitting his own. –  superphonic Jun 16 at 10:03

1 Answer 1

up vote 1 down vote accepted

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:

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

    SET @r = 
        ( SELECT col
          FROM table_name
          WHERE key_col = @key
        ) ;
COMMIT ;
SELECT @r AS col_updated ;
share|improve this answer
    
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 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 at 11:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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