11

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: )
Yoh Deadfall
  • 2,711
  • 7
  • 28
  • 32
flam3
  • 1,897
  • 2
  • 19
  • 26
  • 3
    `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 Mar 16 '18 at 16:13
  • 1
    @MarcGravell, I know that. But do you believe that SELECT NEXT VALUE FOR Seq_Revision returns 0 rows? Sounds impossible. – flam3 Mar 16 '18 at 16:16
  • 1
    I don't know; it is a good question – Marc Gravell Mar 16 '18 at 16:18
  • `NEXT VALUE FOR` does not support read only database. See Limitations and Restrictions at https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql => remove ApplicationIntent=READONLY from connection string – Raphael Hinterndorfer Mar 16 '18 at 17:30
  • @RaphaelHinterndorfer, one of the SQL nodes is indeed used in READONLY mode, but definitely not in these scenarios. "ApplicationIntent=READONLY" connection string is used for other queries. The given code sample "new SqlConnection(connectionString)" has no-readonly connection string. Moreover, if it was the reason, it wouldn't work never, but it returns NULL very-very rare. – flam3 Mar 16 '18 at 19:32
  • 1
    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 a`Thread.Sleep(1000);` just to rule out a timing thing with the disk. Also a good place to add some logging to help form a [mcve], my guess is its intermittent and only reproducible under IO stress. – Jeremy Thompson Apr 20 '18 at 06:32
  • What I would suggest, is to install some sort of a crash-dump analysis tool and set it to take crash dumps during this failure. Analyzing the said crash dump would give a lot of insight. – Abhid Apr 24 '18 at 20:29

1 Answers1

2

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
MtwStark
  • 3,866
  • 1
  • 18
  • 32
  • 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(entityId) – flam3 Apr 19 '18 at 09:21
  • 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 – MtwStark Apr 20 '18 at 10:10