Subject Re: [firebird-support] Re: How can I use a DirtyRead Isolation with Firebird?
Author Ann W. Harrison
thecrgrt wrote:
>>Perhaps you don't understand that pessimistic locking is unnatural to
>>the way Firebird deals with transaction isolation. It has optimistic
>>row-level locking on all reads. This means it does not lock anything
>>until a request arrives to change a row. At that point, the changed
>>row becomes unavailable for writes by any other transaction; but all
>>of the unchanged rows can be accessed by other transactions.
> If this true, why an error has occured when I've used a same SELECT
> with "WITH LOCK" from other transaction? what does it mean about "it
> does not lock anything until a request arrives to change a row"? when
> Firebird will know the row is needed to change?

A select "WITH LOCK" performs a limited update of the row, establishing
the current transaction as the owner. It's a "limited" update because
it doesn't fire triggers.
>>You really must take notice of the warnings in the release notes
>>about using WITH LOCK and never use it for SELECTs that return
>> multiple rows.
> I've read this, it sound like WITH LOCK is useless. what's is the main
> idea of Firebird's explicit lock? and it was designed for what?

Since Firebird (and InterBase) managed to go through seven major
releases without a "WITH LOCK", you might conclude that it's not
essential. However, there are some cases where locking a row is
convenient - if for example, you've got an order record with a
bunch of dependent item record and want to change the items while
excluding others from changing the items or order. That can be
done without an explicit lock, but requires that triggers be
designed not to fire when the record is locked ... Somebody
needed that ability, so he implemented it in the engine.