MySQL事务的隔离级别和并发问题MySQL版本：8.0.271.并发执行事务面临的问题，`name`varchar(10)NOTNULLCOMMENT'name',`age`tinyint(3)UNSIGNEDNOTNULLCOMMENT'age',PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCHARACTERSET=utf8mb4;INSERTINTO`user`VALUES(10,'Xiaoming',16);INSERTINTO`user`VALUES(20,'小红',15);INSERTINTO`user`VALUES(30,'小丽',18);INSERTINTO`user`VALUES(40,'小梅',21);INSERTINTO`user`VALUES(50,'小亮',20);SELECT*FROMuser;+----+--------+-----+|编号|姓名|年龄|+----+--------+-----+|10|小明|16||20|小红|15||30|小丽|18||40|小梅|21||50|小亮|20|+----+-----+-----+设置事务隔离级别为Readuncommitted，方便演示SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED；1、脏读（DirtyRead）如果事务A读取到未提交的事务B修改的数据，则说明发生了脏读。交易A交易Bbegin;begin;SELECT*FROMuserWHEREid=10;--UPDATEuserSETage=11WHEREid=10;SELECT*FROMuserWHEREid=10;读取事务B（age=11）未提交的修改，出现脏读--rollback；SELECT*FROMuserWHEREid=10;读取的数据已改回(age=10)-commit;-2。不可重复读（Non-RepeatableRead）如果事务A读取了已提交事务B修改的数据（事务B修改了未提交事务A读取的数据），则说明发生了不可重复读现象。事务A事务Bbegin;begin;SELECT*FROMuserWHEREid=10;--UPDATEuserSETage=12WHEREid=10;-commit;SELECT*FROMuserWHEREid=10;读取事务B(age=12)提交的修改，发生不可重复的read-commit；3.幻读（PhantomRead）事务A先按照一定的范围条件查询一些记录，事务B写入一些满足条件的新记录，当事务A以相同的条件再次查询时，查询到一条新的记录，这意味着发生了幻读。交易A交易Bbegin;begin;SELECT*FROMuserWHEREid>30;--INSERTINTOuserVALUES(60,'Xiaojing',10);-commit;SELECT*FROMuserWHEREid>30;读到事务B插入的记录有幻读read-commit；二、SQL标准中的四种隔离级别1.READUNCOMMITTED（未提交读）在READUNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也是可见的。也就是说，脏读、不可重复读、幻读都会在这个隔离级别发生。2.READCOMMITTED（提交读）READCOMMITTED解决了脏读问题，它满足事务隔离的简单定义：从一个事务开始到提交，它所做的任何修改对其他事务都是不可见的。3.REPEATABLEREAD（可重复读）REPEATABLEREAD解决了脏读和不可重复读的问题。该级别保证在同一个事务中多次读取同一条记录的结果是一致的。但理论上，可重复读隔离级别仍然无法解决幻读（PhantomRead）问题。InnoDB存储引擎通过MVCC（多版本并发控制）和Next-Key（邻键锁）很大程度上避免了幻读问题。可重复读是MySQL默认的事务隔离级别。4.SERIALIZABLE（序列化）SERIALIZABLE通过强制事务串行执行来避免脏读、不可重复读和幻读等问题。SERIALIZABLE会锁住读取的每一行数据，因此可能会导致很多超时和锁争用问题。3.四种隔离级别的比较脏读可能性不可重复读可能性幻读可能性锁定读READUNCOMMITTED√√√×READCOMMITTED×√√×REPEATABLEREAD××√×SERIALIZABLE×××√4.四个隔离级别事务并行示例查看事务隔离级别SHOWVARIABLESLIKE'transaction_isolation';+--------------------+------------------+|变量名|值|+--------------------+------------------+|事务隔离|REPEATABLE-READ|+------------------------+----------------+设置隔离级别事务SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVEL;level有4个可选值：READUNCOMMITTEDREADCOMMITTEDREPEATABLEREADSERIALIZABLE修改全局隔离级别需要退出会话并重新连接MySQL才能生效。初始数据CREATETABLE`user`(`id`int(10)UNSIGNEDNOTNULLAUTO_INCREMENT,`name`varchar(10)NOTNULLCOMMENT'name',`age`tinyint(3)UNSIGNEDNOTNULLCOMMENT'age',PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCHARACTERSET=utf8mb4;INSERTINTO`user`VALUES(10,'Xiaoming',16);INSERTINTO`user`VALUES(20,'Xiaohong',15);INSERTINTO`user`VALUES(30,'小丽',18);INSERTINTO`user`VALUES(40,'小梅',21);INSERTINTO`user`VALUES(50,'小亮',20);1.READUNCOMMITTED(读未提交)SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;SHOWVARIABLESLIKE'transaction_isolation';+------------------------+----------------+|变量名|值|+--------------------+----------------+|事务隔离|READ-UNCOMMITTED|+--------------------+---------------+脏读事务事务Bbegin;begin;SELECT*FROMuserWHEREid=10;--UPDATEuserSETage=11WHEREid=10;SELECT*FROMuserWHEREid=10;读取事务B（age=11）未提交的修改，脏读--rollback；从用户中选择*其中id=10；读取的数据变回了(age=16)-commit;-2。READCOMMITTED(readcommitted)SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;SHOWVARIABLESLIKE'transaction_isolation';+-----------------------+------------------+|变量名|值|+----------------------+----------------+|事务隔离|已提交读|+----------------------+----------------+不可重复读取事务A事务Bbegin;begin;SELECT*FROMuserWHEREid=10;--UPDATEuserSETage=12WHEREid=10;SELECT*FROMuserWHEREid=10;事务B(age=16)未提交的修改无法读取，也没有脏读--commit;SELECT*FROMuserWHEREid=10;读取事务B（age=12）提交的修改，发生不可重复读-commit；3.REPEATABLEREAD（可重复读）SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;SHOWVARIABLESLIKE'transaction_isolation';+------------------------+----------------+|变量名|值|+--------------------+----------------+|事务隔离|REPEATABLE-READ|+--------------------+----------------+可以重复读交易A交易Bbegin;begin;SELECT*FROMuserWHEREid=10;--UPDATEuserSETage=13WHEREid=10;SELECT*FROMuserWHEREid=10;无法读取事务B(age=12)未提交的修改，没有脏读--commit；SELECT*FROMuserWHEREid=10;事务B（age=12）提交的修改不可读，不存在不可重复读提交；幻读是由于MySQL的InnoDB存储引擎通过MVCC（Multi-VersionConcurrencyControl）和Next-Key（ProximityKeyLock），很大程度上避免了幻读问题，所以大部分幻读现象无法论证，但是InnoDB存储引擎不能完全禁止幻读TransactionATransactionBbegin;begin;SELECT*FROMuserWHEREid>30;--INSERTINTOuserVALUES(60,'Xiaojing',10);-commit;SELECT*FROMuserWHEREid>30;无法读取事务B插入的记录，没有幻读-UPDATEuserSETage=11WHEREid=60;-SELECT*FROMuserWHEREid>30;事务B插入的记录被读取，出现幻读read-commit；4.SERIALIZABLE（序列化）SETSESSIONTRANSACTIONISOLATIONLEVELSERIALIZABLE；显示像“transaction_isolation”这样的变量；+--------------------+------------------+|变量名|值|+--------------------+-----------------+|事务隔离|SERIALIZABLE|+--------------------+---------------+SERIALIZABLE避免了脏读的问题，非-可重复读取和幻读，强制事务串行执行。当事务A读取一条记录时，该记录会被读锁（共享锁），其他事务可以查询这条记录，但不能修改事务A事务Bbegin；SELECT*FROMuserWHEREid=10;--SELECT*FROMuserWHEREid=10;执行成功-UPDATEuserSETage=14WHEREid=10;阻塞当事务A修改一条记录时，这条记录会被加写锁（排他锁），其他事务无法查询和修改这条记录TransactionA事务Bbegin;-UPDATEuserSETage=14WHEREid=10;--SELECT*FROMuserWHEREid=10;blockcommit;--当事务A读取范围记录时执行完成，该范围会被加读锁（共享锁），其他事务不能添加或修改范围内的记录，也不能修改范围外的记录到记录满足范围条件。交易A交易Bbegin;--SELECT*FROMuserWHEREid>30;--INSERTINTOuserVALUES(70,'Xiaohua',10);blockcommit;--执行完成事务A事务Bbegin;SELECT*FROMuserWHEREid>30;--UPDATEuserSETid=31WHEREid=10;blockcommit;--执行完成
