SQL Server force only RowLock

Question

I'm facing a big problem on updating rows of a table which stores counters. I'm using a transaction to get the value and update it, and I try to lock only the row of the counter affected to avoid locks and deadlocks but it's not working.

That's a reduced sample of the code where I can reproduce the error:

CREATE TABLE _COUNTERS_ 
(
    ID INT IDENTITY NOT NULL, 
    CODE VARCHAR(20) NOT NULL,
    CVALUE INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_ID PRIMARY KEY CLUSTERED 
    (
        ID ASC,
        CODE ASC,
        CVALUE ASC
    ) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF)
)

INSERT INTO _COUNTERS_ (CODE, CVALUE) VALUES ('C1', 0)
INSERT INTO _COUNTERS_ (CODE, CVALUE) VALUES ('C2', 0)

I'm trying to force to avoid PageLocks with the index definition.

In SQL Server Management Studio, I execute this statement in a window. I use the first query to lock the COUNTER to avoid other threads o servers (the app is multithread and installed on a farm so I can't use .NET locks) get a bad number num while it's near to update:

-- Window 1
BEGIN TRAN 
SELECT *
FROM _COUNTERS_ this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.CODE = 'C1'
UPDATE _COUNTERS_ SET CVALUE = 1 WHERE ID = 1

In an another SQL Server Management Studio window, I query the locked resources:

-- Window 2
SELECT  L.request_session_id AS SPID,
--        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName,
        P.object_id AS LockedObjectId,
        L.resource_type AS LockedResource,
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,       
--        ES.login_name AS LoginName,
--        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

In a third window I execute the following statement:

-- Window 3
BEGIN TRAN 
SELECT *
FROM _COUNTERS_ this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.CODE = 'C2'
UPDATE _COUNTERS_ SET CVALUE = 2 WHERE ID = 2

And the first select keeps waiting until I commit the first transaction.

Is it possible to keep every counter totally isolated in selects and updates?

Some additional considerations about the real environment:

• SQL Server 2012
• The table has 107 rows
• The first transaction (Window 1) only show one row on the locks query as opposed to the two rows of the screenshot I've posted.
• The second transaction (Window 3), can do the first select without waiting and it keeps waiting on the UPDATE statement.
• A deadlock (only in real environment) is reproduced executing first the two select queries and then the two updates, it can't be reproduced with the posted sample because the first select locks full table.

UPDATE 2020-03-02 Using the index by code (as commented by @larny or posted by @esat) solves the problem on the sample I've posted but in my real table (VISUALSEGCONTADORES), the select is not using the new index:

CREATE INDEX ix_VSGCONTADORES ON VISUALSEGCONTADORES (VSC_ALIAS ASC) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF);

Here the plan of one of the selects (the other uses the same index): https://www.brentozar.com/pastetheplan/?id=BJRWtocE8

And the real table structure (with the special clustered index):

CREATE TABLE [dbo].[VISUALSEGCONTADORES](
    [VSC_Id] [int] IDENTITY(1,1) NOT NULL,
    [VSC_Alias] [varchar](30) NOT NULL,
    [VSC_Objeto] [int] NOT NULL,
    [VSC_Serie] [varchar](5) NULL,
    [VSC_Contador] [decimal](20, 8) NOT NULL,
    [VSC_Enabled] [tinyint] NOT NULL,
    [USR_Id_FC] [int] NOT NULL,
    [USR_Id_FM] [int] NOT NULL,
    [VSC_FC] [datetime] NOT NULL,
    [VSC_FM] [datetime] NOT NULL,
    [LOG_ID_FC] [varchar](255) NULL,
    [LOG_ID_FM] [varchar](255) NULL,
    [LOG_FC] [datetime] NULL,
    [LOG_FM] [datetime] NULL,
    [OFI_ID] [int] NULL,
    [VSC_OFICODE] [int] NOT NULL,
    [TRN_Aud_Id_FC] [int] NULL,
    [TRN_Aud_Id] [int] NULL,
 CONSTRAINT [PK_VISUALSEGCONTADORES] PRIMARY KEY CLUSTERED 
(
    [VSC_Alias] ASC,
    [VSC_OFICODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

The real queries are the following:

-- Window 1
BEGIN TRAN 
SELECT *
FROM VISUALSEGCONTADORES this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.VSC_Alias = 'VSG_UltimoCodEXP' AND VSC_OFICODE = 0
UPDATE VISUALSEGCONTADORES SET VSC_Contador = 9910 WHERE VSC_Id = 142

The other window

-- Window 3
BEGIN TRAN 
SELECT *
FROM VISUALSEGCONTADORES this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.VSC_Alias = 'VSG_ULTIMAMATRIZ' AND VSC_OFICODE = 0

UPDATE VISUALSEGCONTADORES SET VSC_Contador = 1273 WHERE VSC_Id = 121

Defined by business, sometimes the queries are with VSC_OfiCode and sometimes don't, but I've tested both and throws the same results.

The locked resources:

Answer 1

The Window3 query tries to acquire exclusive lock-on a row which is still reading by the Window1. If you analyze the execution of the following query.

SELECT *
FROM _COUNTERS_ this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.CODE = 'C2'

So this query will wait until the commit or rollback of the Window1 query. If you try to execute Window3 update individually, it will be executed

-- Window 3
BEGIN TRAN 
--SELECT *
--FROM _COUNTERS_ this_ WITH (
--        UPDLOCK,
--        ROWLOCK
--        )
--WHERE this_.CODE = 'C2'
UPDATE _COUNTERS_ SET CVALUE = 2 WHERE ID = 2

On the other hand, if we handle this problem we can create a non-clustered index on the CODE column or we can change the query like the below

--Window3
BEGIN TRAN 
SELECT *
FROM _COUNTERS_ this_ WITH (
        UPDLOCK,
        ROWLOCK
        )
WHERE this_.CODE = 'C2' AND ID=2
UPDATE _COUNTERS_ SET CVALUE = 2 WHERE ID = 2

This two option provides to avoid cluster index scan