informix 数据库锁表分析和解决方法

一、前言

在联机事务处理(OLTP)的数据库应用系统中，多用户、多任务的并发性是系统最重要的技术指标之一。为了提高并发性，目前大部分RDBMS都采用加锁技术。
然而由于现实环境的复杂性，使用加锁技术又不可避免地产生了死锁问题。因此如何合理有效地使用加锁技术，最小化死锁是开发联机事务处理系统的关键。

二、死锁产生的原因

在联机事务处理系统中，造成死机主要有两方面原因。

 

一方面，由于多用户、多任务的并发性和事务的完整性要求，当多个事务处理对多个资源同时访问时，若双方已锁定一部分资源但也都需要对方已锁定的资源时，无法在有限的时间内完全获得所需的资源，就会处于无限的等待状态，从而造成其对资源需求的死锁。
另一方面，数据库本身加锁机制的实现方法不同，各数据库系统也会产生其特殊的死锁情况。如在Sybase SQL Server 11中，最小锁为2K一页的加锁方法，而非行级锁。如果某张表的记录数少且记录的长度较短(即记录密度高，如应用系统中的系统配置表或系统参数表就属于此类表)，被访问的频率高，就容易在该页上产生死锁。

2.1 容易发生死锁的几种情况如下:

1>不同的存储过程、触发器、动态SQL语句段按照不同的顺序同时访问多张表; 
2>在交换期间添加记录频繁的表，但在该表上使用了非群集索引(non-clustered); 
3>表中的记录少，且单条记录较短，被访问的频率较高； 
4>整张表被访问的频率高(如代码对照表的查询等)。

 

2.2 以上死锁情况的对应处理方法如下:

1>在系统实现时应规定所有存储过程、触发器、动态SQL语句段中，对多张表的操作总是使用同一顺序。如：有两个存储过程proc1、proc2，都需要访问三张表zltab、z2tab和z3tab，如果proc1按照zltab、z2tab和z3tab的顺序进行访问，那么，proc2也应该按照以上顺序访问这三张表。 

2>对在交换期间添加记录频繁的表，使用群集索引(clustered)，以减少多个用户添加记录到该表的最后一页上，在表尾产生热点，造成死锁。这类表多为往来账的流水表，其特点是在交换期间需要在表尾追加大量的记录，并且对已添加的记录不做或较少做删除操作。 

3>对单张表中记录数不太多，且在交换期间select或updata较频繁的表可使用设置每页最大行的办法，减少数据在表中存放的密度，模拟行级锁，减少在该表上死锁情况的发生。这类表多为信息繁杂且记录条数少的表。

如：系统配置表或系统参数表。在定义该表时添加如下语句： 
with max_rows_per_page=1 
在存储过程、触发器、动态SQL语句段中，若对某些整张表select操作较频繁，则可能在该表上与其他访问该表的用户产生死锁。对于检查账号是否存在，但被检查的字段在检查期间不会被更新等非关键语句，可以采用在select命令中使用at isolation read uncommitted子句的方法解决。

该方法实际上降低了select语句对整张表的锁级别，提高了其他用户对该表操作的并发性。在系统高负荷运行时，该方法的效果尤为显著。 
如： 
select * from titles at isolation read uncommitted 
对流水号一类的顺序数生成器字段，可以先执行updata流水号字段+1，然后再执行select获取流水号的方法进行操作。

http://www.blogjava.net/imdosop/archive/2008/11/06/239085.html

2.3数据库锁表的分析与解决

上面介绍了内存溢出的原因和处理方法，下面再介绍一下数据库锁表及阻塞的原因和处理办法。
数据库和操作系统一样，是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。在实际应用中经常会遇到的与锁相关的异常情况，当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁，严重影响应用的正常执行。
在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

2.3.1死锁的第一种情况

一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
解决方法：
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

2.3.2死锁的第二种情况

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操作，很容易就出现这种死锁的情况。
解决方法：
1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。

3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

2.3.2死锁的第三种情况

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。
解决方法：
SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

2.3小结

总体上来说，产生内存溢出与锁表都是由于代码写的不好造成的，因此提高代码的质量是最根本的解决办法。有的人认为先把功能实现，有BUG时再在测试阶段进行修正，这种想法是错误的。正如一件产品的质量是在生产制造的过程中决定的，而不是质量检测时决定的，软件的质量在设计与编码阶段就已经决定了，测试只是对软件质量的一个验证，因为测试不可能找出软件中所有的BUG。

三、锁表处理步骤

1、onstat -ks|grep HDR+X //查询是哪个表被锁
address wtlist owner lklist type tblsnum rowid key#/bsiz
c1809510 0 d656e774 c181cb3c HDR+X 6002e1 2c602 0

需要关注lklist和type项，从上面来看tblsnum为6002e1（6292193十六进制转换成十进制）的表被锁了。可以重查询是那个表被锁：

dbaccess ：select * from systables where partnum='6292193'得到
tabname basetab_mvpn
owner smpmml
partnum 6292193
tabid 12813
rowsize 464
ncols 61
nindexes 1
nrows 2984
created 12/10/2002
version 839843846
tabtype T
locklevel R
npused 746
fextsize 16
nextsize 16
flags 0

2、onstat -u，将owner(address)为d656e774的线程找出来

address flags sessid user tty wait tout locks nreads nwrites
d656e774 Y--P--- 4261 smp20 - d6ad2330 0 180 99620 16

3、onstat -g sql d656e774可以将这个线程执行过的sql语句打印出来。
4、只要用informix用户执行onmode-z sessid干掉线程
onmode-z 4261

重点说明：onstat -g ses sessid找个进程PID来,然后ps -ef|grep Pid; kill -9 pid
在处理这些问题时还会遇到表被锁是因为该线程还没有执行完毕，此时就不能简单的 onmode -z杀线程
复制代码

四、Informix杀掉死锁的方法和查看死锁的sql语句

Informix锁表产生的原因，要么是多个用户同时访问数据库导致该问题，要么是因为某个进程死了以后资源未释放导致的。如果是前一种情况，
可以考虑将 数据库表的锁级别改为行锁，来减少撞锁的机会；或在应用程序中，用set lock mode wait 3这样的语句，在撞锁后等待若干秒重试。
如果是后一种情况，可以在数据库端用onstat -g ses/onstat -g sql/onstat -k等命令找出锁表的进程，用onmode -z命令结束进程；如果不行，
就需要重新启动数据库来释放资源。

1：$ onstat -k | grep HDR+X

获得sessid，其中HDR+X 为排他锁，HDR 头，X 互斥 ，sessid 是会话标识符编号。

2：$ onstat -g ses sessid
根据sessid得到进程pid，其中pid 是与此会话的前端关联的进程标识。
（可通过$ onstat -g sql sessid 命令查看执行的sql语句）

3：$ ps -ef |grep pid
由此，我们可得到锁表的进程，可根据实际锁表进程的重要程度的具体情况采取相映处理方法：
对于重要且该进程可以自动重联数据库的进程，可以用onmode -z sesid 的方法杀掉锁表session：
$ onmode –z sessid
否则也可直接杀掉锁表的进程 kill pid：
$ kill -9 pid

至此，死锁即被清除掉。 
————————————————————————- 
查找锁定的表名称
通过onstat -k 查找的rowid 等于0的表锁信息的 tblsnum 信息查找表名。
如 tblsnum等于500e19
执行 select * from systables where hex(partnum)=’0×00500e19′
查找到当前锁表的表名。
Informix -244 错误 :
Could not do a physical-order read to fetch next row.
具体错误解释：
#finderr -244
原因：
a.锁表
b.记录太多
c.页损坏
d.某个进程死了以后资源未释放导致
在数据库端用 onstat –g ses/onstat –g sql /
Onstat –k 等找出锁表进程，用onmode –z结束该进程，
不行，重启数据库释放。
锁方式：
行方式（row），页方式（默认page），表方式(table)。
解决：
1．降低锁级别
2．减少加锁事务的时间跨度
3．设置等待解琐时间
相关命令：
）检查索引及页损坏情况
#oncheck –cID database_name:table_name
) 查看锁级别
#oncheck –pt database_name:table_name
)设置锁级别（行方式）
#alter table table_name lock mode(row)
)设置隔离级别
#set isolation to dirty read
)设置等待解锁时间（不宜过大）
#set lock mode to wait second(秒)
不等待
#set lock mode to not wait
还可以这样解决：
如果是日志型数据库，在执行的时候，可以先锁定
begin work;
lock table tab_name in exclusive mode;
要执行的sql语句;
commit work;
如果是非日志的数据
lock table table_name in exclusive mode;
要执行的sql语句;
unlock table tab_name;
中间件全局事务锁问题
中间件与informix连接时会出现onstat -k 中 owner 内容为0的现象。
这是应该查看全局事务onstat -x 和onstat -G
DATABASE sysmaster;

select hex(tx_addr) trans_addr,hex(tx_lklist) lock_addr from systrans 
where hex(tx_addr) like '%c00000006e329778%';
需要说明的是，c000000007674c58是使用onstat -x 或 onstat -G得到的全局事务的地址。
上面SQL语句提供出该全局事务对应的锁地址，这时如果得到的锁地址与锁表的锁地址相同的话，
你就必需从应用端（通常是三层结构的中间件）发命令让该全局事务回滚或提交，否则该锁会被一直持有，直到你执行oninit。
onstat -k 的owner 列中的地址与onstat -x 中的userthread 对应

转自：https://www.cnblogs.com/kongzhongqijing/articles/4462765.html