> Did you request the lock in share mode to
> request next val ? This would help to reduce
> the contention. Normal users would then only
> queue on the exclusive lock that you would
> take for the fix-up.
> lock(shared)
> increment sequence
> release
Good point. No I didn't. :(
actually the sequence is
lock(exclusive)
release
increment sequence
When the maintenance is done, the sequence requestor must wait on the lock
taken by the maintenance routine.
Otherwise there is very little waiting on the lock, as it is released immediately.
Jared
|
| "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Sent by: ml-errors@fatcity.com
01/14/2004 11:34 PM
Please respond to ORACLE-L
|
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
cc:
Subject: Re: Reset sequence at midnight |
Note in-line
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearance2:
March 2004 Hotsos Symposium - Keynote
March 2004 Charlotte NC - OUG Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L@fatcity.com>
Sent: Wednesday, January 14, 2004 7:09 PM
> Interesting, I've actually had to do this before.
>
> Be forewarned that this is not a good method to use for a very busy
> app, as it does introduce some level of serialization.
>
> control access to the sequence through a package
>
> Within the package use a function that sets a lock via dbms_lock.request
> and then immediately release the lock.
>
Did you request the lock in share mode to
request next val ? This would help to reduce
the contention. Normal users would then only
queue on the exclusive lock that you would
take for the fix-up.
lock(shared)
increment sequence
release
>
> The purpose of this will become clear in a moment.
>
> Create a procedure within the package that will be used to reset the
> sequence to 0. It is not necessary to drop the sequence to do this.
>
> eg.
>
> drop sequence s;
>
> create sequence s start with 100;
>
> select s.nextval from dual;
>
> declare
> vs integer;
> inc integer;
> junk integer;
> begin
lock(exclusive)
> select s.nextval into vs from dual;
> inc := 0 - vs;
> execute immediate 'alter sequence s minvalue ' || inc;
> execute immediate 'alter sequence s increment by '||inc;
> select s.nextval into junk from dual;
> execute immediate 'alter sequence s increment by 1';
release()
> end;
> /
>
> select s.nextval from dual;
>
>
> The procedure that does this just needs to take the same dbms_lock.request
> that the function mentioned earlier takes. The difference is that it does
> not
> release the lock until the modification of the sequence is completed.
>
> This forces any requests for new sequence numbers to wait for the
> modification
> to the sequence to complete.
>
> Jared
>
>
>
>
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: jonathan@jlcomp.demon.co.uk
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: Jared.Still_at_radisys.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Jan 15 2004 - 12:19:25 CST
