Ingres Q & A
Ian McNally (icm@infobiz.cityscape.co.uk) wrote:
: Currently, we are running into real problems regarding Ingres locking
: w.r.t our current policy, i.e shared locking for all.
: Is readlock = nolock a viable alternative, and if so, are we in for serious
: grief in implementing it? (Yes, I know about dirty reads etc.)
READLOCK=NOLOCK is a way to provide dirty reads. However, it requires
some specific resources: Whenever a NOLOCK-reader requests a new page
from a table within the database server, a (temporary) lock is requested
for that page (i.e. it should better be called "READLOCK=NOBLOCK"). Then
a block of the buffer manager is reserved exclusively for this thread.
The requested page will then be copied to that private block and the lock
will be released. In the same way all pages, that the NOLOCK-reader uses,
are first copied to the private block.
That means: For every table accessed by the given query, a block of the
buffer manager is reserved for private use. The actual process of
copying will require some (almost negligible) amount of CPU activity.
And there are (short time) locks involved!
: Incidentally, the locking system is sized currently for 500 concurrent users
: and we STILL ran out of lock resources in a test with 100 real concurrent users.
How did you configure your locking system:
How many locks in the system?
How many lock lists?
How many locks per transaction?
Are you using "SET LOCKMODE ... WHERE MAXLOCKS=..."? If so, how?
And what is the actual error message that you get?
: This scares me a bit ( well, actually, a lot)
: Any help is gratefully received..
Hope, I can help you, if I get more info.
- Christopher
--
____________________________________________________________________________
Christopher Etz Kopernikusstr. 28 D-65929 Frankfurt/Main
cetz@cetz.rhein-main.de Tel.: +49 69 318091 Telefax: +49 69 318091
icm@infobiz.cityscape.co.uk (Ian McNally) writes:
[Snip]
I assume that you have read what the Installation Guide and the latest DBA
guide have to say about concurrency and lock management. My installation
uses readlock=nolock and have had no real problems other than the side
effects of dirty reads.
You did not say what platform you are running on. Or much about your
current lock configuration other than the number of users. Ingres makes
some distinctions between how many page locks can be on a table before
going to a table lock and how may locks a transaction can have. Also if
some of your applications have multiple sessions open you will want to
look at the number of locklists your have configured. The defaults that
BUILD calculates are based on some rather minimal lock usage per session.
The last paragraph rambled a bit. What I mean to say is look at the
section in the latest DBA guide on concurrently strategies and locking.
Look at you applications in terms of how many locks they really need.
This would be driven from the number of joins in a query, how many SQL
statements get executed between commits and how many sessions the process
has open. You can use IPM to measure the application's actual usage.
If you are on certain platforms, like VMS clusters, you will also have to
change the platform's locking parameters to allow all those that Ingres
will need.
It is just as important to analyze the transactions
which are causing locks. Perhaps the only hope for
doing this is looking at the lock list for specific
transactions via IPM or by looking at a lock trace
for a specific set of transactions.
Poor locking behavior can be caused by the choice of
bad storage structures, a bad set of primary keys,
a bad secondary index, and the failure to collect
optimizer statistics for some columns used in a query.
Analyzing locking problems after a system goes into
production is very difficult. You need to try and
replicate the transactions in a test environment
so that you can do all of the tracing and isolate
the locking problems.
It is very doubtful that your application is behaving
perfectly - in my opinion most locking issues are caused
by the program, not an overabundance of users trying to
do real work.
Jerry Helms
--
voice 919/380-7252
fax 919/319-9492
In article <39mg4h$r42@ns.cityscape.co.uk>
icm@infobiz.cityscape.co.uk "Ian McNally" writes:
> Currently, we are running into real problems regarding Ingres locking
> w.r.t our current policy, i.e shared locking for all.
> Is readlock = nolock a viable alternative, and if so, are we in for serious
> grief in implementing it? (Yes, I know about dirty reads etc.)
> Incidentally, the locking system is sized currently for 500 concurrent users
> and we STILL ran out of lock resources in a test with 100 real concurrent
> users.
> This scares me a bit ( well, actually, a lot)
> Any help is gratefully received..
I used to think that setting readlock=nolock would save on locking resources.
However, a little birdy from INGRES told me that readlock=nolock just
means that the system creates 'null' locks - it still requires a certain
amount of resources to do this.
Can anyone at CA confirm this ?
-------------------------------------------------------------------------------
Dennis Adams Common Sense Computing (UK) Ltd
dennis@comsense.com Canada House, 272 Field End Rd, Eastcote, Middx HA4 9NA
Phone: 0181 866 4400 Fax: 0181 429 2848
-------------------------------------------------------------------------------
Dennis Adams writes:
>Can anyone at CA confirm this ?
Hi Dennis
READLOCK=NOLOCK is not what it first appears it should really be named
READLOCK=NOBLOCK
A blocking lock is taken out with this setting and a resource is required to do
this. NOLOCK also causes each block to be written to a private buffer in the
server memory this takes resources. Generally NOLOCK requires more CPU to
perform this additional memory block copying - it is not too significant.
Regards
Julian
Ian McNally (icm@infobiz.cityscape.co.uk) wrote:
: Currently, we are running into real problems regarding Ingres locking
: w.r.t our current policy, i.e shared locking for all.
: Is readlock = nolock a viable alternative, and if so, are we in for serious
: grief in implementing it? (Yes, I know about dirty reads etc.)
READLOCK = NOLOCK is not a panacea. It can help in difficult situations
if used properly, but can also cause problems if used inappropriately.
Good transaction design is a prerequisite to good concurrent
performance. LOCKMODE settings are part of the transaction design.
: Incidentally, the locking system is sized currently for 500 concurrent users
: and we STILL ran out of lock resources in a test with 100 real concurrent users.
Most likely you are seeing a misleading message about the locking system
being out of locks, when actually all that has happened is that a lock
list has grown to the maximum number of locks allowed in a transaction,
forcing an escalation to table level locking on one table. If the
escalation deadlocks, the rollback of the transaction is done by the RCP
(a PASS_ABORT) and one of the diagnostic error messages written to the
error log says that the locking system has run out of locks.
What is the exact sequence of messages in your error log when it says you
are out of locks?
: This scares me a bit ( well, actually, a lot)
Be bold! Lots of people use READLOCK = NOLOCK. Very few have trouble
with it. Most who do have trouble either forget about dirty reads, or
act as if it were a substitute for transaction and/or database design.
If this is the first time that you have had to worry about design for
concurrency, it would save you a lot of time on the learning curve to get
some experienced consulting help.
: Any help is gratefully received..
: cheers
: ian mc
Regards,
Jerry
glohr@ozemail.com.au
[Ed.:- The Man From Uncle...oops, Ingres]
Ingres Q & A
To William's Home Page
© William Yuan 2000
Email William