Re: counting rows

Hang on a minute, the whole point about the serialisable transaction isolation is that no other connections can insert rows within the key range your query is operating on.

So how on earth can anybody insert a row for that user if you are in the middle of a COUNT(*)?

The test you are doing is not realistic, you are isolating the connections by partition the data which in reality isn't something that happens.

My point is that the lock held by the trigger will be significantly less in duration than all those COUNT(*) queries.

Post the SQL you used for SQL Server 2000 and I'll take a look and code it to demonstrate what i'm talking about - current version is SQL Server 2005 by the way, its got the snapshot isolation and read committed without writers blocking readers now.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"E. Lefty Kreouzis" <lefty_at_goedel.home.rtfm.gr> wrote in message 
news:slrne2v1io.qol.lefty_at_goedel.home.rtfm.gr...


>

> Just to make things clear I did the test on 3 databases using 2 long

> running transactions. The results were as follows:

>

> Database Isolation Level Result

> Oracle 10g Read committed No locking

> PostgreSQL Read committed No Locking

> PostgreSQL Serializable No Locking

> SQL Server 2000 default (read commited?) Deadlock

>

> The above was done using the default implementation of count(*) for

> each database. My claim is that using a trigger would result in

> locking for all cases just because the trigger would update the

> /same/ row.

>

> Lefty Kreouzis