大多数mysql库的出现的查询慢，锁等待，节点挂掉，其实都是sql语句写的不好，表设计不完善造成的，其实注意其中的细节就能避免掉这些问题。

比如：

MySQL行锁的细节，什么情况下会使用表锁等

InnoDB不同于MyISAM最大的两个特点就是：一是支持事务，二是支持行锁；毋庸置疑，因为这两个特性大部分都采用InnoDB引擎，其中的支持行锁就是InnoDB适合多并发优势所在，但是行锁的一些细节没有深入理解过的话，可能会造成一定的误解，造成“看似命中索引，走行锁，结果却是表锁，最终导致锁等待情况”。

一、InnoDB行锁的实现方式

通过给索引上的索引项加锁来实现的，也就意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。这一点在实际应用中特别需要注意，不然的话可能导致大量的锁冲突，从而影响引发并发性能。

实验一：对没有索引的加锁，导致表锁

　　　1）准备工作：建tab_no_index表，表中无任何索引，并插入数据

　　　　

 

　　　2）Session_1: 我们给id=1的行加上排它锁（for update），由于id没有索引，实际上是表级锁；

　　　　

　　　3）Session_2：我们给id=2的行加上排它锁（for update），由于id没有索引，所以去申请表级锁，但是却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。

 　　　

　　　　备注：MySQL中的for update 仅适用于InnoDB（因为是只有此引擎才有行级锁），并且必须开启事务，在begin与commit之间才生效。for update是在数据库中上锁用的，可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以对这行读取但是不能写入或更新，只能等该事务Rollback, Commit, Lost connection…

　　　实验二：对有索引的键值加锁，会对所有涉及到的数据行加锁

　　　　1）准备工作：对id建索引如下

 　　　　

 　　　2）Session_1：此时id是有索引的，我们对id=1 and name=1的一行加排它锁；

　　　　

　　　　3）Session_2：访问不同于Session_1的id=1, name=5行，但是索引键值是一样的，照样等待锁，锁冲突了。

　　　　

　　　实验三：多个索引时，不同的事务可以使用不同的索引锁定不同的行，不论什么索引，InnoDB都会使用行锁对数据加锁（对有索引的行数据）。

　　　　1）准备工作：对tab_no_index追加name索引：alter table tab_no_index add index name(name);

　　　　

　　　　2）Session_1：开启事务对id=1的行加排它锁，即对name=1与name=5两个数据加锁。

　　　　

　　　　3）Session_2：开启事务对name=2行加锁，因为该数据没有被加锁，索引可以获得锁

　　　　

　　　　4）Session_3：再对name=5的数据进行加锁，由于该数据记录已被Session_1锁定，所以等待获得锁。

　　　

　　注意事项：即便使用了索引，但还是要看MySQL具体对SQL的执行计划，不一定能使用到

　　　　如我们对实验三对name='2'进行加锁，误以为name是int类型，本来name是有索引的，但是最后结果导致表锁：

　　　　

　　

　　具体请看MySQL的索引情况。具体可以参考之前我的一篇博文MySQL优化（1）--------常用的优化步骤、MySQL优化(2)--------常用优化

 

二、间隙锁（Next-Key锁）

　　当用范围条件而不是相等条件检索数据，并请求共享或者排它锁的时候，InnoDB会给符合条件的已有数据记录的索引项加锁；对于不在范围内的但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个间隙加锁，这就是所谓的间隙锁。

　　如：select * from where id>100 for update 对id大于100的数据对加锁，但是此时数据中id只有1,2….100,101，不仅对存在的101的记录加锁，还会对大于101不存在的数据的间隙加锁。

　　 此外，对使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁，如下：

　　Session_1：对不存在的id=6的记录加锁

　　

 

　　Session_2：插入id=6的记录，也会出现锁等待

　　

 

三、什么时候使用表锁？

　　对于InnoDB表，在绝大部分情况下都应该使用行锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由，但在个别情况下也使用表级锁；

　　1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突；

　　2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。

使用表锁需要注意几点：

　　1）使用LOCK TABLES虽然可以给InnoDB加表级锁，表级锁不是InnoDB存储引擎层管理的，而是由其上一层MySQL Server负责的

　　2）在用LOCK TABLES对InnoDB表加锁时需要注意，要将Autocommit设置为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK_TABLES隐含提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加表级锁。

　　　SET AUTOCOMMIT=0;

　　　LOCK TABLES table1 WRITE, table2 READ,...；

　　　[do something....]

　　　COMMIT;

　　　UNLOCK TABLES;