Conference orarep::nomahs::sql_services

2179.0. "hold cursors and autocommit" by M5::JHAYTER () Wed Mar 26 1997 13:16

Hi,

Rdb 7.0, SQS 7.0, ODBC 2.10

Looking to find out the relationship between hold cursors and autocommit.

CT has a MS "c" program, using hold cursors.  From client log I see:

----SQLSRV_EXECUTE_IMMEDIATE
------------len: 22, value: SET HOLD CURSORS 'ALL'

open, fetch cursor A
open, fetch, close cursor B (within 1 second of open fetch cursor A)
open, fetch, close cursor C (within 1 second of open fetch cursor A)

close cursor statements have
--------SQLSRV_CLOSE_CURSOR
...
...
------------AUTO COMMIT
----------------len: 2, value: 1
------------ODBC HOLD CURSORS
----------------len: 2, value: 1

So the question is... on the close cursor with the auto commit/hold cursors, 
is a COMMIT really being done?

Why ask.  The database has snapstops enabled deferred.  And this odbc app
has been found to be stalling other users on the famous "snapshot cursor 0"
lock.  If the close cursor is executing a "commit", then the lock should be
freed, and stalls would be minimal.  But, if due to "hold cursors", ODBC or
SQS is not autocommiting, then the transaction is not ending and extending the
time span of the transaction.  Make sense?

Thanks
Jerry