Re: counting rows
Date: Sun, 2 Apr 2006 11:02:15 +0000 (UTC)
Message-ID: <slrne2vbpq.rpd.lefty_at_goedel.home.rtfm.gr>
On 2006-04-02, Tony Rogerson <tonyrogerson_at_sqlserverfaq.com> wrote:
> 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.
>
No I'm not, the two transactions are inserting rows with different primary keys. Easy to do with autoincrement columns.
> So how on earth can anybody insert a row for that user if you are in the
> middle of a COUNT(*)?
>
the row that the other transaction is insering is not visible to the transaction that does thw count(*) so that it shouldn't modify the result.
> 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.
>
As I said, I am not partitioning anything.
> My point is that the lock held by the trigger will be significantly less in
> duration than all those COUNT(*) queries.
>
As I have said again the lock that the trigger will hold will live for the duration of the holding transaction which can be quite large (think long running transactions).
> 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.
>
I only have access to an MSDE version of SQL server 200 so that is what I ran my tests against. I used SquirrelSQL and the freeTDS jdbc driver, opened two connections, and disabled autocommit.
Then I did the insert, select in the two windows.
It is good that SQL Server 2005 has this feature now.
Lefty Kreouzis Received on Sun Apr 02 2006 - 13:02:15 CEST