Re: Rowlock v. optimistic concurrency

(Roger.Noreply@xxxxxxxxx) 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@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: blocking caused from SQL with (nolock) hint
    ... No more information (environment, SQL code, etc) than the question, means ... If your SQL is doing an update, then it will lock the ... "Jay P" wrote in message ... > nolock would perform a dirty read and would not block readers. ...
    (microsoft.public.sqlserver.server)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: SCAN.EXE - McAfee AntiVirus Software
    ... > | of network computers and have only report summaries sent to be via ... > | Virus Scan Report File ... > | application RemoteProcessLaunch. ...
    (microsoft.public.security.virus)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: error: The operation has timed-out (executionTimeout?)
    ... Actually, there was no SQL timeout issue in this case, and when I said ... fixing the performance of this particular report. ... I had to set executionTimeout in both my web site and the web ... web service timing out, then my web site timing out it's own request to the ...
    (microsoft.public.dotnet.framework.aspnet)