MySQL 加锁处理分析

最新推荐文章于 2021-02-02 06:11:53 发布

YQAG

最新推荐文章于 2021-02-02 06:11:53 发布

阅读量687

点赞数

本文链接：https://blog.csdn.net/suneqing/article/details/79250009

版权

DataBase 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

系统同一时间会有大量的并发访问，如果加锁过度，会极大的降低并发处理能力，并且很有可能出现系统死锁等问题。所以对于加锁的处理，是数据库对于事务处理的精髓所在。这里通过分析MySQL中InnoDB加锁机制与事务隔离级别相结合，去详细描述在每种隔离级别下加锁的处理方式。

MVCC，multi-version concurrency control
mvcc大多数实现的是读非阻塞，写操作只锁必要的行。读写不冲突。
innoDB中简单说MVCC是如何工作的，每行后面保存另个隐藏列来实现的。这两列一个是保存了行的创建时间，一个保存了行的过期时间(或者删除时间)。当然存储的并不是实际的时间，而是系统版本号，每开始一个事务，系统版本号会自动递增。事务开始的版本好会作为事务的版本号，用来和查询到每行记录的版本号进行比较。(摘自《高性能Mysql第三版》p13)
另外，mvcc只在REPEATABLE READ 和READ COMMITTED两个隔离级别下工作。
在MVCC中读有两种方式，快照读：读取记录的可见版本，可能是历史版本，不加锁。快照读：读取的试最新的记录，会加锁，保证其事务不在操作此记录，for update，lock in share mode 查询，insert ，update，delete都属于当前读。

2PL：Two-Phase Locking
数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）
加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

事务隔离级别
上一篇文章中已经介绍了事务隔离级别，在这里就不再赘述。
未提交读(Read Uncommitted)，提交读(Read Committed)，可重复读(Repeated Read)，串行读(Serializable)

下面就针对不同的事务隔离级别描述不同的加锁情况。
在这里对于未提交读(Read Uncommitted)，串行读(Serializable)不再描述。只针对提交读(Read Committed)，可重复读(Repeated Read)加以分析。

CREATE TABLE `city` (
`id`  int(1) NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`state`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`city_code`  int(10) NOT NULL DEFAULT 0 ,
`province_code`  int(10) NOT NULL ,
PRIMARY KEY (`id`),
UNIQUE INDEX `city_index_u` (`city_code`) USING BTREE ,
INDEX `province_index` (`province_code`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='市级信息'
AUTO_INCREMENT=2147483647
ROW_FORMAT=COMPACT
;

提交读(Read Committed)

按照主键条件获取锁

session 1

mysql> SET session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> select @@session.tx_isolation;

+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set
mysql> select * from city;
+----+--------+-------+-----------+---------------+
| id | name   | state | city_code | province_code |
+----+--------+-------+-----------+---------------+
|  1 | 石家庄 | 河北  |      1001 |          1000 |
|  2 | 邯郸   | 河北  |      1002 |          1000 |
|  3 | 沧州   | 河北  |      1003 |          1000 |
+----+--------+-------+-----------+---------------+
mysql> start transaction;
Query OK, 0 rows affected

mysql> update city set name='保定1' where id=3;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

session 2
mysql> SET session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> select @@session.tx_isolation;

+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
mysql> start transaction;
Query OK, 0 rows affected

mysql> update city set name='保定1' where id=3;
1205 - Lock wait timeout exceeded; try restarting transaction

由上可以得出：为了防止并发过程中修改冲突，session 1 给主键ID为3数据更新name时加了行锁，在不提交事务的情况下。session 2操作同一条数据的时候一直拿不到锁，直到超时为止。
如果更新条件是按照唯一索引更新，结果是一样的：

mysql> update city set name='保定' where city_code=1003;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

按照普通索引更新：

session 1

mysql> SET session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> select @@session.tx_isolation;

+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
mysql> start transaction;
Query OK, 0 rows affected

mysql> update city set name='沧州' where province_code=1000;
Query OK, 3 rows affected
Rows matched: 3  Changed: 3  Warnings: 0

session 2

mysql> SET session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> select @@session.tx_isolation;

+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT INTO `city` (`id`, `name`, `state`, `city_code`, `province_code`) VALUES (4, '沧州', '河北', 1004, 1000);
1205 - Lock wait timeout exceeded; try restarting transaction

session 3
mysql> SET session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> select @@session.tx_isolation;

+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT INTO `city` (`id`, `name`, `state`, `city_code`, `province_code`) VALUES (5, '郑州', '河南', 2004, 2001);
Query OK, 1 row affected -- 获取锁并插入数据

由上可以看出，session 1事务给普通索引province_code=1000的记录全部加锁，通过session 2可以看出不能再获取锁插入相同province_code=1000的数据，而对于session 3事务，还可以正常的获取锁，插入数据。这种情况下容易产生幻读的问题。

按照普通字段获取锁

如果是没有索引的state呢？where state=’河北’，那么MySQL会给整张表的所有数据行的加行锁。这里听起来有点不可思议，但是当sql运行的过程中，MySQL并不知道哪些数据行是 state=’河北’，如果一个条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由MySQL Server层进行过滤。
但在实际使用过程当中，MySQL做了一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录释放锁 (违背了二段锁协议的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。可见即使是MySQL，为了效率也是会违反规范的。（参见《高性能MySQL》第三版p181）

总结一下：获取锁的条件中如果是主键，唯一索引，普通索引，只会是行锁，锁住的是相关过滤的数据；如果查询条件是普通字段查询，原则上会锁住全表，但是mysql做了优化以后，会排除一些记录。

可重复读(Repeated Read)

按照主键或者唯一索引获取锁：
这个类似于RC级别下的按照主键和唯一索引获取锁，只会锁住相关记录。

按照普通索引获取锁：

session 1

mysql> select @@session.tx_isolation;

+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set

mysql> start transaction;

Query OK, 0 rows affected
mysql> select * from city;
+----+--------+-------+-----------+---------------+
| id | name   | state | city_code | province_code |
+----+--------+-------+-----------+---------------+
|  1 | 石家庄   | 河北  |      1001 |          1000 |
|  2 | 郑州   | 河南  |      2001 |          2000 |
|  3 | 海淀区 | 北京  |      3001 |          3000 |
+----+--------+-------+-----------+---------------+
3 rows in set
mysql> update city set name='保定' where province_code=1000;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

session 2

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT INTO `city` (`id`, `name`, `state`, `city_code`, `province_code`) VALUES (5, '秦皇岛', '河北', 1006, 1000);
1205 - Lock wait timeout exceeded; try restarting transaction
terminated by user
mysql> INSERT INTO `city` ( `name`, `state`, `city_code`, `province_code`) VALUES ( '秦皇岛', '河北', 1006,998);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `city` ( `name`, `state`, `city_code`, `province_code`) VALUES ( '上海', '上海', 4001, 4000);
Query OK, 1 row affected

RR级别中，session 1在update后加锁，session 2只有一部分数据可以插入。这个锁，就是Gap锁。Gap锁只会锁住相关范围的记录，不会有其他记录插入。

按照普通字段获取锁：
如果使用的是没有索引的字段，那么会给全表加入gap锁。同时，它不能像上文中行锁一样经过MySQL Server过滤自动解除不满足条件的锁，因为没有索引，则这些字段也就没有排序，也就没有区间。除非该事务提交，否则其它事务无法插入任何数据。

行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

总结一下：获取锁的条件中如果是主键，唯一索引，只会产生行锁，普通索引，会产生GAP锁，获取锁后锁住范围记录，不可再进行操作，保证了不会出现幻读的情况；如果查询条件是普通字段查询，全表加入gap锁，但是mysql做了优化以后，会排除一些记录。

参考资料：
《高性能MySQL》第三版