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 ...