Mysql锁

mysql 锁机制

标签（空格分隔）： mysql

参考文档

概念

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同的数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，但是组织其他事务获得相同数据集的共享锁和排他锁。
对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；对于一般的Select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。

共享锁

select * from table_name where .....lock in share mode

Note left of 事务1: select * from table_1 where id=1 lock in share mode;
事务1-->事务2: 
Note right of 事务2: select * from table_1 where id=1 lock in share mode;
事务2-->事务1: 
Note left of 事务1: update table_1 set age=10 where id=1;
Note left of 事务1: 事务1更新时发现此行锁被其他事务享用，等待
事务1-->事务2: 
Note right of 事务2: update table_1 set age=12 where id=1;
Note right of 事务2: 事务2更新时发现此行锁被其他事务享用，也等待，导致死锁

排他锁

select * from table_name where .....for update

Note left of 事务1: select * from table_1 where id=1 for update;
事务1-->事务2: 
Note right of 事务2: select * from table_1 where id=1 for update;
Note right of 事务2: 等待...
事务2-->事务1: 
Note left of 事务1: update table_1 set age=10 where id=1;
Note left of 事务1: 更新完后释放锁
事务1-->事务2: 
Note right of 事务2: 获得锁后，得到其他事务提交的记录

行锁的三种形式

Record lock:锁定一条记录。
Gap lock
Next-key lock

innoDB锁问题

事务（Transaction）及其ACID属性

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务带来的问题

更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题

脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。

不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

事务隔离级别

| 隔离级别 | 脏读| 不可重复读 | 幻读 |
| -| - |
| 未提交读（Read uncommitted） | √ | √ | √ |
| 已提交度（Read committed） | x | √ | √ |
| 可重复读（Repeatable read） |x | x | √ |
| 可序列化（Serializable） | x | x | x |

mysql行锁的特性

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.
实例：
id是主键
| id| name|
| -| - |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
事务1update第一条id=1的数据，事务不提交；事务2接着update第二条id=2的数据的时候等待，原因是id没有加上索引，导致事务1锁的是表锁而不是行锁。
如果是使用相同的索引键，会出现锁冲突。
示例：tab_with_index表中id字段有索引，name字段没有索引。
事务1：

select * from tab_with_index where id = 1 and name = '1' for update;

事务2：

select * from tab_with_index where id = 1 and name = '4' for update;

虽然事务2访问的是和事务1不同的记录，但是因为使用了相同的索引，所以需要等待锁。

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
示例：表tab_with_index的id字段有主键索引，name字段有普通索引。
事务1：

select * from tab_with_index where id = 1 for update;

事务2：

select * from tab_with_index where name = '2' for update;

事务2使用name的索引访问记录，因为记录没有被索引，所以也可以获得锁。

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
示例：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB 使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读.
还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

一致性非锁定读

一致性非锁定读是指InnoDB存储引擎通过多版本并发控制技术来读取当前数据库的数据。如果当前读取的行正在执行delete或者update操作，这时读取操作不会等行锁的释放，而是去读取行的快照数据。

非锁定一致性读.png

快照数据是指改行之前版本的数据，该实现是通过undo段来实现的，而undo段用来在事务中保存回滚数据，因此使用快照没有增加额外的开销。
这是InnoDB存储引擎的默认读取方式。

注意

不同的事务隔离级别下读取的方式不同，并不是每个事务隔离级别下都是采用非锁定的一致性读。四种隔离级别中，READ COMMITTED和REPEATABLE READ这两种隔离级别使用非锁定的一致性读。
不同的事务即使都使用非锁定的一致性读，但是对于快照数据的定义也各不相同。READ COMMITTED级别下非锁定读总是读取锁定行的最新一份快照数据；而REPEATABLE READ级别下非锁定读总是读取事务开始时的数据版本。

例子

事务A	事务B
select * from table where id='1';
.	update table set id =3 where id=1;
select * from table where id='1';
.	commit;
select * from table where id='1';

上述例子中事务B update以后事务A第一次select的时候RC级别和RR级别获取的结果都是id=1的那一条数据；第二次select的时候，由于事务B已经提交，RC级别select的结果就是id=3，而RR级别读取的是事务开始时的数据，id=1。

一致性锁定读

默认配置下事务的隔离级别为REPEATABLE READ，select操作为非一致性锁定读，但某些情况下需要对数据库读取操作进行加锁保证数据的一致性。select 有两种一致的锁定读：

select ... for update
select ... lock in share mode

自增长与锁

InnoDB存储引擎内部对每个含有自增长列的表有一个自增长计数器，当进行insert操作时，首先获取计数器的最大值，加1后进行insert操作。这个操作会加一个特殊的表锁，AUTO-INC LOCK。这个锁并不是在事务提交后才释放，而是在insert语句执行完后释放。

缺点

虽然是insert后就释放锁，不是事务提交后才释放，但是必须等前一个insert的完成才能进行下一次insert，性能较差。

改进

TODO...

外键与锁

在对外键值进行update和insert操作时首先需要查询父表的记录，即select父表，这个select操作不是使用一致性非锁定读，因为会发生数据不一致的问题，为此需要使用一致性锁定读，这时使用的select ... lock in share mode方式。当父表对应记录加X锁后，子表的操作将会阻塞。

例子