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

#11

webmaster (AT) vbusers (DOT) com (Andrew Baker) wrote in message news:<c19b84e5.0407290256.1e869438 (AT) posting (DOT) google.com>...

Quote:

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.

Quote:

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.

Quote:

3. Can you manually tune the sequence numbers to increment by
specified amounts?

Definitely.

Quote:

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.

Quote:

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

#12

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

andrew

ed.prochak (AT) magicinterface (DOT) com (Ed prochak) wrote in message news:<4b5394b2.0407290943.2a755a0b (AT) posting (DOT) google.com>...

Quote:

webmaster (AT) vbusers (DOT) com (Andrew Baker) wrote in message news:<c19b84e5.0407290256.1e869438 (AT) posting (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases.oracle comp.databases.oracle

Re: How to lock a row over a SELECT followed by an UPDATE - 07-29-2004 , 12:43 PM

Re: How to lock a row over a SELECT followed by an UPDATE - 07-30-2004 , 06:14 AM