InnoDB在不同隔离级别下的一致性读及锁的差异 - MariaDB社区

锁和多版本数据是InnoDB实现一致性读和ISO/ANSI SQL92隔离级别的手段，因此，在不同的隔离级别下，InnoDB处理SQL时采用的一致性读策略和需要的锁是不同的。同时，数据恢复和复制机制的特点，也对一些SQL的一致性读策略和锁策略有很大影响。将这些特性归纳成如表20-16所示的内容，以便读者查阅。

表20-16 InnoDB存储引擎中不同SQL在不同隔离级别下锁比较

隔离级别一致性读和锁 SQL		Read Uncommited	Read Commited	Repeatable Read	Serializable
SQL	条件
select	相等	None locks	Consisten read/None lock	Consisten read/None lock	Share locks
select	范围	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
update	相等	exclusive locks	exclusive locks	exclusive locks	Exclusive locks
update	范围	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
Insert	N/A	exclusive locks	exclusive locks	exclusive locks	exclusive locks
replace	无键冲突	exclusive locks	exclusive locks	exclusive locks	exclusive locks
replace	键冲突	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
delete	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
delete	范围	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
Select ... from ... Lock in share mode	相等	Share locks	Share locks	Share locks	Share locks
Select ... from ... Lock in share mode	范围	Share locks	Share locks	Share Next-Key	Share Next-Key
Select * from ... For update	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
Select * from ... For update	范围	exclusive locks	Share locks	exclusive next-key	exclusive next-key
Insert into ... Select ... （指源表锁）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
Insert into ... Select ... （指源表锁）	innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
create table ... Select ... （指源表锁）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
create table ... Select ... （指源表锁）	innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key

从表20-16可以看出：对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大。因此，我们在应用中，应该尽量使用较低的隔离级别，以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求。