Prev: Count Deleted from Output Trick?
Next: Using xp_fixeddrives on a linked server
From: Jack W. on 24 Feb 2010 11:18
> From your original post, I got the impression that you only had
> insertions,
> but I was very tired last night, so I might have misunderstood.

In my case, I have a particular piece of code that's a bottleneck for the
whole app. All it does are insertions that should be kept hidden from other
users until the transaction is committed.

> Anyway, say that one client insert a row with a value of col = 45 and
> leaves
> the transaction uncommitted. Another client now tries to read rows where
> col = 99. If there is an index on col, this should work fine. If there is
> not, SQL Server has to scan the table, and will be blocked on the
> uncommitted row.

Ok, but let's say the client reading 99 has a WHERE clause with multiple
conditions like:

WHERE SomeField = 'Whatever' AND (more conditions)

"SomeField" is indexed and it will never be found in the "45" row. This is
my situation so I wouldn't expect blocking to be an issue. I'm assuming (can
you confirm this) that the transaction will lock row 45 but the WHERE clause
should always exclude that row (given the index on "SomeField"). Apparently
it's not. It appears that it's trying to read it anyway and the transaction
has it locked. I'm not sure how to confirm this in the profiler. I was able
to confirm that once I insert a record in the transaction, it's apparently
blocking any attempt to read the table whatsoever until the transaction is
committed. I inspected the "sysprocesses" table, looking at the blocking
column, etc.

<snip>
> With this setting, READ COMMITTED is implemented using the snapshot. The
> difference to true snapshot isolation is that if you start reading at time
> T and at time T1 another process commits an update, and you read that row
> at time T2, you will see that update. With true snapshot, you would not.

Thanks. I'll play around with this and your other suggestions though it does
seem heavy-handed. Not sure at this stage if it's appropriate, a potential
bottle neck, or just a hack to fix my problem. Still gathering experience
however. Thanks again for your help. Greatly appreciated.

From: Jack W. on 24 Feb 2010 15:15
Just wanted to thank you again (and the others). It looks like your snapshot
suggestion completely fixes the problem. It makes things behave the way I
originally thought they behaved, hiding all transaction changes until
committed. I have more work to do to make sure I completely understand the
details (and possible ramifications) but I seem to be on the right track
again. For the benefit of others, the following article really elucidates
the situation like no other I've come across:

http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level

Thanks again

From: Erland Sommarskog on 24 Feb 2010 17:47
Jack W. (_no_spam@_no_spam.com) writes:
> Ok, but let's say the client reading 99 has a WHERE clause with multiple
> conditions like:
>
> WHERE SomeField = 'Whatever' AND (more conditions)
>
> "SomeField" is indexed and it will never be found in the "45" row. This
> is my situation so I wouldn't expect blocking to be an issue. I'm
> assuming (can you confirm this) that the transaction will lock row 45
> but the WHERE clause should always exclude that row (given the index on
> "SomeField"). Apparently it's not. It appears that it's trying to read
> it anyway and the transaction has it locked.

OK, so we did not tell you that just because there is an index, that
does not mean that SQL Server will use it. I assume this is a non-clustered
index. The optimizer may estimate that it cheaper to scan the table
than to use the index, as the latter could impose many key lookups. It
could also be the case that you have an implicit conversion that voids
the index. Or something else.

Again, I like to stress that since you insist of not showing any code
or tables, you can only get general answers, and they may not be whole-
covering. But you can view the query plan to see whether the index is
used.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Jack W. on 25 Feb 2010 07:35
> OK, so we did not tell you that just because there is an index, that
> does not mean that SQL Server will use it.

Well aware of that :)

> I assume this is a non-clustered index.

Yes

> The optimizer may estimate that it cheaper to scan the table
> than to use the index, as the latter could impose many key lookups. It
> could also be the case that you have an implicit conversion that voids
> the index. Or something else.

Very brittle IOW :)

> Again, I like to stress that since you insist of not showing any code
> or tables, you can only get general answers, and they may not be whole-
> covering.

Haven't posted anything since there are several tables involved in the
transaction but the snapshot seems to work as advertised. I only need it in
this one critical part of the code only, where the transaction may be held
open for a long time (20-30 seconds typically, maybe as long as several
minutes under extreme conditions). During this time it's inserting many
records that nobody needs until committed (i.e., nobody will ever be
accessing them until then).
I've now wrapped these insertions in a transaction with snapshot isolation
and it no longer seems to be blocking anyone. Are you aware of any reasons
why it might eventually fail (cause blocking) or why it would be preferable
not to go this route? It has the advantage that all these insertions are now
hidden from other clients until committed (the natural way it should be IMO,
at least by default) and other clients don't need to do anything special,
such as changing the isolation level to prevent blocking. Neither NOLOCK nor
any special keywords need be applied, and I don't have to worry about
indexes being constructed to avoid the newly inserted records (to prevent
blocking). Even if the indexes worked today (without snapshot isolation), it
could break in 6 months if MSFT changes something or someone on my side
might break it by accident. We'd constantly have to be on guard which is
error-prone and counter-productive. I can post the code and tables here if
you're saying there's a reason I shouldn't use snapshot (in the one and only
case I'll be using it, as just described).

> But you can view the query plan to see whether the index is used.

I've never actually done this but was aware of it. I just need to look up
how.

Thanks again.

From: Erland Sommarskog on 27 Feb 2010 13:37
Jack W. (_no_spam@_no_spam.com) writes:
> Haven't posted anything since there are several tables involved in the
> transaction but the snapshot seems to work as advertised. I only need it
> in this one critical part of the code only, where the transaction may be
> held open for a long time (20-30 seconds typically, maybe as long as
> several minutes under extreme conditions). During this time it's
> inserting many records that nobody needs until committed (i.e., nobody
> will ever be accessing them until then). I've now wrapped these
> insertions in a transaction with snapshot isolation and it no longer
> seems to be blocking anyone. Are you aware of any reasons why it might
> eventually fail (cause blocking) or why it would be preferable not to go
> this route?

Interesting. Are you saying that you set the isolation level for the
*writer* to SNAPSHOT, and thar this resolved the blocking issues?

I can't say why it would help it this case, but in the general case it
does not. The data is inserted into the database, and the rows are locked
until you commit.

To be sure that you avoid blocking issues, the *readers* need to use
SNAPSHOT isolation or READ COMMITTED SNAPSHOT.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2 3
Prev: Count Deleted from Output Trick?
Next: Using xp_fixeddrives on a linked server