--- In firebird-support@yahoogroups.com, "h_urlaf" <h_urlaf@y...>
wrote:

> --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
> wrote:
> To summarize what I've learned so far:
> * the client (converter process) starts a transaction with READ
> COMITTED, selects a candidate row, tries to obtain a pessimistic
> lock by doing an identity update on one of the columns with NO

WAIT,

> and if it succeeds,
> * marks the column as 'in progress', and commits the transaction.

Emiliano, since this moment record is free - any other transaction
will successfully obtain pessimistic lock (dummy update or Select With
Lock - no difference) on it. Sense of pessimistic locking is - create
version of the record and hold it during life of transaction which
created this version, any other attempts to update will get lock
conflict. You need to increase your understanding of transaction
isolation levels and conditions of lock conflict occurance. Can't say
where it is described most understandable, when I learned this I used
API Guide, chapter Working With Transactions. Rather heavy text, but I
don't know better one. Also if you want to depend on mark 'the record
is in progress', think how you will release this mark, particularly
if application whis set it will unexpectedly dye.

> Or could I just keep the first transaction open until the conversion
> is ready? That would make the 'mark in process' unnecesary, but I
> don't know how bad long transactions are.

Yes! You got it. For begining treat Select With Lock as "Select
With Update", it is close to truth - both create version of the record
but "Lock" don't fire triggers unlike "Update". Perhaps
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_lock_records
will help more. What about long read-write transaction - it depends.
On intensivity of transaction creation in your application, isolation
level of this transactions and intensivity of data modifications made
in this transactions. Short and crudely: If you started concurrency
transaction it will stop garbage collection in entire database until
it will be commited. If you started read_commited, it will not stop
collection of garbage made by another transactions but will hold,
naturally, own garbage, in discussed subject - versions of locked
record existed at moment of locking + version created making lock.
There are secondary effects related to gap Oldest Active Transaction -
Next Transaction but if speed of garbage creation (old, unneeded
versions of updated and deleted records) is low, not a big problem.
What is this "low" - can't say, I don't hold transactions more than a
few minutes. But if you have, say, 10 users who make complex
processing on client and make one or two updates in a hour - IMO you
should'nt worry about long-live transactions.

Best regards,
Alexander.