数据库并发处理-锁

作者: 邵增卫 | 来源:发表于2021-10-23 12:02 被阅读0次

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

乐观锁的实现一般有以下两种方式：
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                     |
+-----------------------+---------------+----------------------+------------+-----------+---------------+-------------+------------------------+

数据库并发处理-锁

主键查询（聚族索引）锁

非主键（辅助索引）锁

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读