We have a job table that looks like this
CREATE TABLE [dbo].[Clearing]( [Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL, [BsAcctId] [int] NULL, [Status] [varchar](20) NULL, CONSTRAINT [csPk_Clearing] PRIMARY KEY CLUSTERED ( [Skey] ASC ) )
with a covering index like this
CREATE NONCLUSTERED INDEX [IX_Status] ON [dbo].[Clearing] ( [Status] ASC ) INCLUDE ( [Skey], [BsAcctId])
and we use this query to pick the next job
select top (1) Skey, BsAcctId, Status from Clearing with ( readpast, updlock ) where (Clearing.Status = 'NEW') order by Clearing.Skey
(The real table has about 10 columns. They are all in the index include() clause and the select column list.)
The execution plan is very simple. It does an index seek using IX_Status, then a top operator. Since the index is sorted on (status, skey) the plan does not need a sort.
The table is in a database in an AlwaysOn Availability Group. The group has 2 DB servers. (It is a test system.)
Normally this table and query work great. So we go to apply Windows updates, and do the usual.
- Fail over the primary to the secondary
- Apply Windows updates on the former primary
- Fail over back to the original primary
- Apply Windows updates on the secondary
After the second fail over and all the worker processes get new connections to the new primary, the query starts failing in the sense that multiple processes start getting the same jobs.
The problem is load related. With 4 worker processes running it did not happen. But with 10 workers it happens consistently.
This is using SQL Server 2016 Enterprise. We do not have the query store enabled to see if the execution plan was weird at some point.
Any suggestions on why the query would start failing after two fail overs?
Since the query is only using the index and not touching the table, is UPDLOCK reliable?
Update 1 - we changed the process to list the locks held by the spid (using sp_lock @@spid) just after doing the select. For the same skey, we are seeing different KEY locks held on the IX_Status index (indid=9)
KEY (aad9d6e672f9) U KEY (154698b9131c) U
Update 2 - using index hint did not help.
Update 3 - Removing order by clause in query avoided the problem. But we have a second table with same problem that needs the order by.
Update 4 - Our worker processes maintain a db connection pool. ODBC does not tell us when a fail over happens, so connections to an old primary stay in the pool until we try to use them and they fail. We suspect after we failover DB1 -> DB2 -> DB1, then old connections to DB1 might not fail like they should. We made a change to close all pooled connections after any one connection is lost, and this seems to have avoided the problem. (SQL Server ODBC added a "Connection Resiliency" feature that is fueling this suspicion.)