Re: Lock strategies!

MaRcElO PeReIrA wrote:

> Dave,
>
> I actually use just the sequence, as you wrote!
>
> The biggest problem it that I *can't* have holes in
> that column, so it was because I used id (serial) and
> forn_id (integer).

You could maintain some sort of systemnumber table yourself

create table sysnum (
first int not null,
next int not null,
last int not null
latest_updater text not null,
the_time timestamp? not null);

and get your serial number from the next column.

However, this strategy demands the same logic from
all programs using the table:

pseudo Ada code

loop
begin transaction
select * from sysnum into some Adarecord;
update sysnum
set next=next+1
latest_updater = The_pid_or_name_of_your_process_or_thread
the_time=now (with good enough acurracy)
where
latest_updater = Adarecord.latest_updater and
The_time = Adarecord.The_time;

if Rows_Affected = 0 then
Rollback transaction;
else
commit transaction:
exit
end if;
(perhaps a small delay, say 0.05 sec?)
end loop;

you can get Rows_affected from PQ_Cmd_Tuples

if Rows_affected is 0 then you have a transaction conflict,
and must start all over again, to get a unique value.

What this does to performance, I don't know, but I do know it works,
IF AND ONLY IF all processes follow the same rule.

There should proberly be some code to handle when
you fall over the edge, ie next > last => next = first

/Björn