DEADLOCK_WAIT parameter setting

[Peter van den Bogaart]

Jan,
I will give points for sure!!! I just want to see if more reactions come in!
I appreciate all answers and will value them next week. Hope that is ok.

[Richard J Maher]

Hi Peter,

As everyone else said, set DEADLOCK_WAIT to 1.

But I think that for performance you should be looking at moving to VMS 8.3. (I believe the lock tree remastering in a cluster has been much improved) Also, if anyone has experience of dedicating a CPU to a very busy DLM then I'd like to hear the experiences!

VLM and Fast I/O with Rdb Global Buffers is probably also worth looking up.

Cheer Richard Maher

PS. Be careful not to listen to everything you hear from Rdb engineering as you'll end up only opening your DB on one node :-( Of course their fanatical obsession with Row Cache and single-node configurations has had nothing to do with Rdb position in the VMS market place now has it?

[Peter van den Bogaart]

Richard,
Thanks for your reply. If it was our choice we would go to 8.3 but so far it is a management decision not to upgrade.
DLM is not an issue because it is a standalone (non cluster) node.
Peter

[Wim Van den Wyngaert]

If decreasing the param increases performance significantly (wall time I asume), I would say your application has deadlocks all the time and needs to detect them fast (and it handles them too : they redo the transaction).

The reason why you have deadlocks is very often because not all programs handle father-sun relations in the same sequence. And thus one program first locks the father while others first lock the sun. This should be avoided. May be there is just 1 program misbehaving ...

fwiw

Wim

[Peter van den Bogaart]

Wim,
Avoiding the deadlocks by the code (or dbms design) is unfortunally out of our control. I hope your and others comment can help us to convince the developers.
Thanks for your advise.

[Peter van den Bogaart]

The result of this and other discussions is:
The parameter DEADLOCK_WAIT will be decreased by one (from 5 to 1) day by day instead of a big bang from 5 ==> 1.
Thanks all for your comment.

[Richard J Maher]

Hi Peter,

Being cautious/prudent never hurt anybody, but the sooner the deadlock is identified the sooner the victim will be selected and the resources freed up. Concurrency levels have to be boosted.

As to why you're getting deadlocks being "out of your control", I suggest someone (in devlopment/support?)look at the DB Key being returned in the DEADLOCK message and work out if it's an index node or data page and then see if something as simple as a RESERVING clause or CONSISTENCY LEVEL on the transaction will fix things.

Cheers Richard Maher

[Peter van den Bogaart]

Thnx Richard,
I will pass your tip to the developers and the (technical) DBA. I hope it will push them in the right direction.

[Peter van den Bogaart]

Thanks all for your responces.
It seems I have been a bit to generous giving points. I used the dutch school system instead of the ITRC standard. But I judged all the answers the same so I don't think it is a big deal.
As xpected, the "real and only" answer is not here but it helped us a lot anyway to decide to change the deadloack_wait parameter in steps from 5 to 1.
Peter