Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

jConnect ResultSet and Row Locking

2 posts in JDBC Connect (product renamed to JConnect) Last posting was on 1997-08-19 17:37:55.0Z
Bill Hastings Posted on 1997-08-19 16:04:43.0Z
Message-ID: <33F9C41B.5C7B8E44@emek.org>
Date: Tue, 19 Aug 1997 09:04:43 -0700
From: Bill Hastings <bill@emek.org>
Reply-To: bill@emek.org
X-Mailer: Mozilla 4.01 [en] (WinNT; U)
MIME-Version: 1.0
Subject: jConnect ResultSet and Row Locking
X-Priority: 3 (Normal)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 11
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:366
Article PK: 252118

With the current implementation of ResultSet in JConnect, when exactly
do I have a row locked?

If I execute a statement that returns a ResultSet, and then begin
next()ing through the returned rows, Is the current row locked? Is this
isolation level dependent?

Given a high enough level of isolation, are all the rows in a ResultSet
locked once I've next()ed through them?

Thanks in advance.

Bill Hastings


David Clegg Posted on 1997-08-19 17:37:55.0Z
Message-ID: <33F9D9F3.68DC9EB4@sybase.com>
Date: Tue, 19 Aug 1997 10:37:55 -0700
From: David Clegg <davec@sybase.com>
X-Mailer: Mozilla 3.01 (X11; I; Linux 1.2.13 i586)
MIME-Version: 1.0
To: bill@emek.org
Subject: Re: jConnect ResultSet and Row Locking
References: <33F9C41B.5C7B8E44@emek.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 59
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:364
Article PK: 252116


Bill Hastings wrote:
>
> With the current implementation of ResultSet in JConnect, when exactly
> do I have a row locked?
>
> If I execute a statement that returns a ResultSet, and then begin
> next()ing through the returned rows, Is the current row locked? Is this
> isolation level dependent?
>
> Given a high enough level of isolation, are all the rows in a ResultSet
> locked once I've next()ed through them?

It does depend on your Isolation Level, whether you are using
chained-transactions or autocommit transaction mode, and whether
you are using a cursor on the Statement that gave you the resultSet.

Before proceding, realize that RDBMS's have different locking
strategies and jConnect is not specifying anything special to try
to control this stuff. SQLServer generally doesn't hold locks on
ROWS but rather on 2K PAGES - so depending on the number/size of
columns in your rows you may lock one or more rows at a time.

Be default you are in autocommit mode with transaction isolation level
3. When the select statement executes you would get a logical
read-lock on the pages affected by your select statement - but the
repeatable-read locks you logically get are immediately released
because your transaction (which didn't change anything) will end
immediately because of autocommit. In this case, the locks on pages
(and the rows contained in them) are already released by the time
the first row of the result-set comes back across the network for
you to fetch.

If you do a "BEGIN TRANSACTION" statement, or set autocommit(false)
then these read-locks would indeed be held. The read-locks will not
prevent any other users from accessing (reading) the same pages. If
another transaction tries to update/delete rows in those pages, that
transaction will be blocked from committing until you end your
transaction (Commit or rollback or disconnect - which implicitly
rolls back the transaction).

With a lower isolation level, the read locks would not held even
if you do have a long-running transaction going.

If you use a cursor, SQL-Server can use a different form of locking
known as ladder locking. As soon as you fetch a cursor off a page,
the locks on that page can be released (because SQL-Server doesn't
support backwards scrolling).

dave