MySQL concurrency, how does it work and do I need to handle it in my application

by

SQL statements are atomic. That is, if you execute something like this:

UPDATE Cars SET Sold = Sold + 1

Nobody can change the Sold variable during this statement. It is always incremented by 1, even if somebody else is executing the same statement concurrently.

The problem occurs if you have statements that depend on each other:

a = SELECT Sold FROM Cars;
UPDATE Cars SET Sold = a + 1;

Between these queries, another user can change the table Cars and update Sold. To prevent this, wrap it in a transaction:

BEGIN;
a = SELECT Sold FROM Cars;
UPDATE Cars SET Sold = a + 1;
COMMIT;

Transactions are supported by InnoDB, but not by MyISAM.

More Related Contents:

  1. SQL Query needed for fetch value from Two identical table but not fully identical table
  2. MySQL ERROR 1045 (28000): Access denied for user ‘bill’@’localhost’ (using password: YES)
  3. MySQL: Insert record if not exists in table [duplicate]
  4. How do I escape special characters in MySQL?
  5. Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user
  6. SQL counting all rows instead of counting individual rows
  7. MySQL – ERROR 1045 – Access denied
  8. Remote Connections Mysql Ubuntu
  9. MySQL InnoDB not releasing disk space after deleting data rows from table
  10. In MySQL, should I quote numbers or not?
  11. Dump all tables in CSV format using ‘mysqldump’
  12. MySQL add days to a date
  13. How can I loop through all rows of a table? (MySQL)
  14. Select columns across different databases
  15. Check for x consecutive days – given timestamps in database
  16. LOAD DATA INFILE Error Code : 13
  17. MySql update two tables at once
  18. Returning query results in predefined order
  19. Django MySQL full text search
  20. How to force MySQL to take 0 as a valid auto-increment value
  21. MySQL: Many tables or many databases?
  22. Select only last value using group by at mysql
  23. AWS Aurora MySQL serverless: how to connect from MySQL Workbench
  24. DELETE FROM `table` AS `alias` … WHERE `alias`.`column` … why syntax error?
  25. Incorrect key file for table ‘/tmp/#sql_3c51_0.MYI’; try to repair it [duplicate]
  26. How to store data with dynamic number of attributes in a database
  27. MySQL Auto increment primary key increases by 10
  28. Escape line breaks in MySQL output
  29. MySQL Relationships
  30. Laravel – search with multiple keywords against multiple columns with the search result to be ordered in relevance

Leave a Comment