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:
-
Transactions
-
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.
Best Answer
In addition to Phill's points...
A common pattern:
This does (roughly) one of these:
innodb_lock_wait_timeout
) The current transaction sees a value; the blocked transaction sees the value after yourCOMMIT
.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.
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: