large transaction blocks query  
Author Message
t-jswan





PostPosted: Sun Nov 11 21:32:32 PST 2007 Top

SQL Server Developer >> large transaction blocks query

Hi, I must admit I am new to transactional databases, but as I understand
it, the default behavior of a transaction isolates changes occurring on a
connection from everyone else's view of the data. If my fundamental
undestanding is correct, then I don't understand why I am receiving the
following behavior:

I have a program (happens to be a service broker application written in
t-sql) which will from time to time process a large number of messages
(using today as an example: 13288 messages, resulting in 91653 inserts).
This entire transaction takes about 5 minutes (which is reasonable
considering the amount of processing for each message). During this time,
queries against the table receiving the inserts will either time out, or
block until the entire transaction completes.

The expected result is that the queries produce immediately a resultset that
looks exactly like it did before the transaction began. Is there something I
am doing wrong, or maybe some config option I can tweak to get this
behavior?

Also, I should mention that the table uses a timestamp (aka rowversion)
column, and queries against the table are almost always like this:

SELECT TOP(20) Column1, Column2, ... FROM TableName
WHERE Timestamp > 0x000000000005747C
ORDER BY Timestamp DESC

The more I think about this the more I think that might be part of the
problem (trying to select rows currently locked by the transaction). I am
not positive though, and I do not know how to fix it if that is the problem.
Any ideas?


Chris

SQL Server130  
 
 
Vampyre





PostPosted: Sun Nov 11 21:32:32 PST 2007 Top

SQL Server Developer >> large transaction blocks query
> Hi, I must admit I am new to transactional databases, but as I understand
> it, the default behavior of a transaction isolates changes occurring on a
> connection from everyone else's view of the data. If my fundamental
> undestanding is correct, then I don't understand why I am receiving the
> following behavior:
>
> I have a program (happens to be a service broker application written in
> t-sql) which will from time to time process a large number of messages
> (using today as an example: 13288 messages, resulting in 91653 inserts).
> This entire transaction takes about 5 minutes (which is reasonable
> considering the amount of processing for each message). During this time,
> queries against the table receiving the inserts will either time out, or
> block until the entire transaction completes.
>
> The expected result is that the queries produce immediately a resultset that
> looks exactly like it did before the transaction began. Is there something I
> am doing wrong, or maybe some config option I can tweak to get this
> behavior?
>
> Also, I should mention that the table uses a timestamp (aka rowversion)
> column, and queries against the table are almost always like this:
>
> SELECT TOP(20) Column1, Column2, ... FROM TableName
> WHERE Timestamp > 0x000000000005747C
> ORDER BY Timestamp DESC
>
> The more I think about this the more I think that might be part of the
> problem (trying to select rows currently locked by the transaction). I am
> not positive though, and I do not know how to fix it if that is the problem.
> Any ideas?
>
> Chris

You can query the table with the NOLOCK hint - this could return dirty
data though, so whether this is acceptable or not would depend on the
nature of your query.
Another option would be to break your list of inserts into more
manageable batches, processing a smaller number of records each time.
Hopefully this would free enough time for some user reports to run in
between batches.

 
 
Dan





PostPosted: Mon Nov 12 05:17:06 PST 2007 Top

SQL Server Developer >> large transaction blocks query > Hi, I must admit I am new to transactional databases, but as I understand
> it, the default behavior of a transaction isolates changes occurring on a
> connection from everyone else's view of the data.

The default transaction isolation level in SQL Server is READ COMMITTED,
which guarantees that only committed data are visible. The SQL Server READ
COMMITTED implementation allows data changed after the current transaction
to be visible as long as it's been committed. Locking is used to ensure
only committed data are accessed and this can result in extended blocking if
you have long-running transactions.

> The expected result is that the queries produce immediately a resultset
> that looks exactly like it did before the transaction began. Is there
> something I am doing wrong, or maybe some config option I can tweak to get
> this behavior?

One method to get the behavior you want is to turn on the
READ_COMMITTED_SNAPSHOT database option. This causes SQL Server to use
statement level row versioning instead of the normal locking method to
provide a consistent data view for the READ_COMMITTED isolation level.

Another method is to turn on the ALLOW_SNAPSHOT_ISOLATION database option
and use the SNAPSHOT isolation level (SET TRANSACTION ISOLATION LEVEL
SNAPSHOT). This uses row versioning at the transaction level.

If you use row versioning, be sure tempdb is sized appropriately to
accommodate the version store. See the Books Online for transaction
isolation level details.

--
Hope this helps.

Dan Guzman
SQL Server MVP



> Hi, I must admit I am new to transactional databases, but as I understand
> it, the default behavior of a transaction isolates changes occurring on a
> connection from everyone else's view of the data. If my fundamental
> undestanding is correct, then I don't understand why I am receiving the
> following behavior:
>
> I have a program (happens to be a service broker application written in
> t-sql) which will from time to time process a large number of messages
> (using today as an example: 13288 messages, resulting in 91653 inserts).
> This entire transaction takes about 5 minutes (which is reasonable
> considering the amount of processing for each message). During this time,
> queries against the table receiving the inserts will either time out, or
> block until the entire transaction completes.
>
> The expected result is that the queries produce immediately a resultset
> that looks exactly like it did before the transaction began. Is there
> something I am doing wrong, or maybe some config option I can tweak to get
> this behavior?
>
> Also, I should mention that the table uses a timestamp (aka rowversion)
> column, and queries against the table are almost always like this:
>
> SELECT TOP(20) Column1, Column2, ... FROM TableName
> WHERE Timestamp > 0x000000000005747C
> ORDER BY Timestamp DESC
>
> The more I think about this the more I think that might be part of the
> problem (trying to select rows currently locked by the transaction). I am
> not positive though, and I do not know how to fix it if that is the
> problem. Any ideas?
>
>
> Chris

 
 
Dan





PostPosted: Mon Nov 12 05:49:05 PST 2007 Top

SQL Server Developer >> large transaction blocks query >> Also, I should mention that the table uses a timestamp (aka rowversion)
>> column, and queries against the table are almost always like this:
>>
>> SELECT TOP(20) Column1, Column2, ... FROM TableName
>> WHERE Timestamp > 0x000000000005747C
>> ORDER BY Timestamp DESC
>>
>> The more I think about this the more I think that might be part of the
>> problem (trying to select rows currently locked by the transaction). I am
>> not positive though, and I do not know how to fix it if that is the
>> problem. Any ideas?

I forgot to add in my initial response that I'm not sure what you are trying
to accomplish with these rowversion queries. Rowversion is typically used
as part of optimistic concurrency to check if a row has changed since
initially read. The query you posted seems to be using it as some sort of
chronological ordering. I think this might contribute to your blocking
problems.

--
Hope this helps.

Dan Guzman
SQL Server MVP



>> Hi, I must admit I am new to transactional databases, but as I understand
>> it, the default behavior of a transaction isolates changes occurring on a
>> connection from everyone else's view of the data.
>
> The default transaction isolation level in SQL Server is READ COMMITTED,
> which guarantees that only committed data are visible. The SQL Server
> READ COMMITTED implementation allows data changed after the current
> transaction to be visible as long as it's been committed. Locking is used
> to ensure only committed data are accessed and this can result in extended
> blocking if you have long-running transactions.
>
>> The expected result is that the queries produce immediately a resultset
>> that looks exactly like it did before the transaction began. Is there
>> something I am doing wrong, or maybe some config option I can tweak to
>> get this behavior?
>
> One method to get the behavior you want is to turn on the
> READ_COMMITTED_SNAPSHOT database option. This causes SQL Server to use
> statement level row versioning instead of the normal locking method to
> provide a consistent data view for the READ_COMMITTED isolation level.
>
> Another method is to turn on the ALLOW_SNAPSHOT_ISOLATION database option
> and use the SNAPSHOT isolation level (SET TRANSACTION ISOLATION LEVEL
> SNAPSHOT). This uses row versioning at the transaction level.
>
> If you use row versioning, be sure tempdb is sized appropriately to
> accommodate the version store. See the Books Online for transaction
> isolation level details.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>


>> Hi, I must admit I am new to transactional databases, but as I understand
>> it, the default behavior of a transaction isolates changes occurring on a
>> connection from everyone else's view of the data. If my fundamental
>> undestanding is correct, then I don't understand why I am receiving the
>> following behavior:
>>
>> I have a program (happens to be a service broker application written in
>> t-sql) which will from time to time process a large number of messages
>> (using today as an example: 13288 messages, resulting in 91653 inserts).
>> This entire transaction takes about 5 minutes (which is reasonable
>> considering the amount of processing for each message). During this time,
>> queries against the table receiving the inserts will either time out, or
>> block until the entire transaction completes.
>>
>> The expected result is that the queries produce immediately a resultset
>> that looks exactly like it did before the transaction began. Is there
>> something I am doing wrong, or maybe some config option I can tweak to
>> get this behavior?
>>
>> Also, I should mention that the table uses a timestamp (aka rowversion)
>> column, and queries against the table are almost always like this:
>>
>> SELECT TOP(20) Column1, Column2, ... FROM TableName
>> WHERE Timestamp > 0x000000000005747C
>> ORDER BY Timestamp DESC
>>
>> The more I think about this the more I think that might be part of the
>> problem (trying to select rows currently locked by the transaction). I am
>> not positive though, and I do not know how to fix it if that is the
>> problem. Any ideas?
>>
>>
>> Chris
>

 
 
Chris





PostPosted: Mon Nov 12 08:22:49 PST 2007 Top

SQL Server Developer >> large transaction blocks query

>>> Also, I should mention that the table uses a timestamp (aka rowversion)
>>> column, and queries against the table are almost always like this:
>>>
>>> SELECT TOP(20) Column1, Column2, ... FROM TableName
>>> WHERE Timestamp > 0x000000000005747C
>>> ORDER BY Timestamp DESC
>>>
>>> The more I think about this the more I think that might be part of the
>>> problem (trying to select rows currently locked by the transaction). I
>>> am not positive though, and I do not know how to fix it if that is the
>>> problem. Any ideas?
>
> I forgot to add in my initial response that I'm not sure what you are
> trying to accomplish with these rowversion queries. Rowversion is
> typically used as part of optimistic concurrency to check if a row has
> changed since initially read. The query you posted seems to be using it
> as some sort of chronological ordering. I think this might contribute to
> your blocking problems.

That is exactly what we are using it for. After looking at the code I
realized we're actually sorting ASC. Clients access the data by web service,
which queries 20 records at a time. Each record returned comes with the
timestamp. The client continues to repeatedly ask the web service for more
records using the highest timestamp from the previous batch as a parameter.
In this way, the client can "catch up", obtaining all the records that have
been inserted or changed since he last checked.

Anyhow, thanks Dan and Mr. Vampyre for your input. I guess I have a little
reading to do.

Chris