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.

What version of ASE for NT gives row level locking?

3 posts in Windows NT Last posting was on 2000-07-21 19:14:26.0Z
Breck Carter Posted on 2000-07-21 17:09:15.0Z
From: NOSPAM__bcarter@bcarter.com (Breck Carter)
Subject: What version of ASE for NT gives row level locking?
Date: Fri, 21 Jul 2000 17:09:15 GMT
Organization: xxx
Reply-To: xxx
Message-ID: <397882ca.18959853@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.11/32.235
Newsgroups: sybase.public.sqlserver.nt
Lines: 11
NNTP-Posting-Host: bastion.thepowertoknow.com 199.85.25.61
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1839
Article PK: 1089323

What version of ASE for NT will give us row level locking?
11.9.something, or do we have to move to 12?

Currently we are on 11.5.1.1 for NT and we are experiencing a lot of
blocking and deadlocking.

Followup questions...

To upgrade to the row level locking version do we have to dump and
reload the database?

Is it "true" row level locking, or can there still be problems with
locks on index pages?

Breck


Andy Price Posted on 2000-07-21 19:14:26.0Z
From: "Andy Price" <andy.price@canada.com>
References: <397882ca.18959853@forums.sybase.com>
Subject: Re: What version of ASE for NT gives row level locking?
Date: Fri, 21 Jul 2000 20:14:26 +0100
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <wlikHk08$GA.260@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: dyn85-ras13.screaming.net 212.49.236.85
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1838
Article PK: 1089324

You need to use v11.9.2 or v12. No you don't need to dump and load. To use
it you can change the locking scheme of existing tables using the ALTER
TABLE ... LOCK command. For converting from the standard 'allpages' locking
to any of the new locking schemes, the table will be rebuilt, so ensure you
have enough additional space for 120% of the table size before hand. For
converting from datapage to datarows locking, only the system tables are
updated and will take a few seconds, so go with datapage locking before
trying datarows locking.

There are no transaction locks on index pages with data only locking
schemes, only 'latches' on the index that are held for the duration of the
data modification. Locks on the data pages or data rows are still held. So
yes it is true row level locking (I presume you are comparing it to another
database such as Oracle, as each database vendor will have their own
internal way of handling this) if you choose the datarows locking scheme.

If you are on v11.5 look at max_rows_per_page to reduce your contention or
maybe creating a clustered index on a column containing random data if your
data will allow it. Reducing transaction length into several smaller
transactions, if you have long running transactions, will certainly improve
your concurrency. If you are trying to compensate for a bad design, then get
that sorted before resorting to datapage or datarow locking. There are many
ways to reduce contention, so look into them before going to any form of
row-level locking.

Study the manual pages online for information on the new locking schemes
before deciding to use them.

Andy

--

Andy Price
Sybase Certified Professional - Adaptive Server DBA v12.0
Sybase Certified Associate - Adaptive Server DBA v12.0 & v11.5

"Breck Carter" <NOSPAM__bcarter@bcarter.com> wrote in message
news:397882ca.18959853@forums.sybase.com...
> What version of ASE for NT will give us row level locking?
> 11.9.something, or do we have to move to 12?
>
> Currently we are on 11.5.1.1 for NT and we are experiencing a lot of
> blocking and deadlocking.
>
> Followup questions...
>
> To upgrade to the row level locking version do we have to dump and
> reload the database?
>
> Is it "true" row level locking, or can there still be problems with
> locks on index pages?
>
> Breck


Rob Verschoor Posted on 2000-07-21 18:36:52.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
References: <397882ca.18959853@forums.sybase.com>
Subject: Re: What version of ASE for NT gives row level locking?
Date: Fri, 21 Jul 2000 20:36:52 +0200
Lines: 72
Organization: Sypron B.V.
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <xtKFu208$GA.203@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: i0565.pvu.euronet.nl 194.134.166.55
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1837
Article PK: 1089325


"Breck Carter" <NOSPAM__bcarter@bcarter.com> wrote in message
news:397882ca.18959853@forums.sybase.com...
> What version of ASE for NT will give us row level locking?
> 11.9.something, or do we have to move to 12?

11.9.2 (for all platforms)

> To upgrade to the row level locking version do we have to dump and
> reload the database?

No; for each table you can choose between 3 lock schemes (classic page
locking, "new" page locking and row-level locking). Changing the lock
scheme is done through the "alter table" command.

> Is it "true" row level locking, or can there still be problems with
> locks on index pages?

Very true row-level locking !

In fact, you may want to consider upgrading to ASE 12.0 while you're
at it, as this contains a number of further enhancements w.r.t.
locking that will allow for better concurrency (such as not blocking
on uncommitted inserts, and pseudo-column-level locking -- see the P&T
GUide for further details on this).

HTH,

Rob

--
If you don't want to use X Windows, you could try "sybinit4ever", a
free tool which uses only an ASCII interface to create a new ASE
server. It can be downloaded from
http://www.euronet.nl/~syp_rob/si4evr.html

See the
ASE reference manual / System Administration Guide / Transact-SQL
user's guide
This / These books can be viewed or downloaded as PDF files from the
Sybase website http://sybooks.sybase.com . For more details how to get
there, see http://www.euronet.nl/~syp_rob/sybbooks.html .

For more information on the background of this problem, as well as a
solution, see http://www.euronet.nl/~syp_rob/idgaps.html .

... syntax and description are in the ASE Quick Reference Guide
Supplement, which you can download from
http://www.euronet.nl/~syp_rob/ase_qref.html .

To use dynamic SQL, you need at least ASE version 12.0, which has the
"execute immediate" feature for this. In earlier versions of ASE you
can simulate some types of dynamic SQL though; for more information,
see http://www.euronet.nl/~syp_rob/dynsql.html and
http://www.euronet.nl/~syp_rob/dynsqlcis.html .

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0
Certified Sybase Professional DBA/SQL Developer for ASE 11.5
Certified DBA/Performance & Tuning Specialist for Sybase System 11

email mailto:rob@sypron.nl.*No*Spam*Please*
WWW http://www.euronet.nl/~syp_rob
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------