How to deal with concurrent updates in databases?

by

Use transactions:

BEGIN WORK;
SELECT creds FROM credits WHERE userid = 1;
-- do your work
UPDATE credits SET creds = 150 WHERE userid = 1;
COMMIT;

Some important notes:

  • Not all database types support transactions. In particular, mysql’s old default database engine (default before version 5.5.5), MyISAM, doesn’t. Use InnoDB (the new default) if you’re on mysql.
  • Transactions can abort due to reasons beyond your control. If this happens, your application must be prepared to start all over again, from the BEGIN WORK.
  • You’ll need to set the isolation level to SERIALIZABLE, otherwise the first select can read data that other transactions have not committed yet(transactions arn’t like mutexes in programming languages). Some databases will throw an error if there’s concurrent ongoing SERIALIZABLE transactions, and you’ll have to restart the transaction.
  • Some DBMS provide SELECT .. FOR UPDATE , which will lock the rows retreived by select until the transaction ends.

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.

More Related Contents:

  1. Only inserting a row if it’s not already there
  2. Is SELECT or INSERT in a function prone to race conditions?
  3. SET NOCOUNT ON usage
  4. Return pre-UPDATE column values using SQL only
  5. Do database transactions prevent race conditions?
  6. Using SQL Server as a DB queue with multiple clients
  7. Concurrency handling of Sql transactrion
  8. AFTER DELETE Trigger is not inserting multiple rows [closed]
  9. How to use an expression in a join between two tables?
  10. Passing a varchar full of comma delimited values to a SQL Server IN function
  11. PostgreSQL: using a calculated column in the same query
  12. How to delete duplicate rows without unique identifier
  13. count(*) vs count(column-name) – which is more correct? [duplicate]
  14. Left Outer Join using + sign in Oracle 11g
  15. Is the LIKE operator case-sensitive with SQL Server?
  16. How do I add a foreign key to an existing SQLite table?
  17. How to insert a blob into a database using sql server management studio
  18. Equivalent function for DATEADD() in Oracle
  19. How to create a multi-tenant database with shared table structures?
  20. Anonymous TABLE or VARRAY type in Oracle
  21. Name database design notation you prefer and why?
  22. Incorrect syntax near ”
  23. SQL: How can I get the value of an attribute in XML datatype?
  24. Do clustered indexes have to be unique?
  25. Execution sequence of Group By, Having and Where clause in SQL Server?
  26. Aggregate function in an SQL update query?
  27. How to change string date to MySQL date format at time of import of CSV using MySQL’s LOAD DATA LOCAL INFILE
  28. Oracle/SQL: Why does query “SELECT * FROM records WHERE rownum >= 5 AND rownum
  29. Select statement to return parent and infinite children
  30. HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]

Leave a Comment