Re: HowTo - create user defined sequence with proper serialization

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 Received on Tue Aug 29 2006 - 11:29:58 CDT