Search code examples
mysqltransactionsisolation-level

consistency by isolation levels, while SELECT SUM(...) million records, UPDATE some records

I was testing READ UNCOMITTED and READ COMITTED. For this, using 2.5 million row records, I tried the followings.

First, at Session A:

BEGIN;
SELECT SUM(salary) FROM salaries;

then immediately (before finishing the SUM), at Session B:

BEGIN; UPDATE salaries SET salary = 10000 WHERE emp_no = 250000; COMMIT;

then the result is:

  • READ UNCOMMITTED at Session A gives the result as if it is after UPDATE. (understandable)
  • READ COMMITTED at Session A gives the result as if it is before UPDATE. (confusing for me)

I'm confused about the result of READ COMMITTED. When Session A is scanning the records, I think the UPDATE from Session B is already commited.

In other words, the difference between READ UNCOMMITTED and READ COMMITTED should be if the changes from another connection are COMMITted or not, but in this case there isn't difference in this sense (the UPDATE is COMMITted anyway) but the results are different, which is confusing.

In general, is it true that an UPDATE etc doesn't affect the result when a single giant SELECT is happening as long as the isolation level is READ COMMITTED or the stricter ones? If so, why? (because, isn't the UPDATE already committed when SELECT reaches at the updated rows?) Please note that Session A does read the updated data if SELECT starts after UPDATE even if it's SERIALIZABLE.

FYI: the test data comes from here: https://github.com/datacharmer/test_db

Solution

  • TIt is hard to understand and the wording can öead to missunderstandigs.

    MySQL Homegae about isolation leveels

    For5 READ commitetd:

    each consistent read, even within the same transaction, sets and reads its own fresh snapshot.

    and for uncommited:

    SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. so your statement can not be correct

    So if it is read committed your result would be always without the UPDATE , as a snapshot is used to calculate your SUM

    With UNCOMMITED it can happen, that the UPDATE will be in the SUM but it can not be guaranteed.