By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,377 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,377 IT Pros & Developers. It's quick & easy.

Rowlock v. optimistic concurrency

P: n/a
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.



Feb 5 '08 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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

Feb 8 '08 #2

P: n/a
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.

Feb 8 '08 #3

P: n/a
(Ro***********@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, es****@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
Feb 8 '08 #4

P: n/a
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.
Feb 12 '08 #5

P: n/a
(Ro***********@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, es****@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
Feb 12 '08 #6

P: n/a
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.
Feb 14 '08 #7

P: n/a
(Ro***********@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, es****@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
Feb 14 '08 #8

P: n/a
On 14.02.2008 22:59, Erland Sommarskog wrote:
(Ro***********@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
Feb 16 '08 #9

This discussion thread is closed

Replies have been disabled for this discussion.