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.

Field Level Locks

3 posts in Product Futures Discussion Last posting was on 2003-10-12 01:14:29.0Z
putnamr Posted on 2003-10-10 14:45:40.0Z
Sender: 3429.3f86c6d6.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Field Level Locks
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f86c6e5.342b.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 10 Oct 2003 07:49:09 -0700, 10.22.241.42
Lines: 23
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Oct 2003 07:39:54 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Oct 2003 07:45:40 -0700
X-Trace: forums-1-dub 1065797140 10.22.108.75 (10 Oct 2003 07:45:40 -0700)
X-Original-Trace: 10 Oct 2003 07:45:40 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1413
Article PK: 96419

Hello,

I would like Sybase to investigate using field level locks
on records (rows) if possible and not lock an entire record.
This would be soooooooooooooooooooooooooooooooooooooo
awesome. If Sybase can do this I would suspect that they
would leave the competition in the dust when it comes to
locks.

Introduce field level locking.
1) ALTER TABLE LOCK FIELD THEN ROW THEN PAGE
2) ALTER TABLE LOCK FIELD THEN PAGE
etc... are examples of some alters that would be nice.

Or, use field level locks and escalate to (row) record level
lock only when necessary and then to page (not table which
is done today) and then to table.

Oh, this would be great thing!!! Praise Sybase if they can
do it and do it first.

Thanks,
Ryan Putnam


Rob Verschoor Posted on 2003-10-10 14:58:52.0Z
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3f86c6e5.342b.846930886@sybase.com>
Subject: Re: Field Level Locks
Lines: 64
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: a66246.upc-a.chello.nl
X-Original-NNTP-Posting-Host: a66246.upc-a.chello.nl
Message-ID: <3f86c92c$1@forums-1-dub>
Date: 10 Oct 2003 07:58:52 -0700
X-Trace: forums-1-dub 1065797932 62.163.66.246 (10 Oct 2003 07:58:52 -0700)
X-Original-Trace: 10 Oct 2003 07:58:52 -0700, a66246.upc-a.chello.nl
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1414
Article PK: 96421


<putnamr@river.it.gvsu.edu> wrote in message
news:3f86c6e5.342b.846930886@sybase.com...
> Hello,
>
> I would like Sybase to investigate using field level locks
> on records (rows) if possible and not lock an entire record.
> This would be soooooooooooooooooooooooooooooooooooooo
> awesome. If Sybase can do this I would suspect that they
> would leave the competition in the dust when it comes to
> locks.
>
> Introduce field level locking.
> 1) ALTER TABLE LOCK FIELD THEN ROW THEN PAGE
> 2) ALTER TABLE LOCK FIELD THEN PAGE
> etc... are examples of some alters that would be nice.
>
> Or, use field level locks and escalate to (row) record level
> lock only when necessary and then to page (not table which
> is done today) and then to table.
>
> Oh, this would be great thing!!! Praise Sybase if they can
> do it and do it first.
>
> Thanks,
> Ryan Putnam

FYI, some of this functionality already exists since 12.0. When using
datarow locking, 'pseudo column-level locking' will be used in a
number of circumstances. This is fully automatic, and there is nothing
to configure about this.
The 'pseudo' means that this isn't real 'locking' as such; rather,
when the lock manager can see that two sessions accessing the same row
with conflicting locks while they are actually not in each other's way
at all, will let the access proceed rather then make one session wait
for the other. The effect is the same as when you have column-level
locking: better concurrency.
There is quite a bit more to say about this topic. It was actually
document in the P&T Guide for 12.0, but that section has been removed
in the 12.5 P&T Guide (for reasons unkown to me).
There are a number of criteria to be satisfied before it will be sued
(see the 12.0 P&T Guide).
Anyway, this 'pseudo column-level locking' works wonderfully well. I
think engineering actually did a great job here: improving concurrency
without actually spending any locks on it.

HTH,

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

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------


putnamr Posted on 2003-10-12 01:14:29.0Z
Sender: 4b4.3f88aa4d.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Field Level Locks
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f88aaf5.4b7.846930886@sybase.com>
References: <3f86c6e5.342b.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Oct 2003 18:14:29 -0700
X-Trace: forums-1-dub 1065921269 10.22.241.41 (11 Oct 2003 18:14:29 -0700)
X-Original-Trace: 11 Oct 2003 18:14:29 -0700, 10.22.241.41
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1417
Article PK: 96424

Hello,

Seems to me that Sybase could accomplish this by adding to
syslocks a new column. Lets call it column. This column
would be a VARBINARY(1024) where each bit from the left
represents the colid of the table stored in syscolumns. For
example, if an update was run against two "NOT NULLABLE"
columns or columns whose length is not going to change, and
the colid of the columns is 1 and 3 the bitmap for the
syslocks.column column would be 0x101.

Thanks,
Ryan Putnam

> Hello,
>
> I would like Sybase to investigate using field level locks
> on records (rows) if possible and not lock an entire
> record.
> This would be soooooooooooooooooooooooooooooooooooooo
> awesome. If Sybase can do this I would suspect that they
> would leave the competition in the dust when it comes to
> locks.
>
> Introduce field level locking.
> 1) ALTER TABLE LOCK FIELD THEN ROW THEN PAGE
> 2) ALTER TABLE LOCK FIELD THEN PAGE
> etc... are examples of some alters that would be nice.
>
> Or, use field level locks and escalate to (row) record
> level lock only when necessary and then to page (not table
> which is done today) and then to table.
>
> Oh, this would be great thing!!! Praise Sybase if they
> can do it and do it first.
>
> Thanks,
> Ryan Putnam