Re: How to create pl/sql that run efficiently in background ?

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 04 Feb 2009 10:57:23 +0100
Message-ID: <49896681$0$186$e4fe514c_at_news.xs4all.nl>



phil_herring_at_yahoo.com.au schreef:
> No gaps? Okay.
>
> Don't select the max(pk)+1 from the actual table to get the next
> value, because it's inherently fragile (under some circumstances
> you'll end up with a whole bunch of blocking locks), and a rollback
> can leave a gap.
>
> Instead, pre-load a separate table with a reasonable number of unused
> values. When you create a new record, lock this table, select the
> smallest value, then delete it. If your whole OLTP transaction
> subsequently commits, you get to keep the number. If it rolls back,
> the number returns to the source table automatically and will be used
> for the next record.
>
> This gives you "no gaps".
>
> The table of numbers will only have one column, so it can probably
> have some thousands of values and still stay in memory. A background
> process can run periodically to top it up. By the sounds of it, every
> 24 hours is probably sufficient.
>
> You will get contention, but it won't be as bad as you'd get if you
> were getting max(pk)+1 from the main table.
>
> -- Phil

Well, instead of inserting a lot of values, since this table is locked completely, you have to store only 1 (one) value, and add 1 to it while using its value. A rollback would undo this too. Saves us a background process.

And so we constructed our own sequence, causing a lot of blocking locks, all for the sake of 'no gaps'.

Shakespeare Received on Wed Feb 04 2009 - 03:57:23 CST

Original text of this message