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.

Feching a table with "HOLDLOCK"

2 posts in General Discussion Last posting was on 2010-01-22 15:50:03.0Z
Jose Luis Posted on 2010-01-22 12:20:25.0Z
From: Jose Luis <jose.luis.fdez.diaz@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Feching a table with "HOLDLOCK"
Date: Fri, 22 Jan 2010 04:20:25 -0800 (PST)
Organization: http://groups.google.com
Lines: 29
Message-ID: <4dd1ced6-9058-4666-a672-2f13a3e79221@14g2000yqp.googlegroups.com>
NNTP-Posting-Host: 94.126.240.2
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1264162825 26844 127.0.0.1 (22 Jan 2010 12:20:25 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 22 Jan 2010 12:20:25 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 14g2000yqp.googlegroups.com; posting-host=94.126.240.2; posting-account=1HfkcQoAAAC3iXf8_jGLZLQ9yRZZ5bhF
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.1.7) Gecko/20091221 Firefox/3.5.7,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!news-out.readnews.com!transit4.readnews.com!postnews.google.com!14g2000yqp.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28903
Article PK: 78145

Hi,

Is this program correct? Can I be sure that in the "FECH loop" no more
rows have been inserted in the TABLE_LOCKED table so "max(col1)" has
not been changed?


<<snip begin>>

DECLARE cur_col1 CURSOR FOR select col1 FROM TABLE_LOCKED

BEGIN TRANSACTION

SELECT max(col1) INTO :max_col1 FROM TABLE_LOCKED HOLDLOCK

OPEN cur_col1

COMMIT TRANSACTION


FECH loop

CLOSE cur_col1

<<snip end>>


Thanks in advance,
Jose Luis


"Mark A. Parsons" <iron_horse Posted on 2010-01-22 15:50:03.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Feching a table with "HOLDLOCK"
References: <4dd1ced6-9058-4666-a672-2f13a3e79221@14g2000yqp.googlegroups.com>
In-Reply-To: <4dd1ced6-9058-4666-a672-2f13a3e79221@14g2000yqp.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100122-0, 01/22/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b59c92b$1@forums-1-dub>
Date: 22 Jan 2010 07:50:03 -0800
X-Trace: forums-1-dub 1264175403 10.22.241.152 (22 Jan 2010 07:50:03 -0800)
X-Original-Trace: 22 Jan 2010 07:50:03 -0800, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28905
Article PK: 78146

The HOLDLOCK is being issued within a transaction, so the HOLDLOCK will block all writes to the table.

Once the transaction is closed (eg, COMMIT TRANSACTION) the HOLDLOCK will be released; at this point other processes may
write to the table.

In your example you've closed the transaction (and thus released the HOLDLOCK) prior to entering the FETCH loop, so
other processes will be able to write to the table while you're processing your FETCH loop.

Jose Luis wrote:
> Hi,
>
> Is this program correct? Can I be sure that in the "FECH loop" no more
> rows have been inserted in the TABLE_LOCKED table so "max(col1)" has
> not been changed?
>
>
> <<snip begin>>
>
> DECLARE cur_col1 CURSOR FOR select col1 FROM TABLE_LOCKED
>
> BEGIN TRANSACTION
>
> SELECT max(col1) INTO :max_col1 FROM TABLE_LOCKED HOLDLOCK
>
> OPEN cur_col1
>
> COMMIT TRANSACTION
>
>
> FECH loop
>
> CLOSE cur_col1
>
> <<snip end>>
>
>
> Thanks in advance,
> Jose Luis