数据库并发处理-锁

数据并发处理主要有两种方式乐观锁和悲观锁。
乐观锁 ：是假设并发事务处理时彼此不会相互影响，各种事务能在不产生锁的情况下处理各自影响的那一部分数据；数据在更新时对数据进行检查，其他事务是否更新了该数据，如果更新过则回退，如果没有更新则正常更新。所以乐观锁是一种数据并发处理方法，没有利用数据库提供的锁机制，需要业务实现控制数据并发处理。（乐观锁比较适合读场景比较多，写操作比较少的场景）

乐观锁的实现一般有以下两种方式：
1、数据创建时添加一个version字段

  # 创建表
  create table student_info(id int primary key, name varchar(10), age int, version int);

 # 逻辑操作
 # 1、 读取数据
 select id, name, age, version from student_info where id=${id}

 # 2、更新数据 如果其他事务没有修改数据，则修改成功，否则修改失败
 update student_info set name=${name}, age=${age} , version=version+1 where id=${id} and version=${version}

2、数据库创建时添加update_time（精确到毫秒）字段,处理逻辑与上面逻辑大致相同

悲观锁 : 在数据并发处理时，利用数据库提供的锁机制对数据加锁，阻止其他事务对其进行修改，只有当该事务释放该锁之后其他事务才能继续数据处理。

MySQL数据库一般提供两种锁，读锁（共享锁）、写锁（排它锁）：

读锁（共享锁）： A事务获取了表A'的共享锁，则其他的事务依然可以读取该数据（即获取表A的共享锁），但是不能对该数据进行修改（即获取表A的排它锁）
写锁（排它锁）： A事务获取了表A'的写锁之后，其他事务不能对表A'做任何锁操作。
说明：读锁会阻塞其他事务的写操作，但不阻塞读操作；写锁会阻塞其他事务的读写操作

行锁和表锁
行锁(Record Lock): 针对某行数据加锁，加锁方式select * from test where id=1 for update/lock in share mode 释放锁commit、rollback、kill阻塞进程
Mysql中InnoDB引擎支持行锁，MyISAM引擎不支持行锁。
经过测试无论查询条件是否使用聚族索引，外面很多资料说不使用聚族索引就加表锁的说法是错误的。

表锁(Table Lock): 针对某一个表加锁,加锁方式lock table test write/read;，释放锁unlock tables; unlock table test
** 在一个事务中不会同时持有两个表锁，在授予第二个表锁的时候，隐式释放已经持有的表锁。

行锁的实现方式有三种Record Lock、Gap Lock、 Next-Key Lock三种
行锁（Record Lock）：直接对某一行数据加锁就叫行锁（例如： select * from test where id=1就是对ID=1的记录添加了行锁）

间隙锁（Gap Lock）: 是为了防止事务级别在可重复读（read-repeat）的情况下，出现幻读问题。

Next-Key Lock: 是存储引擎innoDB在事务级别可重复读（read-repeat）的情况下的数据库锁，是行锁和间隙锁的组合，

下面演示一下这三种锁是如何工作的，所有操作均基于假设存在如下数据,并且隔离等级是repeatable-read.

CREATE TABLE `gap_table` (
  `id` varchar(2) NOT NULL,
  `count` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `gap_table_index` (`count`)
) ENGINE=InnoDB DEFAULT

insert into gap_table(id, count) values('a', 1);
insert into gap_table(id, count) values('d', 4);
insert into gap_table(id, count) values('g', 10);

主键查询（聚族索引）锁

1、主键等值查询，数据存在时，会对该主键的值加行锁

# session A
begin
select * from gap_table where id='a' for update;，

select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| lock_test     | gap_table   | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| lock_test     | gap_table   | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 'a'       |
+---------------+-------------+------------+-----------+---------------+-------------+-----------+

对表gap_table添加意向排它锁（IX），对数据a添加行锁（X,REC_NOT_GAP）

2、主键等值查询，数据不存在时，对该间隙加锁

#session A
begin;
select * from gap_table where id='c' for update;

select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| lock_test     | gap_table   | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| lock_test     | gap_table   | PRIMARY    | RECORD    | X,GAP     | GRANTED     | 'd'       |
+---------------+-------------+------------+-----------+-----------+-------------+-----------+

对表gap_table添加意向排它锁（IX），对数据（a~d）添加间隙锁（X,GAP）

3、主键区间查询,比较复杂，这里以8.18版本以后版本说明

begin;
select * from gap_table where id between 'e' and 'f' for update;

select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| lock_test     | gap_table   | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| lock_test     | gap_table   | PRIMARY    | RECORD    | X,GAP     | GRANTED     | 'g'       |
+---------------+-------------+------------+-----------+-----------+-------------+-----------+

对（d,g）间隙加锁（X,GAP）

select * from gap_table where id between 'e' and 'g' for update;

select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| lock_test     | gap_table   | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
| lock_test     | gap_table   | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
| lock_test     | gap_table   | PRIMARY    | RECORD    | X         | GRANTED     | 'g'                    |
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+

select * from gap_table where id > 'e' for update;  

select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| lock_test     | gap_table   | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
| lock_test     | gap_table   | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
| lock_test     | gap_table   | PRIMARY    | RECORD    | X         | GRANTED     | 'g'                    |
+---------------+-------------+------------+-----------+-----------+-------------+------------------------+

对gap_table表的主键加了一个（supremum pseudo-record）锁(个人理解是最大值以上的开区间锁)，对数据(d, g]加行锁nextkey-lock（X）;
但是经过测试对(d, +∞）区间都加锁

select * from gap_table where id < 'f' for update; 

select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+---------------+-------------+-----------------+-----------+---------------+-------------+------------------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME      | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |
+---------------+-------------+-----------------+-----------+---------------+-------------+------------------------+
| lock_test     | gap_table   | NULL            | TABLE     | IX            | GRANTED     | NULL                   |
| lock_test     | gap_table   | gap_table_index | RECORD    | X             | GRANTED     | supremum pseudo-record |
| lock_test     | gap_table   | gap_table_index | RECORD    | X             | GRANTED     | 4, 'd'                 |
| lock_test     | gap_table   | gap_table_index | RECORD    | X             | GRANTED     | 10, 'g'                |
| lock_test     | gap_table   | gap_table_index | RECORD    | X             | GRANTED     | 12, 'a'                |
| lock_test     | gap_table   | PRIMARY         | RECORD    | X,REC_NOT_GAP | GRANTED     | 'a'                    |
| lock_test     | gap_table   | PRIMARY         | RECORD    | X,REC_NOT_GAP | GRANTED     | 'd'                    |
| lock_test     | gap_table   | PRIMARY         | RECORD    | X,REC_NOT_GAP | GRANTED     | 'g'                    |
+---------------+-------------+-----------------+-----------+---------------+-------------+------------------------+

首先对标中的数据a d g都加了行锁，其次在gap_table_index 所以中加NextKey-Lock(相当于锁住了整个gap_table_index 索引的所有区间)

非主键（辅助索引）锁

添加如下数据

create table secondary_index_lock(id int primary key, no int unique, name varchar(2), age int);

insert into secondary_index_lock(id, no, name, age) values(1, 10, 'z1', 10), (2, 20, 'z2', 20), (3, 30, 'z3', 30), (4, 40, 'z4', 40),(9,90, 'z9',90);

1、非主键唯一索引等值查询，数据存在，for update 在主键加锁，for share仅仅在自己索引上加锁

select * from secondary_index_lock where no=20 for update/ lock in share mode;

 select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME          | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+-----------+
|                  2007 | lock_test     | secondary_index_lock | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                  2007 | lock_test     | secondary_index_lock | no         | RECORD    | X,REC_NOT_GAP | GRANTED     | 20, 2     |
|                  2007 | lock_test     | secondary_index_lock | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+-----------+

在主键索引和唯一索引上对该数据加行锁(排他/共享)

2、非主键索引等值查询，数据不存在，无论是否索引覆盖，相当于一个范围查询，仅仅会在非主键索引上加间隙锁

select * from secondary_index_lock where name = 'z5' for update;

select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME          | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
|                  2027 | lock_test     | secondary_index_lock | NULL       | TABLE     | IX        | GRANTED     | NULL      |
|                  2027 | lock_test     | secondary_index_lock | name_index | RECORD    | X,GAP     | GRANTED     | 'z9', 9  |
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+

3、非主键唯一索引范围查询时，不是覆盖索引的时候，会对相应范围内加间隙锁，并且如果存在数据，会对对应的主键加锁

select * from secondary_index_lock where no=25 for update;

select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME          | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+
|                  2008 | lock_test     | secondary_index_lock | NULL       | TABLE     | IX        | GRANTED     | NULL      |
|                  2008 | lock_test     | secondary_index_lock | no         | RECORD    | X,GAP     | GRANTED     | 30, 3     |
+-----------------------+---------------+----------------------+------------+-----------+-----------+-------------+-----------+

在no索引上加间隙锁 （20， 30）

4、非主键唯一索引范围查询时，如果是覆盖索引时，会对所有的后闭区间对应的主键加行锁

select * from secondary_index_lock where no > 25 for update;

select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME          | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+------------------------+
|                  2029 | lock_test     | secondary_index_lock | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
|                  2029 | lock_test     | secondary_index_lock | no         | RECORD    | X             | GRANTED     | supremum pseudo-record |
|                  2029 | lock_test     | secondary_index_lock | no         | RECORD    | X             | GRANTED     | 30, 3                  |
|                  2029 | lock_test     | secondary_index_lock | no         | RECORD    | X             | GRANTED     | 40, 4                  |
|                  2029 | lock_test     | secondary_index_lock | no         | RECORD    | X             | GRANTED     | 100, 10                |
|                  2029 | lock_test     | secondary_index_lock | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3                      |
|                  2029 | lock_test     | secondary_index_lock | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4                      |
|                  2029 | lock_test     | secondary_index_lock | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 10                     |
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+------------------------+

最后编辑于：2021.10.28 23:17:57

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,732评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,496评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,264评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,807评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,806评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,675评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,029评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,683评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 41,704评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,666评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,773评论 1赞 332
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,413评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,016评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,978评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,204评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,083评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,503评论 2赞 343

数据库并发处理-锁

主键查询（聚族索引）锁

非主键（辅助索引）锁

推荐阅读更多精彩内容