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.

locked for delete, insert and update

2 posts in General Discussion Last posting was on 2012-11-28 03:37:07.0Z
rsm Posted on 2012-11-27 21:16:56.0Z
Sender: 2ed2.50b52ae6.1804289383@sybase.com
From: rsm
Newsgroups: sybase.public.ase.general
Subject: locked for delete, insert and update
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50b52dc8.3006.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 27 Nov 2012 13:16:56 -0800
X-Trace: forums-1-dub 1354051016 172.20.134.41 (27 Nov 2012 13:16:56 -0800)
X-Original-Trace: 27 Nov 2012 13:16:56 -0800, 172.20.134.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31544
Article PK: 74433

Hi

I have a Sybase 12.5.4 with all data row locking tables and
have a very big problem.

Sometimes my tables are locked for inserts, updates and
deletes, mostly it is inserts that are bloked and I get no
new inserts in some seconds ( 3 - 90 ), but al of a sudden
everything is back to normal and everything works fine
again. I is different tables so I can't pinpoint if one
table is off, for example if it has some strange constraint
to another table with no index.

I am not sure where to look, perhaps you have some things I
can take a look at?

- Can an index on another table be locked, sometimes it is a
number of different tables that has the insert problem, and
the only thing they have in common is that they has a
constraint to a very big table which referes to its primary
index.


"Mark A. Parsons" <iron_horse Posted on 2012-11-28 03:37:07.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.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: locked for delete, insert and update
References: <50b52dc8.3006.1681692777@sybase.com>
In-Reply-To: <50b52dc8.3006.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 121124-1, 11/24/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50b586e3$1@forums-1-dub>
Date: 27 Nov 2012 19:37:07 -0800
X-Trace: forums-1-dub 1354073827 172.20.134.152 (27 Nov 2012 19:37:07 -0800)
X-Original-Trace: 27 Nov 2012 19:37:07 -0800, vip152.sybase.com
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31546
Article PK: 74435

While the blocking is occurring you'll have to look at the queries being executed by the blocker and blockees, plus the
locks held by the blocker, and it wouldn't hurt to look at the blocker's query plan and query performance (eg, periodic
snapshots of monProcessObject and monProcessWaits).

The excessive blocking could be due to a poorly performing blocker query that's running longer than necessary; or
perhaps the blocker query is actually blocked by some other spid; or perhaps the blocker query is having to wait for an
excessive volume of (relatively slow) disk IOs; or perhaps the blocker query is running under an inefficient isolation
level which is causing a poor query plan; and the list goes on and on ... so the first thing you have to do is collect
some detailed stats about what the blocker is doing and what the blockees are trying to do.

On 11/27/2012 14:16, rsm wrote:
> Hi
>
> I have a Sybase 12.5.4 with all data row locking tables and
> have a very big problem.
>
> Sometimes my tables are locked for inserts, updates and
> deletes, mostly it is inserts that are bloked and I get no
> new inserts in some seconds ( 3 - 90 ), but al of a sudden
> everything is back to normal and everything works fine
> again. I is different tables so I can't pinpoint if one
> table is off, for example if it has some strange constraint
> to another table with no index.
>
> I am not sure where to look, perhaps you have some things I
> can take a look at?
>
> - Can an index on another table be locked, sometimes it is a
> number of different tables that has the insert problem, and
> the only thing they have in common is that they has a
> constraint to a very big table which referes to its primary
> index.