Help needed in troubleshooting a lock

[Anton Shepelev]

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]

[Erland Sommarskog]

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.

[Anton Shepelev]

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.

[Anton Shepelev]

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