MySQL transactions and isolation levels: analyzing dirty reads, non-repeatable reads and phantom reads

Reprint: https://mp.weixin.qq.com/s/XaKqWUrXsDHQIyB_G5aPBw

-- Create database test
create database if not exists test;
use test;

-- Delete table
drop table if exists tb_account;

create table tb_account(
                           id int primary key AUTO_INCREMENT comment 'ID',
                           name varchar(10) comment 'name',
                           money double(10,2) comment 'balance'
) comment 'account table';
insert into tb_account(name, money) VALUES ('李四',2000), ('王五',2000);

--Normal transfer situation

-- 1. Check the balance of John Doe
select * from tb_account where name = '李思';
-- 2. John Doe's balance decreases by 500
update tb_account set money = money - 500 where name = '李思';
-- 3. Wang Wu’s balance increases by 500
update tb_account set money = money + 500 where name = '王五';

-- 4. View account results
select * from tb_account;

-- Transfer exceptions
-- 1. Check the balance of John Doe
select * from tb_account where name = '李思';
-- 2. John Doe's balance decreases by 500
update tb_account set money = money - 500 where name = '李思';
 error....
-- 3. Wang Wu’s balance increases by 500
update tb_account set money = money + 500 where name = '王五';
-- 4. View account results
select * from tb_account;

-- 1. Open transaction
start transaction or BEGIN;

-- 2. Submit transaction
commit;

-- 3. Transaction rollback
rollback;

--Open transaction
start transaction;
-- 1. Check the balance of John Doe
select * from tb_account where name = '李思';
-- 2. John Doe's balance decreases by 1,000
update tb_account set money = money - 500 where name = '李思';
-- If the transfer fails, execute rollback
-- 3. Wang Wu’s balance increases by 1,000
update tb_account set money = money + 500 where name = '王五';
-- If execution is completed normally, commit the transaction
commit;
-- If an error occurs during execution, roll back the transaction
-- rollback;

-- Step 1: Set transaction A isolation level to read uncommitted

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
use test;
-- Step 3: Open transaction A and query the data with ID=1
start transaction;
select * from tb_account where id=1;

-- Step 5: Open transaction A and query the data with ID=1
select * from tb_account where id=1;

commit;

-- Step 2: Set transaction B isolation level to read uncommitted

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
use test;
-- Step 4: Open transaction A,
start transaction;
--Update the data of ID=1 money=money-500
update tb_account set money=money-500 where id=1;
commit;

--Non-repeatable read under read committed isolation level
-- Step 1: Set transaction A isolation level to read committed
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
use test;
-- Step 3: Open transaction A and query the data with ID=1
start transaction;
select * from tb_account where id=1;

-- Step 6: Transaction A, query the data with ID=1
select * from tb_account where id=1;

-- Step 8: Transaction A, query the data with ID=1
select * from tb_account where id=1;
commit;

--Non-repeatable read under read committed isolation level
-- Step 2: Set transaction B isolation level to read committed
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
use test;
-- Step 4: Open transaction B and update the data with ID=1 money=money-500
start transaction;
update tb_account set money=money-500 where id=1;
-- Step 5: Transaction B queries id=1 data
select * from tb_account where id=1;
-- Step 7: Transaction B submits the transaction and queries the results
commit;
select * from tb_account where id=1;

-- Phantom reads under repeatable read isolation level
-- Step 1: Set transaction A isolation level to repeatable read
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
use test;
-- Step 3: Open transaction A and query the data with ID>1
start transaction;
select * from tb_account where id>1;

-- Step 5: Transaction A, query data with ID>1
select * from tb_account where id>1;


-- Step 7: Transaction A, query data with ID>1
select * from tb_account where id>1;
commit;

-- Phantom reads under repeatable read isolation level
-- Step 2: Set transaction B isolation level to repeatable read
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
use test;
-- Step 4: Open transaction B and insert a piece of data
start transaction;
insert into tb_account values(3,'Zhang San',2000);
-- Step 6: Submit transaction B
commit;

SELECT @@TRANSACTION_ISOLATION;

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {<!-- --> READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }