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