Re: gprof SELECT COUNT(*) results

>> > 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 talking about just Informix?

DB2:
Uncommitted Read (UR) mode "Dirty read" isn't the default, or the recommended lock level for most apps. I was considering Cursor 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't anything 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 in 2005, 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. The recently introduced DB2_EVALUNCOMMITTED and DB2_SKIPINSERTED flags provide PostgreSQL like behaviour, new in the very latest release.

Best Regards, Simon Riggs