SAP Mix

RE: Database locks

Posted By:
Date:

Locks are also increased by the number of indexes on any particular table.
The size of your database and/or concurrent users has little impact on locks
used.
It is the number of rows in your active tables, the number of associated
indexes and the jobs that run on them. That is why when deleting say 100,000
rows you can have 400,000 locks - indexes. (example)

We have found many times we kick an abap back to the developers to commit
their work more often, you need a ceiling limit of course, but the number of
locks required in any given system is going to be most dependant on the
number of rows in your larger more active tables. Monitor , gauge and change
accordingly is all you can do.

And I agree that a script detecting thresholds and paging or emailing the
admins is a great idea... better to know before all activity halts for a
rollback.

Nice yapping at you all again :)
Randy

-----Original Message-----
From: James Latimer [mailto:jk_latimer@hotmail.com]
Sent: Friday, February 28, 2003 3:09 AM
To: sapmix@iiug.org
Subject: RE: Database locks [53]


The book 'SAP R/3 for the Informix DBA' indicates that the ratio
(lokwaits/lockreqs)*100 < 1
from the 'onstat -p' is used to judge whether adequate locks have been
defined. The number of locks should be increased when the calculation is
greater than 1%






>From: "NormaJean.S...." <NormaJean.Sebastian@tellabs.com>
>To: sapmix@iiug.org
>Subject: RE: Database locks [51] Date: Thu, 27 Feb 2003 20:33:46 -0500
>(EST)
>
>Hi,
>We run with 1 million locks on our SAP PRD database of 850 GB (10 GB
>memory, with 6GB memory ear-marked for Informix(64bit)).
>We run with 500,000 locks in our SAP QA databases in the landscape.
>
>We run 24x7. Any outage in PRD is not a happy thing. User outage
>includes the database being unavailable due to the rollback of a
>transaction because of lock table overflow. We've had 30+ minutes of
>outage on occasion because of this. It is not acceptable in our
>environment.
>
>We monitor like Marco does and page the on-call DBA if we surpass
>475,000 locks (we change this page-out number as we see fit). If the
>DBA is paged, we have full authority to kill the offending transaction
>as Marco described.
>
>Just because SAP/OSS may encourage 'loose' ABAP code, doesn't mean you
>have to permit it. There is an OSS note (don't know which one) that
>discusses begin/commit/transaction processing in ABAP. It's better to
>make the programmers aware of this and accountable for their code than
>to let them accidentally put 'loose' code into PRD. If that happens,
>and PRD locks up...as you know... fingers are pointed at the DBAs.
>
>We have lower locks on our QA servers than in PRD. We hope that will
>help the programmers code more efficiently and not get our PRD server
>into an outage situation for the good of the entire company.
>
>Norma Jean Sebastian
>
>
>
>-----Original Message-----
>From: Marco.Krijt@VendexKBB-ITS.nl [mailto:Marco.Krijt@VendexKBB-ITS.nl]
>Sent: Wednesday, February 26, 2003 2:04 PM
>To: sapmix@iiug.org; forum.subscriber@iiug.org
>Subject: RE: Database locks [46]
>
>
>We have also 2000000 locks on a database of 1,1TB.
>If it is not enough, I say always to look to the ABAP-programm,
>or the variant.
>
>Sometimes programms have build in a commit, if a job fails or make to
>many
>locks the commit interval has to be reduced in the ABAP-programm (or
>sometimes variant).
>
>We have already 2000000 locks for many years, and it works fine.
>
>With onstat -u, you can see which proces has many locks.
>We also are called by phone automatically if more then 1000000 locks are
>in
>use
>(for 24 hours a day.)
>
>Then we look with:
>onstat -u to the locks and the SID,
>then we look to onstat -g ses|grep <SID> and get the PID
>You can see the PID back in SM66/SM50.
>We tell it to the user (at office hours) and kill the SID with onmode -z
>or
>use SM66/SM50.
>If with onstat -u the third caracter is a R the transaction is rolling
>back
>and will not take any locks any more, After the rollback (sometimes
>hours)
>the problem is solved.
>
>If you wait to long to kill the SAP-proces al other programms will be
>failing with,
>"no more locks available" and that's a pitty, so be on time, that's
>nicer.
>
> > Marco Krijt
> > Medewerker Operationeel Beheer SAP
> > Vendex KBB IT Services b.v.
> > Frankemaheerd 6
> > 1102 AN Amsterdam Z.O.
> > Postbus 12644
> > 1100 AP Amsterdam Z.O.
> > Tel: +31 (0)20-3129254
> > E-mail: marco.krijt@vendexkbb-its.nl
> >
>
>
>-----Oorspronkelijk bericht-----
>Van: Chang, Nicole [mailto:changn@tycoelectronics.com]
>Verzonden: woensdag 26 februari 2003 20:29
>Aan: sapmix@iiug.org
>Onderwerp: Database locks [45]
>
>
>Hello,
>
>I was just wondering how many database locks you have available for a
>Production instance. We have a 344 gig database, with 2 million locks
>available, and an average of 500 users that are logged on.
>
>I ask only because OSS is urging us to increase to 4 million locks,
>which is
>double the locks I have.
>
>Thank you in advance.
>
>Nicole Chang
>Database Analyst
>M/A-COM, A Tyco International Company
>978-442-4389
>
>
>
>
>--openmail-part-39c80cdf-00000002
>Content-Type: application/rtf
>Content-Disposition: attachment; filename="BDY.RTF"
> ;Creation-Date="Thu, 27 Feb 2003 19:30:50 -0600"
>Content-Transfer-Encoding: base64
>
>e1xydGYxXGFuc2lcYW5zaWNwZzEyNTJcZnJvbXRleHQgXGRlZmYwe1xmb250dGJsDQp7XGYw
>XGZzd2lzcyBBcmlhbDt9DQp7XGYxXGZtb2Rlcm4gQ291cmllciBOZXc7fQ0Ke1xmMlxmbmls
>XGZjaGFyc2V0MiBTeW1ib2w7fQ0Ke1xmM1xmbW9kZXJuXGZjaGFyc2V0MCBDb3VyaWVyIE5l
>dzt9fQ0Ke1xjb2xvcnRibFxyZWQwXGdyZWVuMFxibHVlMDtccmVkMFxncmVlbjBcYmx1ZTI1
>NTt9DQpcdWMxXHBhcmRccGxhaW5cZGVmdGFiMzYwIFxmMFxmczIwIEhpLFxwYXINCldlIHJ1
>biB3aXRoIDEgbWlsbGlvbiBsb2NrcyBvbiBvdXIgU0FQIFBSRCBkYXRhYmFzZSBvZiA4NTAg
>R0IgKDEwIEdCIG1lbW9yeSwgd2l0aCA2R0IgbWVtb3J5IGVhci1tYXJrZWQgZm9yIEluZm9y
>bWl4KDY0Yml0KSkuXHBhcg0KV2UgcnVuIHdpdGggNTAwLDAwMCBsb2NrcyBpbiBvdXIgU0FQ
>IFFBIGRhdGFiYXNlcyBpbiB0aGUgbGFuZHNjYXBlLlxwYXINClxwYXINCldlIHJ1biAyNHg3
>LiAgQW55IG91dGFnZSBpbiBQUkQgaXMgbm90IGEgaGFwcHkgdGhpbmcuICBVc2VyIG91dGFn
>ZSBpbmNsdWRlcyB0aGUgZGF0YWJhc2UgYmVpbmcgdW5hdmFpbGFibGUgZHVlIHRvIHRoZSBy
>b2xsYmFjayBvZiBhIHRyYW5zYWN0aW9uIGJlY2F1c2Ugb2YgbG9jayB0YWJsZSBvdmVyZmxv
>dy4gIFdlJ3ZlIGhhZCAzMCsgbWludXRlcyBvZiBvdXRhZ2Ugb24gb2NjYXNpb24gYmVjYXVz
>ZSBvZiB0aGlzLiAgSXQgaXMgbm90IGFjY2VwdGFibGUgaW4gb3VyIGVudmlyb25tZW50Llxw
>YXINClxwYXINCldlIG1vbml0b3IgbGlrZSBNYXJjbyBkb2VzIGFuZCBwYWdlIHRoZSBvbi1j
>YWxsIERCQSBpZiB3ZSBzdXJwYXNzIDQ3NSwwMDAgbG9ja3MgKHdlIGNoYW5nZSB0aGlzIHBh
>Z2Utb3V0IG51bWJlciBhcyB3ZSBzZWUgZml0KS4gIElmIHRoZSBEQkEgaXMgcGFnZWQsIHdl
>IGhhdmUgZnVsbCBhdXRob3JpdHkgdG8ga2lsbCB0aGUgb2ZmZW5kaW5nIHRyYW5zYWN0aW9u
>IGFzIE1hcmNvIGRlc2NyaWJlZC5ccGFyDQpccGFyDQpKdXN0IGJlY2F1c2UgU0FQL09TUyBt
>YXkgZW5jb3VyYWdlICdsb29zZScgQUJBUCBjb2RlLCBkb2Vzbid0IG1lYW4geW91IGhhdmUg
>dG8gcGVybWl0IGl0LiAgVGhlcmUgaXMgYW4gT1NTIG5vdGUgKGRvbid0IGtub3cgd2hpY2gg
>b25lKSB0aGF0IGRpc2N1c3NlcyBiZWdpbi9jb21taXQvdHJhbnNhY3Rpb24gcHJvY2Vzc2lu
>ZyBpbiBBQkFQLiAgSXQncyBiZXR0ZXIgdG8gbWFrZSB0aGUgcHJvZ3JhbW1lcnMgYXdhcmUg
>b2YgdGhpcyBhbmQgYWNjb3VudGFibGUgZm9yIHRoZWlyIGNvZGUgdGhhbiB0byBsZXQgdGhl
>bSBhY2NpZGVudGFsbHkgcHV0ICdsb29zZScgY29kZSBpbnRvIFBSRC4gIElmIHRoYXQgaGFw
>cGVucywgYW5kIFBSRCBsb2NrcyB1cC4uLmFzIHlvdSBrbm93Li4uIGZpbmdlcnMgYXJlIHBv
>aW50ZWQgYXQgdGhlIERCQXMuXHBhcg0KXHBhcg0KV2UgaGF2ZSBsb3dlciBsb2NrcyBvbiBv
>dXIgUUEgc2VydmVycyB0aGFuIGluIFBSRC4gIFdlIGhvcGUgdGhhdCB3aWxsIGhlbHAgdGhl
>IHByb2dyYW1tZXJzIGNvZGUgbW9yZSBlZmZpY2llbnRseSBhbmQgbm90IGdldCBvdXIgUFJE
>IHNlcnZlciBpbnRvIGFuIG91dGFnZSBzaXR1YXRpb24gZm9yIHRoZSBnb29kIG9mIHRoZSBl
>bnRpcmUgY29tcGFueS5ccGFyDQpccGFyDQpOb3JtYSBKZWFuIFNlYmFzdGlhblxwYXINClxw
>YXINClxwYXINClxwYXINCi0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tXHBhcg0KRnJvbTog
>TWFyY28uS3JpanRAVmVuZGV4S0JCLUlUUy5ubCBbbWFpbHRvOk1hcmNvLktyaWp0QFZlbmRl
>eEtCQi1JVFMubmxdXHBhcg0KU2VudDogV2VkbmVzZGF5LCBGZWJydWFyeSAyNiwgMjAwMyAy
>OjA0IFBNXHBhcg0KVG86IHNhcG1peEBpaXVnLm9yZzsgZm9ydW0uc3Vic2NyaWJlckBpaXVn
>Lm9yZ1xwYXINClN1YmplY3Q6IFJFOiBEYXRhYmFzZSBsb2NrcyBbNDZdIFxwYXINClxwYXIN
>ClxwYXINCldlIGhhdmUgYWxzbyAyMDAwMDAwIGxvY2tzIG9uIGEgZGF0YWJhc2Ugb2YgMSwx
>VEIuXHBhcg0KSWYgaXQgaXMgbm90IGVub3VnaCwgSSBzYXkgYWx3YXlzIHRvIGxvb2sgdG8g
>dGhlIEFCQVAtcHJvZ3JhbW0sXHBhcg0Kb3IgdGhlIHZhcmlhbnQuXHBhcg0KXHBhcg0KU29t
>ZXRpbWVzIHByb2dyYW1tcyBoYXZlIGJ1aWxkIGluIGEgY29tbWl0LCBpZiBhIGpvYiBmYWls
>cyBvciBtYWtlIHRvIG1hbnlccGFyDQpsb2NrcyB0aGUgY29tbWl0IGludGVydmFsIGhhcyB0
>byBiZSByZWR1Y2VkIGluIHRoZSBBQkFQLXByb2dyYW1tIChvclxwYXINCnNvbWV0aW1lcyB2
>YXJpYW50KS5ccGFyDQpccGFyDQpXZSBoYXZlIGFscmVhZHkgMjAwMDAwMCBsb2NrcyBmb3Ig
>bWFueSB5ZWFycywgYW5kIGl0IHdvcmtzIGZpbmUuXHBhcg0KXHBhcg0KV2l0aCBvbnN0YXQg
>LXUsIHlvdSBjYW4gc2VlIHdoaWNoIHByb2NlcyBoYXMgbWFueSBsb2Nrcy5ccGFyDQpXZSBh
>bHNvIGFyZSBjYWxsZWQgYnkgcGhvbmUgYXV0b21hdGljYWxseSBpZiBtb3JlIHRoZW4gMTAw
>MDAwMCBsb2NrcyBhcmUgaW5ccGFyDQp1c2UgXHBhcg0KKGZvciAyNCBob3VycyBhIGRheS4p
>XHBhcg0KXHBhcg0KVGhlbiB3ZSBsb29rIHdpdGg6XHBhcg0Kb25zdGF0IC11IHRvIHRoZSBs
>b2NrcyBhbmQgdGhlIFNJRCxccGFyDQp0aGVuIHdlIGxvb2sgdG8gb25zdGF0IC1nIHNlc3xn
>cmVwIDxTSUQ+IGFuZCBnZXQgdGhlIFBJRFxwYXINCllvdSBjYW4gc2VlIHRoZSBQSUQgYmFj
>ayBpbiBTTTY2L1NNNTAuXHBhcg0KV2UgdGVsbCBpdCB0byB0aGUgdXNlciAoYXQgb2ZmaWNl
>IGhvdXJzKSBhbmQga2lsbCB0aGUgU0lEIHdpdGggb25tb2RlIC16IG9yXHBhcg0KdXNlIFNN
>NjYvU001MC5ccGFyDQpJZiB3aXRoIG9uc3RhdCAtdSB0aGUgdGhpcmQgY2FyYWN0ZXIgaXMg
>YSBSIHRoZSB0cmFuc2FjdGlvbiBpcyByb2xsaW5nIGJhY2tccGFyDQphbmQgd2lsbCBub3Qg
>dGFrZSBhbnkgbG9ja3MgYW55IG1vcmUsIEFmdGVyIHRoZSByb2xsYmFjayAoc29tZXRpbWVz
>IGhvdXJzKVxwYXINCnRoZSBwcm9ibGVtIGlzIHNvbHZlZC5ccGFyDQpccGFyDQpJZiB5b3Ug
>d2FpdCB0byBsb25nIHRvIGtpbGwgdGhlIFNBUC1wcm9jZXMgYWwgb3RoZXIgcHJvZ3JhbW1z
>IHdpbGwgYmVccGFyDQpmYWlsaW5nIHdpdGgsXHBhcg0KIm5vIG1vcmUgbG9ja3MgYXZhaWxh
>YmxlIiBhbmQgdGhhdCdzIGEgcGl0dHksIHNvIGJlIG9uIHRpbWUsIHRoYXQncyBuaWNlci5c
>cGFyDQpccGFyDQo+IFx0YWIgXHRhYiBcdGFiIE1hcmNvIEtyaWp0XHBhcg0KPiBcdGFiIFx0
>YWIgXHRhYiBNZWRld2Vya2VyIE9wZXJhdGlvbmVlbCBCZWhlZXIgU0FQXHBhcg0KPiBcdGFi
>IFx0YWIgXHRhYiBWZW5kZXggS0JCIElUIFNlcnZpY2VzIGIudi5ccGFyDQo+IFx0YWIgXHRh
>YiBcdGFiIEZyYW5rZW1haGVlcmQgNlxwYXINCj4gXHRhYiBcdGFiIFx0YWIgMTEwMiBBTiBB
>bXN0ZXJkYW0gWi5PLlxwYXINCj4gXHRhYiBcdGFiIFx0YWIgUG9zdGJ1cyAxMjY0NFxwYXIN
>Cj4gXHRhYiBcdGFiIFx0YWIgMTEwMCBBUCBBbXN0ZXJkYW0gWi5PLlxwYXINCj4gXHRhYiBc
>dGFiIFx0YWIgVGVsOiArMzEgKDApMjAtMzEyOTI1NFxwYXINCj4gXHRhYiBcdGFiIFx0YWIg
>RS1tYWlsOiBtYXJjby5rcmlqdEB2ZW5kZXhrYmItaXRzLm5sXHBhcg0KPiBccGFyDQpccGFy
>DQpccGFyDQotLS0tLU9vcnNwcm9ua2VsaWprIGJlcmljaHQtLS0tLVxwYXINClZhbjogQ2hh
>bmcsIE5pY29sZSBbbWFpbHRvOmNoYW5nbkB0eWNvZWxlY3Ryb25pY3MuY29tXVxwYXINClZl
>cnpvbmRlbjogd29lbnNkYWcgMjYgZmVicnVhcmkgMjAwMyAyMDoyOVxwYXINCkFhbjogc2Fw
>bWl4QGlpdWcub3JnXHBhcg0KT25kZXJ3ZXJwOiBEYXRhYmFzZSBsb2NrcyBbNDVdIFxwYXIN
>ClxwYXINClxwYXINCkhlbGxvLFxwYXINClxwYXINCkkgd2FzIGp1c3Qgd29uZGVyaW5nIGhv
>dyBtYW55IGRhdGFiYXNlIGxvY2tzIHlvdSBoYXZlIGF2YWlsYWJsZSBmb3IgYVxwYXINClBy
>b2R1Y3Rpb24gaW5zdGFuY2UuICBXZSBoYXZlIGEgMzQ0IGdpZyBkYXRhYmFzZSwgd2l0aCAy
>IG1pbGxpb24gbG9ja3NccGFyDQphdmFpbGFibGUsIGFuZCBhbiBhdmVyYWdlIG9mIDUwMCB1
>c2VycyB0aGF0IGFyZSBsb2dnZWQgb24uXHBhcg0KXHBhcg0KSSBhc2sgb25seSBiZWNhdXNl
>IE9TUyBpcyB1cmdpbmcgdXMgdG8gaW5jcmVhc2UgdG8gNCBtaWxsaW9uIGxvY2tzLCB3aGlj
>aCBpc1xwYXINCmRvdWJsZSB0aGUgbG9ja3MgSSBoYXZlLlxwYXINClxwYXINClRoYW5rIHlv
>dSBpbiBhZHZhbmNlLlxwYXINClxwYXINCk5pY29sZSBDaGFuZ1xwYXINCkRhdGFiYXNlIEFu
>YWx5c3RccGFyDQpNL0EtQ09NLCBBIFR5Y28gSW50ZXJuYXRpb25hbCBDb21wYW55XHBhcg0K
>OTc4LTQ0Mi00Mzg5XHBhcg0KXHBhcg0KXHBhcg0KfQ==
>
>--openmail-part-39c80cdf-00000002--
>
>


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail

Messages In This Thread

• RE: Database locks
  -- Tuesday, 4 March 2003, at 12:41 p.m.

SAP Mix is maintained by Administrator with WebBBS 5.12.