--- In ib-support@yahoogroups.com, "Woody \(TMW\)" <woody.tmw@i...>
wrote:

> access the information at a time. Since IB/FB doesn't do row level

locking,

> it takes a little bit of work but it can be done. For example, a

table with

> a structure like this:
>
> CompanyID Integer
> NextID Integer
> Locked Char
>
> You can then build a procedure in code that first checks the Locked

field

> for the company you are looking at. If the Locked field is 'N', then

you try

> to change it to 'Y' and post the change. If the change gets posted,

you can

> get the NextID for that company and increment it. Then post that

change, set

> Locked to 'N' and release the transaction. Anyone who needs to get

the

> NextID for that company will not be able to until you release the
> transaction since it will error for them when they try to change the

Locked

> field. It's not exactly high-tech, but it works in some situations

like

> this.
>
> Of course, the one drawback is that you have to build an

administrator

> function that can clear the locks in case someone crashes after

posting the

> Locked field. <g>
>

Woody, when it is really needed, row locking is very easy emulated
in FB - just update some non-indexed column to it's value and don't
commit transaction. Note that BDE masks this feature ignoring such
dummy updates. So (let's take read_commited rec_version mode only for
simplification), we shall

1. Remove Locked column from your table
2. Start transaction
3. Update Gen_Emul_Table Set NextID=NextID+1

if any other user made the same and did'nt commited yet
a) if your transaction run in wait mode, you'll wait for
commit/rollback of this user and after that make your increment
b) if your transaction run in nowait mode, you'll get lock conflict
exception and can restart transaction and try again or suggest to user
wait some time and try once more.
When you had locked record and incremented counter simultaneously,
you can safely read NextID. If you don't care about holes in
numeration and need high performance, you can do it in separate
transaction and commit it just after reading. If you need auditable
sequence, you should do it in the same transaction with document
creation and if it failed or rolled back counter will be rolled back
too. But, as I said earlier, this is bottleneck.

Best regards, Alexander.