连mysql锁的机制都不了解，怎么做架构师

引言

数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问变得有序所设计的一种规则；对于任何一种数据库来说都需要有相应的锁定机制，Mysql也不例外。

mysql 事务

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

1. 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2. 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
3. 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
4. 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

MYSQL 事务处理主要有两种方法：

1. 用 BEGIN, ROLLBACK, COMMIT来实现
2. 1)、BEGIN 开始一个事务
3. 2)、ROLLBACK 事务回滚
4. 3)、COMMIT 事务确认
5. 直接用 SET 来改变 MySQL 的自动提交模式:
6. 1)、SET AUTOCOMMIT=0 禁止自动提交
7. 2)、SET AUTOCOMMIT=1 开启自动提交

事务隔离级别

mysql事务隔离级别分为如下4种：

(1)读不提交(Read Uncommited，RU)

这种隔离级别下，事务间完全不隔离，会产生脏读，可以读取未提交的记录，实际情况下不会使用。

(2)读提交(Read commited，RC)

仅能读取到已提交的记录，这种隔离级别下，会存在幻读现象，所谓幻读是指在同一个事务中，多次执行同一个查询，返回的记录不完全相同的现象。幻读产生的根本原因是，在RC隔离级别下，每条语句都会读取已提交事务的更新，若两次查询之间有其他事务提交，则会导致两次查询结果不一致。虽然如此，读提交隔离级别在生产环境中使用很广泛。

(3)可重复读(Repeatable Read, RR)

可重复读隔离级别解决了不可重复读的问题，但依然没有解决幻读的问题。那么不可重复读与幻读有什么区别呢？不可重复读重点在修改，即读取过的数据，两次读的值不一样；而幻读则侧重于记录数目变化【插入和删除】。一般教科书上告诉我们只有到串行化隔离级别才解决幻读问题，但mysql的innodb比较特殊，RR即解决了幻读问题，主要通过GAP锁实现。另外，不是所有的数据库都实现了该隔离级别，后面会简单介绍下mysql是如何实现可重复读隔离级别的。

(4)串行化(Serializable)

在串行化隔离模式下，消除了脏读，幻象，但事务并发度急剧下降，事务的隔离级别与事务的并发度成反比，隔离级别越高，事务的并发度越低。实际生产环境下，dba会在并发和满足业务需求之间作权衡，选择合适的隔离级别。

mysql 锁类型

MySQL 各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。

1、行级锁定

锁定对象的颗粒度很小，只对当前行进行锁定，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力；弊端就是获取锁释放锁更加频繁，系统消耗更大，同时行级锁定也最容易发生死锁；

行级锁定的主要是Innodb存储引擎和NDB Cluster存储引擎；

2、表级锁定

一次会将整张表锁定，该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小，而且可以避免死锁问题；弊端就是锁定资源争用的概率最高，并发处理能力最低；

使用表级锁定的主要是MyISAM，Memory，CSV等一些非事务性存储引擎。

锁定颗粒度介于行级锁定与表级锁之间，每页有多行数据，并发处理能力以及获取锁定所需要的资源开销在两者之间；

页级锁定主要是BerkeleyDB 存储引擎；

两段锁协议(2PL)

两段锁协议规定所有的事务应遵守的规则：

1.在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁；

2.在释放一个封锁之后，事务不再申请和获得其它任何封锁；

即事务的执行分为两个阶段：

第一阶段是获得封锁的阶段，称为扩展阶段；第二阶段是释放封锁的阶段，称为收缩阶段；

1begin;insert ... 加锁1 update ... 加锁2 commit; 事务提交时，释放锁1，锁2

如果在加锁2的时候，加锁不成功，则进入等待状态，直到加锁成功才继续执行；

如果有另外一个事务获取锁的时候顺序刚好相反，是有可能导致死锁的；为此有了一次性封锁法，要求事务必须一次性将所有要使用的数据全部加锁，否则就不能继续执行；

锁模式

Innodb 实现了两种类型的行锁：

1. 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
2. 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

1. 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
2. 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

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

意向锁是 innodb 自动加的，不需要用户干预。对于 update、delete 和 insert 语句，innodb 会自动给涉及数据集加排它锁（X）；对于普通 select 语句，innodb 不会加任何锁。

事务可以通过以下语句显式给记录集加共享锁或排它锁。

共享锁（S）：select * from table_name where … lock in share mode.

排它锁（X）: select * from table_name where … for update.

用 select… in share mode 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 update 或者 delete 操作。但是如果当前事务也需要对该记录进行更新操作,则有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 select… for update 方式获得排他锁。

事务隔离级别和锁的关系

数据库隔离级别：未提交读(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)和可串行化(Serializable)；

1. 未提交读(Read uncommitted)：可能读取到其他会话中未提交事务修改的数据，会出现脏读(Dirty Read)；
2. 已提交读(Read committed)：只能读取到已经提交的数据，会出现不可重复读(NonRepeatable Read)；
3. 可重复读(Repeatable read)：InnoDB默认级别，不会出现不可重复读(NonRepeatable Read)，但是会出现幻读(Phantom Read);
4. 可串行化(Serializable)：强制事务排序，使之不可能相互冲突，从而解决幻读问题，使用表级共享锁，读写相互都会阻塞；

常用的2种隔离级别是：已提交读(Read committed)和可重复读(Repeatable read)；mysql的默认隔离级别是可重复读。

行锁的实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。如果不同的索引碰巧都落到了同一个行上，那么同样会阻塞。

即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

间隙锁

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

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

Select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要

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

MySQL的恢复机制是通过BINLOG记录来执行IUD操作来同步Slave的，这就要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的。

INSERT…SELECT…和 CREATE TABLE…SELECT…语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。实际上，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，不推荐使用。

测试mysql隔离级别

已提交读

接下来我们测试下mysql的已提交读，我们先看下mysql的隔离级别：

1mysql> SELECT @@tx_isolation;

2+----------------+

3| @@tx_isolation |

4+----------------+

5| REPEATABLE-READ |

6+----------------+

设置隔离级别为已提交读

1set session transaction isolation level read committed;

2Query OK, 0 rows affected (0.02 sec)

3

4mysql> SELECT @@tx_isolation;

5+----------------+

6| @@tx_isolation |

7+----------------+

8| READ-COMMITTED |

9+----------------+

101 row in set (0.01 sec)

准备测试数据:

1CREATE TABLE `test_lock` (

2 `id` int(11) NOT NULL AUTO_INCREMENT,

3 `name` varchar(255) NOT NULL,

4 `type` int(11) DEFAULT NULL,

5 PRIMARY KEY (`id`)

6) ENGINE=InnoDB DEFAULT CHARSET=utf8

7

8mysql> insert into test_lock values(null,'zhaohui',1);

9mysql> insert into test_lock values(null,'zhaohui2',2);

模拟多个事务交叉执行:

Session1执行查询

1mysql> begin

2 -> ;

3Query OK, 0 rows affected (0.02 sec)

4

5mysql> select * from test_lock where id=1;

6+----+---------+------+

7| id | name | type |

8+----+---------+------+

9| 1 | zhaohui | 1 |

10+----+---------+------+

111 row in set (0.05 sec)

Session2更新数据

1 begin;

2Query OK, 0 rows affected (0.08 sec)

3

4mysql> update test_lock set name='zhaohui_new' where id=1;

5Query OK, 1 row affected (0.02 sec)

6Rows matched: 1 Changed: 1 Warnings: 0

7

8mysql> commit;

9Query OK, 0 rows affected (0.03 sec)

10

Session1执行查询

1select * from test_lock where id=1;

2+----+-------------+------+

3| id | name | type |

4+----+-------------+------+

5| 1 | zhaohui_new | 1 |

6+----+-------------+------+

71 row in set (0.04 sec)

Session1中出现了不可重复读(NonRepeatable Read)，也就是在查询的时候没有锁住相关的数据，导致出现了不可重复读，但是写入、修改和删除数据还是加锁了，如下所示：

Session1更新数据

1mysql> begin;

2Query OK, 0 rows affected (0.01 sec)

3

4mysql> update test_lock set name='zhaohui_new2' where id=1;

5Query OK, 1 row affected (0.01 sec)

6Rows matched: 1 Changed: 1 Warnings: 0

Session2更新数据

1mysql> begin

2 -> ;

3Query OK, 0 rows affected (0.05 sec)

4

5mysql> update test_lock set name='zhaohui_new3' where id=1;

6ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session2更新在更新同一条数据的时候超时了，在更新数据的时候添加了排他锁；

可重复读

查看和设置隔离级别

1mysql> set session transaction isolation level repeatable read;

2Query OK, 0 rows affected (0.07 sec)

3

4mysql> SELECT @@tx_isolation;

5+-----------------+

6| @@tx_isolation |

7+-----------------+

8| REPEATABLE-READ |

9+-----------------+

101 row in set (0.05 sec)

模拟多个事务交叉执行:

Session1执行查询

1mysql> begin;

2Query OK, 0 rows affected (0.03 sec)

3

4mysql> select * from test_lock where type=2;

5+----+----------+------+

6| id | name | type |

7+----+----------+------+

8| 2 | zhaohui2 | 2 |

9+----+----------+------+

101 row in set (0.02 sec)

Session2更新数据

1mysql> begin;

2Query OK, 0 rows affected (0.05 sec)

3

4mysql> update test_lock set name='zhaohui2_new' where type=2;

5Query OK, 1 row affected (0.03 sec)

6Rows matched: 1 Changed: 1 Warnings: 0

7

8mysql> commit;

9Query OK, 0 rows affected (0.06 sec)

Session1执行查询

1mysql> select * from test_lock where type=2;

2+----+----------+------+

3| id | name | type |

4+----+----------+------+

5| 2 | zhaohui2 | 2 |

6+----+----------+------+

71 row in set (0.03 sec)

可以发现2次查询的数据结果是一样的，实现了可重复读(Repeatable read)，再来看一下是否有幻读的问题；

Session3插入数据

1mysql> begin;

2Query OK, 0 rows affected (0.01 sec)

3

4mysql> insert into test_lock(name,type) values('zhaohui3',2);

5Query OK, 1 row affected (0.02 sec)

6

7mysql> commit;

8Query OK, 0 rows affected (0.02 sec)

Session1执行查询

1mysql> select * from test_lock where type=2;

2+----+----------+------+

3| id | name | type |

4+----+----------+------+

5| 2 | zhaohui2 | 2 |

6+----+----------+------+

71 row in set (0.01 sec)

这里发现一个奇怪的现象,按理说应该会出现两条记录，但是mysql在重复读的隔离级别下没有出现幻读的情况；

我们知道innodb默认是行锁级别，如何通过悲观锁的方式去实现可重复读和不出现幻读的现象，对读取的数据加共享锁，对同样的数据执行更新操作就只能等待，这样就可以保证可重复读，但是对于不出现幻读的现象无法通过锁定行数据来解决；

最终看到的现象是没有幻读的问题，同时如果对读取的数据加共享锁，更新相同数据应该会等待，上面的实例中并没有出现等待，所以mysql内部应该还有其他锁机制--MVCC机制；

共享锁使用

Session1查询数据

1mysql> begin;

2Query OK, 0 rows affected (0.03 sec)

3

4mysql> select * from test_lock where type=2 lock in share mode;

5+----+--------------+------+

6| id | name | type |

7+----+--------------+------+

8| 2 | zhaohui2_new | 2 |

9| 3 | zhaohui3 | 2 |

10+----+--------------+------+

112 rows in set (0.15 sec)

Session2查询数据

1mysql> begin;

2Query OK, 0 rows affected (0.01 sec)

3

4mysql> select * from test_lock where type=2 lock in share mode;

5+----+--------------+------+

6| id | name | type |

7+----+--------------+------+

8| 2 | zhaohui2_new | 2 |

9| 3 | zhaohui3 | 2 |

10+----+--------------+------+

112 rows in set (0.05 sec)

Session3 更新数据

1mysql> begin;

2Query OK, 0 rows affected (0.02 sec)

3

4mysql> update test_lock set name='zhaohui3_new' where id=3;

5ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session1和Session2使用了共享锁，所以可以存在多个，并不冲突，但是Session3更新操作需要加上排他锁，和共享锁不能同时存在；

排他锁使用

Session1查询数据

1mysql> begin;

2Query OK, 0 rows affected (0.07 sec)

3

4mysql> select * from test_lock where type=2 for update;

5+----+--------------+------+

6| id | name | type |

7+----+--------------+------+

8| 2 | zhaohui2_new | 2 |

9| 3 | zhaohui3 | 2 |

10+----+--------------+------+

112 rows in set (15.02 sec)

Session2查询数据

1mysql>begin;

2Query OK, 0 rows affected (0.07 sec)

3

4mysql> select * from test_lock where type=2 for update;

5ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

排他锁只能有一个同时存在，如果session1获取了锁，那么session2将会等待超时。

mysql MVCC

MVCC 简介

MVCC (Multiversion Concurrency Control)，即多版本并发控制技术,它使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，取而代之的是把数据库的行锁与行的多个版本结合起来，只需要很小的开销,就可以实现非锁定读，从而大大提高数据库系统的并发性能

MVCC 实现

MVCC是通过保存数据在某个时间点的快照来实现的. 不同存储引擎的MVCC. 不同存储引擎的MVCC实现是不同的,典型的有乐观并发控制和悲观并发控制.

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，没开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.

innodb存储的最基本row中包含一些额外的存储信息 DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT

1. 6字节的DATA_TRX_ID 标记了最新更新这条行记录的transaction id，每处理一个事务，其值自动+1
2. 7字节的DATA_ROLL_PTR 指向当前记录项的rollback segment的undo log记录，找之前版本的数据就是通过这个指针
3. 6字节的DB_ROW_ID，当由innodb自动产生聚集索引时，聚集索引包括这个DB_ROW_ID的值，否则聚集索引中不包括这个值.，这个用于索引当中
4. DELETE BIT位用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候

具体的执行过程

begin->用排他锁锁定该行->记录redo log->记录undo log->修改当前行的值，写事务编号，回滚指针指向undo log中的修改前的行

上述过程确切地说是描述了UPDATE的事务过程，其实undo log分insert和update undo log，因为insert时，原始的数据并不存在，所以回滚时把insert undo log丢弃即可，而update undo log则必须遵守上述过程

下面分别以select、delete、 insert、 update语句来说明：

SELECT

Innodb检查每行数据，确保他们符合两个标准：

1、InnoDB只查找版本早于当前事务版本的数据行(也就是数据行的版本必须小于等于事务的版本)，这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行

2、行的删除操作的版本一定是未定义的或者大于当前事务的版本号，确定了当前事务开始之前，行没有被删除

符合了以上两点则返回查询结果。

InnoDB为每个新增行记录当前系统版本号作为创建ID。

DELETE

InnoDB为每个删除行的记录当前系统版本号作为行的删除ID。

UPDATE

InnoDB复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。

这里简单做下总结：

1. insert操作时 “创建时间”=DB_ROW_ID，这时，“删除时间 ”是未定义的；
2. update时，复制新增行的“创建时间”=DB_ROW_ID，删除时间未定义，旧数据行“创建时间”不变，删除时间=该事务的DB_ROW_ID；
3. delete操作，相应数据行的“创建时间”不变，删除时间=该事务的DB_ROW_ID；
4. select操作对两者都不修改，只读相应的数据

閱讀更多 科技伍小黑 的文章

關鍵字: DBA 保证数据 技术