MySQL InnoDB 锁表与锁行

共计 1804 个字符，预计需要花费 5 分钟才能阅读完成。

由于 InnoDB 预设是 Row-Level Lock，所以只有「明确」的指定主键，MySQL 才会执行 Row lock (只锁住被选取的资料例)，否则 MySQL 将会执行 Table Lock (将整个资料表单给锁住)。

举个例子: 假设有个表单 products，里面有 id 跟 name 二个栏位，id 是主键。

例 1: (明确指定主键，并且有此笔资料，row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例 2: (明确指定主键，若查无此笔资料，无 lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例 3: (无主键，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例 4: (主键不明确，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例 5: (主键不明确，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注：

1. FOR UPDATE 仅适用于 InnoDB，且必须在交易区块 (BEGIN/COMMIT) 中才能生效。2. 要测试锁定的状况，可以利用 MySQL 的 Command Mode，开二个视窗来做测试。

在 MySql 5.0 中测试确实是这样的

另外：MyAsim 只支持表级锁，InnerDB 支持行级锁
添加了 (行级锁 / 表级锁) 锁的数据不能被其它事务再锁定，也不被其它事务修改（修改、删除）
是表级锁时，不管是否查询到记录，都会锁定表

此外，如果 A 与 B 都对表 id 进行查询但查询不到记录，则 A 与 B 在查询上不会进行 row 锁，但 A 与 B 都会获取排它锁，此时 A 再插入一条记录的话则会因为 B 已经有锁而处于等待中，此时 B 再插入一条同样的数据则会抛出 Deadlock found when trying to get lock; try restarting transaction 然后释放锁，此时 A 就获得了锁而插入成功

Innodb 中的行锁与表锁

前面提到过，在 Innodb 引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

在实际应用中，要特别注意 InnoDB 行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

行级锁与死锁

MyISAM 中是不会产生死锁的，因为 MyISAM 总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在 InnoDB 中，锁是逐步获得的，就造成了死锁的可能。

在 MySQL 中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条 sql 语句操作了主键索引，MySQL 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。在 UPDATE、DELETE 操作时，MySQL 不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的 next-key locking。

当两个事务同时执行，一个锁住了逐渐索引在等待其他相关索引，一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

发生死锁后，InnoDB 一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

有多种方法可以避免死锁，这里只介绍常见的三种，具体如下

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-02/141110.htm