CODEWITHSUNDEEP
mysql
medium
medium

Reputation: 4236

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

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?

Upvotes: 42

Views: 29674

Answers (2)

Folco Tandiono
Folco Tandiono

Reputation: 51

Imagine if you have this query

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

Between the select statement and update statement, anyone can change the sold value, so your query must be:

BEGIN;
a = SELECT Sold FROM Cars where id = 1 for update;
UPDATE Cars SET Sold = a + 1 where id = 1;
COMMIT;

the for update will lock the row with id = 1 from cars table, so other transaction that access the cars table with id = 1 will temporarily freeze until the current transaction finish.

Upvotes: 0

Sjoerd
Sjoerd

Reputation: 75599

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.

Upvotes: 42

Related Questions