MySQL GAP锁初步认识

前段时间呢,富春云研发小伙伴找到我,表示线上系统程序插入数据库越来越慢,插入一条记录需要好几秒,但是发现单独插入数据响应时间很快,于是对数据库进行分析后发现,有SQL相互之间存在锁导致了插入慢。本次呢,就简单讲讲,让大家初步了解下锁。

锁,在现实生活中是为我们想要隐藏于外界所使用的一种工具。在计算机中,是协调多个进程或县城并发访问某一资源的一种机制。在数据库当中,除了传统的计算资源(CPU、RAM、I/O等等)的争用之外,数据也是一种供许多用户共享访问的资源。如何保证数据并发访问的一致性、有效性,是所有数据库必须解决的一个问题,锁的冲突也是影响数据库并发访问性能的一个重要因素。从这一角度来说,锁对于数据库而言就显得尤为重要。

MySQL中根据锁粒度分三种锁机制:行锁(锁定粒度小,发生锁冲突的概率低,并发度高),表锁(锁定力度大,发生锁冲突概率高,并发度最低),页锁(锁定粒度介于表锁和行锁之间,并发度一般)。

而常用的InnoDB存储引擎实现了以下两种类型的行锁。

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

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

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

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

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

今天我们主要了解下X锁中的gap锁:

MySQL GAP锁存在于MySQL隔离级别为REPEATABLE-READ或更高级别情况下,为了防止幻读,于是有gap锁和next-key锁存在,除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。

下面我们分别做相应的测试:

测试一:非唯一索引

隔离级别:REPEATABLE-READ

表结构:id为非唯一索引

表内容:

mysql>select * from test2;

+------+----------+

| id | name |

+------+----------+

| 10 | xbb |

| 20 | meacheal |

| 30 | bingo |

+------+----------+

3 rows in set(0.00 sec)

session1:update一条记录(未提交)

mysql> setautocommit =0;

Query OK, 0rows affected (0.00 sec)

mysql>update test2 set name='icey' where id=20;

Query OK, 1 rowaffected (0.00 sec)

Rows matched:1 Changed: 1 Warnings: 0

session2:insert记录

mysql> setautocommit =0;

Query OK, 0rows affected (0.00 sec)

mysql>insert into test2 values(10,'aa');

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

mysql>insert into test2 values(16,'aa');

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

mysql>insert into test2 values(22,'aa');

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

mysql>insert into test2 values(30,'aa');

Query OK, 1 rowaffected (0.00 sec)

mysql>UPDATE test2 set name='icey' whereid=10;

Query OK, 1 rowaffected (0.00 sec)

Rows matched:1 Changed: 1 Warnings: 0

mysql>UPDATE test2 set name='icey' whereid=20;

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

mysql>UPDATE test2 set name='icey' whereid=30;

Query OK, 2rows affected (0.00 sec)

Rows matched:2 Changed: 2 Warnings: 0

mysql>delete from test2 where id =20;

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

mysql>delete from test2 where id =11;

Query OK, 0 rowsaffected (0.00 sec)

查看锁内容:

mysql tables inuse 1, locked 1

LOCK WAIT 2lock struct(s), heap size 1184, 1 row lock(s), undo log entries 2

insert intotest2 values(16,'aa')

------- TRX HASBEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKSspace id 13379 page no 4 n bits 80 index `idx_id` of table `test`.`test2` trxid 50833735 lock_mode Xlocks gap before recinsert intention waiting

mysql tables inuse 1, locked 1

LOCK WAIT 2lock struct(s), heap size 360, 1 row lock(s)

update test2set name ='meachel' where id=20

------- TRX HASBEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKSspace id 13379 page no 4 n bits 80 index `idx_id` of table `test`.`test2` trxid 50835541 lock_mode Xwaiting

mysql tables inuse 1, locked 1

LOCK WAIT 2lock struct(s), heap size 360, 1 row lock(s)

delete fromtest2 where id =20

------- TRX HASBEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKSspace id 13379 page no 4 n bits 80 index `idx_id` of table `test`.`test2` trxid 50835541 lock_mode X waiting

由此可知:在隔离级别是RR或者更高的情况下,事务一对某记录进行update(select * for update)时,未提交。

当事务二insert表记录的时候将会gap锁,锁的范围是[上条记录,下一条记录)。

当事务二update,delete表记录的时候将只有X锁,即只对事务更新的那条记录加X锁。

当表中无索引,无主键。当事务一进行更新记录,将锁全表,事务二无法进行insert,update,delete操作。

亲爱的小伙伴,你get√了吗~

FCHYUN Talking

热点挖掘 · 视点分享·观点新解!

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180928G0LDDO00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券