MySQL社区

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 10848|回复: 2

07 InnoDB在不同隔离级别下的一致性读及锁的差异

[复制链接]
发表于 2010-9-14 17:29:16 | 显示全部楼层 |阅读模式

前面讲过,锁和多版本数据是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

范围

None locks

Consisten read/None lock
Consisten read/None lock
Share Next-Key
update

相等

exclusive locks
exclusive locks
exclusive locks
Exclusive locks
范围

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
键冲突

exclusive next-key
exclusive next-key
exclusive next-key
exclusive next-key
delete

相等

exclusive locks
exclusive locks
exclusive locks
exclusive locks
范围

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
范围
Share locks
Share locks
Share Next-Key
Share Next-Key
Select * from ... For update

相等
exclusive locks
exclusive locks
exclusive locks
exclusive locks
范围
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
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
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动态改变隔离级别的方式满足需求。

什么时候使用表锁
对于InnoDB表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中,也可以考虑使用表级锁。
  • 第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。
  • 第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。

当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表了。

在InnoDB下,使用表锁要注意以下两点。
(1)使用LOCK TABLES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层──MySQL Server负责的,仅当autocommit=0、innodb_table_locks=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server也才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁;否则,InnoDB将无法自动检测并处理这种死锁。有关死锁,下一小节还会继续讨论。
(2)在用LOCK TABLES对InnoDB表加锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁。正确的方式见如下语句:

例如,如果需要写表t1并从表t读,可以按如下做:

  1. SET AUTOCOMMIT=0;
  2. LOCK TABLES t1 WRITE, t2 READ, ...;
  3. [do something with tables t1 and t2 here];
  4. COMMIT;
  5. UNLOCK TABLES;
复制代码

本帖被以下淘专辑推荐:

 楼主| 发表于 2012-4-19 15:08:06 | 显示全部楼层
广播之
发表于 2012-7-29 17:54:44 | 显示全部楼层
感谢您

QQ|申请友链|小黑屋|Archiver|手机版|MySQL社区 ( 京ICP备07012489号   
联系人:周生; 联系电话:13911732319

GMT+8, 2024-2-23 19:39 , Processed in 0.074048 second(s), 28 queries , Gzip On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表