Informix 表被锁定
我们的 J2EE Web 应用程序在 Informix 9.40 上运行。
有时，一张表被锁定，然后任何具有 SQL 代码访问该表的 JSP 都会返回错误。我知道如何使用 onstat -k 来查看当前的锁，但我想知道是否有任何方法可以跟踪 历史上发生的所有锁以及导致表锁的相关会话和SQL语句？
因为我不知道下次什么时候会发生同样的错误......
Our J2EE web application runs against Informix 9.40.
Sometimes one table is locked and then any JSP having SQL code accessing this table will return an error. I know how to use onstat -k to see current locks, but I want to know is there any way to track
all locks happened in history and the related session and SQL statement that caused the table lock?
Because I don't know when the same error will happen next...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，您应该知道 IBM 不再支持 IBM Informix Dynamic Server (IDS) 版本 9.40；后续版本 10.00 也不是。您应该计划升级，可能升级到 11.70，也可能只是升级到 11.50。
没有一种方法可以全面跟踪锁。原因之一是这种跟踪会给系统带来相当大的减慢——非常显着的减慢。锁通常是共享内存的第二大组成部分（位于包含数据页的缓冲池之后），因此锁的数量很多，而且它们通常非常活跃，并且锁通常被短暂持有（对于一小部分）一秒，甚至几分之一毫秒）。此类记录生成的数据量将非常大并且非常难以筛选。
如果您使用的是更高版本，可能会有一些选项可以帮助使用 DB-Cron 和 Admin API（不确定，但机会会更好），但在 9.40 中，选项受到相当严格的限制。
我能想到的最接近的方法是使用
onstat -k -u -r 1进行快照。-k报告锁表（正如您所知）；-u报告用户（会话），-r 1每秒重复该命令。您可以根据需要添加其他数据请求；-g ses将提供有关会话的更详细报告（如果您是 DBSA，通常是用户informix，在运行该命令时）。但这是 1 秒采样...唯一的好消息是，当将锁放在表上时，它很可能会挂起一段时间（因为您注意到了问题），因此-g ses< /code> 或-u输出将告诉您谁或什么应用了锁定。但中间的数据量会很大。您需要检查系统周围的所有 SQL，寻找表锁定的内容。时间上有一致性吗？例如，它可能是由 cron 作业运行的 UPDATE STATISTICS 吗？我相信，这会很快获得表锁；自您的 IDS 版本以来，它也是在版本中得到改进的。
First, you should be aware that IBM Informix Dynamic Server (IDS) version 9.40 is no longer supported by IBM; neither is the successor version, 10.00. You should be planning to upgrade, probably to 11.70, possibly just to 11.50.
There isn't a way of comprehensively tracking locks. One reason for this is that such tracking would impose considerable slowdown on the system - very considerable slowdown. The locks are usually the second biggest component of shared memory (behind the buffer pool(s) containing data pages), so there are lots of them, and they are often very active, and the locks are often held fleetingly (for small fractions of a second, or even fractions of a millisecond). The volume of data generated from such logging would be extraordinarily large and very difficult to sift through.
If you were on a later version, there might be some options to help with DB-Cron and the Admin API (not certain of that, but the chances would be better), but with 9.40 the options are rather strictly limited.
The nearest approach I can think of is snapshotting with
onstat -k -u -r 1. The-kreports on the lock table (as you already know); the-ureports on users (sessions), and the-r 1repeats the command every second. You can add other data requests as you see fit;-g seswould provided a more detailed report on sessions (if you are the DBSA, usually userinformix, when running the command). But this is 1 second sampling...the only good news is that when the lock is placed on the table, it is likely to hang around for a while (because you notice the problem), so the-g sesor-uoutput will tell you who or what applies the lock. But the volume of data in the interim will be large.You need to review all the SQL around the system, looking for table locking stuff. Is there any consistency to the timing? Could it be UPDATE STATISTICS being run by a cron job, for example? That acquires table locks fleetingly, I believe; it is also something that has been improved in the releases since your version of IDS.