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

zhan_yl

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

        本文讲述INFORMIX数据库锁的基本原理，由2部分组成。IDS是OLTP应用及内嵌式系统的最佳解决方案。通过本文可以帮助你理解数据

库锁的使用方法，便于你及时处理、分析锁冲突。

介绍
        在多用户数据库系统中拥有成千上万的并发用户在同时访问数据，因此我们需要有某种机制来保护数据以维护其数据一致性。除了事

物日志机制外，锁机制就是我们采用的另一个主要手段。
        然而，锁机制经常会导致冲突及等待现象的发生。这通常是一个DBA在日常管理工作中会碰到的一个普遍问题。如果缺乏一些适当的脚

本或手段，往往会导致在分析锁问题时变得越来越复杂并出现错误。
        本文将阐述IDS的锁机制，帮助你分析出现的锁冲突及锁等待的情况。以下的示例基于数据库stores_demo，该数据库可通过以下命令

创建：
        dbaccessdemo stores_demo -log

锁类型
        IDS包含几种不同的锁。通常有：

共享锁
        共享锁可以被放置在没有设置排他锁的记录上。其他用户可以在相同的记录上放置共享锁或更新锁，但是不能再放置排他锁。

更新锁
        更新锁是一种在使用更新游标时产生的特殊类型的锁。更新锁只能被放置在当前没有设置更新锁或排他锁的记录上。一旦记录被加上

更新锁，它的数据在被修改的同时就会立刻升级为排他锁。

排他锁
        排他锁只能被放置在没有任何锁的记录上。一旦排他锁被放置在该记录上，则其他锁也就不能在增加到该记录了。该记录将被被会话

独占使用。

内部锁
        内部锁包含多种特殊类型的锁。举个例子，当一条记录被修改时，该记录上被放置一个排他锁，同时所涉及的数据库表上也被放置一

个排他的内部锁。这是为了确保当该表的数据在排他使用时其他的会话不能在该表上再设置共享或排他锁。

锁粒度
        IDS允许应用开发人员在不同的对象上放置锁。有以下对象。

数据库锁
        数据库可以以排他或共享的方式被锁住。在排他的情况下将防止其他任何人再访问该数据库。在共享的情况下允许并发用户读取或修

改该数据库的数据，但是不允许在该数据库上再放置排他锁。

数据库共享锁：
        数据库共享锁在你打开数据库的同时将被自动设置。这将防止没有其他用户可以在该数据库上再放置排他锁或删除数据库。一些数据

库工具：onunload也会在数据库上放置共享锁
        SQL语句：
                database stores_demo

Listing 1. Share lock on a database

                                                       
Output from onstat -k:
----------------------
Locks
address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
300583dc 0        400d6998 0        HDR+S    100002   207      0

        在这里你可以看到数据库上加有共享锁HDR+S。tblsnum=100002表明了数据库的表空间。rowid=207是该数据库信息存储在数据库

sysmaster，表sysdatabases中记录的16进制信息。数据库表sysmaster:sysdatabases通常也被称作数据库的表空间。

        数据库及他们的16进制rowid信息可以通过以下sql语句获得：

Listing 2. Query on database sysmaster retrieving the row ID of a database tablespace entry

                                                       
Query:
------
database sysmaster;

select  name, hex(rowid) hex_rowid
from sysdatabases;

Result:
-------
name                 hex_rowid

linux_mag            0x00000204
onpload              0x00000206
stores_demo          0x00000207
sysmaster            0x00000201
sysuser              0x00000203
sysutils             0x00000202
tpcb                 0x00000208

数据库排他锁：

        一个排他锁可以被排他的设置在数据库上。数据库工具：dbexport在倒出数据的时候就会在数据库上放置一个排他锁。
       
        SQL 语句：
                database stores_demo exclusive

Listing 3. Exclusive lock on a database

                                                       
Output from onstat -k:
----------------------
Locks
address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
300583dc 0        400d63d8 0        HDR+X    100002   207      0   

        在这里你可以看到一个排他锁(HDR+X)被放置在数据库上，其对应的rowid为207

表锁：
        与数据库锁类似，数据库表也可以以排他或共享的形式被锁住。排他锁可以防止其他用户读取或修改该表信息。一种例外是该会话运

行在脏读的隔离级下。这样就可以从已设置排他锁的表中读取数据，但是该数据可能是不准确的（正在修改中，没有真正提交）。在表上设置

共享锁可以允许其他用户读取该表数据，但是不允许修改数据。

表共享锁：
        共享锁可以以共享的形式放置在数据库表上。数据库工具：onunload、oncheck在使用的时候会在数据库表上设置共享锁（依赖于表的

锁模式是页还是记录）

        SQL 语句：
                begin work; lock table orders in share mode

Listing 4. Share lock on a table

                                                               
Output from onstat -k:
----------------------
Locks
address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
300582e0 0        400d63d8 300583dc HDR+S    10013b   0        0   

        你可以看到一个共享锁(HDR+S)和rowid被设置为0(rowid=0)。rowid为0表明是一个表锁。tablespace number (tblsnum=10013b)构成

该表的16进制partition number。
        会导致这种情形的2种可能：
                oncheck命令
                Data Dictionary查询命令

Listing 5. Identifying a table through its hexadecimal partition number

                                                       
Dictionary Query:
-----------------
database stores_demo;

select st.tabname, dbinfo("dbspace", st.partnum), hex(st.partnum)
from systables st
where hex(st.partnum) = "0x0010013B";

Result:
------
tabname              dbspace              hex_partnum

orders               rootdbs              0x0010013B


oncheck command:
----------------
oncheck -pt 0x0010013b

Result:
-------

TBLspace Report for stores_demo:informix.orders

    Physical Address               1:1988
    Creation date                  08/21/2006 20:07:41
    TBLspace Flags                 801        Page Locking
    TBLspace use 4 bit bit-maps
    Maximum row size               80
    Number of special columns      0
...
...
                               
        注意到你需要使用0x00对通过onstat -k获得的16进制信息进行补足，使其形成8位完整的16进制数。同时需将小写字母转换为大写字

母，例如0x10013b-》0x0010013B。

        如果是分片表的partnum，你需要在sysfragments表里面查找相应信息：

Listing 6. Identifying a fragmented table through its hexadecimal partition number

                                                       
Query:
------
database stores_demo;

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   hex(sf.partn) = "0x0010013B";

Result:
-------
No rows found.
                                                                               
        本例中的partition number 0x0010013B不是分片表的一个分片，因此在sysfragments表中不存在对应信息。但是对于分片表则会有信

息记录在sysfragments表中，请使用前面提供的语句进行查询。

        oncheck -pt <hex_partnum>不能对分片表信息进行查询，它不会显示真实的分片表名，仅有分片信息。

表排他锁：

        排他锁可以被显式的或隐式的被设置在对应的表上。例如：alter table
       
        SQL语句：
                begin; lock table orders in exclusive mode

Listing 7. Exclusive lock on table

                                                               
Output from onstat -k:
----------------------
Locks
address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
300582e0 0        400d63d8 300583dc HDR+X    10013b   0        0   

        在这里一个排他锁(HDR+X)被设置在表上，对应的tblsnum为10013b

页或记录级锁：

        锁的级别（页或记录锁）可以在创建表的时候进行指定，或使用alter table对已有表进行修改。
        建表：
                reate table t1 (f1 int) lock mode (row)
        修改表：
                alter table t1 modify lock mode (page)

        缺省锁模式，在创建表的时候采取何种锁模式，可以通过在配置文件中配置DEF_TABLE_LOCKMODE来进行指定或通过环境变量

IFX_DEF_TABLE_LOCKMODE来指定。在建表时所指定的锁模式会优先于通过DEF_TABLE_LOCKMODE或IFX_DEF_TABLE_LOCKMODE所指定的锁模式。

        使用页锁意味着即使只修改一条记录也会将该记录所在的整个数据页进行加锁。依赖于记录的大小及数据页的大小，采用这种方式会

降低数据库的并发处理性能。特别需要注意的是采取页锁不仅会对相关数据页有影响，而且对索引页同样也会有影响。这样会进一步降低数据

库的并发处理性能，因为索引页相对于数据页来说通常会包含更多的记录。

        你可以通过以下2种方式来确定当前表采取了何种锁模式：

Listing 8. Determining the lock mode of a table using oncheck (a) or a dictionary query (b)

                                       
a) Command:
-----------
oncheck -pt stores_demo:eherber.orders

Output:
-------
TBLspace Report for stores_log:informix.orders

Physical Address               1:3905
Creation date                  05/08/2006 16:47:43
TBLspace Flags                 801        Page Locking
...
...

b) Query:
---------
database stores_demo;

select tabname, locklevel
from systables
where tabname = "orders";

Result:
-------
tabname              locklevel

orders               P
                               
        以下为IDS中锁模式的缩写标志：
        B 视图（需要检查其依赖的锁模式）
        P 页级锁
        R 记录锁

        可以通过以下SQL语句产生相应的修改已存在表的锁模式的语句：

Listing 9. Using meta SQL to convert all tables to row locking

                                       
Meta SQL:
---------
database stores_demo;

output to alter_table.sql without headings
select "alter table '" || trim(owner) || "'." || trim(tabname) || " lock mode(row);"
from    'informix'.systables
where   tabid > 99
and     tabtype = 'T'
and     locklevel = 'P';

Generated output file 'alter_table.sql':
----------------------------------------
alter table 'informix'.customer lock mode(row);
alter table 'informix'.orders lock mode(row);
...
...
       
页锁：
        对于配置为使用页锁的表，在数据处理的过程中页锁会被设置在对应的数据页上。锁的类型可能为排他锁（插入、修改、删除）或共

享锁（设置隔离级为重复读）
        SQL语句：
                begin; update orders set ship_charge = (ship_charge*1.2) where order_num = 1005

Listing 10. Exclusive lock on a page

                                                               
Output from onstat -k:
----------------------
Locks
address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
440cf9bc 0        4506c3d0 0        HDR+S    100002   207         0
440cfa14 0        4506c3d0 440cf9bc HDR+IX   10013b   0           0
440d082c 0        4506c3d0 440cfa14 HDR+X    10013b   100         0

        本例出现了3中类型的锁：
        1.数据库上的共享锁(HDR+S)。tblsnum=100002是该数据库的partnum。
                该共享锁可以防止其他用户在该数据库上设置排他锁
        2.表上的内部排他锁(HDR+IX)。tblsnum=0表明这是一个表锁。
                内部排他锁可以防止其他用户在该表上设置共享或排他锁。
        3.数据页上的排他锁(rowid=100，如果rowid的后2位为00，则表明该锁为页锁）
                排他锁可以防止其他用户在该数据页上放置共享锁或排他锁。

记录锁：
        如果数据库表被设置为记录级锁，则在数据的处理过程中，只有相关的记录上被设置上锁。锁的类型可能为排他锁（插入、修改、删

除）或共享锁（设置隔离级为重复读）

        SQL语句：
                begin; set isolation to repeatable read; select * from customer where customer_num = 110

Listing 11. Share lock on a row

                                                               
Output from onstat -k:
----------------------
Locks
address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
440cf9bc 0        4506c3d0 0        HDR+S    100002   207         0
440cfa14 0        4506c3d0 440cf9bc HDR+IS   100139   0           0
440cfd84 0        4506c3d0 440d082c HDR+S    100139   10a         0
440d082c 0        4506c3d0 440cfa14 HDR+SR   10013a   10a      K- 1
                                                                                       
        本例出现了4种类型的锁：
        1、数据库上的共享锁(HDR+S)。tblsnum=100002是该数据库的partnum。
                该共享锁可以防止其他用户在该数据库上设置排他锁
        2、表上的内部排他锁(HDR+IX)。tblsnum=0表明这是一个表锁。
                内部排他锁可以防止其他用户在该表上设置共享或排他锁。
        3、在记录上设置的共享锁(rowid=10a)
                记录上设置的共享锁可以防止其他用户在该记录上设置排他锁。
        4、索引上设置的共享锁(K-1)
                在索引上设置的共享锁可以防止其他用户在该索引值上设置排他锁。

        你可以通过以下SQL查询到被设置记录锁的相关记录：

Listing 12. Identifying a locked row

                                                               
Query:
------
database stores_demo;
set isolation to dirty read;

select *
from  customer
where hex(rowid) = "0x0000010A";

Result:
-------
customer_num  110
fname         Roy
...
...

索引键锁：

        类似于数据记录上的锁，IDS同样在索引键上设置对应的锁，以保护其内容。键值锁可以确保唯一索引保持其唯一性，在事物未提交以

前不允许相同值的记录插入到同样的索引位置。
        索引键锁可以通过在onstat -k的输出中于key#/bsiz字段中由K来表示出来。

数据库的日志模式

        IDS中可以使用的数据库日志模式为以下4种：

        无日志模式
        缓冲日志模式
        非缓冲日志模式
        ANSI日志模式

        这些日志模式在后续章节中进行阐述。

无日志模式：
        运行在无日志模式下的数据库。不能执行begin、rollback、commit操作，事物信息不会被记录在逻辑日志中。每条SQL语句的执行，

自动提交。所涉及的锁只会影响当前正在执行的语句。

        缺省隔离级：
                脏读。这是在采用无日志模式的数据库上唯一可以使用的隔离级
        SQL语句：
                create database stores_demo

缓冲日志模式：
        事物信息被记录在逻辑日志中。如果你想包含多条SQL操作于一个事物中，则显式的begin操作是必须的。事物的结束以commit或

rollback为标志。如果没有显式的begin作为事物的开始，则每条SQL语句将被看为独立的事物。

        如果commit命令被执行，整个事物的信息并不是立即被写入磁盘上的逻辑日志。这些信息将会被保存在共享内存中的log buffer里。

当log buffer满或使用非缓冲日志方式的数据库事物、使用ANSI日志模式的数据库事物在事物提交的时候这些信息才真正的被写入到磁盘上。

        你应该衡量由此带来的对于数据库事物吞吐率提升的好处与当数据库宕机时造成的数据丢失风险之间的利弊。如果你需要较高的事物

吞吐率，同时需要保存关键的业务数据，使用高速磁盘或使用电池的ramdisk是一个不错的选择。

        缺省隔离级：
                提交读
        使用该日志模式的数据库缺省隔离级为提交读，但是用户可以使用set isolation to <isol_level> 命令设置适当的其他隔离级
        SQL命令：
                create database stores_demo with buffered log

非缓冲日志模式：
        与缓冲日志模式类似，但是使用该日志模式的数据库在事物提交时将会立即将相关的事物信息写入磁盘。你不会丢失任何已提交的事

物信息。

        缺省隔离级：
                提交读
        使用该日志模式的数据库缺省隔离级为提交读，但是用户可以使用set isolation to <isol_level> 命令设置适当的其他隔离级
        SQL命令：
                create database stores_demo with log

ANSI日志模式：
        使用该日志模式的数据库的缺省隔离级为重复读，这意味着在处理的每条记录上将会被设置上共享锁。这会导致锁冲突及所等待现象

的出现。隔离级别可以通过set isolation to <isolation_level> 命令进行切换。
       
        缺省隔离级：
                重复读
        使用该日志模式的数据库可以使用set isolation to <isol_level>命令来进行隔离级的切换
        SQL命令：
                create database stores_demo with log mode ansi

Database logging modes and default isolation levels
Database logging mode                Create statement                                Default isolation level
No logging                         create database stores_demo                        Dirty read
Buffered logging                 create database stores_demo with buffered log        Committed read
Unbuffered logging                 create database stores_demo with log                Committed read
Mode ANSI (unbuffered logging)        create database stores_demo with log mode ansi        Repeatable read

        当前数据库日志模式可以通过onmonitor工具或查询sysmaster数据库来获得：
        onmonitor (status-databases)

        N -- 无日志
        B -- 缓冲日志模式
        U -- 非缓冲日志模式
        U* -- ANSI模式 (unbuffered logging)

        sysmaster query

Listing 13. Determining the logging mode of a database

                                                       
Query:
------
database sysmaster;

select name, is_logging, is_buff_log, is_ansi
from sysmaster:sysdatabases;

Result:
-------
name         stores_ansi
is_logging   1
is_buff_log  0
is_ansi      1

name         stores_demo
is_logging   1
is_buff_log  0
is_ansi      0
                               
        你可以通过ontape或ondblog工具进行数据库日志模式的切换。需要注意的是一旦数据库设置为ANSI日志模式，它将不能被切换为其他

的日志模式。

隔离级：

        在IDS中依赖于采用不同日志模式的数据库，有4中不同的隔离级可供使用。后面将一一予以介绍。

IDS isolation levels and their ANSI counterparts
Informix SQL                ANSI SQL
Dirty read                 Read uncommittted
Committed read                 Read committed
Cursor stability         Not available
Repeatable read         Serializable

脏读(ANSI: Read uncommitted)
        如果你在读取数据的时候采取该隔离级，那么你将不会在涉及到的数据上设置锁，也不会碰到任何锁的问题。但是，你有可能得到的

是一个不准确的数据，因为该数据可能还没有被提交。
        脏读是未带日志模式的数据库唯一可供选择的隔离级别。
       
        informix SQL
                set isolation to dirty read [retain update locks]
        ANSI SQL
                set transaction isolation level read uncommitted

提交读(ANSI: Read committed)
        使用该隔离级可以确保所读出的数据是已经被提交后的。

        运行在提交读隔离级下的会话会尝试在需要读取的数据上设置共享锁，但不真正设置。这样就能确保不会读取到其他正在并发修改的

数据。然而，在读取数据以后，由于没有在该数据上设置任何锁，因此其他的并发事物可以对该数据进行修改。

        提交读是使用缓冲日志模式和非缓冲日志模式数据库锁采用的缺省隔离级，但非使用ANSI日志模式的数据库所采取的隔离级。

        Informix SQL
                set isolation to committed read [retain update locks]
        ANSI SQL
                set transaction isolation level read committed

游标稳定读(ANSI: --)
        游标稳定读隔离级使用在更新游标中。

        IDS在当前读取的数据上放置一个更新锁。如果该数据在此时被修改，则转换为一个排他锁并一直保持到事物结束，并不依赖于游标所

处的当前位置。如果读取到下一条数据而当前的数据没有被修改，那么当前数据上的更新锁将被释放，同时在下一条数据上放置更新锁。如果

在set isolation命令中加上retain update locks子句则会改变这种情况。这样在读取到下一条数据的时候上一条数据上所放置的更新锁将不

会被释放。

        更新锁只能被放置在当前没有被设置任何更新锁或排他锁的数据上。然而，一旦更新锁被放置，其他会话仍然是可以在该数据上放置

共享锁的。这种情况下如果你试图去更新这些数据将会导致错误，因为由于存在共享锁IDS将不能把一个更新锁升级成为一个排他锁。这是我们

希望得到的一种结果。如果你确定在后续的操作中你肯定需要更新数据，那么你可以使用dummy update将所有的更新锁都升级为排他锁。这将

避免其他的并发会话在相同的记录上设置共享锁。

        Informix SQL
                set isolation to cursor stability [retain update locks]
        ANSI SQL
                --

重复读(ANSI: Serializable)

        使用重复读隔离级将会在所读取的记录上都设置共享锁（如果使用页级锁将会在所涉及的数据页上都设置共享锁）。这将可以避免其

他用户更改这些数据。

        retain update locks子句在这种隔离级下将不能使用，因为这种隔离级本身就会自动保持共享锁或更新锁。

        重复读这种隔离级是采用ANSI日志模式的数据库所使用的缺省隔离级别。

        Informix SQL
                set isolation to repeatable read
        ANSI SQL
                set transaction isolation level serializable

Informix SQL 与 ANSI SQL之间的区别：

        在informix使用的SQL命令set isolation to <isol_level>和ANSI使用的SQL命令set transaction isolation level <isol_level>之

间具有2个重要的区别。

        在informix中所执行的命令是基于会话的。也就是说在整个会话中你一旦设置好隔离级就会立刻生效直到你下一次使用set isolation

to <isol_level>命令重新设置为止。

        对应的在ANSI中所执行的命令是基于事物的。隔离级设置好以后将会只对当前事物有效。一旦事物结束，则隔离级别将会自动切换为

当前打开数据库所具有的缺省隔离级。更多的信息，请参照数据库日志模式一节。

        在数据库事物中你将不能使用ANSI的SQL命令set transaction isolation level <isol_level>来设置新的隔离级别。这是不允许的。

但是informix允许在事物中进行隔离级别的切换。

会话的隔离级别：

        你可以通过执行onstat -g sql命令来得到当前正在执行的会话所使用的隔离级别。

Listing 14. Listing isolation levels of individual database sessions

                                       
Command:
---------
onstat -g sql

Output:
-------
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain
50    -              stores_demo        RR  Wait 10    0    0    9.03 Off
45    -              stores_demo        DR  Not Wait   0    0    9.03 Off
...
...
               
        当前数据库所采用的隔离级别将显示在Iso Lvl字段下，以下为其缩写方式：
        DR -- 脏读
        CR -- 提交读
        CS -- 游标稳定读
        RR -- 重复读

小结：

        现在你对数据库锁的类型、锁的粒度、数据库的日志模式、隔离级别有了一个初步的认识。在后续部分我们将对以下内能进行介绍：
        锁的等待时间
        锁的动态分配
        死锁
        锁的等待情况

本文作者：Eric Herber