Re: How to lock a row over a SELECT followed by an UPDATE

Thanks to everyone who has helped me with this...

andrew

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0407290943.2a755a0b_at_posting.google.com>...
> webmaster_at_vbusers.com (Andrew Baker) wrote in message news:<c19b84e5.0407290256.1e869438_at_posting.google.com>...
> > After thinking through swapping to using a sequence number I came up
> > with a couple of potential gotchas:
> >
> > 1. When we fail over to our DR (disaster recovery) boxes the numbers
> > must continue their sequence. Would a sequence number work under this
> > circumstance?
>
> I think the real DBAs can answer this better. If the DR box is a
> mirror system, then it has the same value by definition. If you are
> talking about restoring the DB from backup, then note that sequences
> are backed up as well. So I'd say YES.
>
> >
> > 2. When the box reboots/crashes does it continue the sequence number
> > from where it was before the restart?
>
> the number from the last transaction. In ORACLE, some values are, by
> default, cached so they would be "lost" on restart. But the number of
> values cached is controllable, so you can reduce it to where none are
> lost.
> >
> > 3. Can you manually tune the sequence numbers to increment by
> > specified amounts?
>
> Definitely.
>
> >
> > 4. Is a sequence number system wide? ie. I can it be table specific
> > like IDENTITY columns in SQL server?
>
> The SEQUENCE is SCHEMA wide and identified by its own name. It is tied
> to a specific table via a trigger.
>
> >
> > thanks again
> > andrew
> >
>
> In your other post you mentioned the need for a auditable sequence of
> numbers (ie no gaps, or at least no large gaps). In that case, a table
> might be best. There have been other discussions of this topic. (do a
> GOOGLE search in comp.databases.oracle.misc)
>
> Keep in mind the differences
> SEQUENCE
> ORACLE controls number allocation allowing multiple sessions access
> without locking issues.
> cached values may be "lost" (meaing allocated but never used, creating
> "gaps")
> tied to a table by a trigger
> values accessed by pseudocolumns nextval, currval.
>
> SINGLE ROW TABLE
> single threaded access (via SELECT FOR UPDATE locks)
> no "lost" values so no gaps
> tied to a table by a trigger
> values accessed by custom functions.
>
> MULTIROW TABLE (has 2 attributes: value and used_flag, one row for
> every possible value, marked whether it's used or not)
> possible multithreaded access (controlled by custom functions)
> no lost values, but depending on allocation algorithm there can be
> temorary gaps
> tied to a table by a trigger
> values accessed by custom functions
>
> If you really need to not lose values, the table may be better. But
> the SEQUENCE can be set to cache nothing so it would not lose any
> values either. Test them both in production level enviroments
> (especially number of simultaneous requests).
>
> HTH,
> ed
Received on Fri Jul 30 2004 - 13:14:00 CEST