mysql innodb引擎什么时候表锁什么时候行锁？

InnoDB基于索引的行锁

• InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁
• 在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

innodb行锁和表锁的情况

• 由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)：
  • (明确指定主键，并且有此笔资料，row lock)
    SELECT * FROM products WHERE id='3' FOR UPDATE;
    SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;
  • (明确指定主键，若查无此笔资料，无lock)
    SELECT * FROM products WHERE id='-1' FOR UPDATE;
  • (无主键，table lock)
    SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
  • (主键不明确，table lock)
    SELECT * FROM products WHERE id<>'3' FOR UPDATE;
  • (主键不明确，table lock)
    SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

有时有索引也会导致表锁的情况

• 导致这个的原因第一是sql语句写法问题，没有合理构建和使用索引。
• 第二个原因是mysql的优化器，有时优化器发现：即使使用了索引，还是要做全表扫描，故而放弃了索引，也就没有使用行锁，却使用了表锁

索引类型对锁类型的影响

• 主键：众所周知，自带最高效的索引属性
• 唯一索引：属性值重复率为0，可以作为业务主键
• 普通索引：属性值重复率大于0，不能作为唯一指定条件
• 注意：对于普通索引，当“重复率”低时，甚至接近主键或者唯一索引的效果时，依然是行锁；但是如果“重复率”高时，Mysql不会把这个普通索引当做索引，即会造成一个没有索引的SQL，从而形成表锁。

锁等待

• 锁等待理解
  • 即当一个事务要操作一个资源时，这个资源已经被其它事务先锁定使用，那么后者事务只能等待上一个事务执行完毕释放资源使用权后才能继续操作，而这个等待的过程就是锁等待；
  • 如果等待时间过长，超过配置项中的设定的时间，则会报错（锁等待错误）
• 原因
  • 都使用表锁
  • 一个使用行锁，一个使用表锁
  • 都是用行锁

参考：