| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: HowTo - create user defined sequence with proper serialization
p.santos..._at_gmail.com wrote:
> Steve Howard wrote:
> > p.santos000_at_gmail.com wrote:
> > > Folks,
> > > Here is a difficult question that I'm hoping you can help me solve.
> > >
> > > Requirements:
> > > - Create a user defined like "sequence" that increments by
> > > power of 2.
> > > - This defined "sequence" must distribute values like
> > > 1,2,4,16,256 etc ..
> > > - If a value is deleted, then the sequence must re-use that
> > > value.
> > > - acess to this "sequence" must be serialized so that no two
> > > users can
> > > grab the same value
> > >
> > > Example: (GROUPS table)
> > >
> > > ACCOUNT_ID NAME SEQ
> > > ======= ===== =====
> > > 347164311 GROUP1 1
> > > 347164311 GROUP2 2
> > > 347164311 GROUP2 4
> > >
> > > - So if account id " 347164311" wanted to create a new group in the
> > > GROUPS
> > > table, it would obtain a new sequence with the value of 16 and it
> > > would insert it.
> > >
> > > - If at some point GROUP2 was removed from the table, the custom
> > > sequence
> > > would distribute 2 as the "NEXTVAL" ..
> > >
> > > The key thing here is serialization and I'm not sure how to achive
> > > this.
> > > Here is the sample function that delivers the next available sequence,
> > > but
> > > doesn't yet serialize access to it.
> > >
> > > CREATE OR REPLACE FUNCTION GET_SEQ (ACCTID NUMBER)
> > > RETURN NUMBER IS
> > > i NUMBER := 0;
> > > two_power_i NUMBER := power(2,i);
> > > curr_mask groups.seq%TYPE;
> > >
> > > CURSOR masksCursor IS SELECT seq FROM groups WHERE account_id = acctid
> > > ORDER BY seq;
> > >
> > > BEGIN
> > > OPEN masksCursor;
> > > LOOP
> > > FETCH masksCursor INTO curr_mask;
> > > EXIT WHEN curr_mask != two_power_i OR masksCursor %NOTFOUND;
> > > i := i + 1;
> > > two_power_i := power(2,i);
> > > END LOOP;
> > > CLOSE masksCursor;
> > > RETURN two_power_i;
> > > END;
> > > /
> > >
> > > Any idea on how to make the above function serial so that if 2
> > > concurrent users execute the function, they don't both get the same
> > > value?
> > >
> > > -peter
> >
> > If you absolutely need the sequence numbers to be unique/reuseable,
> > etc., you'll have to change your SELECT to a SELECT FOR UPDATE. This
> > locks the current row so that no one else can update, and you also know
> > the value won't change between the time you read it and you update it.
> >
> > You can test this by opening two sessions. In the first one, issue
> > something like...
> >
> > select c from my_sequence for update;
> >
> > ...and in the second session, issue something like...
> >
> > update my_sequence set c = 1;
> >
> > The second one will hang until you commit or rollback in the first
> > session.
> >
> > I would try to use sequences if you can, because depending on your
> > load, you'll see a *lot* of waits on "enq: TX - row lock contention",
> > because, well, it's a queue :)
> >
> > Regards,
> >
> > Steve
> >
> > Regards,
> >
> > Steve
>
Peter,
You're going to need to be very "pessimistic" in your coding ...
-bdbafh Received on Tue Aug 29 2006 - 12:09:07 CDT
 |
 |