并行性是指可以同时由多个交互式用户或应用程序共享资源。
DB2并发控制介绍
高并发需要解决问题
丢失更新
访问未落实的数据
不可重复读
幻像读
通常并发控制实现
多版本并发控制（Multi-version Concurrency Control, MVCC）
严格两阶段锁定（Strict Two-Phase Locking, S2PL）
和乐观并发控制（Optimistic Concurrency Control, OCC）
不同实现带来几个问题：读是否阻塞写，写是否阻塞读，写是否阻塞写
不讨论DB2并发控制实现，反正没看到MVCC，OCC
DB2隔离级别
可重复读 (RR)
读稳定性 (RS)
游标稳定性 (CS)
未落实的读 (UR)
注：
RR:程序在同一个工作单元中(事务)发出 SELECT 语句两次，那么每次将返回相同的结果
在该 UOW 完成之前，其他应用程序均无法更新、删除或插入将会影响结果集的行
所引用的每一行都将被锁定，而不仅仅是锁定所检索的行
容易超出 locklist 和 maxlocks 数据库配置参数所指定的限制，锁定升级
对于 RR 而言，不可能出现丢失更新、访问未落实的数据、不可重复读以及幻像读
RS:读稳定性隔离级别只锁定应用程序在工作单元运行期间检索的那些行
对于 RS 而言，不可能出现访问未落实的数据以及不可重复读等情况,有可能进行幻像读
CS 是缺省隔离级别，执行2阶段锁；访问时加锁，访问下一行时锁收缩释放。
事务执行期间所访问的任何行上时锁定该行。此锁定在下一行被访存或者事务终止之前将保持有效。
UR:未落实的读隔离级别允许应用程序访问其他事务未落实的更改
在 V9.7 所引入的当前已落实语义下，将像以前那样只返回已落实的数据，但读取者现在不等待更新者释放行锁定。而是，读取者返回基于当前已落实版本的数据；即，写操作启动前的数据 。即返回前一版本，不可等同于MVCC
| 隔离级别 | 访问未落实的数据 | 不可重复读 | 幻像读 |
|---|---|---|---|
| 可重复读 (RR) | 不可能 | 不可能 | 不可能 |
| 读稳定性 (RS) | 不可能 | 不可能 | 可能 |
| 游标稳定性 (CS) | 不可能 | 可能 | 可能 |
| 未落实的读 (UR) | 可能 | 可能 | 可能 |
DB2 并发控制验证
数据准备
CREATE TABLE t(
id int NOT NULL,
c int DEFAULT NULL,
d int DEFAULT NULL,
PRIMARY KEY (id)
) ;
create index idx_c on t(c);
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
runstats on table t with distribution and detailed indexes all ;
设置隔离级别为RR
db2 set CURRENT ISOLATION=RR
db2 values current isolation
db2 Select current isolation from sysibm.sysdummy1
数据：
db2 "select * from t with ur "
ID C D
----------- ----------- -----------
0 0 0
5 5 5
10 10 10
15 15 15
20 20 20
25 25 25
等值条件操作间隙（RR）
更新一条不存在的记录，还是持有下一个键值的X锁
session A 持有 02000400060000000000000052 U锁
session B 持有 020004000A0000000000000052 X锁 等待 02000400060000000000000052 NW锁
session C 等待 02000400060000000000000052 X锁
在 RR 扫描期间，将以 S 方式锁定与跟在扫描范围末尾后面的键相对应的行。如果没有任何键跟在扫描范围末尾后面，那么将获取表结束锁定以锁定索引的末尾
| sessionA | sessionB | sessionC | |
| T1 | db2 +c “update t set d=d+1 where id=7” | ||
| T2 | db2 +c “insert into t values(8,8,8)” | ||
| T3 | db2 +c "update t set d=d+1 where id=10 " | ||
| T4 |
session B ,session C 阻塞
T4时刻查看锁等待：sessionA->sessionB->sessionC
db2pd -d testdb -wlocks
Database Member 0 -- Database TESTDB -- Active -- Up 0 days 13:16:58 -- Date 2024-07-27-16.53.01.504975
Locks being waited on :
AppHandl [nod-index] TranHdl Lockname Type Mode Conv Sts CoorEDU AppName AuthID AppID
1033 [000-01033] 3 02000400060000000000000052 RowLock ..U G 77 db2bp DB2INST1 *LOCAL.db2inst1.240727080001
1044 [000-01044] 13 02000400060000000000000052 RowLock .NW W 80 db2bp DB2INST1 *LOCAL.db2inst1.240727080610
1045 [000-01045] 12 02000400060000000000000052 RowLock ..X W 79 db2bp DB2INST1 *LOCAL.db2inst1.240727080620
查看sessionA持锁情况
db2pd -d testdb -locks
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x00007FC9B8E6C380 12 01000000020000000100406ED6 VarLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007FC9B8E6FB00 13 010000000300000001000067D6 VarLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007FC9B8E79900 3 02000400060000000000000052 RowLock ..U G 3 1 0 0x00000010 0x40000000 1
0x00007FC9B8E70800 13 02000400060000000000000052 RowLock .NW W 3 0 0 0x00000000 0x00000000 0
0x00007FC9B8E75380 12 02000400060000000000000052 RowLock ..X W 3 0 0 0x00400000 0x00000000 0
0x00007FC9B8E6BF80 13 020004000A0000000000000052 RowLock ..X G 3 1 0 0x00200008 0x40000000 0
0x00007FC9B8E79700 3 4141414141664164FE8BC716C1 PlanLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007FC9B8E6C980 13 4141414141664164FE8BC716C1 PlanLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007FC9B8E72D00 12 4141414141664164FE8BC716C1 PlanLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007FC9B8E78500 12 02000400000000000000000054 TableLock .IX G 3 1 0 0x00203000 0x40000000 0
0x00007FC9B8E6E180 3 02000400000000000000000054 TableLock .IX G 3 1 0 0x00202000 0x40000000 0
0x00007FC9B8E6C900 13 02000400000000000000000054 TableLock .IX G 3 1 0 0x00203000 0x40000000 0
查看具体lockname,可以看到在表t上id=10加了锁
db2 -x " SELECT SUBSTR(NAME,1,18) ,SUBSTR(VALUE,1,18)
FROM TABLE( MON_FORMAT_LOCK_NAME('02000400060000000000000052')) as LOCK with ur"
ROWID 6
DATA_PARTITION_ID 0
PAGEID 0
TBSP_NAME USERSPACE1
TABSCHEMA DB2INST1
TABNAME T
rid()=ROWID+PAGEID*65536 (large数据表空间）
或者rid()=ROWID+PAGEID*256 （regular数据表空间）
db2 "select * from t where rid()=6 with ur"
ID C D
----------- ----------- -----------
10 10 10
非唯一索引等值锁（RR）
| sessionA | sessionB | sessionC | |
| T1 | db2 +c “select id from t where c=5” | ||
| T2 | db2 +c “update t set d=d+1 where id=5” | ||
| T3 | db2 +c “insert into t values(7,7,7)” | ||
| T4 |
查看锁等待
可以看到sessionA 持有2个行锁 分别为id=10 ,id =5两条记录
02000400060000000000000052 S
02000400050000000000000052 S
sessionB 等待 02000400050000000000000052 X 即等待id=5
sessionC 等待 02000400060000000000000052 NW 即等待id=6
可以得到1个小的结论：非主键索引查询，会锁定记录本身和下一键；这点与MYSQL类似
查询也会阻塞写，读阻塞写（这问题有点大）
db2pd -d testdb -wlocks
Locks being waited on :
AppHandl [nod-index] TranHdl Lockname Type Mode Conv Sts CoorEDU AppName AuthID AppID
1033 [000-01033] 3 02000400060000000000000052 RowLock ..S G 77 db2bp DB2INST1 *LOCAL.db2inst1.240727080001
1045 [000-01045] 12 02000400060000000000000052 RowLock .NW W 79 db2bp DB2INST1 *LOCAL.db2inst1.240727080620
1033 [000-01033] 3 02000400050000000000000052 RowLock ..S G 77 db2bp DB2INST1 *LOCAL.db2inst1.240727080001
1044 [000-01044] 13 02000400050000000000000052 RowLock ..X W 80 db2bp DB2INST1 *LOCAL.db2inst1.240727080610
DB2V9.7对锁改进点： cur_commit -“当前已落实”，但控制的是游标稳定性 (CS) 扫描的行为
所以调整隔离级别到CS来测试一下
确认cur_commit已开启
db2 get db cfg |grep -i cur
Currently Committed (CUR_COMMIT) = ON
确认db2set相关配置开启
[db2inst1@VM-4-7-centos ~]$ db2set -i |grep -i DB2_EVALUNCOMMITTED
DB2_EVALUNCOMMITTED=YES
[db2inst1@VM-4-7-centos ~]$ db2set -i |grep -i DB2_SKIPDELETED
DB2_SKIPDELETED=ON
[db2inst1@VM-4-7-centos ~]$ db2set -i |grep -i DB2_SKIPINSERTED
DB2_SKIPINSERTED=ON
确认已经开启CS隔离级别
db2 set CURRENT ISOLATION=CS
db2 values current isolation
db2 Select current isolation from sysibm.sysdummy1
等值条件（CS）
| sessionA | sessionB | sessionC | |
| T1 | db2 +c “update t set d=d+1 where id=7” | ||
| T2 | db2 +c “insert into t values(8,8,8)” | ||
| T3 | db2 +c "update t set d=d+1 where id=10 " | ||
| T4 |
锁等待情况
无锁等待记录
db2pd -d testdb -wlocks
持锁情况
sessionA未访问到记录，不加锁
session B 持有 020004000A0000000000000052 X rowid=10 即id=8记录
session c 持有 02000400060000000000000052 X 锁 rowid=6 即id=10记录
CS模式下不持有间隙锁
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x00007FC9B8E76A80 3 02000400060000000000000052 RowLock ..X G 3 1 0 0x00200000 0x40000000 0
0x00007FC9B8E70100 12 020004000A0000000000000052 RowLock ..X G 12 1 0 0x00200008 0x40000000 0
0x00007FC9B8E6F580 13 4141414141664164FE8BC716C1 PlanLock ..S G 13 1 0 0x00000000 0x40000000 0
0x00007FC9B8E6A880 12 4141414141664164FE8BC716C1 PlanLock ..S G 12 1 0 0x00000000 0x40000000 0
0x00007FC9B8E79F00 3 4141414141664164FE8BC716C1 PlanLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007FC9B8E77200 12 02000400000000000000000054 TableLock .IX G 12 1 0 0x00202000 0x40000000 0
0x00007FC9B8E75C80 3 02000400000000000000000054 TableLock .IX G 3 1 0 0x00203000 0x40000000 0
非唯一索引（CS）
| sessionA | sessionB | sessionC | |
| T1 | db2 +c “select id from t where c=5” | ||
| T2 | db2 +c “update t set d=d+1 where id=5” | ||
| T3 | db2 +c “insert into t values(7,7,7)” | ||
| T4 |
查看锁等待
db2pd -d testdb -wlocks
查看持锁情况
查询可知
session A 未持行锁
session B 持有 020004000A0000000000000052 X rowid=10 即id=8记录
session c 持有 02000400060000000000000052 X 锁 rowid=6 即id=10记录
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x00007FC9B8E71800 3 020004000A0000000000000052 RowLock ..X G 3 1 0 0x00200008 0x40000000 0
0x00007FC9B8E78080 12 02000400050000000000000052 RowLock ..X G 12 1 0 0x00200000 0x40000000 0
0x00007FC9B8E6FF80 13 4141414141664164FE8BC716C1 PlanLock ..S G 13 1 0 0x00000000 0x40000000 0
0x00007FC9B8E6A880 12 4141414141664164FE8BC716C1 PlanLock ..S G 12 1 0 0x00000000 0x40000000 0
0x00007FC9B8E6F500 3 4141414141664164FE8BC716C1 PlanLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007FC9B8E77200 12 02000400000000000000000054 TableLock .IX G 12 1 0 0x00203000 0x40000000 0
0x00007FC9B8E6E200 3 02000400000000000000000054 TableLock .IX G 3 1 0 0x00202000 0x40000000 0
总结
1.db2 读会阻塞写
2.CS 隔离级别下：只是锁当前行，执行下一行即会释放，且CUR_COMMIT优化了读前镜像即前一版本避免过多锁
3.RS隔离级别下：符合查询条件的还是会加NS锁，阻塞更新
4.只有RR隔离级别会加间隙锁
5.索引对加锁有影响的只是RR隔离级别，如等值查询会继续锁定到不满足条件的下一行。CS,RS是按当前执行/返回匹配的记录来加锁，索引影响不大，加速过程是二阶段，先加锁然后收缩。
