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

星期日, 2009-07-26 | Author:

LeeAuthor: Lee

Name: lee

Email: service@i5a6.com

Site: https://www.pomelolee.com/About: webgame网页,手机游戏服务端架构和开发者

| Database, JAVA-and-J2EE |

11,940 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

~谢谢打赏~

赏