关于MySQL Insert语句事务机制的探索

背景：MySQL InnoDB默认Session事务隔离级别是REPEATABLE-READ

表结构： A(PK)| B|C (BC组合唯一索引)

问题：两个insert同时插入PK不同的，但是组合索引相同的数据会发生insert1先插入后提交的情况吗?

即如下情况：

insert1: 插入data1

        -insert2: 插入data2

        -insert2: 提交成功

insert1: 提交失败

答案是不会出现这种情况；

先做实验，再探究所以然

1.建表 A 主键，BC唯一组合索引

2.设置mysql手动提交事务

SET @@autocommit=0;

SHOW VARIABLES LIKE '%autocommit%';

3.准备好两个client分别执行insert语句

insert1: 执行不提交

BEGIN;
INSERT INTO test (`A`, `B`, `C`) VALUES (1,1,1);

--COMMIT;先执行Insert不提交事务

insert2:执行提交

BEGIN;
INSERT INTO test (`A`, `B`, `C`) VALUES (1,1,1);
COMMIT;执行Insert提交事务

insert1:提交

COMMIT;

实验现象1：（前提insert1和insert2的PK和组合唯一键数据不存在在表里面）

insert1提交前阻塞等待状态，insert2 处于阻塞状态，直到insert1提交后，insert2出现

"Duplicate entry '1-1-1' for key 'uni_BC'"

根据现象得出insert1和insert2互斥

实验现象2：（前提insert1和insert2的PK或组合唯一键数据存在在表里面）

insert1或insert2执行未提交前均提示"Duplicate entry '' for key ，事务未提交也不阻塞下一insert执行

疑问1：现象1，insert1执行未提交前其他insert都不能提交,insert上表锁？

疑问2：现象2，insert语句执行前会做唯一性校验吗？

疑问3：现象2，未提交前出现了Duplicate entry，我们再提交或回滚会出现bug?

分析实验1：

insert1提交成功具有剖析和状态信息，insert2出现Duplicate Key则只有状态信息

insert1的两张图：

 insert2的一张图：

 

分析实验2：

 执行未提交：

 执行自动提交：

 从状态对比发现：

insert1比insert2多了 Handler_commit;

insert2比insert1多了Key_read_requests，Key_reads，Key_write_requests，Key_writes，Table_locks_immediate

什么意思呢？

实验一：

insert1提交成功，insert2未提交成功，Handler_commit 差异就出来了

insert2 Key_read_requests 从cache中读取1026次，Key_reads 从磁盘中读取7次，Table_locks_immediate 请求可立即授予的表锁671次

实验二：

读取了磁盘和缓存就可以知道了唯一性问题，可见索引起了作用

释疑：

insert上表锁;

insert前做唯一校验,cache级别大体可以确定是索引结构;

未提交前丢异常事务应该会被丢弃，不影响后续提交和其他操作（猜测还不知怎么验证，分析里面没有commit和rollback且不影响再次commit，猜测应该被丢弃了）;

2021/12/27 接上文；验证未提交前丢异常事务，再次commit或者rollback是有问题的，虽mysql空白提交commit并没有报错,但是Spring中会异常 llegalTransactionStateException: Transaction is already completed，足见未提交前丢异常事务会自动中断

分析定位后

TransactionExecution 在 AbstractPlatformTransactionManager 设置了是否完成的状isCompleted, 详见AbstractPlatformTransactionManager.processCommit(),当事务为完成状态是再提交会抛异常llegalTransactionStateException