SQL SERVER CURSOR RECORD LOCK NOT DROPPED

[PUMASOFT]

There are a couple of bugs in SQL Server whereby Locks applied by Cursors are not dropped when the Cursor is closed - does anyone know of a way round this other than dropping the connection?

 

[Dimandja]

You can try to use the CLOSE CURSOR statement that includes the WITH RELEASE clause, if supported. Do not use WITH RELEASE when operating under RS or RR isolation levels.

Dimandja

 

[PUMASOFT]

Thanks for your input - have tried this one but unfortunately I do actually need the RR Isolation Level in the application for other reasons.

(I should have noted this down originally) I have tried most things like various (applicable) isolation levels, different cursor definitions and currently have a call outstanding with Microsoft.

Do you have any more ideas that I might try?

 

[Dimandja]

Hi PUMASOFT,

What's the name and version of your SQL product?

 

[PUMASOFT]

The application has to work on SQL SERVER 7.0, SQK SERVER 2000 and Oracle

 

[Dimandja]

Hi PUMASOFT,

Is it 'official' that MS SQL 7.0 has the bugs you described? I couldn't find any mention of it where I looked.

On the other hand, the are a couple of things you should be aware of:

1. SET IMPLICIT TRANSACTIONS ON. This switch will implicitly start a transaction (as in BEGIN TRANSACTION) on the first update statement encountered.

2. You may be explicitly starting a transaction.

In either case, you must COMMIT or ROLLBACK TRANSACTION before any locks are released. Simply closing the cursor will not do it.

Dimandja

 

[PUMASOFT]

Things are a little bit more complicated than this. In a normal SQL structured environment - you normally do have 'controlled' transactions. This application is an extreme legacy C-ISAM product and in effect we are replacing C-ISAM reads/inserts/updates/deletes with calls to libraries which actually perform the SQL statements. This means that we have open ended transactions that are never committed or rolled back... (its really bad design but needs must)

This is why we have multiple cursors open on the connection (and why I cannot drop the connection and why I need RR isolation levels) I also cannot set implicit transactions because this returns errors in our COBOL - the cursors do not finish opening at the point we try to fetch from the cursor.

This is possibly because the Select in SQL (in order to mimic ISAM) is a >= key which is open ended and can return 1000's of rows (I appreciate that this is also a problem in its own right for SQL and Server performance - but we are 'ignoring' this for the moment).

The bugs are logged on MICROSOFT Knowledge base article nos 201905/273879

I do not really think there is a real solution to this one - except to ignore record locking for the time being

 

[Dimandja]

Hi PUMASOFT,

First, let me point out that this is not the correct forum for C/ISAM/SQL problems. This Forum deals with COBOL related problems.

Next, I checked those articles in Knowledge Base. One is a bug not related to your problem. The other is a clarification of a feature of SQL 7.0.

I believe the problem you are experiencing is an application design problem as we both seem to agree, and not an SQL 7.0 bug.

Dimandja