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.

Lock in DOL Table

6 posts in General Discussion Last posting was on 2011-08-23 19:43:02.0Z
RGS Posted on 2011-08-23 13:38:00.0Z
Sender: 5bfe.4e53aa7c.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Lock in DOL Table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e53ad38.5da1.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Aug 2011 06:38:00 -0700
X-Trace: forums-1-dub 1314106680 10.22.241.41 (23 Aug 2011 06:38:00 -0700)
X-Original-Trace: 23 Aug 2011 06:38:00 -0700, 10.22.241.41
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30492
Article PK: 72671

I have a table:

account
(
number_account varchar(10),
owner varchar(20),
debts money,
credits money
)
create noncluster unique index i1 on account
(number_account)
lock DATAROWS
50,000 records

I have 40 concurrency users than execute stored procedures
that make operations on the table. Always, all of the users
affect a "number_account" different, never the same (e.d.
different records) and make insert, update and delete
operations

Although the table is DOL I see LOCK's in the ASE log.

Why appear LOCK's if the table is DOL and the concurrency
users never use the same records?

The most lock's appear between INSERT and DELETE operations
of different users

How can I reduce this injustify locks? Is good idea to
partition the table?

Thanks!!


Rob V Posted on 2011-08-23 14:09:23.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0) Gecko/20110812 Thunderbird/6.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Lock in DOL Table
References: <4e53ad38.5da1.1681692777@sybase.com>
In-Reply-To: <4e53ad38.5da1.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e53b493$1@forums-1-dub>
Date: 23 Aug 2011 07:09:23 -0700
X-Trace: forums-1-dub 1314108563 10.22.241.152 (23 Aug 2011 07:09:23 -0700)
X-Original-Trace: 23 Aug 2011 07:09:23 -0700, vip152.sybase.com
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30493
Article PK: 72672


On 23-Aug-2011 15:38, RGS wrote:
> I have a table:
>
> account
> (
> number_account varchar(10),
> owner varchar(20),
> debts money,
> credits money
> )
> create noncluster unique index i1 on account
> (number_account)
> lock DATAROWS
> 50,000 records
>
> I have 40 concurrency users than execute stored procedures
> that make operations on the table. Always, all of the users
> affect a "number_account" different, never the same (e.d.
> different records) and make insert, update and delete
> operations
>
> Although the table is DOL I see LOCK's in the ASE log.
>
> Why appear LOCK's if the table is DOL and the concurrency
> users never use the same records?
>
> The most lock's appear between INSERT and DELETE operations
> of different users
>
> How can I reduce this injustify locks? Is good idea to
> partition the table?
>
> Thanks!!

You write: "I see LOCK's in the ASE log."
This is unclear: the ASE errorlog does not show information about locks.
What do you mean with 'the ASE log'?
Do you mean deadlocks?

Please specify an example of what you're seeing.

--

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

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

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


RGS Posted on 2011-08-23 16:32:29.0Z
Sender: 5bfe.4e53aa7c.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Lock in DOL Table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e53d61d.6f81.1681692777@sybase.com>
References: <4e53b493$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Aug 2011 09:32:29 -0700
X-Trace: forums-1-dub 1314117149 10.22.241.41 (23 Aug 2011 09:32:29 -0700)
X-Original-Trace: 23 Aug 2011 09:32:29 -0700, 10.22.241.41
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30495
Article PK: 72674

I am sorry, it is not the ASE LOG, I see it in the results
of "sp_lock" command

There are not DEADLOCK's, only temporal LOCK's

"sp_lock" command show me the lines of the programs and this
lines are "insert/update/delete" of my table

> On 23-Aug-2011 15:38, RGS wrote:
> > I have a table:
> >
> > account
> > (
> > number_account varchar(10),
> > owner varchar(20),
> > debts money,
> > credits money
> > )
> > create noncluster unique index i1 on account
> > (number_account)
> > lock DATAROWS
> > 50,000 records
> >
> > I have 40 concurrency users than execute stored
> > procedures that make operations on the table. Always,
> > all of the users affect a "number_account" different,
> > never the same (e.d. different records) and make insert,
> > update and delete operations
> >
> > Although the table is DOL I see LOCK's in the ASE log.
> >
> > Why appear LOCK's if the table is DOL and the
> > concurrency users never use the same records?
> >
> > The most lock's appear between INSERT and DELETE
> > operations of different users
> >
> > How can I reduce this injustify locks? Is good idea to
> > partition the table?
> >
> > Thanks!!
>
> You write: "I see LOCK's in the ASE log."
> This is unclear: the ASE errorlog does not show
> information about locks. What do you mean with 'the ASE
> log'? Do you mean deadlocks?
>
> Please specify an example of what you're seeing.
>
> --
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>


Rob V Posted on 2011-08-23 16:43:22.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0) Gecko/20110812 Thunderbird/6.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Lock in DOL Table
References: <4e53b493$1@forums-1-dub> <4e53d61d.6f81.1681692777@sybase.com>
In-Reply-To: <4e53d61d.6f81.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e53d8aa$1@forums-1-dub>
Date: 23 Aug 2011 09:43:22 -0700
X-Trace: forums-1-dub 1314117802 10.22.241.152 (23 Aug 2011 09:43:22 -0700)
X-Original-Trace: 23 Aug 2011 09:43:22 -0700, vip152.sybase.com
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30496
Article PK: 72675


On 23-Aug-2011 18:32, RGS wrote:
> I am sorry, it is not the ASE LOG, I see it in the results
> of "sp_lock" command
>
> There are not DEADLOCK's, only temporal LOCK's
>
> "sp_lock" command show me the lines of the programs and this
> lines are "insert/update/delete" of my table

Please run "sp_lock @verbose=1" and post an example of a lock you think
should not be there.

Rob V.


"Mark A. Parsons" <iron_horse Posted on 2011-08-23 18:21:52.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Lock in DOL Table
References: <4e53ad38.5da1.1681692777@sybase.com>
In-Reply-To: <4e53ad38.5da1.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e53efc0$1@forums-1-dub>
Date: 23 Aug 2011 11:21:52 -0700
X-Trace: forums-1-dub 1314123712 10.22.241.152 (23 Aug 2011 11:21:52 -0700)
X-Original-Trace: 23 Aug 2011 11:21:52 -0700, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30497
Article PK: 72676

A couple issues you could be running into:

1 - one process has affected enough rows that it has obtained an exclusive lock on the entire table, which would block
anyone else trying to access the table; this can be addressed by insuring no individual commands (eg,
INSERT/UPDATE/DELETE) affect enough rows to allow an escalation to a table level exclusive lock (eg, reduce number of
rows affected by a single command, increase the lock escalation LWM/HWM settings for the table); if you are experiencing
this issue then sp_lock should show a block on an exclusive table lock

2 - each process is scanning parts (or all) of the table, which can lead to blocks as you try to scan past someone
else's exclusive row lock; you can get around this in a couple ways : 1) including a WHERE clause that targets just the
desired unlocked rows, 2) using the 'readpast' option to allow your query to skip over a locked row that you don't care
about; to see if you're running into this issue we'd need to see some of your queries that are being blocked (or you
could just run a query with the 'readpast' option to see if still blocks)

NOTE: The 'readpast' option is listed after the table's name (in the 'from'/'join' clause) and can be used with
SELECT/UPDATE/DELETE commands, eg:

... from my_table readpast ...

... from my_table mt readpast ...

... from my_table mt (index 0 mru) readpast ...

... join my_table mt readpast on ...

On 08/23/2011 09:38, RGS wrote:
> I have a table:
>
> account
> (
> number_account varchar(10),
> owner varchar(20),
> debts money,
> credits money
> )
> create noncluster unique index i1 on account
> (number_account)
> lock DATAROWS
> 50,000 records
>
> I have 40 concurrency users than execute stored procedures
> that make operations on the table. Always, all of the users
> affect a "number_account" different, never the same (e.d.
> different records) and make insert, update and delete
> operations
>
> Although the table is DOL I see LOCK's in the ASE log.
>
> Why appear LOCK's if the table is DOL and the concurrency
> users never use the same records?
>
> The most lock's appear between INSERT and DELETE operations
> of different users
>
> How can I reduce this injustify locks? Is good idea to
> partition the table?
>
> Thanks!!


Bret Halford Posted on 2011-08-23 19:43:02.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0) Gecko/20110812 Thunderbird/6.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Lock in DOL Table
References: <4e53ad38.5da1.1681692777@sybase.com>
In-Reply-To: <4e53ad38.5da1.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e5402c6$1@forums-1-dub>
Date: 23 Aug 2011 12:43:02 -0700
X-Trace: forums-1-dub 1314128582 10.22.241.152 (23 Aug 2011 12:43:02 -0700)
X-Original-Trace: 23 Aug 2011 12:43:02 -0700, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30498
Article PK: 72677


On 8/23/2011 7:38 AM, RGS wrote:
> I have a table:
>
> account
> (
> number_account varchar(10),
> owner varchar(20),
> debts money,
> credits money
> )
> create noncluster unique index i1 on account
> (number_account)
> lock DATAROWS
> 50,000 records
>
> I have 40 concurrency users than execute stored procedures
> that make operations on the table. Always, all of the users
> affect a "number_account" different, never the same (e.d.
> different records) and make insert, update and delete
> operations
>
> Although the table is DOL I see LOCK's in the ASE log.
>
> Why appear LOCK's if the table is DOL and the concurrency
> users never use the same records?
>
> The most lock's appear between INSERT and DELETE operations
> of different users
>
> How can I reduce this injustify locks? Is good idea to
> partition the table?
>
> Thanks!!

ASE still has to lock the rows for DRL tables; it has no way of
knowing that you have somehow externally ensured no two processes
will try to access the same row concurrently.

-bret