Subject Re: [firebird-support] How to hold lock on record
Author Bogusław Brandys
Ann W. Harrison wrote:
> At 12:16 PM 10/29/2004, =?ISO-8859-2?Q?Bogus=B3aw_Brandys?= wrote:
>>Sorry , I didn't mention that this question is related to my previous
>>question about unbroken series of document numbers wrapped monthly.
> The most frequently used solution in this case is to insert a document
> with a null number, using an artificial primary key. Commit that.
> Then in a single short transaction, generate your document number and
> update the document to have the new number.

Please be patient, but I don't understand why would this short
transaction started after inserting document, be better then update at
the end of single transaction ?

Ok,maybe becouse if this "generating of number" would become that last
instruction in stored procedure ,in the single transaction ,which in
turn modifies a lot of records here and there ,then commit stage will
take long time and during commit this single row will be locked (even if
this was the last instruction in transaction). Am I right ?

That will requires a
> scavenge program that runs from time to time, finding documents without
> numbers and asking their creators whether the document is still valid.
> Alternately, you can create a number, and store a null document under
> that number with enough information to identify the person who created
> it and the time of creation. Then you need a scavenge program that
> finds numbers without documents.

OK. Such program would for example find each document which date is not
current and number is null.No problem, but I'd like to avoid such
program if I can.

Best Regards
Bogusław Brandys