What's the common way to deal with concurrent updates in an SQL database ?
Consider a simple SQL schema(constraints and defaults not shown..) like
create table credits (
update credits set creds= 150 where userid = 1;
Begin; select creds from credits where userid=1; do application logic to calculate new value, update credits set credits = 160 where userid = 1; end;
update credits set creds = creds - 150 where userid=1;
BEGIN WORK; SELECT creds FROM credits WHERE userid = 1; -- do your work UPDATE credits SET creds = 150 WHERE userid = 1; COMMIT;
Some important notes:
Combining transactions with SQL stored procedures can make the latter part easier to deal with; the application would just call a single stored procedure in a transaction, and re-call it if the transaction aborts.