Forum OpenACS Q&A: Nextval from dual foolproof?

Posted by Ola Hansson on 11/17/00 09:19 PM

Hello,

In a project of mine I need to know what 'sequence_id' a certain insert produce so I looked over the scripts in /register to see how that's been solved. It left me wondering though if it isn't possible (at least in theory) that another insert makes it to the db before mine, leaving me with a false idea of what the the id is?
Is this ever a problem or am I just talking about hair-splitting?

If I can't trust that my actual 'sequence_id' will be what the nextval call suggests, are there better ways to solve this? (In my case it'll be a matter of tens, if not hundreds, of concurrent users each making several inserts per minute. *Not* new users😊
One more thing! What the case with the 'double click' thing in the catch statement below?

Example from /register

In /register/user-new.tcl the user_id is fetched from the sequence...

set db [ns_db gethandle]

set user_id [database_to_tcl_string $db "select
user_id_sequence.nextval from dual"]
# we don't need it anymore so let's release it for another thread
ns_db releasehandle $db

...and in the next; /register/user-new-2.tcl that user_id is inserted in db along these lines:

set double_click_p 0

set db [ns_db gethandle]

with_catch errmsg {
    ns_db dml $db "begin transaction"
    ns_db dml $db $insert_statement
} {
    # if it was not a double click, produce an error
    ns_db dml $db "abort transaction"
    if { [database_to_tcl_string $db "select count(user_id) from users
where user_id = $user_id"] == 0 } {
	ns_db releasehandle $db
	ad_return_error "Insert Failed" "We were unable to create your user
record in the database."
	ns_log Error "Error insert new user:
$errmsg"
	return 
    } else {
	# assume this was a double click
	set double_click_p 1
    }
}

if { $double_click_p == 0 } {
    ns_db dml $db "end transaction"
}
ns_db releasehandle $db

Thank you!

2: Response to Nextval from dual foolproof? (response to 1)

Posted by Don Baccus on 11/17/00 10:37 PM

"nextval" is guaranteed, in Oracle as well as Postgres, to return a unique value, and "currval" the current value for the sequence in YOUR context.

Let's say process A does a nextval and gets 1
process B does a nextval and gets 2
process A does a currval and gets 1
process A does a nextval and gets 3

The thing you CAN'T depend on is nextval being one greater than currval.

You can't do a currval before a nextval (because you must aquire a sequence value before currval is set for your context).

This also means that code like:

insert into foo value (nextval('sequence_name'), ...);
insert into foo_related (currval('sequence_name'), ...);

guarantees that the value derived from the sequence is the same. There's no need for you to do any explicit locking on the sequence or anything screwy like that.

The other thing you're asking about is that the pages allocate an id from a sequence early, in the form page rather than the script that services the POST from the form. If the response time is slow (this is the internet, remember? :) the user might get think that nothing's happening and click "submit" the same. Since the form page has allocated the id, this will result in a POST with the same id value being passed to the second page. When this is inserted into the database, an exception will be raised because the first page handling the post will have already inserted the value (well, we have a race condition so we don't really know which "click" gets processed first, but we don't care).

If the page processing the POST allocated the ID from the sequence, the script handling the POST would allocate two differnet ids, and insert whatever it builds from the form, with no error since the ids wouldn't violate the primary key uniqueness requirement. So, for instance, your bulletin board post or ad or whatever would show up twice.

The double-click code helps to prevent this.

3: Response to Nextval from dual foolproof? (response to 1)

Posted by Ola Hansson on 11/18/00 11:18 AM

Thanks a lot for the explanation!