Mysql锁专题：InnoDB锁概述

一 概述

InnoDB与MyISAM有两处不同：

1）InnoDB支持事务；

2）默认采用行级锁（也可以支持表级锁）

对于更新操作（UPDATE、INSERT、DELETE），InnoDB会自动给涉及到的数据集加排他锁（X）；对于普通的SELECT语句，InnoDB不加任何锁（所以即使有一个线程的写操作在占用锁，不影响其他线程的读，但是如果某个线程试图加共享锁则不行）。

InnoDB的行锁模式及加锁方法

InnoDB实现了以下两种类型的行锁。

共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；

排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，InnoDB还有两张内部使用的意向锁，都是表锁：

意向共享锁(IS)：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前先必须取得该表的意向共享锁；

意向排他锁(IX)：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的意向排他锁。

上述几种锁的兼容性如下：

表20-6 InnoDB行锁模式兼容性列表

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。

对于更新操作（UPDATE、INSERT、DELETE），InnoDB会自动给涉及到的数据集加排他锁（X）；对于普通的SELECT语句，InnoDB不加任何锁（所以即使有一个线程的写操作在占用锁，不影响其他线程的读，但是如果某个线程试图加共享锁则不行）。

显式的给记录集加共享锁：

共享锁： SELECT * FROM tableName WHERE …. LOCK IN SHARE MODE

排他锁： SELECT * FROM tableName WHERE …. FOR UPDATE

二、 共享锁中执行update操作容易导致死锁

注意：**用共享锁然后执行了update操作，则有可能和别的线程的update操作发生锁冲突，从而死锁。死锁后Mysql会自动关闭一个线程的事务操作，让锁被一个线程使用。**如下所示：

1）线程A和线程B对同一行记录使用了共享锁，两个线程读都没有问题（读不需要加锁，不管当前记录加了共享锁还是排他锁，都不影响单独的读操作）；

2）线程A进行更新操作，因为更新操作需要加独占锁，而线程B还对当前记录保留了共享锁，故线程A无法获得当前线程的独占锁，要等待线程B释放共享锁；

3）线程B也进行了更新操作，它也要对当前记录加独占锁。那么显然它也无法获得到该记录的独占锁，两个线程都会等待下去，也就是死锁。

4）此时Mysql会自动根据一定规则把锁交给某个线程，另一个线程失去锁重新启动事务。

另外，注意，默认情况下单行执行后就会自动提交事务，此时锁也就被自动释放了。需要关闭事务的自动提交。

set autocommit = 0;

对于需要更新的操作，应当直接使用排他锁。这种情况下，因为线程A已经占有了排他锁，线程B无法获得共享锁和排他锁，只能等待。但是注意，InnoDB的读操作不需要加锁，所以可以照常的读。

当使用SELECT…FOR UPDATE加锁后再更新记录，出现如表20-8所示的情况。

表20-8 InnoDB存储引擎的排他锁例子

三、 InnodDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的。这一点Mysql和Oracle不同，Oracle是通过直接在数据块中对相应数据行加锁来实现的。

InnoDB的这种特性意味着：只有通过索引条件检索数据，InnoDB才使用行级锁；否则InnoDB将使用表锁。

1）非索引字段加锁变成表锁

表20-9 InnoDB存储引擎的表在不使用索引时使用表锁例子

注意，对于表没有加索引，线程A仅要求获取id=1的记录的独占锁，但是因为没有加索引，所以该语句锁住了整个表，使用了表锁。

当我们对id行添加索引

alter table tab_with_index add index id(id);

则会有下面的例子：

2）相同索引键导致阻塞

由于Mysql的行锁是针对索引加的锁，而不是针对记录加的，所以即使是访问不同行，但是如果使用了相同的索引键，依然会冲突：

mysql> select * from tab_with_index where id = 1;

±-----±-----+

| id | name |

±-----±-----+

| 1 | 1 |

| 1 | 4 |

±-----±-----+

例如对于上表，如果对id加了索引，但是有两个记录的id相同，也就是索引相同。此时两个线程分别试图获取两个记录的独占锁依然会导致阻塞，因为mysql的行锁是加在索引上的。

3）不同索引键指向同一行记录也会导致阻塞

mysql> alter table tab_with_index add index name(name);

alter table tab_with_index add index id(id);

假设我们分别对id和name增加索引，那么不管是什么索引，InnoDB都会使用行锁来锁定不同的行。

如果是不同的索引，但是指向了同一条记录，那么依然会导致阻塞。

我的理解是不同索引最后指向了同一条主键id，锁住了注解id，故依然会阻塞，应该不是锁住记录。

4）间隙锁

当我们使用范围条件而不是相等条件来检索数据，并请求共享或排他锁时，InnoDB会给所有符合条件的已有数据记录的索引加锁；对于键值在条件范围内但是并不存在的记录，叫做间隙gap，InnoDB也会对这些间隙加锁。这种锁机制就是间隙锁。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from emp where empid > 100 for update;

这是一个范围条件的检索，InnoDB不仅会对empid为101的记录加锁，对于大于101的不存在间隙也会加锁。

**Mysql使用间隙锁的目的是防止幻读（应该只是一部分满足，不能完全回避），以满足相关隔离级别的要求。**比如对于上面的情况，如果不加锁，那么其他事务插入了empid为102的记录，则会导致本事务内再次执行上述语句时得到empid为102的记录，也就导致了幻读。另一方面，也是为了满足其回复和复制的需要。

因此，在使用范围条件检索并锁定记录时，InnoDB的这种间隙加锁机制会阻塞符合条件范围内键值的并发插入，从而导致严重的锁等待。因此，对于并发插入较多的应用，我们要尽量优化业务逻辑，尽量用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

5）关于恢复和复制的需要，对InnoDB锁机制的影响

Mysql通过BINLog记录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySql数据库的回复和主从复制。Mysql的恢复记录（复制实际就是在Slave Mysql不断的做基于BINLOG的恢复）有以下特点：

一是MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。

二是MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。

**根据上述的特点，Mysql的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。****这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因。**比如在用范围条件更新记录时，无论是Read Commited还是Repeatable Read隔离级别，InnoDB都要使用间隙锁，这并不是隔离级别的要求，而是由于Mysql恢复和复制的要求。

MySQL SQL 任务调度

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。