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

Groups > comp.databases.ms-sqlserver > #2077 > unrolled thread

Help needed in troubleshooting a lock

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#2077 — Help needed in troubleshooting a lock

Hello, all

I  have  a  several databases on several MSSQL-2017 servers,
each containing the same table @GTTABLEKEYS created  by  the
following command:

   CREATE TABLE [dbo].[@GTTABLEKEYS]
   (  [Code]         [nvarchar](   50) NOT NULL,
      [Name]         [nvarchar](  100) NOT NULL,
      [U_CurrentKey] [numeric] (19, 6) NULL,
      CONSTRAINT [KGTTABLEKEYS_PR] PRIMARY KEY CLUSTERED
      (  [Code] ASC )
      WITH
      (  PAD_INDEX       = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
         ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS       = ON  )
      ON [PRIMARY]
   )
   ON [PRIMARY]

As  you  will have understood, this table stores our custom,
manually implemented sequences, but we  cannot  use  MSSQL's
built-in  mechanisms  instead because of the limitations im-
posed by higher-level software that uses this database.  For
purposes of debugging and monitoring, I am trying to write a
T-SQL script that shall output currently locked rows in @GT-
TABLEKEYS, assuming that write access to @GTTABLEKEYS is al-
ways made with (ROWLOCK). Here is what have come up with:

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

On all of my databases but one, the script works as  expect-
ed. When I lock a row with, e.g.:

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   BEGIN TRAN
   UPDATE [@GTTABLEKEYS] WITH (ROWLOCK)
   SET U_CurrentKey = U_CurrentKey + 1
   WHERE Code = '00000003'
   -- ROLLBACK TRAN

from  one connection and then run my script from another, it
dutifully returns the code of  the  locked  row -- 00000003.
When,  however, I perform the same test on the single myste-
riously affected database, the query:

   SELECT Code, U_CurrentKey INTO #FREE FROM [@GTTABLEKEYS] WITH (READPAST)

hangs on a lock until I end the transaction with the UPDATE.
I  have  made sure that the execution plans for both the UP-
DATE and SELECT statements are the same in both working  and
affected  environments,  the only difference being quantita-
tive: in the "affected" database @GTTABLEKEYS has much  more
rows that in working ones.

When  the SELECT above is thus locked, I see that the UPDATE
has taken the following locks:

   <Locks>
     <Lock request_mode="S" request_status="GRANT" request_count="1" />
   </Locks>
   <Objects>
     <Object name="@GTTABLEKEYS" schema_name="dbo">
       <Locks>
         <Lock resource_type="KEY" index_name="KGTTABLEKEYS_PR" request_mode="X" request_status="GRANT" request_count="1" />
         <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
         <Lock resource_type="PAGE" page_type="*" index_name="KGTTABLEKEYS_PR" request_mode="IX" request_status="GRANT" request_count="1" />
       </Locks>
     </Object>
   </Objects>

whereas the UPDATE is waiting:

   <Locks>
     <Lock request_mode="S" request_status="GRANT" request_count="1" />
   </Locks>
   <Objects>
     <Object name="@GTTABLEKEYS" schema_name="dbo">
       <Locks>
         <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
         <Lock resource_type="PAGE" page_type="*" index_name="KGTTABLEKEYS_PR" request_mode="S" request_status="WAIT" request_count="1" />
       </Locks>
     </Object>
   </Objects>

Can you please help me troubleshoot the problem  or  explain
the locking of my script on only one of the many structural-
ly identical databases?

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

[toc] | [next] | [standalone]

#2078

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> I am trying to write a
> T-SQL script that shall output currently locked rows in @GT-
> TABLEKEYS, assuming that write access to @GTTABLEKEYS is al-
> ways made with (ROWLOCK). Here is what have come up with:
> 
>    -- 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

There is more than one way that this can go wrong. One thing is that 
things may change quickly in a concurrent environment.

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.

Then again, if the number of rows in this table are constant, or 
rows are added or deleted very rarely, it could work. 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.
 
> whereas the UPDATE is waiting:

The SELECT is waiting?

> 
>   <Locks>
>      <Lock request_mode="S" request_status="GRANT" request_count="1" />
>   </Locks>
>   <Objects>
>      <Object name="@GTTABLEKEYS" schema_name="dbo">
>        <Locks>
>          <Lock resource_type="OBJECT" request_mode="IS" 
request_status="GRANT" request_count="1" />
>          <Lock resource_type="PAGE" page_type="*" 
index_name="KGTTABLEKEYS_PR" request_mode="S" request_status="WAIT" 
request_count="1" />
>        </Locks>
>      </Object>
>   </Objects>
> 

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.

[toc] | [prev] | [next] | [standalone]

#2079

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]

[toc] | [prev] | [next] | [standalone]

#2080

I wrote to Erland Sommarskog:

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

A simplified version of my query:

   SELECT A.Code
   FROM      [@GTTABLEKEYS] A WITH (NOLOCK)
   LEFT JOIN [@GTTABLEKEYS] F WITH (READPAST,ROWLOCK) ON F.Code = A.Code
   WHERE F.Code IS NULL

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web