Hi,

Sql-Server 2000, 2005.
A report fetches a lot of rows using the "WITH (ROWLOCK)" syntax (the
sql is generated on the fly by a tool and not easily changeable).
SELECT col1, col2 FROM mytab WITH (ROWLOCK) WHERE ...."

The select-clause runs for several minutes.
Another user fetches one of those rows and tries to update it. The
result is a lock timeout.
I suppose that the long running select-clause has put a shared lock on
the rows and the updater (exclusive-lock) will have to wait for the
long-running select and so the lock timeout is expiring.

Are all those rows "shared locked" until all are fetched?
Would there be any change if the "WITH (ROWLOCK)" is removed, isn't
although "shared lock" the default behaviour?
The "WITH (NOLOCK)" would probably help?
What about the definition of optimistic concurrency, shouldn't all
select-clauses contain "WITH (NOLOCK)" to allow an optimistic
concurrency scenario?

Regards Roger.

PS. Probably some misunderstanding from me here, but this should be
the right place to get it right.

(Roger.Noreply (AT) gmail (DOT) com) writes:
Note that lock timeouts are by default off in SQL Server.

There are two possible reasons:
1) The client has not picked up all rows, that the query has selected.
As long as the rows are unconsumed, the lock will linger.

2) The query needs to scan one or more indexes. In this case row locks
be row locks on all rows in the index.

The latter is the more likely explanation. To veridfy this, you would
need to analyse the blocking situation, to see which locks the updater
fails to get.

If the hint prevents escalation to a table lock, removing the hint
could cut the response time a bit. But the most likely it would not
have any effect at all.

Yes. However, since you would read uncommitted data, the report may
produce inconsistent or incorrect results.

NOLOCK has nothing to do with optimistic concurrency. Usually whan you talk
about optimistic concurrency you mean techniques where you detect simul-
taneous updates without keeping rows locked.

Putting NOLOCK on all SELECT is not a good idea at all. Sure, a report
that is used to give you trends or be input for marketing people can
probably work with NOLOCK, since it does not have to be wholly accurate.
But if it is a financial report telling your standings, NOLOCK is out
of the question.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi!

Unfortunately I mistakenly sent my last message to Erland and not to
the newsgroup. Here is our continuing discussion.


SET LOCK_TIMEOUT.

Yes. The default is -1 for SET LOCK_TIMEOUT which means "wait
forever".

Many client APIs has a query timeout with a default of 30 seconds,
but this is nothing about locks. They simply give up if no data has
been returned from the query in that time frame. Of course, a query
can
can timeout because it's blocked, but it could also time out because
it takes long time to run in itself.

If you are actually seeing a lock timeout, that means that the
application
explicitly sets a lock timeout.

Amd with isolation level READ COMMITTED, the SELECT lock releases
the lock as soon as the data is in the output buffer. That is, the
lock is typically not helt until the end of the query.

Just beware that there may be updates that are carried out in several
steps so that the intermediate value does not make sense. For
instance,
when using NOLOCK, you may read the same row twice. For more details,
read this blog post from Tony Rogerson:
http://sqlblogcasts.com/blogs/tonyro...1/10/1280.aspx



I should also have mentioned that in SQL 2005, you may want to
consider
snapshot isolation or Read Committed Snapshot. With snapshot, readers
do not block writers.

This is a different context. Imagine a function that reads information
from the database and displays to the user, and permits the user
to update the data. Pessimistic concurrency calls for the row being
locked while the user has it on the screen. Optimistic concurrency
means that you do hold locks while waiting for user input. You still
lock the rows when you actually read the data. but that usually only
takes an instant.

/Erland

On 8 helmi, 09:47, Roger.Nore... (AT) gmail (DOT) com wrote:
So running long lasting reports will stronly negatively affect the
whole concept of optimistic concurrency. Actually the updater can't
update the row as the report has put a "shared lock" on the rows.
Is using some kind of Olap-server (old data gathered) a better way
than (nolock) in a realtime environment?

Regards Roger.

(Roger.Noreply (AT) gmail (DOT) com) writes:
Again, I object to the use of "optimistic concurrency" in this context.
That deals with a different scenario, essentially how to prevent that
two process do not perform conflicting updates. This is about a
conflict between updater and readrers

This is indeed a popular solution. Beside avoiding lock conflicts, you
also move off load from the OLTP server. The report server can be
fed in different ways depending on requirements. The simplest method
is to restore a backup mightly. If you want more frequent updates,
log shipping and replication are options. Log shipping is easier to
set up and maintain, but users has to be kicked out to apply logs, it's
less practical.

If there is no need to take off load from the OLTP server, using snapshot
isolation may be the best option.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi!

Thanks Erland for very good answers. I think that snapshot isolation
is the answer to this problem. Unfortunately I'm using a tool
generating the SQL so there will be a lot of problems to accomplish
the change, especially as the tool is supporting several different
databases. But we have to live with this and try to minimize the
"length" of the select-clauses and perhaps investigate how long the
tool is holding a shared lock on the rows.

I suppose that there would't be any improvement to use a view, or is a
view a way to simulate snapshot isolation?

Regards Roger.

(Roger.Noreply (AT) gmail (DOT) com) writes:
I don't think you have to meddle with the tool at all. It can be as simple
as

ALTER DATABASE db READ_COMMITTED_SNAPSHOT ON

From this point and on the isolation level READ COMMITTED will be
implemented with the snapshot technique. Thus, this does not affect
your queries at all. But your locking issues will be gone.

There are some differences between true snapshot isolation and RCSI.
In true snapshot, your queries will give a consistent result from the
database as it looked when the query started running. RCSI may still
include the result of updates that were committed after the query
started running. But this is not different from READ COMMITTED.

There is one situation where snapshort or RCSI does not work and
that is if you use timestamp for syncing purposes, but this is not
very common.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sounds great! So then we will just have to ask our customers to hurry
up updating to Sql Server 2005 (or later).

Regards Roger.

(Roger.Noreply (AT) gmail (DOT) com) writes:
If they are in hurry, they should stay at SQL 2005, as SQL 2005 will not
ship until Q3. :-)


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 14.02.2008 22:59, Erland Sommarskog wrote:
Sounds strange to stay on a product that is shipped in the future. If
you take me with you on one of your time travels I won't tell anyone. ;-)

A really funny typo once in a while.

Cheers

robert