INFORMIX锁机制及如何分析其锁冲突（第二部分）

本文是讲述IBM informix锁机制系列文档的第二部分。

介绍
在多用户数据库系统中拥有成千上万的并发用户在同时访问数据，因此我们需要有某种机制来保护数据以维护其数据一致性。除了事物日志机制外，锁机制就是我们采用的另一个主要手段。
然而，锁机制经常会导致冲突及等待现象的发生。这通常是一个DBA在日常管理工作中会碰到的一个普遍问题。如果缺乏一些适当的脚本或手段，往往会导致在分析锁问题时变得越来越复杂并出现错误。
本文将阐述IDS的锁机制，帮助你分析出现的锁冲突及锁等待的情况。以下的示例基于数据库stores_demo，该数据库可通过以下命令创建：
dbaccessdemo stores_demo -log

锁的动态分配

它是如何工作的

在onconfig配置文件中的参数LOCKS（最大值：8000000）指定了整个实例在启动时初始化可供使用的锁资源。然而后续的IDS版本具 有动态分配锁资源的能力（大约是9.3X以后）。如果锁的使用量达到最初设置的初始值，则IDS会自动扩大其内部锁资源表的大小。这个过程将会重复15 次。每次扩大最多可以增加100000个锁资源，因此IDS锁资源的最大值为9500000。

8.000.000 inital + (15 x 100.000) additions = 9.500.000 total

动态锁资源的分配功能能够使当锁的使用量在达到预先设置的最大值以后，仍然可以继续分配锁资源，确保应用的继续进行，同时又能保证锁资源表不是特别大，以达到较高的运行性能。

在IDS 10.0.FC5及后续版本，可设置的LOCKS最大值扩大为500000000，每次可动态增加的锁资源扩大为1000000，可重复扩充的次数增大为 100，因此锁资源使用上限扩大为600000000。但应注意的是锁资源并不是设置得越大越好，因为这会浪费资源降低系统运行性能，因此应根据需要合理 设置。

非优化的应用
有时候，动态分配锁资源会将一些设计不好的应用程序掩盖掉。比较好的一种方式是应该能够对单个会话所能申请的最大锁资源进行控制。这样就能够帮助DBA快速定位那些执行不好的应用，同时又不好影响其他应用的正常运行。
目前当数据库达到锁资源的上限以后，如果仍需申请锁资源，那么所有申请资源正在运行的会话都会接收到锁表溢出的错误。这意味着很多应用都会由于一个编写不好的应用所牵连。这并不是一个理想的状态。

锁等待时间

针对每个数据库会话进行设置

每个数据库会话都可以单独设置锁等待时间，以防止由于锁等待超时而影响到整个应用。IDS缺省是不设置锁等待时间的，这也就是说一旦数据库检测到锁冲突存在，数据库会立刻返回错误给对应的应用。

-244: Could not do a physical-order read to fetch next row
107: ISAM error: record is locked

为此你可以对每个会话设定相应的锁等待时间：

set lock mode to not wait
set lock mode to wait
set lock mode to wait <#sec>

分布式事物：

对于分布式事物，配置参数DEADLOCK_TIMEOUT将会指定本地IDS数据库等待远端数据库锁资源的最长时间，如果在此时间内未能获得相应的锁资源则返回错误给应用。

推荐方法：

如果指定无限制的锁等待(set lock mode to wait) 这并不是一个好方法。这会加剧锁冲突和死锁现象的出现，这是一个不好的编程习惯。

在OLTP环境中一般设置锁等待时间为5-10秒是比较合理的。如果在指定的时间内不能获得对应的锁资源，数据库会返回一个SQL错误给应用。这时应用可以决定是否重新发起该操作或回滚整个事物。

检查会话的设置情况

你可以通过onstat -g sql命令获得当前正在运行会话的隔离级及锁等待的模式：

Listing 1. Session isolation level - onstat -g sql

Output from the onstat -g sql command:
--------------------------
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
18 - stores_demo CR
Not Wait 0 0 9.03 Off
16 - stores_demo RR
Wait 10 0 0 9.03 Off

本例显示了2个采用不同隔离级的数据库会话：
Session 18, CR Committed Read, Not Wait
Session 16, RR Repeatable Read, Wait 10

后续一个比较好的功能可能是：在没有显式使用set lock mode to wait...命令设置锁等待时间的情况下，数据库具有在数据库或实例一级设置缺省的锁等待时间的能力。

死锁：

死锁出现在2个用户同时拥有各自的锁资源，同时他们又互相申请属于对方的锁资源。为了避免死锁情况的出现，会话在申请一个锁资源的时候，IDS通过 扫描内部锁表来确定是否可能产生死锁，一旦发现有此可能即会发送ISAM error code -143 (deadlock detected)给相关的会话。

检测到的可能死锁数目：

通过onstat -p命令中的(deadlks column)字段将会显示自数据库启动或执行onstat -z (zero statistics)命令以来数据库所检测到的可能死锁数目。你可以通过以下SQL命令获得精确的最近一次数据库启动时间：

select dbinfo("utc_to_datetime", sh_pfclrtime) from sysmaster:sysshmvals

Sysmaster表：

不幸是IDS没能提供更多的关于如何检测死锁及相关应用的信息。这将使一旦出现死锁情况以后难于对其如何解决进行分析。sysmaster数据库中的一些表能够提供进一步的信息：

sysprofile：该表中所保存的死锁检测数与onstat -p命令中deadlks字段所提供的信息是对应的
syssesprof：该表中保存了每个数据库会话所检查到的可能死锁数
sysptprof：该表中保存了每个数据库表所检查到的可能死锁数

跟踪死锁：

IDS的错误跟踪机制将有助于分析死锁的现象。因为每次在IDS检测到一个可能出现的死锁的时候将会抛出ISAM error code -143，因此你可以使用onmode -I 143命令去跟踪该错误。当下一个死锁可能被检查到的时候数据库会立刻产生一个断言错误文件，同时会保存onstat -a的信息。这样你就可对这些收集到的信息进行分析，找出可能会造成死锁的对应会话，它的SQL执行语句及拥有锁资源的其他相关会话信息。

死锁超时：

配置参数DEADLOCK_TIMEOUT不会对本地死锁的检测时间或本地应用的整个等待时间产生任何影响。对于出现在本地的可能死锁现象IDS是能够立刻检测出来的。

如果一个锁等待的情况出现在试图查询或修改远端数据库的数据的时候，本地数据库实例将会按照通过DEADLOCK_TIMEOUT所指定的等待时间 上限进行等待，如果超过该时间，将会被判定为可能出现了一个死锁情况。ISAM error code -154 (deadlock timeout expired - possible deadlock) 将被返回给相关应用。

目前IDS还不能跨数据库实例的检查内部锁表信息；因此它只能通过DEADLOCK_TIMEOUT参数来指定分布式的锁超时时间，以此达到检测可 能死锁的情况。在分布式事物中可以通过set lock mode to wait去重新设置相应的由DEADLOCK_TIMEOUT参数所指定的死锁等待超时时间。

分析锁冲突：

在OLTP环境中通常会有众多的并发会话同时访问同样的记录。因此，一个比较好的情况是我们使执行的事物尽可能的小以减少其出现锁冲突的可能性。

如果在你的应用中你不同通过set lock mode to wait来设定锁等待时间，当你在访问一个数据，而这个数据已经被其他用户设置上锁的时候，你将立刻得到锁失败的SQL错误。这种错误通常是大家所不希望 的，所以在应用中一般通过set lock mode to wait命令来重新设定锁等待时间，这样IDS的SQL执行线程在出现锁冲突的时候就会进行等待，直到获得锁资源或锁超时。但是这种情况又会使整个实例的 运行效率下降。由此用户会抱怨响应时间慢或长时间的执行批量作业。通常，你不能将锁等待的时间设置为无限等待。

在一个正在运行的环境中分析锁等待的情况通常具有一定的挑战性。一个比较好的方式是可以通过使用一些恰当的脚本来实时分析这些锁等待的情况。在下节 我们将会提供一些有用的onstat命令。你可以由此为起点编写出更好的自动运行脚本，你也可以使用我们提供的lockwt工具。

一些有用的onstat命令：

出现锁冲突以后使用onstat -u命令是一个较好的起点。它包含2个有用的信息：

当前每个会话所拥有的锁资源信息
locks字段将会告诉你该会话拥有多少锁资源。那些使用了较多锁资源的会话通常会导致锁冲突。这并不是必然情况，但使用较大锁资源的应用间接也说明了它可能不是一个比较优化的应用。

会话当前等待锁的情况
在flags字段的首位将会以‘L’的形式表明该会话正在等待一个锁资源

Listing 2. Locks held and sessions waiting for a lock - onstat -u

Output from the onstat -u command:
---------------------
address flags sessid user tty wait tout locks nreads nwrites
4506b44c L-BPR-- 20 informix 11 440cfac4 -1 17 19 0
4506b978 Y--P--D 16 informix - 4407d138 0 0 0 0
...
...

你可以通过执行onstat -k | grep 'L-'命令来获得所有当前正在等待锁的所有会话信息。通过onstat -g ses <sessid>命令，你可以确定哪个具体的SQL正在被会话执行。你也可以看到当前被打开的数据库(Current Database)，当前所使用的隔离级(Iso Lvl)，锁的等待时间(Lock Mode)。在status字段，你可以看到还剩余多少时间将到达锁超时所设定的时间。

Listing 3. Analyze sessions in lock wait status - onstat -g ses <sessid>

Output from the onstat -g ses 20 command:
-----------------------------
...
...
42 sqlexec 4506b44c L-BPR-- 7168 sleeping(secs: 9)
...
...
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
20 DELETE (all) stores_demo
CR
Wait 60 0 0 9.03 Off
...
...
Current SQL statement:
delete from customer

下一步，需要确定锁资源的拥有者，它将导致锁等待。使用onstat -k命令可以列出数据库实例当前所分配的所有锁资源信息：

Listing 4. Locks currently allocated in IDS - onstat -k

Output from the onstat -k command:
--------------------------
IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up 00:01:29 -- 66016 Kbytes

Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
5411a970 0 55a769dc 0 HDR+S 100002 206 0
5411a9d0 0 55a763e0 0 S 100002 206 0
5411aa30 0 55a76fd8 0 S 100002 206 0
5411aa90 0 55a775d4 0 HDR+S 100002 205 0
5411aaf0 0 55a775d4 5411e210 HDR+X 20003e 203 0 U
5411ab50 0 55a775d4 5411e510 HDR+X 200054 302 K- 1 I
5411ac70 0 55a775d4 5411aaf0 HDR+X 20003e 204 0 U
5411d910 0 55a775d4 5411e630 HDR+X 20003e 108 0 U
5411dc70 0 55a775d4 5411de50 HDR+X 20003e 104 0 U
5411dcd0 0 55a775d4 5411e990 HDR+X 20003e 20c 0 U
5411dd30 0 55a775d4 5411e690 HDR+X 20003e 10a 0 U
5411dd90 0 55a775d4 5411eab0 HDR+X 20003e 302 0 I
5411de50 0 55a775d4 5411deb0 HDR+X 20003e 103 0 U
5411deb0 0 55a775d4 5411df70 HDR+X 20003e 100 0
5411df10 0 55a775d4 5411e090 HDR+X 20003e 208 0 U
5411df70 55a77bd0 55a775d4 5411e330 HDR+X 20003e 102 0 U
5411dfd0 0 55a775d4 5411df10 HDR+X 20003e 209 0 U
5411e030 0 55a775d4 5411dd30 HDR+X 20003e 10b 0 U
5411e090 0 55a775d4 5411e150 HDR+X 20003e 207 0 U
5411e0f0 0 55a775d4 5411e390 HDR+X 20003e 106 0 U
5411e150 0 55a775d4 5411e3f0 HDR+X 20003e 206 0 U
5411e1b0 0 55a775d4 5411e030 HDR+X 20003e 10c 0 U
5411e210 0 55a775d4 5411e750 HDR+X 20003e 200 0
5411e270 0 55a775d4 5411e6f0 HDR+X 20003e 201 0 D
5411e2d0 0 55a77bd0 0 S 100002 205 0
5411e330 0 55a775d4 5411aa90 HDR+IX 20003e 0 0
5411e390 0 55a775d4 5411dc70 HDR+X 20003e 105 0 U
5411e3f0 0 55a775d4 5411ac70 HDR+X 20003e 205 0 U
5411e450 0 55a775d4 5411e5d0 HDR+X 20003f 302 K- 1 I
5411e4b0 0 55a775d4 5411e1b0 HDR+X 20003e 10d 0 U
5411e510 0 55a775d4 5411e450 HDR+X 200054 201 K- 1 D
5411e570 0 55a775d4 5411dfd0 HDR+X 20003e 20a 0 U
5411e5d0 0 55a775d4 5411e270 HDR+X 20003f 201 K- 1 D
5411e630 0 55a775d4 5411e0f0 HDR+X 20003e 107 0 U
5411e690 0 55a775d4 5411d910 HDR+X 20003e 109 0 U
5411e6f0 0 55a775d4 5411e4b0 HDR+X 20003e 10e 0 U
5411e750 0 55a775d4 5411ab50 HDR+X 20003e 202 0 U
5411e990 0 55a775d4 5411e570 HDR+X 20003e 20b 0 U
5411e9f0 0 55a775d4 5411dcd0 HDR+X 20003e 20d 0 U
5411ea50 0 55a775d4 5411e9f0 HDR+X 20003e 20e 0 U
5411eab0 0 55a775d4 5411ea50 HDR+X 20003e 301 0 U
5411edb0 0 55a77bd0 5411e2d0 IX 20003e 0 0
...
...

在这部分第二个字段wtlist能够提供一些有用信息。它包含了正在等待该锁资源的用户线程16进制地址信息。但是由于当前实例所配置的锁资源可能较多， 因此onstat -k命令的输出也较大。你可以使用以下命令快速定位哪个会话是当前申请锁资源的拥有者，例如线程地址55a77bd0正在等待的锁资源：

AWK : onstat -k | awk '$2 ~/55a77bd0/ { print }'
PERL: onstat -k | perl -ane 'print if $F[1] eq "55a77bd0"'

owner字段将告诉你锁资源拥有者的内存地址信息。然后你可以根据这些信息通过onstat -u和grep命令找到对应的会话信息。这样你就可以使用onstat -g <sessid>命令来具体分析是什么原因导致了锁等待。如果你发现这个锁资源的拥有者也在等待其他的锁资源（在onstat -u输出的flags字段第一个符号为‘L’表示），那么你应该重复上述的步骤找到该锁资源的下一个拥有者。

/home/informix/115:onstat -u |grep 55a775d4
55a775d4 Y-BP--- 26 informix 0 5741b530 0 37 42 0

/home/informix/115:onstat -g ses 26

IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up 00:10:37 -- 66016 Kbytes

session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
26 informix - 0 16503 zyl-fedo 1 94208 71536 off

tid name rstcb flags curstk status
89 sqlexec 55a775d4 Y-BP--- 5696 cond wait netnorm -

Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
26 V 5754a028 90112 20256 110 20
26*O0 V 574fb028 4096 2416 1 1

name free used name free used
overhead 0 3360 scb 0 96
opentable 0 3808 filetable 0 992
ru 0 464 log 0 16512
temprec 0 21600 keys 0 832
gentcb 0 1208 ostcb 0 2632
sqscb 0 14552 sql 0 40
rdahead 0 832 hashfiletab 0 280
osenv 0 1744 sqtcb 0 2376
fragman 0 208

sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind directives
5770a018 576f7018 0 0 0 2 1

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
26 - stores CR Not Wait 0 0 9.24 Off

Last parsed SQL statement :
update customer set address2="zylzyl"

/home/informix/115:onstat -u |grep 55a77bd0
55a77bd0 L--PR-- 27 informix 1 5411df70 20 2 0 0

/home/informix/115:onstat -g ses 27

IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up 00:11:08 -- 66016 Kbytes

session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
27 informix - 1 16504 zyl-fedo 1 61440 47968 off

tid name rstcb flags curstk status
146 sqlexec 55a77bd0 Y--P--- 5696 cond wait netnorm -

Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
27 V 577a6028 57344 11056 91 12
27*O0 V 57554028 4096 2416 1 1

name free used name free used
overhead 0 3360 scb 0 96
opentable 0 1792 filetable 0 352
log 0 16512 temprec 0 2144
keys 0 160 gentcb 0 1208
ostcb 0 2632 sqscb 0 14544
sql 0 40 rdahead 0 832
hashfiletab 0 280 osenv 0 1744
sqtcb 0 2064 fragman 0 208

sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind directives
578ee018 577a7018 0 0 0 2 1

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
27 - stores CR Wait 20 0 0 9.24 Off

Last parsed SQL statement :
update customer set address2="zyl111"

Lockwt工具：

使用Esql/C编写的工具lockwt非常适合用来分析锁等待的情况。为了能使用该工具，你需要安装Informix Client SDK及C编译器，然后编译该工具。该工具会搜索sysmaster数据库里面的系统表以找到出现的锁等待情况信息。

该工具将显示每个会话所占有锁资源的信息，及哪些会话在等待这些锁资源释放。执行lockwt -r <#sec> 命令可以按照指定的时间间隔重复收集信息（类似于onstat -r命令）。

该工具可以实时监控复杂的锁资源等待情况，同时以简单易读的形式将相关信息显示出来。

Listing 5. Lockwt - Description of the output format

Output from lockwt:
-------------------
(0) (1) (2) (3) (4) (5) (6) (7) (8) (9)
|-------10--------20--------30--------40--------50--------60--------70--------80--------9|
|-------- XML error: The previous line is longer than the max of 90 characters ---------|
WAIT SID :PID PROCNAME USERNAME LKTYPE DATABASE:TABLENAME LKOBJ

0 - 13900:12303 workprocess3 dbuser X rome :orders row
|-------10--------20--------30--------40--------50--------60--------70--------80--------9|
|-------- XML error: The previous line is longer than the max of 90 characters ---------|
1 W 53600:23613 batchp12 dbuser rome :orders


Colno Purpose
(0) Sequence number

(1) Waiting or not waiting, possible values are:
"-" - this session is the holder of the lock and is always listed first.
"W" - this session(s) is(are) waiting for the above session.

(2) Session ID of this session in the database server

(3) Process ID of the UNIX process, remote connections have pid -1

(4) Process name of the UNIX process. If it is a remote connection
(pid = -1), no process name will be available.

(5) UNIX username of this session

(6) Type of lock, possible values are:
"X" - Exclusive Lock
"S" - Shared Lock
"U" - Update Lock
For additional lock types, execute the following sql-statement:
-> select txt from sysmaster:flags_text where tabname = "syslcktab"

(7) Database name

(8) Table name, the lock is on. If it is an index lock and the index is detached
from the table (has it's own partition number), the name of that index
is shown here.

(9) Type of object locked, possible values are:
"table" - this is a table lock
"idx" - this is an index key lock
"page" - this is a page lock
"row" - this is a row lock

 

Listing 6. Lockwt - Lock wait situation I

Output from lockwt:
-------------------
WAIT SID :PID PROCNAME USERNAME LKTYPE DATABASE:TABLENAME LKOBJ

0 - 13900:12303 workprocess3 dbuser X rome :orders row
1 W 53600:23613 batchp12 dbuser rome :orders

本例中，会话13900 (process "workprocess3")是表orders上锁资源的拥有者，会话53600正在等待这些锁资源的释放，因此你需要通过执行onstat -g ses 13900命令去检查会话13900以证实其运行是否正常。

Listing 7. Lockwt - Lock wait situation II

Output from lockwt:
-------------------
WAIT SID :PID PROCNAME USERNAME LKTYPE DATABASE:TABLENAME LKOBJ

0 W 3894: -1 (remote) eherber1 X rome :status row
1 W 17048: 3140 batchp3 dbuser rome :status

0 - 63296: -1 (remote) eherber1 X rome :customer_order row
1 W 3894: -1 (remote) eherber1 rome :customer_order

本例是一个交复杂的情况。会话17048正在等待会话3894设置在表status上的锁资源释放。但是通过检查下一部分可以发现，会话3894本身也在 等待会话63296释放相应的锁资源。这是一个典型的锁等待传递案例，因为会话3894占据了别的会话需要使用的锁资源，而它本身又在等待被其他会话所拥 有的锁资源。因此我们应该通过onstat -g ses 63296去具体分析会话63296是否在正常执行。

从工具lockwt的源代码中，你可以抽取那些与sysmaster相关的查询语句以形成你自己的查询语句。

打开游标的问题：

可能你在修改表的时候曾经碰到过类似的问题。即使你能够在该表上设置排他锁，你仍然不能对该表进行修改。以下为示例的整个过程：

Listing 8. Non-exclusive access on a table

Output from dbaccess -e stores_demo <script.sql>:
--------------------------------------------------
begin;
Started transaction.

lock table customer in exclusive mode;
Table locked.

alter table customer add (mycol integer);
242: Could not open database table (informix.customer).
106: ISAM error: non-exclusive access.

出现这种情况有可能是由于有其他的用户在表customer上使用了游标进行数据读取。由于游标并不在具体的数据记录上放置任何锁，否则我们就不可能能够将该表以排他的方式锁住，但是它却能防止其他用户对表的partition信息进行修改。

1、要解决该问题，首先需要找到该表的16进制partnum：
Select hex(partnum) from systables where tabname = "customer".
2、如果partnum为0，那么这可能是一个分片表。你需要执行以下命令来找到分片表的partnum：
Select st.tabname, dbinfo("dbspace", sf.partn), hex(sf.partn) from systables st, sysfragments sf, where st.tabid = sf.tabid and sf.fragtype = "T"and st.tabname = "customer".
3、用找到的partnum结合onstat命令搜索当前打开表的信息：
onstat -g opn | grep -i <hex_partnum>
4、从rstcb字段，它表明了相关会话线程的内存地址信息，据此信息使用onstat -u命令进行搜索：
onstat -u | grep <rstcb_without_leading_0x>

在确定与此相关的会话以后，你可以通过onmode -z <sessid>终止相关的会话。

如果你使用IDS 7.31.xD5, 9.40 or 10，你可以使用环境变量IFX_DIRTY_WAIT。该环境变量可以被设置在服务器端或客户端。该变量指定了在执行DDL命令时将等待数据库完成修改 操作所需的时间。如果指定的时间超时，数据库将返回与没有设置该变量时同样的错误。

总结：

在一个拥有众多并发事物正在运行的环境下，实时分析锁冲突的情况是一个比较艰巨的任务。但是通过本文对数据库锁机制的介绍，将有助于大家在分析锁冲突的时候缩短所需时间。