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
