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

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 29 Jul 2004 10:43:48 -0700
Message-ID: <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 Thu Jul 29 2004 - 19:43:48 CEST

Original text of this message