Subject Re: [firebird-support] Unbroken series of numbers
Author Bogusław Brandys
Ann W. Harrison wrote:
> At 03:55 PM 10/27/2004, =?ISO-8859-2?Q?Bogus=B3aw_Brandys?= wrote:
>>I'd like to know how in Firebird is possible to have unbroken series of
>>for example document's numbers but wrapped monthly for example.
> The answer depends somewhat on how many documents you expect to store
> and whether you need to be absolutely certain that you never get a hole
> in the sequence.

Yes,I must be sure that no hole exists ;-) Even delete documents should
be only hidden but not really delete (for audit purpose)

> If the answer that holes are OK, then use a generator and reset
> it monthly.

I wish I can use this solution,but probably I coudn't.

> If the answer is a few, and holes are not OK, then setup a table
> that contains only the next number. Reset the number monthly.
> Increment the value by updating the record that holds it before
> storing the document. If your transaction rolls back, the number
> becomes available again.

This is what I do currently, but I wish to avoid deadlock or lock
conflict when updating this value.Today these are only tests, but with
100 or more users using the same database over rather slow 128 kb/s ADSL
,this probably will going to be a problem.

> If the answer is a lot and holes are not OK, then you've got a
> problem and should look at the "auditable sequences" answers.

That's I'm looking for , but unfortunately article about audible
sequences does not mention how to use generators for audible series of
numbers but with limited period (if it's possible).
Is resetting generators when new month starts not dangerous ?

Best Regards
Bogusław Brandys