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