MySQL 间隙锁

最新推荐文章于 2024-08-31 23:36:42 发布
最新推荐文章于 2024-08-31 23:36:42 发布
阅读量121 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/jenvid/p/9074780.html
版权
一.根据案例二：不同索引加锁顺序的问题，模拟重现死锁（详细操作步骤）
  • 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 1session 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 1session 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 1session 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 transactionsession 1 >delete from user_info1 where level=8;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionsession 1 >delete from user_info1 where level=20;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionsession 1 >delete from user_info1 where level=7;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionsession 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
---|---
&nbsp; | begin;</br>insert into user_info1 values(2,'BB',1);
delete from user_info1 where  name='BB';</br>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction | 此时是RC级别 session 2首先插入数据并加X锁
session 1 >delete from user_info1 where name='BC';</br>Query OK, 0 rows affected (0.00 sec)</br>session 1 >delete from user_info1 where name='BB';</br>ERROR 1205 (HY000): Lock wait timeout xceeded; try restarting transaction</br>session 1 >delete from user_info1 where level=8;</br>Query OK, 0 rows affected (0.01 sec)</br>session 1 >delete from user_info1 where level=20;</br>Query OK, 1 row affected (0.01 sec)</br>session 1 >delete from user_info1 where level=7;</br>Query OK, 1 row affected (0.00 sec)</br>session 1 >delete from user_info1 where level=2;</br>Query OK, 1 row affected (0.00 sec)</br>|此时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.间隙内或者间隙外都无法插入新数据

转载于:https://www.cnblogs.com/jenvid/p/9074780.html

a82831154
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql间隙锁
dw147258dw的专栏
06-28 5597
Mysql的间隙锁是为了在RR级别解决幻读问题而引入的，间隙锁是gap lock ,而mysql 用的是间隙锁和gap锁的结合，也就是next-key lock,而在不同的索引上，mysql加锁的方式也不一样： 唯一索引上：如果条件为=5 ，间隙锁退化为行锁，也就是只会锁住条件中的那一行对象，如果是>5，则会添加一个[5, ∞) 的一个next-key锁，5这个行锁和(5,∞)这个间隙锁 普通索引上：如果条件为5，那么mysql会通过5查询左右两边的一个间隙，也就是比5小的第一个值和比5大的第一个值，然
mysql 间隙锁原理深度详解
congge
08-30 6765
mysql 间隙锁原理深度详解
Mysql间隙锁实战
代码缔造者的博客
05-01 1050
前言 本次实战是基于数据库mysql5.7.14 什么是间隙锁？ 间隙锁是对索引记录之间间隙的锁，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如:SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 For UPDATE;防止其他事务向列t.c1插入值15，无论列中是否已经有这样的值，因为范围内所有现有值之间的差距是锁定的。 什么时候会使用间隙锁？ InnoDB 存储引擎隔离级别是RR前提下同时有事务发生。 间隙锁有什么作用？ 首先有个前提是知
452-MySQL(表锁,行锁,排他锁,共享锁,间隙锁)
LINZEYU666的博客
08-21 288
事务隔离级别的实现原理 锁+MVCC 事务-》事务的ACID特性-》事务的隔离性-》事务要能够允许并发执行-》数据的安全性，一致性和并发的效率问题-》事务的隔离级别 串行化-》锁实现，给所有的事务都排个序，并发的效率就低了，数据的安全性高。 未提交读-》没有做任何的并发控制，明知是错误的，造成脏读，数据的安全性最低。优点是并发效率非常高。 我们用的是下面这2个隔离级别： 已提交读 可重复读 结合了数据的安全性，一致性，并发的效率 ，由MVCC多版本并发控制实现的。 表级锁&行级锁 表级锁：对整张表
mysql主键查询gap锁失效_Mysql中的GAP锁(间隙锁)
weixin_31175771的博客
02-03 491
Mysql中的GAP锁(间隙锁)发布时间：2018-07-12 16:46,浏览次数：649, 标签：MysqlGAPMysql各种场景的加锁过程可以看这篇博文,讲的很细又易懂,下面大部分内容也出自这篇博文http://hedengcheng.com/?p=771#_Toc374698322以前的Mysql在repetable read级别下，还会出现幻读，现在的已经不会了，原因就是因为多了个Ga...
mysql 间隙锁(GAP-LOCK)演示
weixin_41638098的博客
07-25 1003
mysql 间隙锁(GAP-LOCK)演示
MySQL间隙锁问题
追梦人的博客
01-03 8540
间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。 最近用户反馈说系统老是出现insert时，等待超时了，最后发现是insert间隙锁！间隙锁是innodb中行锁的一种， 但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，有时候也会给我们带来麻烦
mysql间隙锁
weixin_44865424的博客
12-09 2518
关于mysql间隙锁的介绍，使用和优化介绍
MySQL间隙锁死锁问题
java_beautiful的博客
07-02 4422
当时同事A在线上代码中使用了Mybatis-plus的如下方法 com.baomidou.mybatisplus.extension.service.IService saveOrUpdate(T, com.baomidou.mybatisplus.core.conditions.Wrapper) 该方法先执行了update操作，如果更新到就不再执行后续操作，如果没有更新到，才进行主键查询，查询到了就修改，未查询到就新增。具体方法如下...
Mysql 间隙锁，next-key的原理
weixin_43063328的博客
01-22 2424
mysql的间隙锁原理 首先明确一个大前提 就是主键索引 和唯一索引是锁的当前行 而间隙锁锁定的是 索引字段 靠近左边的第一个值+1 以及靠近右边的第一个字段-1 所以 间隙锁 在很多的情况下 差不多就是行锁 可以这么主观的认为 然后还要明确一个大前提 会对什么加锁 首先where条件中的字段进行加锁 insert会加锁 看这个id是否被间隙锁给锁住。 然后还有一个地方 对于set的字段会查看要set成的值 是否被锁定。很神奇（下面找一个场景来介绍） 在还有一点就是 不...
mysql更新死锁问题
fatshaw的专栏
07-29 9407
mysql更新死锁问题 问题现象 同一时间点，一个商户下多个模板（模板id不同）同时更新； 券模板操作流水更新时发生死锁，报 Deadlock found when trying to get lock; try restarting transaction 错误 相关业务表信息 表：log_xx索引信息：log_no：分库分表全局唯一uk; merchant
未加索引的外键导致的死锁模拟实验以及问题
blockheading的博客
10-15 1192
EODA@PROD1> create table pk_t (x int primary key); --建立主角 Table created. EODA@PROD1> insert into pk_t select rownum from dual connect by level <= 40; 40 rows created. EODA@PROD1> commit; Commit
谈谈InnoDB下的记录锁，间隙锁，next-key锁
weixin_43525722的博客
12-23 695
innodb下的记录锁（也叫行锁），间隙锁，next-key锁统统属于排他锁。行锁，即记录锁，其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。就不多做介绍，主要介绍间隙锁，next-key锁。 间隙锁 间隙锁，顾名思义就是存在间隙，在间隙之间上锁保证在间隙间不可以进行操作。举一个生活中比较好理解得例子，A,B,C依次排成一排，为了让新来的D不能插在B的旁边，只要将B和A之间的空隙封锁，将B和C之间的空隙封锁，那么D就不能插在B的旁边了。他们之间的空隙也就是间隙，而封锁他们之间距离的锁，叫做间隙锁。这里
MySQL当中基于索引的加锁和死锁问题
summerZBH123的博客
07-26 3402
    在介绍MySql引擎的时候，只是大概介绍了以下InnerDB支持行级锁、表锁，MyISAM支持表锁，这篇文章主要是来介绍关于数据库什么时候加锁，加什么样的锁，还有死锁的问题。     在介绍锁的相关内容之前，需要了解下关于MySql数据库的事务级别，MySQL数据库事务的特性和隔离级别     关于数据库当中锁的分类,及不同类型的锁,他们的特点     1、行锁：真对数据库当中特定的...
MySQL 死锁套路：再来看一例走不同索引更新的例子
weixin_34115824的博客
05-25 355
前面几篇文章介绍了利用调试MySQL源码的方式来调试锁相关的信息，这里利用这个工具来解决一个比较简单的问题，线上的表字段较多，这里简单成为了一个表 CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', ...
MySql间隙锁的一个实现
qq_26093341的博客
07-02 1592
首先创建表demo:create table demo( id int, keyw varchar(20), PRIMARY KEY (id), key keyw (keyw)) 插入数据： INSERT INTO demo VALUES(1,'d1'),(5,'d1'),(2,'d2'),(6,'d2'); 开启两个事务-----------------------...
Redis基础命令和事务，redis持久化和主从复制
最新发布
baomingshu的博客
08-31 1726
nosql数据库介绍：解释为 non-relational(非关系型数据库)。在NoSQL 数据库中数据之间是无联系的数据的结构是松散的，可变的。优势：大数据量，高性能，灵活的数据模型，高可用，低成本劣势：（1）无关系，数据之间是无联系的。
数据库管理-第236期 数据库一体机的价值（20240829）
yhw1809的博客
08-29 1741
数据库一体机是硬件、操作系统和数据库之间的融合，利用更少的硬件实现小体积的高性能密度。
mysql间隙锁的范围
05-21
MySQL 间隙锁的范围是在事务中对索引记录的范围锁定，但不包括实际存在的数据记录。当事务对一个范围的索引记录进行查询、插入或更新操作时，MySQL 在该范围内的记录都会被锁定，以防其他事务同时对该范围内的记录...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值