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

user_7422128 0
Viewed 115

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.

I am confused here so just want some clarity, please help.

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.

mysql
2 Answers

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.

Bill_Karwin_208600
user_54446180
Related