| > Now they turn on fast commit...And they start to get locks all over the
> place.
>
> Stall messages show processes with "Hibernating until next checkpoint"
> and Active User Stall messages shows both "Hibernating until next
> checkpoint" and "Waiting for global checkpoint (CR)"
That does not seem to indicate additional locking. One effect of fast commit is
that processes hold on to page locks beyond commits. That could produce more
lock conflicts for high-contention pages (without fast commit, those locks are
released voluntarily at each commit).
However, the above messages just document the operation of the checkpointing
mechanisms. Are they also using record caches by any chance ? What is the
chekpoint interval set to ? Look at the "checkpoint" screen in rmu/sh stats.
/albert
|
| Sorry. Just a new boy and I cannot get my head arround the mountains of
syntax that comes with SQL.
Snapshots are enabled imediate.
They were trying to use record cache, yes. But the locking problem
seems to exist with or without the record cache.
Interval is set to 120 seconds.
Update activity is low, and with fast commit most of the updates are
failing due to lock timeouts.
What does one look for on the checkpoint screens? As I said I am no
expert.
I can understand the page locks, but why are they not released? Must
they wait for the checkpoint even if we know someone is waiting?
I am not sure I understand why a row cache should cause more row locks?
They are accessing the same rows in the same way, so are not the locks
the same? Or is this a case of ALG not being usable? But then we should
get less conflicts not more?
Confussed of Danmark (Colin)
|
| Dear Confused,
> Snapshots are enabled imediate.
OK.
> They were trying to use record cache, yes. But the locking problem
> seems to exist with or without the record cache.
>
> Interval is set to 120 seconds.
> Update activity is low, and with fast commit most of the updates are
> failing due to lock timeouts.
That means the application also uses the "lock timeout" mechanism, i.e. uses
"SET TRANSACTION WAIT n" or has a lock timeout set at database level or uses
logical name RDM$BIND_LOCK_TIMEOUT_INTERVAL.
Is this the case ? How long is the timeout ? Was this also set with the prior
version of the database (when fast commit was not used)
That mechanism has the effect of limiting the time a process will wait to get a
lock. If it cannot get the lock for that interval, then it will fail. That
seems to indicate that transactions became significantly longer.
> What does one look for on the checkpoint screens? As I said I am no
> expert.
How frequently they happen and for what reasons. But then, I don't see the
relationship between checkpoints and the lock conflicts you see. I was asking
this since your .0 only indicated stalls caused by snapshoting.
> I can understand the page locks, but why are they not released? Must
> they wait for the checkpoint even if we know someone is waiting?
With fast commit, the page locks are retained after commit, but given up as
necessary as other users want to access the same pages.
My suggestion: if necessary, raise the lock timeout interval (this is so you
have enough time to observe the locking condition). Then, once you observe one
or more processes stalling for too long (see the "stall messages" screen in
rmu/sh stats), issue the command RMU/SH LOCKS/MODE=BLOCKING. The output will
help you find out what process is blocking what other.
/albert
|