>> > OTOH DB2 and SQLServer take block level
>> > read locks, so they can do this too, but at major loss of
>concurrency
>> > and threat of deadlock.
>
>Note, that in the usual committed read isolation, they do not need to
>read lock a row ! e.g. Informix only verifies, that it could lock the
>row
>(that there is no write lock). Only cursor stability leaves one read
>lock
>until the next fetch, serializable actually leaves all read locks,
>and select for update an intent update lock.
Not sure which product you're thinking about there. No such isolation level in DB2 or SQLServer, AFAIK. Were you
talkingabout just Informix?
DB2:
Uncommitted Read (UR) mode "Dirty read" isn't the default, or the recommended lock level for most apps. I was
consideringCursor Stability mode (or higher), which is the default unless you specifically set the system default
otherwise.You can always skip the deadlock threat by using Uncommitted Read, by risking getting wrong results. There
isn'tanything there I would ever want to emulate.
SQLServer:
READ COMMITTED does take share locks. There's a NO LOCK hint, true, but its not a default. READ_COMITTED_SNAPSHOT, new
in2005, does row versioning like Oracle/PostgreSQL, and doesn't take locks.
Out of interest:
DB2 has learned from PostgreSQL that its OK to read a row and check whether it can see it before worrying about locks.
Therecently introduced DB2_EVALUNCOMMITTED and DB2_SKIPINSERTED flags provide PostgreSQL like behaviour, new in the
verylatest release.
Best Regards, Simon Riggs