Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2079

Re: Help needed in troubleshooting a lock

Show all headers | View raw

Erland Sommarskog to Anton Shepelev:

> > -- 1. Select the free (unlocked) rows:
> > SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> > SELECT Code, U_CurrentKey INTO #FREE FROM [@GTTABLEKEYS] WITH (READPAST)
> >
> > -- 2. Select all the rows:
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > SELECT Code INTO #ALL FROM [@GTTABLEKEYS]
> > SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> >
> > -- 3. Report rows that are not free (i.e. locked):
> > SELECT #ALL.Code
> > FROM #ALL
> > LEFT JOIN #FREE ON  #FREE.Code = #ALL.Code
> > WHERE #FREE.Code IS NULL
> >
> > DROP TABLE #FREE
> > DROP TABLE #ALL
>
> There  is  more  than  one way that this can go wrong. One
> thing is that things may change quickly  in  a  concurrent
> environment.

The  environment  is indeed concurrent, but all write access
to @GTTABLEKEYS is made via my own key-generation procedure,
which  uses  (ROWLOCK) to serialise the process.  My purpose
is the detection of long-standing locks, such as from trans-
actions that somebody has forgotten to end, so I do not wor-
ry about transient effects.

> Another is that a NOLOCK read amy skip  already  committed
> rows,  if  you  come  right in the middle of page split or
> something like this.

That is OK for my situation, where I do not care about  get-
ting wrong results occasionally.

> Then  again,  if the number of rows in this table are con-
> stant, or rows are added or deleted very rarely, it  could
> work.

It chages very rarely. Most of the time, only the values for
individual counters are updated.

> Yet, then again, in that case, you can keep a shadow  copy
> of  the  table, and keep it maintained from a trigger, and
> you don't need the NOLOCK read at all.

Thanks for the recommendation, but  triggers  are  generally
considered  a  code  smell, perhaps becuase of their lack of
transparency, and the software that relies on  our  database
will  not let us add triggers to it, as it forbids even cov-
ering indexes, which is outright stupid. What is the  advan-
tage  of  a  shadow copy over a NOLOCK read -- better perfo-
mance and lower impact of the monitoring query upon the load
of the production table?

> > whereas the UPDATE is waiting:
>
> The SELECT is waiting?

Yes,  I beg your pardon. The UPDATE has taken a lock and the
SELECT is waiting on it.

> The SELECT wants a page lock but is blocked by the IX lock
> held  by  the  UPDATE statement. You need to add a ROWLOCK
> hint to that query.

That solved it, thanks. Somehow it did not occur to me  that
I  needed  a (ROWLOCK) for the SELECT as well. Thank for the
help, Erland.

-- 
()  ascii ribbon campaign - against html e-mail
/\  http://preview.tinyurl.com/qcy6mjc [archived]

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Help needed in troubleshooting a lock Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-03-09 16:47 +0300
  Re: Help needed in troubleshooting a lock Erland Sommarskog <esquel@sommarskog.se> - 2021-03-09 20:57 +0100
    Re: Help needed in troubleshooting a lock Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-03-10 12:52 +0300
      Re: Help needed in troubleshooting a lock Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-03-10 13:05 +0300

csiph-web