Table remains locked after disconnecting recordset

Table remains locked after disconnecting recordset

Post by Tom Merten » Thu, 15 Jun 2000 04:00:00



Hello,
I have a * problem using ADO with the OLE DB Provider for ODBC and
the Client Access ODBC Driver to retrieve data from a DB2/400 database
(on AS/400): after a recordset is retrieved and disconnected from the
database the table lock disappears from the AS/400. When I close the
recordset and re-open it, without modifying something, and I disconnect
the recordset again, the table lock remains on the table until the
connection is closed (which I do not want to do in a regular
client/server application). Take a look at some demo code:

Set adoRs = New ADODB.Recordset
adoRs.CursorLocation = adUseClientBatch
adoRs.Open "SELECT * FROM XYZ", adoCon, adOpenStatic,
adLockBatchOptimistic
'Table lock is active
Set adoRs.ActiveConnection = Nothing
'Table lock is gone
adoRs.Close
Set adoRs = Nothing
'Restarting all over
Set adoRs = New ADODB.Recordset
adoRs.CursorLocation = adUseClientBatch
adoRs.Open "SELECT * FROM XYZ", adoCon, adOpenStatic,
adLockBatchOptimistic
'Table lock is active
Set adoRs.ActiveConnection = Nothing
adoRs.Close
Set adoRs = Nothing
'Table lock is stays active. The only way I found to remove the table
lock is to close the connection.

I have tried lots of different parameters, but could not find a solution
yet. Does anyone knows how to resolve this? (As temporary work-around
the connection is closed and re-opened before retrieving the data, but
the performance degrades a lot and the overhead for the database server
is not really appreciated).

Best regards,
Tom Mertens

 
 
 

1. Value / row level locking with disconnected recordsets

Using VB6-SP2, ADO2.0, SQL7, MTS.

I've got a disconnected recordset (static cursor, batchoptimistic).
When I do an updatebatch, it checks the values in the underlying table
against those in the database.  As you would expect, if any of them have
changed it doesn't allow you to update the record.

This can be slow on some tables that contain a lot of fields.  Within SQL,
you can add a timestamp to the table, then use that for checking instead,
known as optimistic using rows.  This then only checks to see if the
timestamp has changed, which makes the whole thing quicker and more secure.

My problem is that despite adding the timestamp to the table, updatebatch
still seems to be performing an optimistic using values type update.  I
cannot find anything in MSDN etc that tells me how to get the cursor to use
row checking instead of values.  Can someone tell me how?  MSDN describes it
for FoxPro, but not for VB6, which I'm using.

2. SQL query not running (using Join)

3. Help Finding SQL Server equivalent of IIF

4. Lock record with disconnected recordsets - avoid lost updates

5. Modify alias pathname in code

6. Storing a disconnected recordset into a different table?

7. OLAP?

8. Sproc using temp tables & disconnected recordset

9. How to?: Use a disconnected recordset to populate a table in another DB

10. Disconnected recordsets & SQL temporary tables

11. Disconnected recordsets deleting data from joined tables

12. Copying Tables Using Disconnected Recordsets