db2频现“锁等待”或“911”错误–案例分析之原因及处理方法

星期日, 2009-07-26 | Author: Lee | Database, JAVA-and-J2EE | 10,604 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

Tags: , , ,

文章作者: Lee

本文地址: https://www.pomelolee.com/470.html

除非注明,Pomelo Lee文章均为原创,转载请以链接形式标明本文地址

No comments yet.

Leave a comment

Search

文章分类

Meta