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.

Newbie: cursor for update

4 posts in General Discussion Last posting was on 2009-10-08 21:01:40.0Z
Jose Luis Posted on 2009-10-08 09:18:06.0Z
From: Jose Luis <jose.luis.fdez.diaz@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Newbie: cursor for update
Date: Thu, 8 Oct 2009 02:18:06 -0700 (PDT)
Organization: http://groups.google.com
Lines: 15
Message-ID: <cc69aedb-1f6d-47c3-ad41-64a83150a482@s6g2000vbp.googlegroups.com>
NNTP-Posting-Host: 213.170.46.90
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1254993486 26563 127.0.0.1 (8 Oct 2009 09:18:06 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 8 Oct 2009 09:18:06 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: s6g2000vbp.googlegroups.com; posting-host=213.170.46.90; 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.3) Gecko/20090824 Firefox/3.5.3,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!s6g2000vbp.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28436
Article PK: 77679

Hi,

I use a cursor to update a table, but a "cursor for update" takes Sh-
intent locks.

The cursor run in a batch process that doesn't content with other
concurrent process in access to the table to update, so the sh-intent
lock is unnecessary.

Is there a way to avoid the lock in a cursor that does updates on the
current row? What is the overload of a sh-intent lock in a cursor?


Thanks in advance,
Jose Luis.


J Posted on 2009-10-08 20:50:44.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Newbie: cursor for update
Reply-To: J@bogusemailAddress.com
Message-ID: <4ace4fad.22312533@forums.sybase.com>
References: <cc69aedb-1f6d-47c3-ad41-64a83150a482@s6g2000vbp.googlegroups.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 8 Oct 2009 13:50:44 -0700
X-Trace: forums-1-dub 1255035044 10.22.241.152 (8 Oct 2009 13:50:44 -0700)
X-Original-Trace: 8 Oct 2009 13:50:44 -0700, vip152.sybase.com
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28437
Article PK: 77680

On Thu, 8 Oct 2009 02:18:06 -0700 (PDT), Jose Luis
<jose.luis.fdez.diaz@gmail.com> wrote:

You should get the locks elevated to an exclusive table lock if you
are holding a lot of the update locks on a table so I don't think the
lock overhead should be a big concern.

"declare cursor c1 for select ...... for update" - is this the correct
form of the sql?

Jay

>Hi,
>
>I use a cursor to update a table, but a "cursor for update" takes Sh-
>intent locks.
>
>The cursor run in a batch process that doesn't content with other
>concurrent process in access to the table to update, so the sh-intent
>lock is unnecessary.
>
>Is there a way to avoid the lock in a cursor that does updates on the
>current row? What is the overload of a sh-intent lock in a cursor?
>
>
>Thanks in advance,
>Jose Luis.


J Posted on 2009-10-08 21:00:44.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Newbie: cursor for update
Reply-To: J@bogusemailAddress.com
Message-ID: <4ace52ab.23077904@forums.sybase.com>
References: <cc69aedb-1f6d-47c3-ad41-64a83150a482@s6g2000vbp.googlegroups.com> <4ace4fad.22312533@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 8 Oct 2009 14:00:44 -0700
X-Trace: forums-1-dub 1255035644 10.22.241.152 (8 Oct 2009 14:00:44 -0700)
X-Original-Trace: 8 Oct 2009 14:00:44 -0700, vip152.sybase.com
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28438
Article PK: 77681

On 8 Oct 2009 13:50:44 -0700, jtotally_bogus@sbcglobal.net (J) wrote:

bad wording in my previous post "elevated" should have been
"promoted".

Look at "lock promotion" for values.
sp_configure "lock promotion"

Jay

>On Thu, 8 Oct 2009 02:18:06 -0700 (PDT), Jose Luis
><jose.luis.fdez.diaz@gmail.com> wrote:
>
>You should get the locks elevated to an exclusive table lock if you
>are holding a lot of the update locks on a table so I don't think the
>lock overhead should be a big concern.
>
>"declare cursor c1 for select ...... for update" - is this the correct
>form of the sql?
>
>Jay
>>Hi,
>>
>>I use a cursor to update a table, but a "cursor for update" takes Sh-
>>intent locks.
>>
>>The cursor run in a batch process that doesn't content with other
>>concurrent process in access to the table to update, so the sh-intent
>>lock is unnecessary.
>>
>>Is there a way to avoid the lock in a cursor that does updates on the
>>current row? What is the overload of a sh-intent lock in a cursor?
>>
>>
>>Thanks in advance,
>>Jose Luis.
>


"Mark A. Parsons" <iron_horse Posted on 2009-10-08 21:01:40.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: Newbie: cursor for update
References: <cc69aedb-1f6d-47c3-ad41-64a83150a482@s6g2000vbp.googlegroups.com>
In-Reply-To: <cc69aedb-1f6d-47c3-ad41-64a83150a482@s6g2000vbp.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091004-0, 10/04/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ace5334$1@forums-1-dub>
Date: 8 Oct 2009 14:01:40 -0700
X-Trace: forums-1-dub 1255035700 10.22.241.152 (8 Oct 2009 14:01:40 -0700)
X-Original-Trace: 8 Oct 2009 14:01:40 -0700, vip152.sybase.com
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28439
Article PK: 77682

I'm not sure what your concern is ...

A read only cursor will also obtain shared intent locks ... that's just the way the cursor works.

In fact, a stand alone SELECT (not in a cursor) is capable of obtaining shared locks.

If, as you say, there is no contention by other processes ... then why worry about the shared intent locks?

Jose Luis wrote:
> Hi,
>
> I use a cursor to update a table, but a "cursor for update" takes Sh-
> intent locks.
>
> The cursor run in a batch process that doesn't content with other
> concurrent process in access to the table to update, so the sh-intent
> lock is unnecessary.
>
> Is there a way to avoid the lock in a cursor that does updates on the
> current row? What is the overload of a sh-intent lock in a cursor?
>
>
> Thanks in advance,
> Jose Luis.