InnoDB在不同隔离级别下的一致性读及锁的差异
锁和多版本数据是InnoDB实现一致性读和ISO/ANSI SQL92隔离级别的手段，因此，在不同的隔离级别下，InnoDB处理SQL时采用的一致性读策略和需要的锁是不同的。同时，数据恢复和复制机制的特 点，也对一些SQL的一致性读策略和锁策略有很大影响。将这些特性归纳成如表20-16所示的内容，以便读者查阅。表20-16 InnoDB存储引擎中不同SQL在不同隔离级别下锁比较隔离级别 一致性读和锁SQLRead UncommitedRead CommitedRepeatable ReadSerializable
SQL条件
select相等None locksConsisten read/None lockConsisten read/None lockShare locks
范围None locksConsisten read/None lockConsisten read/None lockShare Next-Key
update相等exclusive locksexclusive locksexclusive locksExclusive locks
范围exclusive next-keyexclusive next-keyexclusive next-keyexclusive next-key
InsertN/Aexclusive locksexclusive locksexclusive locksexclusive locks
replace无键冲突exclusive locksexclusive locksexclusive locksexclusive locks
键冲突exclusive next-keyexclusive next-keyexclusive next-keyexclusive next-key
delete相等exclusive locksexclusive locksexclusive locksexclusive locks
范围exclusive next-keyexclusive next-keyexclusive next-keyexclusive next-key
Select ... from ... Lock in share mode相等Share locksShare locksShare locksShare locks
范围Share locksShare locksShare Next-KeyShare Next-Key
Select * from ... For update相等exclusive locksexclusive locksexclusive locksexclusive locks
范围exclusive locksShare locksexclusive next-keyexclusive next-key
Insert into ... Select ...（指源表锁）innodb_locks_unsafe_for_binlog=offShare Next-KeyShare Next-KeyShare Next-KeyShare Next-Key
innodb_locks_unsafe_for_binlog=onNone locksConsisten read/None lockConsisten read/None lockShare Next-Key
create table ... Select ...（指源表锁）innodb_locks_unsafe_for_binlog=offShare Next-KeyShare Next-KeyShare Next-KeyShare Next-Key
innodb_locks_unsafe_for_binlog=onNone locksConsisten read/None lockConsisten read/None lockShare Next-Key
从表20-16可以看出：对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范 围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大。因此，我们在应用中，应该尽量使用较低的隔离级别，以减少锁争用 的机率。实际上，通过优化事务逻辑，大部分应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求。
页:
[1]