Subject Re: [firebird-support] Re: How to implement SQLServer's WITH(NOLOCK) statment?
Author Helen Borrie
At 11:56 PM 14/04/2008, Martijn Tonies wrote:

>> >>
>> >> ?
>> >
>> > Right, I'll rephrase, "no plain select is ..." ;-)
>> >
>> > Either way, I don't think such a select would lock out other
>> > readers, but would only lock for other updaters?
>> It will lock other readers as well. ;-)
>Hm, nasty.

That's why the documentation explicitly warns people to know and understand what they're doing when using the pessimistic lock. It is *certainly* not to be used with a query that returns more than a tiny few rows (ideally, one row).

However, as Thomas pointed out, even without the pessimistic lock, there *are conditions* where inappropriate settings on two read committed transactions can cause one of the transactions to be blocked from reading while the other has a write pending on the selected rows (or on the master row[s] where a FK relationship is involved).