Sample code for the test script for indexes and locks at RR and RC isolation levels

select * from table where ?;

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values ​​();
update table set ? where ?;
delete from table where ?;

-- Basic Operations --
--Query transaction isolation level, the default is RR
show variables like '%isolation%';

-- Set the transaction isolation level to RC
set session transaction isolation level read committed;


-- Data initialization --
begin;
drop table if exists user;
CREATE TABLE `user` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(64) NOT NULL,
 `age` int(11) NOT NULL,
 `address` varchar(64) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uniq_email` (`email`),
 KEY `idx_age` (`age`)
);

insert into user (email, age, address) values ​​("[email protected]", 18, "address1");
insert into user (email, age, address) values ​​("[email protected]", 20, "address2");
insert into user (email, age, address) values ​​("[email protected]", 20, "address3");

commit;
select * from user;



-- 1. trx_id example begin;
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
select * from user;
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
SHOW ENGINE INNODB STATUS;
update user set age = 22 where id = 3;
-- Query transaction id
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
-- INNODB engine status SHOW ENGINE INNODB STATUS;
commit;

-- 2. Repeatable read and non-repeatable read examples -- session1
set session transaction isolation level read committed;
begin;
--session2
set session transaction isolation level repeatable read;
begin;
--session1
select * from user;
--session2
select * from user;
--session3
begin;
insert into user (email, age, address) values ​​("[email protected]", 30, "address4");
commit;
-- Session1 is in RC mode, so it can read the new data submitted by trx3. If you want to prove non-repeatable read, you should use update instead of insert.
select * from user;
commit;
-- Session2 is RR here, so it will not read the new data submitted by trx3 select * from user;
commit;

-- 3. Snapshot read phantom read example -- session1
set session transaction isolation level repeatable read;
begin;
-- Here we use snapshot read select * from user;
--session2
begin;
insert into user (email, age, address) values ​​("[email protected]", 30, "address4");
commit;
select * from user;
--session1
select * from user; -- The data of test4@ cannot be read here because it is RR
-- A phantom read occurs here insert into user (email, age, address) values ​​("[email protected]", 30, "address4"); -- Insertion fails because of email unique index conflict commit;

-- 4. Current read phantom read example -- RC
--session1
set session transaction isolation level read committed;
begin;
-- Here, all records with age = 20 that meet the condition will be locked. Because it is RC, there is no GAP lock. delete from user where age = 20;
select * from user;
--session2
set session transaction isolation level read committed;
begin;
-- Because trx1 does not have a GAP lock, records with age=20 can be inserted into user (email, age, address) values ​​("[email protected]", 20, "address4");
select * from user; -- You can find 4 data, and you can read the deleted data of trx1. Because it is RC, trx1 has not been submitted, so it does not affect trx2.
commit;
--session1
select * from user; -- You can read the newly inserted data of trx2. Although trx1 is currently reading, the corresponding next-key lock is not added, which does not prevent the new data of trx2 from being inserted and committed;

--RR
--session1
set session transaction isolation level repeatable read;
begin;
delete from user where age = 20;
select * from user;
--session2
begin;
-- This will cause a blockage because trx1 adds a GAP lock around age=20. -- For a non-unique index, first locate the first record that meets the query criteria through the index, add an X lock on the record, add a GAP lock on the GAP, and then add an X lock on the record on the primary key clustered index;
-- Then read the next one and repeat. Until the first record that does not meet the conditions is reached, at this time, there is no need to add a record X lock, but a GAP lock is still required, and finally return to the end.
insert into user (email, age, address) values ​​("[email protected]", 20, "address4");
-- Until timeout, ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- If you query at this time, you can see 3 records committed;
--session1
-- Only one record can be seen at this time, and the other two have been deleted select * from user;
commit;

-- Unique index + RC
--session1
set session transaction isolation level read committed;
begin;
delete from user where email = "[email protected]";
--session2
begin;
-- Can be read, because trx1 is RC
select * from user where email = "[email protected]";
-- Try to update the age of this record, it will be blocked until timeout, because email is the only index that has been locked by trx1, and the corresponding primary key index will also be locked. -- Note that the id=3 operated here is the same row record of email operated in trx1 update user set age = 40 where id = 3;
--session1
commit;
--session2
commit;

-- No index + RC
--session1
set session transaction isolation level read committed;
begin;
-- Since the address field has no index, Innodb will lock all rows, and the MySQL server will make a judgment and release the lock. delete from user where address = "address3";
--session2
set session transaction isolation level read committed;
begin;
-- This line will succeed because this line is not locked (first locked and then released)
update user set age = 10 where address = "address2";
-- This line will also be blocked because it has been locked by the statement of trx1. All the statements that meet the conditions are locked. update user set age = 10 where address = "address3";
--session1
commit;
--session2
commit;

-- Non-unique index + RR
--session1
set session transaction isolation level repeatable read;
begin;
delete from user where age = 20;
--session2
set session transaction isolation level repeatable read;
begin;
-- This will cause a blockage because the record with age=20 has been locked in trx1 and a GAP lock has been added, so 18 has fallen into the lock interval insert into user (email, age, address) values ​​("[email protected]", 18, "address4");
--session1
commit;
--session2
commit;

-- No index RR
--session1
set session transaction isolation level repeatable read;
begin;
-- If there is no index, all records in the table will be locked, and all gaps on the primary key index will be locked to prevent all concurrent update operations delete from user where address = "address3";
--session2
set session transaction isolation level repeatable read;
begin;
-- This will block because the primary key has been given a GAP lock, so the new insert cannot be executed successfully insert into user (email, age, address) values ​​("[email protected]", 18, "address4");
--session1
commit;
--session2
commit;

-- Simple deadlock example -- session1
begin;
delete from user where id = 1;
--session2
begin;
delete from user where id = 3;
--session1
delete from user where id = 3;
--seession2
-- Here MySQL determines that a deadlock has occurred and interrupts a trx
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
delete from user where id = 1;
--session1
rollback;
--session2;
rollback;

-- 5. Deadlock insert example drop table if exists t1;
begin;
create table t1 (
 `id` bigint not null auto_increment,
 primary key (`id`)
);
insert into t1 values(1);
insert into t1 values(5);
commit;
select * from t1;
--session1
begin;
insert into t1 values ​​(2);
--sessioin2
begin;
-- This will block insert into t1 values ​​(2);
--session3
begin;
-- This will block insert into t1 values ​​(2);
--session1;
-- At this time, rollback occurs, trx2 and trx3 receive notification, and MySQL automatically interrupts one trx because a deadlock occurs -- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
rollback;
--session2;
rollback;
--session3;
rollback;