1 package dbms_lock is
2
3
4
5 ------------
6 -- OVERVIEW
7 --
8 -- These routines allow the user to request, convert and release locks.
9 -- The locks are managed by the rdbms lock management services. All
10 -- lock ids are prepended with the 'UL' prefix so that they cannot
11 -- conflict with DBMS locks. These locks will show up in the SQL*DBA
12 -- lock monitor screen and in the appropriate fixed views.
13 --
14 -- Deadlock detection is performed on these locks.
15 --
16 -- Locks are automatically released when the session terminates.
17 -- It is up to the clients to agree on the use of these locks. The
18 -- lock identifier is a number in the range of 0 to 1073741823.
19 --
20 -- The allocate_unique call can be used to allocate a unique lockid
21 -- (in the range of 1073741824 to 1999999999) given a lock name. This is
22 -- provided since it may be easier for applications to coordinate
23 -- their use of locks based on lock names rather than lock numbers.
24 -- The first session to call allocate_unique with a new lock name will
25 -- cause a unique lockid to be generated and stored in the
26 -- dbms_lock_allocated table. Subsequent calls (usually by other
27 -- sessions) will return the lockid previously generated. A lock name
28 -- will be associated with the returned lockid for at least
29 -- 'expiration_secs' (defaults to 10 days) past the last call to
30 -- allocate_unique with the given lock name. After this time, the row
31 -- in the dbms_lock_allocated table for this lock name may be deleted
32 -- in order to recover space. Allocate_unique performs a commit.
33 --
34 -- A sleep procedure is also provided which causes the caller to sleep
35 -- for the given interval.
36
37
38 ------------------------------------------------
39 -- SUMMARY OF SERVICES PROVIDED BY THIS PACKAGE
40 --
41 -- allocate_unique - allocate a unique lock given a name
42 -- request - request a lock of given mode
43 -- convert - convert lock from one mode to another
44 -- releas - release the lock
45 -- sleep - sleep for the specified time
46
47
48 ---------------
49 -- LIMITATIONS
50 --
51 -- The implementation does not support large numbers of locks efficiently.
52 -- A few hundred locks per session should be the limit.
53
54
55 ------------
56 -- SECURITY
57 --
58 -- There may be OS-specific limits on the maximum number of total
59 -- locks available. You will need to consider this when using locks,
60 -- or making this package available to users. You may wish to only
61 -- grant execute to those users or roles that you trust. An
62 -- alternative is to create a cover package for this package which
63 -- limits those locks used. Then, instead of granting execute on this
64 -- package to public, grant execute on the cover package
65 -- only to specific users. A cover package might look like this:
66 --
67 -- create package lock_100_to_200 is
68 -- nl_mode constant integer := 1;
69 -- ss_mode constant integer := 2;
70 -- sx_mode constant integer := 3;
71 -- s_mode constant integer := 4;
72 -- ssx_mode constant integer := 5;
73 -- x_mode constant integer := 6;
74 -- maxwait constant integer := 32767;
75 -- function request(id in integer,
76 -- lockmode in integer default x_mode,
77 -- timeout in integer default maxwait,
78 -- release_on_commit in boolean default FALSE)
79 -- return integer;
80 -- function convert(id in integer;
81 -- lockmode in integer,
82 -- timeout in number default maxwait)
83 -- return integer;
84 -- function release(id in integer) return integer;
85 -- end;
86 -- create package body lock_100_to_200 is
87 -- begin
88 -- function request(id in integer,
89 -- lockmode in integer default x_mode,
90 -- timeout in integer default maxwait,
91 -- release_on_commit in boolean default FALSE)
92 -- return integer is
93 -- begin
94 -- if id < 100 or id > 200 then
95 -- raise_application_error(-20000,'Lock id out of range');
96 -- endif;
97 -- return dbms_lock.request(id, lockmode, timeout, release_on_commit);
98 -- end;
99 -- function convert(id in integer,
100 -- lockmode in integer,
101 -- timeout in number default maxwait)
102 -- return integer is
103 -- begin
104 -- if id < 100 or id > 200 then
105 -- raise_application_error(-20000,'Lock id out of range');
106 -- endif;
107 -- return dbms_lock.convert(id, lockmode, timeout);
108 -- end;
109 -- function release(id in integer) return integer is
110 -- begin
111 -- if id < 100 or id > 200 then
112 -- raise_application_error(-20000,'Lock id out of range');
113 -- endif;
114 -- return dbms_lock.release(id);
115 -- end;
116 -- end;
117 --
118 -- Grant execute on the lock_100_to_200 package to those users who
119 -- are allowed to use locks in the 100-200 range. Don't grant execute
120 -- on package dbms_lock to anyone. The lock_100_200 package
121 -- should be created as sys.
122 --
123 -- The "dbms_session.is_role_enabled" procedure could also be used
124 -- in a cover package to enforce security.
125
126 ---------------------
127 -- SPECIAL CONSTANTS
128 --
129 nl_mode constant integer := 1;
130 ss_mode constant integer := 2; -- Also called 'Intended Share'
131 sx_mode constant integer := 3; -- Also called 'Intended Exclusive'
132 s_mode constant integer := 4;
133 ssx_mode constant integer := 5;
134 x_mode constant integer := 6;
135 -- These are the various lock modes (nl -> "NuLl", ss -> "Sub Shared",
136 -- sx -> "Sub eXclusive", s -> "Shared", ssx -> "Shared Sub eXclusive",
137 -- x -> "eXclusive").
138 --
139 -- A sub-share lock can be used on an aggregate object to indicate that
140 -- share locks are being aquired on sub-parts of the object. Similarly, a
141 -- sub-exclusive lock can be used on an aggregate object to indicate
142 -- that exclusive locks are being aquired on sub-parts of the object. A
143 -- share-sub-exclusive lock indicates that the entire aggregate object
144 -- has a share lock, but some of the sub-parts may additionally have
145 -- exclusive locks.
146 --
147 -- Lock Compatibility Rules:
148 -- When another process holds "held", an attempt to get "get" does
149 -- the following:
150 --
151 -- held get-> NL SS SX S SSX X
152 -- NL SUCC SUCC SUCC SUCC SUCC SUCC
153 -- SS SUCC SUCC SUCC SUCC SUCC fail
154 -- SX SUCC SUCC SUCC fail fail fail
155 -- S SUCC SUCC fail SUCC fail fail
156 -- SSX SUCC SUCC fail fail fail fail
157 -- X SUCC fail fail fail fail fail
158 --
159 maxwait constant integer := 32767;
160 -- maxwait means to wait forever
161
162 ----------------------------
163 -- EXCEPTIONS
164 --
165
166 badseconds_num NUMBER := -38148;
167
168 ----------------------------
169 -- PROCEDURES AND FUNCTIONS
170 --
171 procedure allocate_unique(lockname in varchar2,
172 lockhandle out varchar2,
173 expiration_secs in integer default 864000);
174 -- Given a name, generate a unique lockid for this lock. This procedure
175 -- always performs a 'commit'.
176 -- Input parameters:
177 -- lockname
178 -- name of lock to generate unique lockid for. If this name already
179 -- has been assigned a lockid, then return a handle to that lockid.
180 -- Otherwise generate a new lockid and return a handle to it.
181 -- WARNING: Do not use locknames beginning with 'ORA$'; these names
182 -- are reserved for products supplied by Oracle Corporation. The
183 -- name can be up to 128 bytes, and is case-sensitive.
184 -- expiration_secs
185 -- number of seconds after an 'allocate_unique' is last performed on
186 -- this lock name that this lock is subject to cleanup (i.e.,
187 -- deleting from the dbms_lock_allocated table). Defaults to 10
188 -- days.
189 -- Output parameters:
190 -- lockhandle
191 -- The actual lockid is not returned, rather a handle to it is
192 -- returned. Use this handle in subsequent calls to request,
193 -- convert and release. Up to 128 bytes are returned. A handle
194 -- is used to reduce the chance that a programming error can
195 -- accidentally create an incorrect but valid lockid. This will
196 -- provide better isolation between different applications that are
197 -- using this package.
198 --
199 -- All sessions using a lockhandle returned by a call to
200 -- allocate_unique using the same name will be referring to the same
201 -- lock. Different sessions may have different lockhandles for the
202 -- same lock, so lockhandles should not be passed from one session
203 -- to another.
204 --
205 -- The lockid's generated by allocate_unique are between 1073741824
206 -- and 1999999999, inclusive.
207 --
208 -- This routine will always do a commit.
209 --
210 -- Errors raised:
211 -- -20000, ORU-10003: Unable to find or insert lock <lockname>
212 -- into catalog dbms_lock_allocated.
213
214 procedure allocate_unique_autonomous(lockname in varchar2,
215 lockhandle out varchar2,
216 expiration_secs in integer default 864000);
217 -- Autonomous version of the allocate_unique procedure. The Input
218 -- and Output parameters are the same as that of allocate_unique.
219 -- This procedure works exactly same as that of allocate_unique, except
220 -- that the procedure will run as an autonomous transaction. Therefore the
221 -- commits in this procedure will not affect the calling procedure.
222
223 function request(id in integer,
224 lockmode in integer default x_mode,
225 timeout in integer default maxwait,
226 release_on_commit in boolean default FALSE)
227 return integer;
228 function request(lockhandle in varchar2,
229 lockmode in integer default x_mode,
230 timeout in integer default maxwait,
231 release_on_commit in boolean default FALSE)
232 return integer;
233 -- Request a lock with the given mode. Note that this routine is
234 -- overloaded based on the type of its first argument. The
235 -- appropriate routine is used based on how it is called.
236 -- If a deadlock is detected, then an arbitrary session is
237 -- chosen to receive deadlock status.
238 -- ***NOTE*** When running both multi-threaded server (dispatcher) AND
239 -- parallel server, a multi-threaded "shared server" will be
240 -- bound to a session during the time that any locks are held.
241 -- Therefore the "shared server" will not be shareable during this time.
242 -- Input parameters:
243 -- id
244 -- From 0 to 1073741823. All sessions that use the same number will
245 -- be referring to the same lock. Lockids from 2000000000 to
246 -- 2147483647 are accepted by this routine. Do not use these as
247 -- they are reserved for products supplied by Oracle Corporation.
248 -- lockhandle
249 -- Handle returned by call to allocate_unique.
250 -- lockmode
251 -- See lockmodes and lock compatibility table above
252 -- timeout
253 -- Timeout in seconds. If the lock cannot be granted within this
254 -- time period then the call returns a value of 1. Deadlock
255 -- detection is performed for all "non-small" values of timeout.
256 -- release_on_commit
257 -- If TRUE, then release on commit or rollback, otherwise keep until
258 -- explicitly released or until end-of-session. If a transaction
259 -- has not been started, it will be.
260 -- Return value:
261 -- 0 - success
262 -- 1 - timeout
263 -- 2 - deadlock
264 -- 3 - parameter error
265 -- 4 - already own lock specified by 'id' or 'lockhandle'
266 -- 5 - illegal lockhandle
267 --
268 function convert(id in integer,
269 lockmode in integer,
270 timeout in number default maxwait)
271 return integer;
272 function convert(lockhandle in varchar2,
273 lockmode in integer,
274 timeout in number default maxwait)
275 return integer;
276 -- Convert a lock from one mode to another. Note that this routine is
277 -- overloaded based on the type of its first argument. The
278 -- appropriate routine is used based on how it is called.
279 -- If a deadlock is detected, then an arbitrary session is
280 -- chosen to receive deadlock status.
281 -- Input parameters:
282 -- id
283 -- From 0 to 1073741823.
284 -- lockhandle
285 -- Handle returned by call to allocate_unique.
286 -- lockmode
287 -- See lockmodes and lock compatibility table above.
288 -- timeout
289 -- Timeout in seconds. If the lock cannot be converted within this
290 -- time period then the call returns a value of 1. Deadlock
291 -- detection is performed for all "non-small" values of timeout.
292 -- Return value:
293 -- 0 - success
294 -- 1 - timeout
295 -- 2 - deadlock
296 -- 3 - parameter error
297 -- 4 - don't own lock specified by 'id' or 'lockhandle'
298 -- 5 - illegal lockhandle
299 --
300 function release(id in integer) return integer;
301 function release(lockhandle in varchar2) return integer;
302 -- Release a lock previously aquired by 'request'. Note that this routine
303 -- is overloaded based on the type of its argument. The
304 -- appropriate routine is used based on how it is called.
305 -- Input parameters:
306 -- id
307 -- From 0 to 1073741823.
308 -- Return value:
309 -- 0 - success
310 -- 3 - parameter error
311 -- 4 - don't own lock specified by 'id' or 'lockhandle'
312 -- 5 - illegal lockhandle
313 --
314 procedure sleep(seconds in number);
315 -- Suspend the session for the specified period of time.
316 -- Input parameters:
317 -- seconds
318 -- In seconds, currently the maximum resolution is in hundreths of
319 -- a second (e.g., 1.00, 1.01, .99 are all legal and distinct values).
320
321 end;
[Home] [Help]