Re: The DBMS_LOCK package

From: <markp28665_at_aol.com>
Date: 1996/11/14
Message-ID: <19961114230900.SAA03854@ladder01.news.aol.com>#1/1

Author states:

(1) DBMS_LOCK is quite slow especially when a lot of locks are used.
(2) DBMS_LOCK does not report who (sessions etc.) holds a lock.
(3) DBMS_LOCK has no monitor that allows to inspect the current locks.
(4) DBMS_LOCK has problmes when using more than 10000 locks.

And concludes by stating that he is thinkin of writing own lock package.

My opinions -
1) I suggest you rethink writing your own lock package. Unless your application is pure batch, you will run into Oracle lock waits on your locks if you use Oracle tables to hold your locks. Placing commits in your code free the lock records can have undesirable side effects on the application. Pure batch could use OS files and remove the lock management problem from Oracle, but can not guarentee access control from sql*plus etc...
2) You can see DBMS_LOCKS allocated via the v$lock table as type 'UL'. You can join v$lock to v$session for more information. See your DBA if you can not see and are not familiar with the v$ tables. 3) Locks take a lot of memory. Any application that needs 1000 locks much less the 10,000 listed above needs very careful consideration. There are no clues to the purpose, number of tables, modules, or users so maybe the number is not outlandish, but it seems impractical to me.

I do not wish to sound negative so I offer the following for consideration.
1) Control access to the tables through stored procedures/packages to insure all applications use the same order of accessing table rows

2)  Database referential integrity rules
3)  Triggers to verify availability from an application point of view
4)  Some form of message switching where application module inserts into

table
msg which is read by a continuously running batch program which reads commited rows, updates other tables as appropriate, deletes the msg, and inserts a task completed message into a reply table. We use a similiar process for a couple of applications that run 24 hrs accross multiple distributed systems that have down windows. The data accumulates until the other system can process it and reply records get sent. The production line does not stop for lack of a reply and the production numbers will show as soon as the missing system is started back up.

Best of luck with whatever this is.

Mark Powell -- The only advise that counts is the advise you follow  so follow your own advise Received on Thu Nov 14 1996 - 00:00:00 CST