Alexander V.Nevsky wrote:

>--- 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.
>
>

Just to be sure (and it may be the key point for Emiliano AND for me,
since i'm getting more and more confused):

1) If there's a newer and uncommitted version of a row, NO OTHER
TRANSACTION REGARDLESS OF ITS ISOLATION LEVEL is allowed to either
update or remove the row and it doesn't even see it, although it may (or
may not, this depends on the I. L.) see it for SELECTs; and...

2) Having said that, it's a feature intended for as least frequent usage
as it can be.

Am I right?

Emiliano, the key - as far as i understand - is, that once you SELECT
FOR UPDATE WITH LOCK that one particular row, you can safely convert
your BLOB to your heart's content and no other transaction will render
your CPU time.

A possibly stupid question: What if there were more rows to be updated?
Using SELECT FOR UPDATE WITH LOCK in an SP or using ESQL allows safe
time-consuming manipulations with a single row (SELECT WITH LOCK ->
manipulate -> UPDATE WHERE CURRENT OF). But if i updated let's say 10
rows which would take some 30 minutes and in the 27th minute i'd have a
transaction collision, the previous 27 min's would have to be rolled
back. (I intentionally set aside the nature of the transaction, ic ould
be for example converting some document formats, it doesn't
matter...something atypical, that's the point)

Does SFUWL work even for SELECTs that aren't 'a part of a cursor
construction?' (sorry, can't come up with a better name...) I mean, does
it lock the records with creating a new version without firing triggers
even when called per se, outside SP, for example? (So that the rest of
the transaction can safely crunch the data and update it after a while
without risking lost CPU cycles and still without resorting to SNAPSHOT
TABLE STABILITY or RESERVING ... FOR PROTECTED WRITE which would be an
overkill for updating few rows)

These are still unmapped waters for me and i'm definitely going to read
http://www.ibphoenix.com/a7365.htm ASAP (which is something Emilio
should also do, if he hadn't read it before asking here :).

Jakub Hegenbart