Re: Stupidity or sequences?
Date: Fri, 12 Apr 2013 17:03:28 +0100
Message-ID: <CAGRZYUd9O96t8cr5Bq9ybQp0M87N+kqdQeismxgEHdNFHJ4_qw_at_mail.gmail.com>
Johan
A mechanism that can work very well (especially for high volume batch processing) is to reserve many IDs in one round trip (I have seen this done with sequences as well as with code control tables, by setting the sequence interval accordingly). This can be done in a separate (perhaps autonomous) transaction.
So the program gets the next value, and then updates the control code table to (value + 500) say. It then manages that cache of 500 ID values internally, and doesn't need to go back to the database until the 500 values are exhausted. This is how Oracle manages its own sequences, of course, and has the same drawback, that unused cached IDs can be lost, so there may be large gaps. And if you have multiple processes doing this, the ID values won't be monotonic with time (just as they aren't when you pull cached sequence numbers from different RAC instances). If that doesn't bother you, this approach can be pretty much as scalable as you like.
HTH Nigel
On 12 April 2013 16:24, Johan Eriksson <valpis_at_gmail.com> wrote:
> Hi all,
> I think most of us has seen someone trying to be smart or trying to gain
> database independencies by not using oracle sequence but instead roll their
> own system by using a table, and a row for each "sequence".
> Almost every attempt on this I yet have seen has been plagued with row lock
> contention or other concurrencies, scalability zero...
>
> Have anyone actually seen some implementation of this kind work when load
> increase?
>
> /johan
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 12 2013 - 18:03:28 CEST