Deleting grains and restarting cluster using AdoNetGrainStorage leads to RecordExists inconsistencies

[shalabala]

When using ado storage with SqlServer or PostgreSQL (I assume other providers behave the same way) and deleting a grain that has previously been persisted the IPersistentState.RecordExists property returns false. Once the cluster is shut down and restarted however it once again returns true.

Repro steps:

• initialize the orleans database with the provided sql scripts link
• configure ado grain storage with sql server
• create a grain new and check whether it exists with IPersistentState.RecordExists -> false
• persist its state for the first time with _persistentState.WriteStateAsync();
• check whether it exists with IPersistentState.RecordExists -> true
• delete the grain with _persistentState.ClearStateAsync();
• check whether it exists with IPersistentState.RecordExists -> false
• restart cluster
• check whether it exists with IPersistentState.RecordExists -> true

Versions

I have tried this with 8.0.0 and 9.2.1 (newest at the time of writing this)

Possible workarounds

In the OrleansQuery table if the ClearStorageKey entry is overwritten to fully delete the grain record instead of just setting its state to null the error no longer presents itself

Sample repro project

The database TestProjectOrleans has to be initialized in the SQLEXPRESS for this to work

Output first run:

IN THE BEGINNING: The grain Does not exist
AFTER SAVING: The grain Exists
AFTER DELETING: The grain Does not exist

Output every subsequent run

IN THE BEGINNING: The grain Exists
AFTER SAVING: The grain Exists
AFTER DELETING: The grain Does not exist

ErrorRepro.zip

[shalabala]

This is my workaround:

Postgre:

UPDATE orleansquery SET querytext = 'DELETE FROM OrleansStorage
 WHERE
     GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
     AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
     AND GrainIdN0 = @GrainIdN0 AND @GrainIdN0 IS NOT NULL
     AND GrainIdN1 = @GrainIdN1 AND @GrainIdN1 IS NOT NULL
     AND GrainTypeString = @GrainTypeString AND @GrainTypeString IS NOT NULL
     AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
     AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
     AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
 Returning Version + 1 as NewGrainStateVersion' WHERE querykey = 'ClearStorageKey'

MsSql

UPDATE OrleansQuery SET QueryText = 'BEGIN TRANSACTION;
SET XACT_ABORT, NOCOUNT ON;
DECLARE @NewGrainStateVersion AS INT = @GrainStateVersion + 1;
DELETE FROM OrleansStorage
WHERE
    GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
    AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
    AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
    AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
    AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
    AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
    AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
    AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
    OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));

SELECT @NewGrainStateVersion;
COMMIT TRANSACTION;
' WHERE QueryKey = 'ClearStorageKey'

[Valenrw]

This is my workaround:

Postgre:

UPDATE orleansquery SET querytext = 'DELETE FROM OrleansStorage
 WHERE
     GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
     AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
     AND GrainIdN0 = @GrainIdN0 AND @GrainIdN0 IS NOT NULL
     AND GrainIdN1 = @GrainIdN1 AND @GrainIdN1 IS NOT NULL
     AND GrainTypeString = @GrainTypeString AND @GrainTypeString IS NOT NULL
     AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
     AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
     AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
 Returning Version + 1 as NewGrainStateVersion' WHERE querykey = 'ClearStorageKey'

MsSql

UPDATE OrleansQuery SET QueryText = 'BEGIN TRANSACTION;
SET XACT_ABORT, NOCOUNT ON;
DECLARE @NewGrainStateVersion AS INT = @GrainStateVersion + 1;
DELETE FROM OrleansStorage
WHERE
    GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
    AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
    AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
    AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
    AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
    AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
    AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
    AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
    OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));

SELECT @NewGrainStateVersion;
COMMIT TRANSACTION;
' WHERE QueryKey = 'ClearStorageKey'

I had the same problem and this workaround solved it. Would be nice not to need it though.