MySQL 8 Cookbook

Read committed

The current transaction can read only the data committed by another  transaction, which is also called non-repeatable read. 

Take the same example again where A has $400 and B has $600.

# Transaction 1 (adding amount)	# Transaction 2 (transferring amount)
BEGIN;	BEGIN;
UPDATE account SET balance=balance+500WHERE account_number='A';	--
--	SELECT balance INTO @a.balFROM accountWHERE account_number='A';# A sees 400 here because transaction 1has not committed the data yet
COMMIT;	--
--	SELECT balance INTO @a.balFROM accountWHERE account_number='A';# A sees 900 here because transaction 1has committed the data.

 

You can notice that, in the same transaction, different results are fetched for the same SELECT ...