Re: HowTo - create user defined sequence with proper serialization

p.santos000_at_gmail.com wrote:
> The key thing is that if in the event that there are 2
> people creating groups for the same customer .. it's important that one
> blocks the other so that they both don't pull back the same value.

If you use the FOR UPDATE, they shouldn't, depending on your function, of course. A SELECT FOR UPDATE cursor isn't even executed until the user with the first lock commits or performs a rollback of his transaction. You can test this by the following...

create a table with one column, and insert one row with value of 1 into it and commit
issue a select for update of that one row update the row to 2
in a second session, issue a select for update against the table commit in the first session
the second session should immediate get back one row with 2 as the value.

That way, your function would be guaranteed to see and more importantly update, values in the table that would not be subject to "phantom" application updates (changing while you are assuming they are not).

Regards,

Steve Received on Tue Aug 29 2006 - 13:30:42 CDT