db2频现“锁等待”或“911”错误–案例分析之原因及处理方法
星期日, 2009-07-26 | Author: Lee | Database, JAVA-and-J2EE | 14,476 views
为什么db2频现“锁等待”或“911”错误
—开始以为911不会引发什么严重的问题，然事实上情况要比我想象的严重，不单纯是拆除事务的原因了，要处理对应的信息了，下列的分析对自己有很大帮助，特此摘录，也感谢撰写本文的原作者。
——————以下为摘录——————————————————————————
大家都知道，多个事务同时更新同一个数据行时必定要发生锁等待。虽然这个是造成锁等待或锁超时的原因，但不是全部。有不少同事在开发过程中发现这样的问题：自己只做一个根据主键update语句，就更新一行，且确知没有别人在更新这行，为什么语句迟迟没反应，想死锁了一样。本人通过几次尝试和试验发现了“秘密”。即db2的严重影响并发性能的地方：
1、无索引，relation scan 锁等待；
2、有索引，update时索引不能并发访问，需“串行”独占访问；
3、锁升级，行锁升级为表锁，阻塞其他事务的行级更新。
试验环境：
db2 v8系列或v9.1.4
db2命令行
主要步骤:
D:\>db2 create table t(id numeric not null,id2 numeric not null,name varchar(10),constraint t_p primary key(id,id2))
DB20000I SQL 命令成功完成。
D:\>db2 insert into t values(1,1,’chennan’),(1,2,’dba’),(1,3,’spsoft’),(2,1,’hubert’),(2,2,’nj_dba’),(2,3,’gdsy’)
DB20000I SQL 命令成功完成。
D:\>db2 select * from t
ID ID2 NAME
——- ——- ———-
1. 1. chennan
1. 2. dba
1. 3. spsoft
2. 1. hubert
2. 2. nj_dba
2. 3. gdsy
6 条记录已选择。
开两个db2命令行窗口，模拟两个事务，考虑到最大并发性能，将这两个事务的隔离级别设为UR；且取消“自动提交”选项，以模拟长事务：
db2 => update command options using c off
DB20000I UPDATE COMMAND OPTIONS 命令成功完成。
db2 => set current isolation = ur
DB20000I SQL 命令成功完成。
================================================================
从前面表的定义，看出此表有主键，即在主键上有索引。下面看看这个索引对更新语句的影响：
事务一：
db2 => update t set name=’eee’ where id=1 and name=’chennan’
DB20000I SQL 命令成功完成。
db2 =>
事务二：
db2 => update t set name=’fff’ where id=1 and name=’spsoft’
现象是事务二的语句被阻塞，一直等待，如果数据库参数LOCKTIMEOUT=-1，只一直等待下去；如果是指定一个时间，则到时间会报“911”错误。
从更新内容上看，这两条语句更新不同的数据行，“应该”不会互相影响的，但事实上确实发生锁等待事件了。
开第三个db2命令行窗口，监视事务一、事务二的sql语句的执行路径相同：
Access Table Name = CWGLADM.T ID = 2,4
| Index Scan: Name = CWGLADM.T_P ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ID (Ascending)
| | | 2: ID2 (Ascending)
| #Columns = 0
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: 00001
| | Stop Key: Inclusive Value
| | | | 1: 00001
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Exclusive
| | Row : Update
| Sargable Predicate(s)
| | #Predicates = 1
Update: Table Name = CWGLADM.T ID = 2,4
看出是根据主键索引执行更新的。但问题恰恰出现在索引上！笔者试过把更新语句的where条件写成引用主键所有字段的形式，即
事务一中写成where id=1 and id2=1 and name=’chennan’；
事务二中写成where id=1 and id2=3 and name=’spoft’；
则两个事务不会阻塞，其执行路径为：
Access Table Name = CWGLADM.T ID = 2,4
| Index Scan: Name = CWGLADM.T_P ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ID (Ascending)
| | | 2: ID2 (Ascending)
| #Columns = 0
| Single Record
| Fully Qualified Unique Key
| #Key Columns = 2
| | Start Key: Inclusive Value
| | | | 1: 00001
| | | | 2: 00001
| | Stop Key: Inclusive Value
| | | | 1: 00001
| | | | 2: 00001
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Exclusive
| | Row : Update
| Sargable Predicate(s)
| | #Predicates = 1
Update: Table Name = CWGLADM.T ID = 2,4
——————————-
Access Table Name = CWGLADM.T ID = 2,4
| Index Scan: Name = CWGLADM.T_P ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ID (Ascending)
| | | 2: ID2 (Ascending)
| #Columns = 0
| Single Record
| Fully Qualified Unique Key
| #Key Columns = 2
| | Start Key: Inclusive Value
| | | | 1: 00001
| | | | 2: 00003
| | Stop Key: Inclusive Value
| | | | 1: 00001
| | | | 2: 00003
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Exclusive
| | Row : Update
| Sargable Predicate(s)
| | #Predicates = 1
Update: Table Name = CWGLADM.T ID = 2,4
对比执行路径，发现仅仅是索引的Key取值范围不一致。
================================================================
下面看看不使用索引的情况下，两个不同事务，在更新不同行时情况：
事务一：
db2 => update t set name=’eee’ where name=’chennan’
DB20000I SQL 命令成功完成。
db2 =>
事务二：
db2 => update t set name=’fff’ where name=’spsoft’
现象是事务二被阻塞，发生锁等待事件。
在第三个监视窗口里，查看事务一、事务二的执行路径相同：
Access Table Name = CWGLADM.T ID = 2,4
| #Columns = 0
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Exclusive
| | Row : Update
| Sargable Predicate(s)
| | #Predicates = 1
Update: Table Name = CWGLADM.T ID = 2,4
虽然更新不同行，事务二仍然被阻塞。
================================================================
下面看看锁升级的例子，我从现场库导入含两个帐套的acc_acctitle，以更新此表为例。
数据分布情况：
db2 => select accset_id,datasetver_id,count(*) from acc_acctitle group by accset_id,datasetver_id
ACCSET_ID DATASETVER_ID 3
——————– ——————– ———–
6029 6574 11091
6029 23559698 11467
6627 6741 859
6627 23559923 919
4 条记录已选择。
事务一：
db2 => update acc_acctitle set acctitle_name=’eee’ where accset_id=6029 and DATASETVER_ID=6574
DB20000I SQL 命令成功完成。
db2 =>
事务二：
db2 => update acc_acctitle set acctitle_name=’eee’ where accset_id=6627 and datasetver_id=23559923 and acctitle_id=25142218
现象是事务二被阻塞，发生锁等待事件。
从第三个监视窗口查看事务一的执行路径情况：
Access Table Name = CWGLADM.ACC_ACCTITLE ID = 2,3
| Index Scan: Name = CWGLADM.ACC_ACCTITLE_P ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ACCSET_ID (Ascending)
| | | 2: DATASETVER_ID (Ascending)
| | | 3: ACCTITLE_ID (Ascending)
| #Columns = 0
| #Key Columns = 2
| | Start Key: Inclusive Value
| | | | 1: 6029
| | | | 2: 6574
| | Stop Key: Inclusive Value
| | | | 1: 6029
| | | | 2: 6574
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Exclusive
| | Row : Exclusive
Update: Table Name = CWGLADM.ACC_ACCTITLE ID = 2,3
事务二的执行路径：
Access Table Name = CWGLADM.ACC_ACCTITLE ID = 2,3
| Index Scan: Name = CWGLADM.ACC_ACCTITLE_P ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ACCSET_ID (Ascending)
| | | 2: DATASETVER_ID (Ascending)
| | | 3: ACCTITLE_ID (Ascending)
| #Columns = 0
| Single Record
| Fully Qualified Unique Key
| #Key Columns = 3
| | Start Key: Inclusive Value
| | | | 1: 6627
| | | | 2: 23559923
| | | | 3: 25142218
| | Stop Key: Inclusive Value
| | | | 1: 6627
| | | | 2: 23559923
| | | | 3: 25142218
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Exclusive
| | Row : Exclusive
Update: Table Name = CWGLADM.ACC_ACCTITLE ID = 2,3
看出虽然是引用索引不同的取值范围，但因行锁升级为表锁，所以还是发生锁等待，查看锁定情况：
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt
0x080AC650 2 53514C4332453037FE5E49DE41 Internal P ..S G 2 1 0
0x080ACC90 3 53514C4332453037FE5E49DE41 Internal P ..S G 3 1 0
0x080ACA38 3 02000000010000000100420056 Internal V ..S G 3 1 0
0x080ACCB8 2 53514C4445464C5428DD630641 Internal P ..S G 2 1 0
0x080AC510 3 53514C4445464C5428DD630641 Internal P ..S G 3 1 0
0x080ACE48 2 02000300000000000000000054 Table ..X G 2 1 0
0x080ACC68 3 02000300000000000000000054 Table .IX W 2 1 0
明显看出是表锁
==============================================================================
综上分析，db2开发过程中要尽量避免无索引的更新语句，有索引的话，尽量引用索引里全部字段，再就是尽量缩小更新语句的数据范围，以避免行锁升级为表锁！
看来db2要解决并发性能，不是仅仅修改隔离级别就能解决的啊！
本文来自CSDN博客，来源地址：http://blog.csdn.net/njhart2003/archive/2008/07/23/2696240.aspx
文章作者: Lee
本文地址： https://www.pomelolee.com/470.html
除非注明，Pomelo Lee文章均为原创，转载请以链接形式标明本文地址
No comments yet.
Leave a comment
Search
相关文章
热门文章
最新文章
文章分类
- ajax (10)
- algorithm-learn (3)
- Android (6)
- as (3)
- computer (85)
- Database (30)
- disucz (4)
- enterprise (1)
- erlang (2)
- flash (5)
- golang (3)
- html5 (18)
- ios (4)
- JAVA-and-J2EE (186)
- linux (143)
- mac (10)
- movie-music (11)
- pagemaker (36)
- php (50)
- spring-boot (2)
- Synology群晖 (2)
- Uncategorized (6)
- unity (1)
- webgame (15)
- wordpress (33)
- work-other (2)
- 体味生活 (40)
- 前端 (21)
- 大数据 (8)
- 游戏开发 (9)
- 爱上海 (19)
- 读书 (4)
- 软件 (3)