MySQL锁的常见误区

今天给大家分享的内容是MySQL锁的常见误区。MySQL的锁包括两种lock和latch。latch的面向对象是线程，主要用来管理数据库临界资源的并发访问，锁的时间非常短，也不会产生死锁。不需要人工干预，所以这里我们不再做介绍。而lock则是面向事务的，操作的对象是数据库的表、页及行，用来管理并发线程对共享资源的访问，会产生死锁。因为我们现在数据库使用的是innodb存储引擎。所以今天主要给大家介绍的是innodb的lock的常见几个误区。

    在介绍之前，我们需要再了解lock的几个概念：

行锁：innodb实现了多粒度锁，作用对象为表则为表锁，作用对象为行（Record）则为行锁。其中行锁包括共享行锁和排他行锁。

共享行锁（S）：允许事务读取一行数据。

排他行锁（X）：允许事务删除或更新一行数据。

意向锁：数据库需要对细粒度的对象上锁，需要首先给粗粒度的对象上锁。在粗粒度对象上上的锁成为意向锁。innodb的意向锁包括共享意向表锁和排他意向表锁。

共享意向表锁（IS）：S锁对应IS锁

排他意向表锁（IX）：X锁对应IS锁

锁兼容：兼容是指在同一对象上允许同种或不同种锁同时存在。

下面是各锁之间的兼容情况：

好了，有了上面的知识积累，下面给大家解释这几个常见的误区：

误区一：select col1，col2 from table1 where col1=‘xxx‘ 或select count(*) from  table1;会锁表；

事实上这样的select语句是不会对访问的资源加锁的。因为这样的查询会使用一致性非锁定读，它访问的是资源的镜像（此处用到的技术是mvcc即multi version concurrency control），所以不会堵塞其它事务也不会被其它事务堵塞（感兴趣的同学可以通过实验验证，不清楚实验方法的话可以私信我）。当然这只是一般的select语句。如果是如下这种格式的语句仍然会对访问的资源加锁：

select col1,col2 from table1 for update;(加X锁)

select col1,col2 from table1 lock in share mode;(加S锁)

    关于什么是共享锁（S）什么是互斥锁（X），上面已经做了介绍。我们需要注意的是S锁和S锁是兼容的，S锁和X锁、X锁和X锁是不兼容的。这里说的兼容指的是不同事务对同一行（row）资源的访问的兼容性。显式加锁的select请求，会堵塞其它资源对该表的意向锁请求，从而堵塞其它请求。所以一般情况下，如无特殊需求，是不允许应用对select语句显示加锁的。

误区二：update table1 set col1=‘xxx‘ where col2=‘xxx‘不走索引对数据库的性能没有多大影响；

当使用update语句时，首先该语句会对它所访问的表加意向排它锁（IX），如果update语句走了索引，那么它会使用行锁（X） ，只锁定访问的记录及间隙（想了解更多可以百度MySQL锁的算法）。而如果它没有走索引，就会进行全表扫，这时会给整个表加上排他锁（X）。这是这句SQL就会堵塞所有会对该表加意向锁（意向读及意向写）请求，从而堵塞其它请求。

误区三：自增长键会在整个事务过程中，锁住自增长值；

现在我们数据库中的表的主键都是设为自增长的。很多同事认为，这样会不会非常影响数据库的插入效率。事实上使用自增长值确实会影响数据入库的效率，当时mysql 5.1.22版本后，对数据库的自增长设计做了很大的优化，性能已经得到了很大的提升。在5.1.22 之前的版本，使用的是auto_inc locking来生成自增长主键。它并不是在整个事务过程中锁住自增长资源而是在要生产主键的SQL执行完后就释放资源。这就是我们为什么会碰到，事务回滚后，自增长值确仍旧增大的原因。5.1.22及之后，使用了轻量级互斥量（mutex）来实现自增长，并通过inodb_atuoinc_lock_mode来控制自增长的模式。数据库默认改参数值为1，一般情况下都是用mutex来控制自增长，只有当bulk inserts的时候才会使用auto_inc locking模式。

误区四：使用外键加强约束，不会影响性能；

很多同事在设计表结构的时候喜欢使用外键，这里会给大家说明，为什么不建议大家使用外键。

如果使用外键，那么当子表需要更新或插入数据的时候会去检索父表。问题就出现在，检索父表的使用并不是使用的是一致性非锁定读，而是使用的一致性锁定读。

内部检索会是下面的格式：select * from parent where ... lock in share mode;这样的检索就会堵塞同时访问该父表的其它事务的请求，从而影响性能。

原文：http://www.cnblogs.com/janehoo/p/5603983.html