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@gmail.com) writes:
> 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.

Note that lock timeouts are by default off in SQL Server.

> 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.

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.

> 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?

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.

> The "WITH (NOLOCK)" would probably help?

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

> What about the definition of optimistic concurrency, shouldn't all
> select-clauses contain "WITH (NOLOCK)" to allow an optimistic
> concurrency scenario?

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@sommarskog.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.


>What settings in Sql-Server are you thinking of?

SET LOCK_TIMEOUT.

>Do you mean that by default the updater will wait until the long
>running sql-clause is ready as the lock timeout is by default
>infinite?

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.

>But do we agree that the default behaviour is that, if you don't use
>(nolock), the select-clause puts a shared lock on the rows fetched?

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.

>Most reports need the situation at a specific time, and there isn't a
>problem if some rows are being updated at the moment, the value before
>or after the updating will do.

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.

>Here a definition:
>***Optimistic concurrency means you read the database record, but
>don't lock it. Anyone can read and modify the record at anytime and
>you will take your chances that the record is not modified by someone
>else before you have a chance to modify and save it. As a developer,
>the burden is on you to check for changes in the original data
>( collisions ) and act accordingly based on any errors that may occur
>during the update.***
>
>But how can an application use optimistic cocurrency (which contains:
>read the database record, but don't lock it) without (NOLOCK),

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...@gmail.com wrote:
> >Here a definition:
> >***Optimistic concurrency means you read the database record, but
> >don't lock it. *Anyone can read and modify the record at anytime and
> >you will take your chances that the record is not modified by someone
> >else before you have a chance to modify and save it. *As a developer,
> >the burden is on you to check for changes in the original data
> >( collisions ) and act accordingly based on any errors that may occur
> >during the update.***
>
> >But how can an application use optimistic cocurrency (which contains:
> >read the database record, but don't lock it) without (NOLOCK),
>
> 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

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@gmail.com) writes:
> 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.

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

> Is using some kind of Olap-server (old data gathered) a better way
> than (nolock) in a realtime environment?

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@sommarskog.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@gmail.com) writes:
> 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?

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@sommarskog.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

> 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.

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@gmail.com) writes:
> Sounds great! So then we will just have to ask our customers to hurry
> up updating to Sql Server 2005 (or later).

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@sommarskog.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:
> (Roger.Noreply@gmail.com) writes:
>> Sounds great! So then we will just have to ask our customers to hurry
>> up updating to Sql Server 2005 (or later).
>
> If they are in hurry, they should stay at SQL 2005, as SQL 2005 will not
> ship until Q3. :-)

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