数据库概论.陈立军.07.并发控制(2)

Posted on Edited on In Views:
并发控制、显式锁类型、各种操作下的锁授予（锁持有分析）

并发控制

显示锁类型

SQL Server 查询

  • 使用 sp_lock 或者查询 sys.dm_tran_locks 视图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE VIEW DBlocks AS
SELECT request_session_id as spid,
    db_name(resource_database_id) as dbname,
    CASE WHEN resource_type='OBJECT'
            THEN object_name(resource_associated_entity_id)
        WHEN resource_associated_entity_id=0
            THEN'n/a'
        ELSE object_name(p.object_id) END as entity_name,
    index_id,resource_type as resource, resource_description as description,
    request_mode as mode,
    request_status as status
FROM sys.dm_tran_locks t LEFTJOIN sys.partitions p
    ON p.hobt_id = t.resource_associated_entity_id
WHERE resource_database_id=db_id()

repeatable read

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
USE student

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT *
FROM S
WHERE AGE>20

/* 查询当前进程的锁 */
SELECT *
FROM Dblocks
WHERE spid=@@spid
AND entity_name='S'

COMMIT TRAN

read committed

  • read committed 是短锁，执行完就释放了
1
2
3
4
5
6
7
8
9
10
11
12
13
USE student

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

SELECT *
FROM S
WHERE AGE>20

EXEC sp_lock@@spid

COMMIT TRAN
  • 只有数据库上有一个读锁
spid Database ObjId IndId Type Resource Mode Status
51 student 0 0 DB S GRANT

repeatabel read

  • 长锁
1
2
3
4
5
6
7
8
9
10
11
12
13
USE student

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT *
FROM S
WHERE AGE>20

EXEC sp_lock @@spid

COMMIT TRAN
  • 自己加读锁，祖先节点加意向读锁
    • 聚簇索引，主码上有索引，KEY 来标一条记录
spid Database ObjId IndId Type Resource Mode Status
51 student 0 0 DB S GRANT
51 student 2025058250 2 PAG 1:31 IS GRANT
51 student 2025058250 1 PAG 1:30 IS GRANT
51 student 2025058250 1 KEY (930085dfee46) S GRANT
51 student 2025058250 0 TAB IS GRANT
51 student 2025058250 2 KEY (a8007c139223) S GRANT
51 student 2025058250 2 KEY (ab008cdbe580) S GRANT
51 student 2025058250 1 KEY (930022f0d614) S GRANT

serializable

1
2
3
4
5
6
7
8
9
10
11
12
13
USE student

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT *
FROM S
WHERE AGE>20

EXEC sp_lock@@spid

COMMIT TRAN
  • 为了防止幻象，得加码范围锁
spid Database ObjId IndId Type Resource Mode Status
51 student 0 0 DB S GRANT
51 student 2025058250 2 PAG 1:31 IS GRANT
51 student 2025058250 1 PAG 1:30 IS GRANT
51 student 2025058250 1 KEY (930085dfee46) S GRANT
51 student 2025058250 0 TAB IS GRANT
51 student 2025058250 2 KEY (a8007c139223) RangeS-S GRANT
51 student 2025058250 2 KEY (ab008cdbe580) RangeS-S GRANT
51 student 2025058250 2 KEY (ffffffffffff) RangeS-S GRANT
51 student 2025058250 1 KEY (930022f0d614) S GRANT

SQL Server 更新

read committed

1
2
3
4
5
6
7
8
9
10
11
12
USE student

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

UPDATE S
SET SEX=0
WHERE AGE>20
EXEC sp_lock@@spid

ROLLBACK TRAN
spid Database ObjId IndId Type Resource Mode Status
52 student 0 0 DB S GRANT
52 student 2025058250 1 PAG 1:30 IX GRANT
52 student 2025058250 1 KEY (930085dfee46) X GRANT
52 student 2025058250 0 TAB IX GRANT
52 student 2025058250 1 KEY (930022f0d614) X GRANT

SQL Server 创建表

read committed

1
2
3
4
5
6
7
8
9
10
11
12
13
14
USE student

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

SELECT *
INTO OldS
FROM S
WHERE AGE>20

EXEC sp_lock2@@spid

COMMIT TRAN
  • 创建表加写锁
  • Sch-M 模式修改锁，避免在建表的时候其他事务访问它
spid Database ObjId IndId Type Resource Mode Status
54 student 0 0 EXT 1:112 X GRANT
54 student 0 0 PAG 1:127 X GRANT
54 student 0 0 PAG 1:124 X GRANT
54 student 0 0 PAG 1:125 X GRANT
54 student 0 0 PAG 1:122 X GRANT
54 student 0 0 PAG 1:123 X GRANT
54 student 0 0 PAG 1:120 X GRANT
54 student 0 0 PAG 1:121 X GRANT
54 student 2041058307 0 TAB Sch-M GRANT

SQL Server 行锁

read committed

1
2
3
4
5
6
7
8
9
10
11
12
USE student

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN
UPDATE OldS
SET SNAME='王非'
WHERE S# = 's1'

EXEC sp_lock@@spid

COMMIT TRAN
  • SQL Server 没有指定主码，则不会建立聚簇索引，是一个无序的堆表
    • Row ID
      • 文件号：文件里面的页面号：页面内的偏移
    • MySQL 会默认有一个自增的字段
spid Database ObjId IndId Type Resource Mode Status
52 student 0 0 DB S GRANT
52 student 2073058421 0 PAG 1:96 IX GRANT
52 student 2073058421 0 RID 1:96:0 X GRANT
52 student 2073058421 0 TAB IX GRANT

MySQL 的锁字典表

innodb_trx

字段名 说明
trx_id 事务id
trx_state 事务状态
trx_started 事务开始时间
trx_requested_lock_id 等待事务的锁id
trx_wait_started 事务等待开始的时间
trx_weight 事务的权重，锁住的行数，死锁时回滚权重最小的事务
trx_mysql_thread_id 线程id
trx_query 事务运行的sql语句

innodb_locks

字段名 说明
lock_id 锁id
lock_trx_id 事务id
lock_mode 锁模式
lock_type 锁类型，表锁还是行锁
lock_table 锁定的表
lock_index 锁定的索引
lock_space 锁对象的space id
lock_page 锁定的页
lock_rec 锁定的行
lock_data 锁定行的主码

innodb_lock_waits

字段名 说明
requesting_trx_id 申请锁资源的事务id
requesting_lock_id 事务正在申请的锁的id
blocking_trx_id 阻塞的事务id
blocking_lock_id 阻塞的锁的id

各种操作下的锁授予

  • 以查询为例
    • 当你发出一个查询请求之后，数据库会先经过一个查询优化器，确定一个执行计划（直接扫描表/借助某个索引），系统会对执行计划的每一个路径加锁

聚簇表和堆表的不同（SQL Server）

  • ClusterOrders：基于 OrderID 的聚簇表
    • 在 ClusterOrders 的 ShipName 上建有非聚簇索引
  • HeapOrders：堆表
    • 在 HeapOrders 的 EmployeeID 上建有非聚簇索引

查询

聚簇表：基于聚簇索引的查找
  • 索引
1
2
3
select *
from ClusterOrders
where OrderID=10250

堆表：基于非索引项的查找
  • RID
1
2
3
select *
from HeapOrders
where OrderID=10250

堆表：基于非聚簇索引的查找
1
2
3
select *
from HeapOrders
where EmploeeID = 5
  • 执行计划如下
    • 先在非聚簇索引上查找，找到满足条件的索引码之后再到原表中查找
    • 现在满足条件的非聚簇索引上加锁，然后在 RID 上加锁

  • 当修改查询语句如下时，查询计划改变了
    • 为什么？
    • 系统发现在索引中有很多满足条件的项，基于索引反而是低效的
    • 转变为基于表扫描的查找
1
2
3
select *
from HeapOrders
where EmploeeID=4

删除

堆表：删除行操作（非索引）
  • RID 加写锁
1
2
delete from HeapOrders
where orderID=10253

堆表：删除行操作（索引、非聚簇索引）
  • 索引码、原表中对应的行都得加写锁
1
2
delete from HeapOrders
where EmployeeID = 9

更新

堆表：基于非聚簇索引的更新操作
  • 索引、RID 加写锁
1
2
3
update HeapOrders
set ShipVia = 3
where EmployeeID = 5

聚簇表：更新操作
  • 只有索引表需要加锁
    • 因为 ShipName 上有非聚簇索引，同时需要给这些记录加写锁
1
2
3
update ClusterOrders
set ShipName = 'tatatata'
where OrderID = 10255

缺少索引而引起的阻塞（SQL Server）

  • 建立索引能够减少减少阻塞的情况，能够提高索引的并发度

没有索引的情况

  • 建立两个表 T1、T2
1
2
3
4
create table T1(id int, col1 char(10))
create table T2(id int, col2 char(10))
insert into T1 values (101,'A'), (102,'B'), (103,'C')
insert into T2 values (201,'A'), (202,'B'), (203,'C')
  • 事务 1
1
2
3
4
begin tran
update T1
set col1 = 'a'
where id = 101
  • 事务 2
1
2
3
4
begin tran
update T1
set col1 = 'a'
where id = 103
  • 实际过程中，事务 2 被事务 1 阻塞了
    • 事务 1 加了写锁，更新事务在更新完整个表之后才会释放所有的锁
      • 这样的原因如下
        • 举个例子，如果更新完一条记录就释放这条记录上的锁，事务 1 更新完 A 记录后马上释放锁，此时事务 2 也更新 A 记录，但是事务 2 出现错误，需要回滚，此时出现问题，回滚之后把事务 2 的更新也回滚掉了
      • SQL Server 的更新操作
        • 先把所有满足条件的行找出来，此时会申请 U 锁（先读后写），满足条件的行被更新时升级为写锁
    • 事务 2 在通过 where 查询整个表的时候，查询被事务 1 锁住的记录的时候，被阻塞
  • 输出两个进程持有锁的情况
1
2
3
4
5
/* 事务 1 */
SELECT *
FROM Dblocks
WHERE spid=52
AND entity_name='T1'

1
2
3
4
5
/* 事务 2 */
SELECT *
FROM Dblocks
WHERE spid=54
AND entity_name='T1'

  • 注意上面事务 2 的行探查阶段申请的是 U 锁

在 id 上建立索引

  • 在更新的时候不需要扫描所有行，可以通过索引直接定位到行
1
create index idx_T1_id on T1(id)
  • 此时不会出现事务 2 被事务 1 挂起的情况，锁持有情况如下
    • 先加 U 锁，更新的时候再升级为写锁

MySQL 的当前读

  • 当前读：特殊的读操作，需要加锁
1
2
3
4
5
6
7
8
9
select * from table where ? lock in share mode

select * from table where ? for update

insert into table values(...)

update table set ? where ?

delete from table where ?

MySQL 加锁处理分析

1
2
3
4
5
/* SQL1 */
select * from t1 where id=10;

/* SQL2 */
delete from t1 where id=10;
  • 前提
    • 前提一：id列是不是主键？
    • 前提二：当前系统的隔离级别是什么？
    • 前提三：id列如果不是主键，那么id列上有索引吗？
    • 前提四：id列上如果有二级索引，这个索引是唯一索引吗？
    • 前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？
  • 通过对前提的不同组合会有不同的结果
  • 组合
    • 组合一：id列是主键，RC隔离级别
    • 组合二：id列是二级唯一索引，RC隔离级别
    • 组合三：id列是二级非唯一索引，RC隔离级别
    • 组合四：id列上没有索引，RC隔离级别
    • 组合五：id列是主键，RR隔离级别
    • 组合六：id列是二级唯一索引，RR隔离级别
    • 组合七：id列是二级非唯一索引，RR隔离级别
    • 组合八：id列上没有索引，RR隔离级别
    • 组合九：Serializable隔离级别

组合一：id主键+RC

组合二：id唯一索引+RC

组合三：id非唯一索引+RC

组合四：id无索引+RC

组合七：id非唯一索引+RR

组合八：id无索引+RR