如何解决Sybase表锁（VB6）？
我不是一个优秀的 VB 程序员，但我的任务是维护/增强使用 Sybase ASE 作为后端的 VB6 桌面应用程序。 这个应用程序有大约 500 个用户。
最近，我向该应用程序添加了功能，该功能对数据库中的单行执行额外的插入/更新，关键字段是事务号，并且该字段已建立索引。 正在更新的表中通常有大约 6000 条记录，因为事务完成时记录会被删除。 部署后，该应用程序运行良好一天半，然后用户报告性能缓慢。
最终，我们将性能问题追溯到数据库中的表锁，并且不得不回滚到应用程序的先前版本。 第一天使用是星期一，这通常是系统使用非常繁忙的一天，所以我很困惑为什么那天没有出现问题。
在现有的代码中，有一个用于启动 Sybase 事务的调用。 在 BeginTrans 和 CommitTrans 之间的块内，有一个对更新数据库的 DLL 文件的调用。 我将新代码放置在 DLL 的类模块中。
我很困惑为什么对单行的单个插入/更新会导致这样的问题，特别是因为系统在更改之前一直工作正常。 难道我在这里暴露了一个更大的问题吗？ 或者我只需要重新考虑我的方法？
在此先感谢有过类似情况并能提供建议的人。
I am not a great VB programmer, but I am tasked with maintaining/enhancing a VB6 desktop application that uses Sybase ASE as a back-end. This app has about 500 users.
Recently, I added functionality to this application which performs an additional insert/update to a single row in the database, key field being transaction number and the field is indexed. The table being updated generally has about 6000 records in it, as records are removed when transactions are completed. After deployment, the app worked fine for a day and a half before users were reporting slow performance.
Eventually, we traced the performance issue to a table lock in the database and had to roll back to the previous version of the app. The first day of use was on Monday, which is generally a very heavy day for system use, so I'm confused why the issue didn't appear on that day.
In the code that was in place, there is a call to start a Sybase transaction. Within the block between the BeginTrans and CommitTrans, there is a call to a DLL file that updates the database. I placed my new code in a class module in the DLL.
I'm confused as to why a single insert/update to a single row would cause such a problem, especially since the system had been working okay before the change. Is it possible I've exposed a larger problem here? Or that I just need to reconsider my approach?
Thanks ahead for anyone who has been in a similar situation and can offer advice.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
事实证明，罪魁祸首是出现在 BeginTrans 和 CommitTrans 调用范围内的消息框。 拥有消息框的用户将在数据库上保持阻塞锁，直到他们确认该消息。 解决方案是将消息框移到上述范围之外。
It turns out that the culprit was a message box that appears within the scope of the BeginTrans and CommitTrans calls. The user with the message box would maintain a blocking lock on the database until they acknowledged the message. The solution was to move the message box outside of the aforementioned scope.
如果没有您正在使用的 SQL 代码，我无法理解完整的情况。
另外，如果是单个插入或更新，为什么要使用事务？ 是否有可能许多用户会尝试更新同一行？
I am not able to understand the complete picture without the SQL code, that you are using.
Also, if it is a single insert OR update, why are you using a transaction? Is it possible that many users will try to update the same row?
如果您发布了 VB 代码和 SQL（如果可能的话，还附有查询计划），将会很有帮助。 然而，根据我们掌握的信息； 我将对表运行
update stats table_name以确保查询计划是最新的。如果您确定您的代码必须在事务中运行，您是否尝试过添加自己的包含 SQL 的事务块，而不是使用已有的事务块？
It would be helpful if you posted both the VB code and your SQL (with the query plan if possible). However with the information we have; I would run
update statistics table_nameagainst the table to make sure that the query plan is up to date.If you're sure that your code has to run within a transaction have you tried adding your own transaction block containing your SQL rather than using the one already there?