38

I am currently running a MySQL database. All of my tables are using the Table Engine InnoDB.

Everyone who logs into my application can view records and I am worried that at some point two users might update or insert a record at the same time. Does MySQL handle this type of concurrency issue gracefully, or is this something that I am going to have to program into my code?

If I do have to program it into my code how do you go about handling a concurrency case like this?

medium
  • 4,136
  • 16
  • 55
  • 66

1 Answers1

38

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.

Sjoerd
  • 74,049
  • 16
  • 131
  • 175
  • 20
    Even in a transaction there's no guarantee. MySQL by default, does row-level locking/table-locking only for write operations, even in transactions. To make **a** have a correct value, it's necessary to put *FOR UPDATE* in SELECT, or to specify a higher *isolation level* on TRANSACTION as *SERIALIZABLE*. **More info:** [Internal locking](https://dev.mysql.com/doc/refman/5.6/en/internal-locking.html), [Isolation levels](https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html), [Locking Reads](https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html) – Alan CN Jan 05 '17 at 18:51
  • is this statement true? _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_ – nulll Jan 07 '21 at 11:04
  • 1
    @nulll Yes, it's true, the it's A in [ACID](https://en.wikipedia.org/wiki/ACID), Atomicity. – doug65536 Nov 17 '21 at 23:47
  • What about the following concurrent queries? UPDATE jobs SET assigned_to = 1 WHERE assigned IS NULL LIMIT 1; UPDATE jobs SET assigned = 2 WHERE ts IS NULL AND assigned_to IS NULL LIMIT 1; They will always "assign" two rows? Or I can have a situation where only one row is assigned (to 1 or 2). Trying to make forever running concurrent scripts pulling jobs to do with SELECT * FROM jobs WHERE assigned = (1 or 2), exit if empty, obviously without premature terminating scripts if there are jobs to do. – Marco Marsala Dec 14 '22 at 09:39