Select from sequence with SqlCommand.ExecuteScalar() returns NULL when high disk usage

Question

I encounter that SqlCommand.ExecuteScalar() returns NULL sometimes in production environment.

I've crossed a lot of similar questions here, the most close one is: SqlCommand.ExecuteScalar returns null but raw SQL does not. But the advice given is not about my case.

The code sample is here:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT NEXT VALUE FOR Seq_Revision";
        command.CommandType = CommandType.Text;

        return (long)command.ExecuteScalar(); //<---ExecuteScalar() here returns NULL sometimes
    }
}

Seq_Revision here is simple MSSQL sequence like this:

CREATE SEQUENCE [dbo].[Seq_Revision] 
 AS [bigint]
 START WITH 0
 INCREMENT BY 1
 MINVALUE -9223372036854775808
 MAXVALUE 9223372036854775807
 CACHE  10 
GO

And I'm pretty sure that it will never actually return NULL.

Also I observe similar strange (non-repeatable behaviour) when NULL is returned in this code sample, while I'm sure there is an entity with this ID:

NHibernate.ISession.Get<FooEntity>(entityId)

What is interesting, returning NULL by this method correlates well with the time frames when there is a high disk activity on SQL node (disk queue length > ~50).

It may be important: we use AlwaysON cluster with 2 nodes, one of the nodes is used in read mode (ApplicationIntent=READONLY in the connection string).

MSSQL version is:

Microsoft SQL Server 2014 (SP2-CU5) (KB4013098) - 12.0.5546.0 (X64) 
    Apr  3 2017 14:55:37 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

ExecuteScalar returning null means: no rows were returned. If a row was returned with a null value, it would be DBNull.Value. That doesn't answer "why?", but it may be useful context — Marc Gravell
– Marc Gravell, Commented Mar 16, 2018 at 16:13
@MarcGravell, I know that. But do you believe that SELECT NEXT VALUE FOR Seq_Revision returns 0 rows? Sounds impossible. — flam3
– flam3, Commented Mar 16, 2018 at 16:16
NEXT VALUE FOR does not support read only database. See Limitations and Restrictions at learn.microsoft.com/en-us/sql/t-sql/functions/… => remove ApplicationIntent=READONLY from connection string — Raphael Hinterndorfer
– Raphael Hinterndorfer, Commented Mar 16, 2018 at 17:30
Can you test for this condition eg if (command.GetType() == typeof(DBNull)) or it catch it with an InvalidCastException and then check for the value or retry after aThread.Sleep(1000); just to rule out a timing thing with the disk. Also a good place to add some logging to help form a minimal reproducible example, my guess is its intermittent and only reproducible under IO stress. — Jeremy Thompson
– Jeremy Thompson, Commented Apr 20, 2018 at 6:32

MtwStark · Accepted Answer · 2018-04-20 10:08:06Z

2

+50

I think that the problem could be related to sequence caching.

Maybe there is something unhandled that result in the loss of sequence numbers remaining in the cache.

Try to disable cache in your sequence:

ALTER SEQUENCE [dbo].[Seq_Revision] 
 NO CACHE
GO

or try to use higher value for cache:

ALTER SEQUENCE [dbo].[Seq_Revision] 
 CACHE 100
GO

edited Apr 20, 2018 at 10:08

answered Apr 18, 2018 at 11:35

MtwStark

4,0961 gold badge23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

flam3 Over a year ago

Thank you, we will try this theory. But that won't explain why I sometimes get NULL from ISession.Get<>(id), while I'm sure an entity with this ID does exist: NHibernate.ISession.Get<FooEntity>(entityId)

MtwStark Over a year ago

ISession.Get hits the database or session cache to retrieve the entity data. If the entity exists it is returned, otherwise NULL will be returned. This means that if there are unhandled problems with session cache.. you can get NULLs

Collectives™ on Stack Overflow

Select from sequence with SqlCommand.ExecuteScalar() returns NULL when high disk usage

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related