网站首页 | 最新手游 | 最新软件 |
网站导航
手机游戏
休闲益智 角色扮演 动作冒险 射击枪战 体育竞速 经营策略 MOBA
手机软件
网络浏览 社交聊天 影音播放 学习教育 生活常用 阅读资讯 效率办公 拍照摄影
技术中心
PHP教程 php框架 前端框架 其他 数据库 运维 系统工具 软件教程 手机系统教程
博客中心
数据库 APP开发 电脑问题 编程语言 网络系统 前端开发 互联网
插件下载
输入 banner图 图片脚本 客服 导航分类 播放器 css3 jQuery脚本 jqueryhtml5
源码下载
企业程序 小说有声 网站源码 第三方软件 新闻资讯 第三方源码
电脑软件
聊天工具 视频软件 音乐软件 游戏娱乐 网络游戏 浏览器 图形图像 安全杀毒
电脑软件
输入法 下载工具 股票网银 办公软件 阅读翻译 压缩刻录 系统工具 编程开发
电脑软件
手机数码 教育学习 网络应用 主题壁纸 其他软件 font字体 存储管理 压缩解压
电脑软件
系统设置 改名软件 文本编辑 拼音输入 降温程序 电子阅读 打印工具 刻录软件
电脑软件
翻译软件 系统优化 网页浏览 广告过滤 网站推广 下载工具 FTP工具 邮件收发
专题合集

热搜：蛋仔派对官服版  大话诛仙bt版  懒人听书正式版  mm131纯净版  美剧鸟破解版  

首页 手机游戏 手机软件 软件下载 jquery插件库 程序下载 手机教程 专题
您的位置：首页 > 博客中心 > 数据库 >

MySQL 间隙锁

时间：2022-03-15 08:21

一.根据案例二：不同索引加锁顺序的问题，模拟重现死锁（详细操作步骤）

  • 1.RR级别下，更新操作默认会加行级锁，行级锁会对索引加锁
  • 2.如果更新语句使用多个索引，行级锁会先锁定普通索引，再锁定聚簇索引
  • 3.如果两个SQL用到了不同的普通索引，或者一个用了，另外一个没用
  • 4.会导致这两个SQL加行级锁的顺序不一致，形成多个事物之间X锁的循环等待，形成死锁
1.1表结构
root@slave01 22:28:  [sqltest]> create table user_info (id int primary key,username varchar(20), status tinyint,key(username));
Query OK, 0 rows affected (0.03 sec)
#
root@slave01 22:54:  [sqltest]> show create table user_info;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                            |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_info | CREATE TABLE `user_info` (
  `id` int(11) NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#
insert into user_info values(1,‘yzw1‘,1);
insert into user_info values(3,‘yzw3‘,0);
insert into user_info values(5,‘yzw5‘,1);
1.2执行计划
root@master 23:15:  [sqltest]> desc update user_info set status=1 where username=‘yzw3‘;
+----+-------------+-----------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | user_info | NULL       | range | username      | username | 63      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set (0.01 sec)
#
root@master 23:15:  [sqltest]> desc update user_info set username=‘yzw33‘ where id=3;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | user_info | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
1.3重现
session 1 session 2
begin;
update user_info set status=1 where username=‘yzw3‘;		  
此时session除了给索引username加x锁，还给主键索引id=3的行记录加了x锁，但是这两个锁并不是同时获取的  
第一步先获取普通索引username上的X锁  
此时session 2进行更新 begin;
update user_info set username=‘yzw33‘ where id=3;
  session 2
第一步需要先获取聚簇索引，也就是主键索引上的X锁
第二步需要获取普通索引username上的X锁
  出现死锁ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
需要session 2的主键索引X锁 需要session 1的普通索引username X锁 
root@master 23:11:  [(none)]> select * from information_schema.innodb_locks;
+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table            | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| 48736590:1135:3:3 | 48736590    | X         | RECORD    | `sqltest`.`user_info` | PRIMARY    |       1135 |         3 |        3 | 3         |
| 48733648:1135:3:3 | 48733648    | X         | RECORD    | `sqltest`.`user_info` | PRIMARY    |       1135 |         3 |        3 | 3         |
+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
1.4重现

  • 1.给status列增加索引

    create index idx_status on user_info(status);

  • 2.执行计划，两条语句都是走的普通索引

    root@master 23:26:  [sqltest]> desc update user_info set status=1 where username=‘yzw3‘;
+----+-------------+-----------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | user_info | NULL       | range | username      | username | 63      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set (0.04 sec)
#
root@master 23:29:  [sqltest]> desc update user_info set username=‘yzw33‘ where status=0;
+----+-------------+-----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | user_info | NULL       | range | idx_status    | idx_status | 2       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set (0.01 sec)

  • 3.两个会话更新索引

    session 1 session 2
    begin;
    update user_info set status=1 where username=‘yzw3‘;    		  
    第一步已经获取了username的普通索引X锁 begin;
    update user_info set username=‘yzw33‘ where status=0;
      第一步已经获取status的普通索引X锁
    第二步给主键索引加X锁
    第二步需要给主键索引加X锁，无法获取 出现死锁ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
      因为要更新非主键索引username，因此要给username加X锁，但是无法获取 
    root@master 23:30:  [sqltest]> select * from information_schema.innodb_locks;
+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table            | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| 48739611:1135:5:2 | 48739611    | X         | RECORD    | `sqltest`.`user_info` | idx_status |       1135 |         5 |        2 | 0, 3      |
| 48738737:1135:5:2 | 48738737    | X         | RECORD    | `sqltest`.`user_info` | idx_status |       1135 |         5 |        2 | 0, 3      |
+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
    1.5多查询一次取主键，解决死锁
    begin; 
update user_info set status=1 where id in (select id  from (select id from user_info where username=‘yzw3‘) t);
begin;
update user_info set username=‘yzw33‘ where id in (select id from  (select id from user_info where status=0) t);
    root@master 23:56:  [sqltest]> select * from information_schema.innodb_locks;
+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table            | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| 48744368:1135:3:3 | 48744368    | X         | RECORD    | `sqltest`.`user_info` | PRIMARY    |       1135 |         3 |        3 | 3         |
| 48744353:1135:3:3 | 48744353    | X         | RECORD    | `sqltest`.`user_info` | PRIMARY    |       1135 |         3 |        3 | 3         |
+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

    依然存在死锁，因为都给主键id加了X锁

二.根据方案三：TMS死锁分析和处理，模拟重现死锁（详细操作步骤）

  • 1.构造数据

    create table user_info1 (id int primary key,name varchar(10), level tinyint);
insert into user_info1 values(1,‘AA‘,0);
insert into user_info1 values(3,‘CC‘,0);
insert into user_info1 values(5,‘EE‘,2);
insert into user_info1 values(7,‘GG‘,5);
insert into user_info1 values(9,‘GG‘,7);
insert into user_info1 values(11,‘JJ‘,20);
session 1 >select * from user_info1;
+----+------+-------+
| id | name | level |
+----+------+-------+
|  1 | AA   |     0 |
|  3 | CC   |     0 |
|  5 | EE   |     2 |
|  7 | GG   |     5 |
|  9 | GG   |     7 |
| 11 | JJ   |    20 |
+----+------+-------+
6 rows in set (0.00 sec)

  • 2.过程

    session 1 session 2
      begin;
    insert into user_info1 values(2,‘BB‘,1);
    delete from user_info1 where name=‘BB‘;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction    		 此时是RC级别 session 2首先插入数据并给这一行加X锁，因为没有索引，同时在两边加gap锁
    delete from user_info1 where name=‘BC‘;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction    		  
    delete from user_info1 where level=8;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction    		  
    delete where条件没有索引，会对整表扫描并加gap锁，但是此时session2已经加了gap锁，session1的delete事务被回退   
    delete from user_info1 where  name=‘BB‘
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1137 page no 3 n bits 80 index PRIMARY of table `sqltest`.`user_info1` trx id 48906774 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

  • 3保持RCset tx_isolation=‘READ-COMMITTED‘;级别，增加索引

    session 1 >create index idx_name_level on user_info1(name,level);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
    session 1 session 2
      begin;
    insert into user_info1 values(2,‘BB‘,1);
    delete from user_info1 where name=‘BB‘;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction    		 此时是RC级别 session 2首先插入数据并加gap锁，
    session 1 >delete from user_info1 where name=‘BC‘;
    Query OK, 0 rows affected (0.00 sec)    		 RC级别下，增加了索引，没有了gap锁
    session 1 >delete from user_info1 where name=‘BB‘;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    session 1 >delete from user_info1 where level=8;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    session 1 >delete from user_info1 where level=20;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    session 1 >delete from user_info1 where level=7;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    session 1 >delete from user_info1 where level=2;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    		 索引(a,b)，如果where字段使用了索引a，则不存在gap锁了，猜测是单独使用索引b还是会存在gap锁，因为无法使用索引 
    root@master 16:31:  [(none)]> select * from information_schema.innodb_locks;
+-------------------+-------------+-----------+-----------+------------------------+------------+------------+-----------+----------+-----------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table             | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------+-------------+-----------+-----------+------------------------+------------+------------+-----------+----------+-----------+
| 48942672:1137:3:8 | 48942672    | X         | RECORD    | `sqltest`.`user_info1` | PRIMARY    |       1137 |         3 |        8 | 2         |
| 48942589:1137:3:8 | 48942589    | S         | RECORD    | `sqltest`.`user_info1` | PRIMARY    |       1137 |         3 |        8 | 2         |
+-------------------+-------------+-----------+-----------+------------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

  • 4增加索引(b)，验证

    session 1 >create index idx_level on user_info1(level);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
    session 1 session 2
      begin;
    insert into user_info1 values(2,‘BB‘,1);
    delete from user_info1 where name=‘BB‘;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction    		 此时是RC级别 session 2首先插入数据并加X锁
    session 1 >delete from user_info1 where name=‘BC‘;
    Query OK, 0 rows affected (0.00 sec)
    session 1 >delete from user_info1 where name=‘BB‘;
    ERROR 1205 (HY000): Lock wait timeout xceeded; try restarting transaction
    session 1 >delete from user_info1 where level=8;
    Query OK, 0 rows affected (0.01 sec)
    session 1 >delete from user_info1 where level=20;
    Query OK, 1 row affected (0.01 sec)
    session 1 >delete from user_info1 where level=7;
    Query OK, 1 row affected (0.00 sec)
    session 1 >delete from user_info1 where level=2;
    Query OK, 1 row affected (0.00 sec)
    		 此时b有了索引，RC级别下也就不存在gap锁了

  • 5更改为RR级别set tx_isolation=‘REPEATABLE-READ‘;，先使用索引(a,b)

    session 2 >drop index idx_level on user_info1;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
session 2 >begin;
Query OK, 0 rows affected (0.00 sec)
#
session 2 >insert into user_info1 values(2,‘BB‘,1);
Query OK, 1 row affected (0.00 sec)
#
session 1 >delete from user_info1 where name=‘BB‘;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session 1 >delete from user_info1 where name=‘BC‘;
Query OK, 0 rows affected (0.00 sec)
#
session 1 >delete from user_info1 where level=8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session 1 >delete from user_info1 where level=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session 1 >delete from user_info1 where name=‘CB‘;
Query OK, 0 rows affected (0.00 sec)
#
session 1 >delete from user_info1 where name=‘BA‘;
Query OK, 0 rows affected (0.00 sec)

  • 6总结
    1) RC和RR级别下如果更新没有索引的字段，会加gap锁
    2) 必须能用上索引，(a,b)只用b是无效索引
    3) RC增加了索引后就不存在gap锁了
    3) RR级别在没有索引的情况，用不上索引也会产生gap锁
    4) 解决方法，最好使用RC级别+索引，或者RR下修改参数innodb_locks_unsafe_for_binlog=on
    5) 间隙锁只会block住insert操作

    session 1 >select * from user_info1;
+----+------+-------+
| id | name | level |
+----+------+-------+
|  1 | AA   |     0 |
|  3 | BA   |     1 |
|  5 | EE   |     2 |
|  7 | GG   |     5 |
|  9 | GG   |     7 |
| 11 | JJ   |    20 |
+----+------+-------+
6 rows in set (0.00 sec)
#
session 1 >begin;
Query OK, 0 rows affected (0.00 sec)
#
session 1 >update user_info1 set level=3 where name=‘BA‘;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
session 2 >begin;
Query OK, 0 rows affected (0.01 sec)
#
session 2 >insert into user_info1 values(2,‘AB‘,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

三.根据案例四：MySQL间隙锁导致的Deadlock分析，模拟重现死锁（详细操作步骤）

3.1 造数据
create table t3 (id int auto_increment,coupon_id int,uid int,is_inuse tinyint,primary key(id),key (coupon_id,uid));
insert into t3 (coupon_id,uid,is_inuse) values(16,160825,1);
insert into t3 (coupon_id,uid,is_inuse) values(16,160835,1);
insert into t3 (coupon_id,uid,is_inuse) values(16,160845,1);
insert into t3 (coupon_id,uid,is_inuse) values(16,160855,1);
insert into t3 (coupon_id,uid,is_inuse) values(16,160865,1);
root@master 22:56:  [sqltest]> select * from t3;
+----+-----------+--------+----------+
| id | coupon_id | uid    | is_inuse |
+----+-----------+--------+----------+
|  1 |        16 | 160825 |        1 |
|  2 |        16 | 160835 |        1 |
|  3 |        16 | 160845 |        1 |
|  4 |        16 | 160855 |        1 |
|  5 |        16 | 160865 |        1 |
+----+-----------+--------+----------+
5 rows in set (0.00 sec)
3.2 行记录两边间隙锁范围内无法插入

  • 1.session 1给id=3的行记录加X锁，同时加间隙锁：160835~160845，160845~160855

    set tx_isolation=‘REPEATABLE-READ‘;
set autocommit=0;
start transaction;
select * from t3 where (uid=160845 and coupon_id=16 and is_inuse=1) for update;
+----+-----------+--------+----------+
| id | coupon_id | uid    | is_inuse |
+----+-----------+--------+----------+
|  3 |        16 | 160845 |        1 |
+----+-----------+--------+----------+
1 row in set (0.00 sec)

  • 2.session 2插入记录，在间隙范围：160835~160845

    set tx_isolation=‘REPEATABLE-READ‘;
set autocommit=0;
start transaction;
insert into t3 (coupon_id,uid,is_inuse) values (16,160840,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  • 3.lock

    root@master 23:08:  [sqltest]> select * from information_schema.innodb_locks;
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+---------------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table     | lock_index | lock_space | lock_page | lock_rec | lock_data     |
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+---------------+
| 49020303:1139:4:4 | 49020303    | X,GAP     | RECORD    | `sqltest`.`t3` | coupon_id  |       1139 |         4 |        4 | 16, 160845, 3 |
| 49020011:1139:4:4 | 49020011    | X         | RECORD    | `sqltest`.`t3` | coupon_id  |       1139 |         4 |        4 | 16, 160845, 3 |
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+---------------+
2 rows in set, 1 warning (0.00 sec)

  • 4.session 2插入记录，在间隙范围：160845~160855

    set tx_isolation=‘REPEATABLE-READ‘;
set autocommit=0;
start transaction;
insert into t3 (coupon_id,uid,is_inuse) values (16,160850,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  • 5.lock

    root@master 23:11:  [sqltest]> select * from information_schema.innodb_locks;
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+---------------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table     | lock_index | lock_space | lock_page | lock_rec | lock_data     |
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+---------------+
| 49022356:1139:4:5 | 49022356    | X,GAP     | RECORD    | `sqltest`.`t3` | coupon_id  |       1139 |         4 |        5 | 16, 160855, 4 |
| 49020011:1139:4:5 | 49020011    | X,GAP     | RECORD    | `sqltest`.`t3` | coupon_id  |       1139 |         4 |        5 | 16, 160855, 4 |
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+---------------+
2 rows in set, 1 warning (0.00 sec)
    3.3 行记录间隙锁范围外可以插入
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session 2 >insert into t3 (coupon_id,uid,is_inuse) values (16,160860,1);
Query OK, 1 row affected (0.01 sec)
    3.4 update不存在的记录，也会加间隙锁

  • 1.数据

    session 1 >select * from t3;
+----+-----------+--------+----------+
| id | coupon_id | uid    | is_inuse |
+----+-----------+--------+----------+
|  1 |        16 | 160825 |        1 |
|  2 |        16 | 160835 |        1 |
|  3 |        16 | 160845 |        1 |
|  4 |        16 | 160855 |        1 |
|  5 |        16 | 160865 |        1 |
+----+-----------+--------+----------+
5 rows in set (0.00 sec)

  • 2.update一条不存在的记录，此时gap锁范围：160835~160845，160845~160855

    set tx_isolation=‘REPEATABLE-READ‘;
set autocommit=0;
start transaction;
update t3 set is_inuse=0 where coupon_id=16 and uid=160845 and is_inuse=1;

  • 3.在间隙范围插入新值会产生死锁

    set tx_isolation=‘REPEATABLE-READ‘;
set autocommit=0;
start transaction;
insert into t3 (coupon_id,uid,is_inuse) values (16,160840,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  • 4.lock

    root@master 23:11:  [sqltest]> select * from information_schema.innodb_locks;
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+---------------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table     | lock_index | lock_space | lock_page | lock_rec | lock_data     |
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+---------------+
| 49023844:1139:4:4 | 49023844    | X,GAP     | RECORD    | `sqltest`.`t3` | coupon_id  |       1139 |         4 |        4 | 16, 160845, 3 |
| 49023813:1139:4:4 | 49023813    | X         | RECORD    | `sqltest`.`t3` | coupon_id  |       1139 |         4 |        4 | 16, 160845, 3 |
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+---------------+
2 rows in set, 1 warning (0.00 sec)
3.5删除索引，即使是间隙范围外的插入也会被block

  • 1.删除索引

    session 2 >drop index coupon_id on t3;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
#
session 2 >commit;
Query OK, 0 rows affected (0.00 sec)

  • 2.事务1加锁

    set tx_isolation=‘REPEATABLE-READ‘;
set autocommit=0;
start transaction;
select * from t3 where (uid=160845 and coupon_id=16 and is_inuse=1) for update;

  • 3.事务2在间隙锁范围外插入，此时不是间隙锁，而是全表锁，无法插入

    set tx_isolation=‘REPEATABLE-READ‘;
set autocommit=0;
start transaction;
select * from t3 where (uid=160850 and coupon_id=16 and is_inuse=1) for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  • 4.lock

    root@master 23:27:  [sqltest]> select * from information_schema.innodb_locks;
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table     | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
| 49025074:1139:3:2 | 49025074    | X         | RECORD    | `sqltest`.`t3` | PRIMARY    |       1139 |         3 |        2 | 1         |
| 49025016:1139:3:2 | 49025016    | X         | RECORD    | `sqltest`.`t3` | PRIMARY    |       1139 |         3 |        2 | 1         |
+-------------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
3.6 总结
  • 1.RR级别下，有索引，更新的时候会给行加X锁，前后加GAP锁
  • 2.gap锁范围内无法插入新数据，避免产生幻读
  • 3.gap锁范围外可以正常插入新数据
  • 4.如果没有索引，更新的事务则加的就是全表锁了
  • 5.间隙内或者间隙外都无法插入新数据

相关推荐

电脑软件

热门排行

今日推荐

热门手游