MySQL 加锁处理分析

系统同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力,并且很有可能出现系统死锁等问题。所以对于加锁的处理,是数据库对于事务处理的精髓所在。这里通过分析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》第三版

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值