Are transactional settings used only during updates?

[Pradeep]

Hi,

In the Web application I am working on, data is read from a SQL Server
database. At any time, there are about 15 people browsing the web.

The SQL Server database is updated with new information once every
hour. The update takes a couple of minutes. The isolation level during
the update is so to Serialazable so that the front-end does not get
any incorrect data.

Now, here is my problem. When the web page is being loaded, the server
side ASP.NET code uses several SELECT statements at multiple places.
For various design reasons, these SELECT statements cannot be combined
into a single statement. As a result, it may happen that during the
page load, we get some data before an update and some data after an
update.

I am wondering if I must used a transactional lock even for the Web
application although technically it is not updating the database.

Also, after playing with various transactional settings, I noticed the
following behavior for the readers when a writer enters a transaction:

1. If the reader app has not yet executed the query, the call to query
execution blocks until the writer has done its job.
2. If the reader app has already begun executing the query, the call
is not blocked and SQL Server provides the needed isolation.

I do not wish to block the readers while the update is going on.
Ideally, I would like it to be such that even if the writer is
updating, the readers must continue to get the old data, that is,
until the writer commits the update. However, I did not find any
isolation settings that would let me achieve this non-blocking
behavior. Am I missing something?

Thank you in advance for enlightening me.

Pradeep

[Erland Sommarskog]

Pradeep (ip******@msn.com) writes:

The SQL Server database is updated with new information once every
hour. The update takes a couple of minutes. The isolation level during
the update is so to Serialazable so that the front-end does not get
any incorrect data.
The isolation level for the update process has nothing to do with
what happens at the front-end. What matters for the front-end is its
isolation level.
Now, here is my problem. When the web page is being loaded, the server
side ASP.NET code uses several SELECT statements at multiple places.
For various design reasons, these SELECT statements cannot be combined
into a single statement. As a result, it may happen that during the
page load, we get some data before an update and some data after an
update.

I am wondering if I must used a transactional lock even for the Web
application although technically it is not updating the database.
Yes, this is the place for serialiable isolation level. You start a
transaction, so if the update process chimes in while you are reading,
it will be blocked until you are completed and commit.

There is certainly all reason to be careful here. For instance, don't wait
for user input before you commit, because then the update process could
be blocked forever.
I do not wish to block the readers while the update is going on.
Ideally, I would like it to be such that even if the writer is
updating, the readers must continue to get the old data, that is,
until the writer commits the update. However, I did not find any
isolation settings that would let me achieve this non-blocking
behavior. Am I missing something?

This would be possible in Oracle today, and a new isolation level called
Snapshot isolation in the upcoming version of SQL Server, SQL 2005, also
makes this possible.

But now we are in SQL2000, and will have to do the best we can. I would
probably look into that update process. I don't know how much data that
is involved, but "a couple of minutes" for the update sounds a tad long
to me. I would not be surprised if that time can be significantly reduced.
Particularly the time for inserting data into the actual target tables.
If you need to clean up data, you can use staging tables, that are only
used by the update process.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp