mysql 表级别的锁和行级别的_MySQL 表锁和行锁机制

案例分析

目前，MySQL常用的存储引擎是InnoDB，相对于MyISAM而言。InnoDB更适合高并发场景，同时也支持事务处理。我们通过下面这个案例(坑)，来了解行锁和表锁。

业务：因为订单重复导入，需要用脚本将订单状态为"待客服确认"且平台是"xxx"的数据批量修改为"已关闭"。

说明：避免直接修改订单表造成数据异常。这里用innodb_lock 表演示InnoDB的行锁。表中有三个字段：id，k(key值)，v(value值)。表在github上：https://github.com/ITDragonBlog/daydayup/tree/master/MySQL/

步骤：

第一步：连接数据库，这里为了方便区分命名为Transaction-A，设置autocommit为零，表示需手动提交事务。

第二步：Transaction-A，执行update修改id为1的命令。

第三步：新增一个连接，命名为Transaction-B，能正常修改id为2的数据。再执行修改id为1的数据命令时，却发现该命令一直处理阻塞等待中。

第四步：Transaction-A，执行commit命令。Transaction-B，修改id为1的命令自动执行，等待37.51秒。

总结：多个事务操作同一行数据时，后来的事务处于阻塞等待状态。这样可以避免了脏读等数据一致性的问题。后来的事务可以操作其他行数据，解决了表锁高并发性能低的问题。

# Transaction-A

mysql> set autocommit = 0;

mysql> update innodb_lock set v='1001' where id=1;

mysql> commit;

# Transaction-B

mysql> update innodb_lock set v='2001' where id=2;

Query OK, 1 row affected (0.37 sec)

mysql> update innodb_lock set v='1002' where id=1;

Query OK, 1 row affected (37.51 sec)

有了上面的模拟操作，结果和理论又惊奇的一致，似乎可以放心大胆的实战。。。。。。但现实真的很残酷。

现实：当执行批量修改数据脚本的时候，行锁升级为表锁。其他对订单的操作都处于等待中，，，

原因：InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁！而模拟操作正是通过id去作为检索条件，而id又是MySQL自动创建的唯一索引，所以才忽略了行锁变表锁的情况。

步骤：

第一步：还原问题，Transaction-A，通过k=1更新v。Transaction-B，通过k=2更新v，命令处于阻塞等待状态。

第二步：处理问题，给需要作为查询条件的字段添加索引。用完后可以删掉。

总结：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。索引失效的原因在上一章节中已经介绍：http://www.cnblogs.com/itdragon/p/8146439.html

Transaction-A

mysql> update innodb_lock set v='1002' where k=1;

mysql> commit;

mysql> create index idx_k on innodb_lock(k);

Transaction-B

mysql> update innodb_lock set v='2002' where k=2;

Query OK, 1 row affected (19.82 sec)

从上面的案例看出，行锁变表锁似乎是一个坑，可MySQL没有这么无聊给你挖坑。这是因为MySQL有自己的执行计划。

当你需要更新一张较大表的大部分甚至全表的数据时。而你又傻乎乎地用索引作为检索条件。一不小心开启了行锁(没毛病啊！保证数据的一致性！)。可MySQL却认为大量对一张表使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突问题，性能严重下降。所以MySQL会将行锁升级为表锁，即实际上并没有使用索引。

我们仔细想想也能理解，既然整张表的大部分数据都要更新数据，一行一行地加锁效率则更低。其实我们可以通过explain命令查看MySQL的执行计划，你会发现key为null。表明MySQL实际上并没有使用索引，行锁升级为表锁也和上面的结论一致。

本章重点介绍InnoDB的行锁及其相关的事务知识。如果想了解MySQL的执行计划，请看上一章节。

行锁

行锁的劣势：开销大；加锁慢；会出现死锁

行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，Inn