How to ensure that two or more simultaneous requests to a MySQL table don't interfere

Question

Suppose I have about a 1000 simultaneous requests to a table in my DB which consists of these statements (in order):

SELECT

INSERT or UPDATE (could be multiple)

DELETE

and I want to make sure that none of the requests interfere with one another and the table is in an updated state for the next incoming request. For example:

Request #1 comes in.

Request #2 comes in.

Request #1 starts to get processed.

Request #2 should start only when Request #1 is processed so that the data view is consistent and updated for the next request.

I searched for this situation around the web and found out two things that could be of help:

1. Transactions

2. Table Locking

But when I read about Transactions, I found out that while a row is being processed by a transaction, other transactions can still come along and modify that row which defeats my purpose.

Also in case of locking, a column can only be locked in one mode i.e. either in READ or WRITE mode, which again fails to keep the data consistent as while reading a row it may get updated and the data which was just read would become an older version of what the table actually is right now.

Is there a solid way to achieve this which can make sure that no request interferes with another and the table is in an updated state for the next incoming request?

I am using MySQL with PHP if this helps.

Answer 1

In addition to Phill's points...

A common pattern:

BEGIN;
SELECT ... FOR UPDATE;
use the values from that SELECT
UPDATE ... the row(s) that were SELECTed for UPDATE.  (Or skip if you decide)
COMMIT;

This does (roughly) one of these:

• The entire transaction runs without any fuss.
• Another connection touches the row(s) at the 'same' time, but is temporarily blocked. (Cf, innodb_lock_wait_timeout) The current transaction sees a value; the blocked transaction sees the value after your COMMIT.
• Another connection touches the row(s) at the 'same' time, but is deadlocked. This happens when simply delaying won't resolve the conflicts. One transaction will complete 'normally'; the other will get an error -- both connections must check for errors.

SERIALIZABLE is overkill. It is a brute force way to disallow the concurrent access as described above.

More on your mention of a data view.

Each transaction sees a "snapshot" of the data. This snapshot is all the data as it stood at the beginning of the transaction. The implementation involves keeping copies of rows (cf "history list") so that each transaction can find the data as it stood some time in the past.

1000 simultaneous requests to a table

That is very hard to achieve. And very hard to probe. You can easily verify "1000 requests to a table in the same 1-second period". But, if you look at the details, you may find that no more than 10 "requests" are actually running "simultaneously". But the are finishing in 10ms, so the entire set finishes in one second.

So, let's break the question into 2 things:

• Ability to make progress on both of two connections that are touching the same row of the same table. I have discussed that above. Whether it is "2" or "1000" makes no difference.
• Ability to run 1000 queries/second. If you have having trouble in this area, let's discuss indexes, query formulation, etc. 1000 qps is often possible even with transactional integrity, but it may take some extra work.

Answer 2

Transactions are the way to go.

... I found out that while a row is being processed by a transaction, other transactions can still come along and modify that row ...

Cite your source because, frankly, it's just plain wrong.
The whole point of Transactions is that they give you exactly the kind of "atomicity" of change that you're looking for.

Table locking is far too broad a brush to use here.
Locking many rows just update one is not efficient, creates more contention, more waiting and just slows everything down. To draw a parallel, it would be like trying to deliver a letter to one house by blocking up every letterbox on every other house in the city.