When two or more transactions occur at the same time, the isolation level defines the degree at which a transaction is isolated from the resource or data modifications made by other transactions. There are four types of isolation levels; to change the isolation level, you need to set the tx_isolation variable which is dynamic and has session level scope.
To change this level, execute SET @@transaction_isolation = 'READ-COMMITTED';.
The current transaction can read data written by another uncommitted transaction, which is also called dirty read.
For example, A wants to add some amount to his account and transfer it to B. Assume both the transactions happen at the same time; the flow will be like this.
A initially has $400 and wants to transfer $500 to B after adding $500 to his account.
# Transaction 1 (adding amount) | # Transaction 2 (transferring amount) |
BEGIN; | BEGIN; |
UPDATE account SET balance=balance+500 WHERE account_number='A'; | -- |
-- | SELECT balance... |