Dear Mr. Bishaljit Purkayasth,

There can be many reasons why you see this locking error messages.

Possible reasons can be :
- table is not regurarly modified (overflow) and optimized (QEP
statistics)
- there are too many rows in one page that gets accessed concurrently
- transaction lacks commits
- insufficient primary keys specificied ( or index keys ) in the
queries
- underlying database rules get locked or don't get processed fast
enough
- updates involves index key columns
- ...

Please post the output of 'help table the_name_of_your_table;' in an
sql session on the database.
And if possible the queries that your are talking about.

From tomorrow on, I'll be abroad and therefore will not be able to
attend this discussion thread for a few days.

Regards,

Franky Leeuwerck
Advanced Projects & Products
http://www.ap2.be

There is an almost unlimited number of possible causes of what you are
seeing.

The first thing you need to be sure of is that ING_SYSTEM_SET is actually
doing what you want.  Any session connecting to the Ingres installation from
another machine/installation will not use it.  Set system_maxlocks in the
DBMS server configuration(s) instead.

Secondly, it may well be that a session that uses more than 80 page locks
will also use more than 400 page locks.  A poorly restricted query with poor
or inappropriate statistics could easily do that.  I actually rather doubt
this is what is happening, because the LKB Highwater is just 748, which
suggests that relatively few locks are ever taken (assuming there are a
significant number of concurrent sessions).  That would tend to suggest that
either the optimizer is very good at predicting when a table lock will be
required (so that there is no need to escalate), or very few sessions are
locking extensively.  That could be because they are highly selective and
the physical keys are well-chosen, or it could be because a significant
number of sessions are ignoring ING_SYSTEM_SET as noted above.

Thirdly, the chances of a deadlock at escalation are greatly increased if
any locks are excessively prolonged.  You mention that this table is a
B-tree, and you also mention the inserts, which suggests to me that it might
be quite insert-intensive (since you bothered to mention the inserts).
Knowing programmers' and DBAs' fondness for surrogate keys, and given that
those are virtually always sequential, I would not be surprised to see
significant lock serialization on "last" page of the B-tree.  That would
prolong locks and lockwaits far beyond what you might guess, very quickly.

Fourth, you might be running into a bug (I forget the number).  You can test
for it very easily using this procedure:

- start a session
- update a row in some table (create a table for this if you need to)
- do not commit
- start another session
- select the same row from the same table (so the session waits)
- don't commit (you can't anyway)
- return to the first session
- select the row you just updated

Normally you should just see the updated row.  But if you have the bug you
will get a (spurious) deadlock error.

I can think of loads of other possible explanations but other demands on my
time prevent me from droning on here.

I also note that system has a stupendous surplus of locks configured
(303132, of which no more than 748 have been used since start-up).  Your
non-swappable locking segment is at least 134Mb and very likely bigger.
(Run iishowres -d to see what it is.)  I don't imagine that Barclays buy
machines that are short of memory, but it does suggest that someone needs to
give the system configuration a systematic sanity check.  The default
configuration makes very little sense, and this is just one example.

I don't know how serious this problem is for you, but given the large number
of possible causes it might be useful to arm yourself with all the tools and
techniques for diagnosing and fixing it.  Our "Advanced Ingres Programming"
course covers every Ingres-related performance problem you are likely to
see, systematically, in detail, with solutions.  You can find the course
outline on www.rationalcommerce.com under "Training".

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"