MySQL – Locking Tables and Rows on Select Statement

lockingMySQLselect

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…

Best Answer

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 ;
Related Question