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
Subject: locked for delete, insert and update
X-Mailer: WebNews to Mail Gateway v1.1t
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
Xref: forums-1-dub sybase.public.ase.general:31544
Article PK: 74433
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:220.127.116.11) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
Subject: Re: locked for delete, insert and update
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
X-Antivirus: avast! (VPS 121124-1, 11/24/2012), Outbound message
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
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.