How to resolve Sybase table locks (VB6)?

Question

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.

Answer 1

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?

Answer 2

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_name against 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?

Answer 3

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.