CODEWITHSUNDEEP
mysql
user7422128
user7422128

Reputation: 932

Which version of record will be returned in read committed isolation of MYSQL

I have a scenario where my cluster is in read committed isolation mode and the use case is like below:

A select statement when executed takes around 1 minutes to run the query and get the response back.

During which updates (Committed) to data can happen during this time frame of 1 minute.

So my question is will I get the updated record in the response or the old record? I read the documentation and it's mentioned any phantom reads are allowed.

Using READ COMMITTED has additional effects(Reference MYSQL docs):

  1. For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.

  2. For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.

Upvotes: 2

Views: 296

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562310

There is no way concurrent updates to data can modify a given query while it is executing. It's as if every query runs in its own REPEATABLE READ snapshot, even if your transaction is READ COMMITTED.

It will return rows that had been committed at the time the statement began executing. It will not include any rows committed after the statement began.

Re your comment:

No, there is no transaction isolation level that can change this. Even if you use READ UNCOMMITTED, a given query reads only rows that were committed at the time the query began executing.

If you want to query recent updates, you can only do it by starting a new query.

If you're concerned that you aren't getting notified about recent updates, then you need to optimize your query so it doesn't take 60 seconds to execute.

This is starting to sound like you're polling the database. Running frequent expensive queries to poll a database is an indication that perhaps you need to use a message queue instead.

Re your second comment:

Locking SQL statements, including UPDATE and DELETE and also locking SELECT statements do function like READ COMMITTED even when your transaction is REPEATABLE READ. Locking statements always read the most recent row that was committed at the time the statement started.

But they still cannot read new rows committed after the statement started. If for no other reason than they can't get the locks on those rows.

Your original question was about SELECT statements, and I assumed you meant non-locking SELECT (that is, without the options of FOR UPDATE or LOCK IN SHARE MODE). Those SELECT statements also cannot view rows added after the SELECT started.

P.S. I have never found a good use of READ UNCOMMITTED for any purpose.

Upvotes: 4

Guillaume F.
Guillaume F.

Reputation: 6463

By default, INNOBD will lock the tables during processing, but there are ways to do an UNLOCKED SELECT. In that case, it will run on a versioned snapshot of the table, so any COMMIT during the processing won't alter the result.

For more information: https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

In all cases, the ACID property of databases will always prevent unstable functions: https://en.wikipedia.org/wiki/ACID

Upvotes: 2

Related Questions