Some Orleans stress testing

orleans_persistence_stress_tests.sql

-- The index being NONCLUSTERED is by design. The intent is that when
-- a grain is added to the Storage, deletting it, or setting it to "NULL"
-- in application code will only SET NULL to the Payload fields. This
-- should prevent alleviate need for index or statistics maintenance
-- with the loss of some bytes of storage space. Naturally the DB
-- can be scrubbed with a separate maintenance operation.
--
-- This design should be fast on lower number of rows, but scale
-- quite well even to the largest reasonable table sizes.
DROP TABLE Storage;
CREATE TABLE Storage
(
    -- These are for the book keeping. Orleans calculates
    -- these hashes (Jenkins), which are unsigned 32 integers mapped to
    -- the *Hash fields. The mapping is done in the code. The
    -- *String columns contain the corresponding clear name fields.
	--
	-- If there are duplicates, they are resolved by using GrainIdString
	-- and GrainNameString fields. It is assumed these would be rarely needed.
    GrainIdHash     INT NOT NULL,
    GrainIdString   NVARCHAR(512) NOT NULL,
    GrainTypeHash   INT NOT NULL,
    GrainTypeString NVARCHAR(512) NOT NULL,

    -- The usage of the Payload records is exclusive in that
    -- only one is populated at any given time and two others
    -- are NULL. When all three are returned, the application
    -- knows how to handle the situation. The advantange on separating
	-- these by types is that various DB engines include additional
	-- in-storage processing and "compression" capabilities depending on type.  
    PayloadBinary VARBINARY(MAX) NULL,
    PayloadXml XML NULL,
    PayloadJson NVARCHAR(MAX) NULL,

    -- Informational field, no other use.
    ModifiedOn DATETIME2(3) NOT NULL,

    -- If this particular object has been deleted from the database
    -- or not. The objects can be inserted, deleted and reinserted.
    -- Would it be beneficial to set the Payload* columns to NULL
    -- to save space but still to avoid index fragmentation?
    Version INT NOT NULL

    -- The following would in principle be the primary key, but it would be too thick
	-- to be indexed, so the the values are hashed and only collisions will be solved
	-- by using the fields. That is, after the indexed queries have pinpointed the right
	-- rows down to [0, n] relevant ones, n being the number of collided value pairs.
    -- CONSTRAINT PK_Storage PRIMARY KEY NONCLUSTERED (GrainIdString, ClassNameString)        
);
CREATE NONCLUSTERED INDEX IX_Storage ON Storage(GrainIdHash, GrainTypeHash) INCLUDE(Version, GrainTypeString, GrainIdString, PayloadBinary, PayloadJson, PayloadXml);

-- This INSERT INTO isn't valid syntax for Oracle.
INSERT INTO Storage
(
	GrainIdHash,
	GrainIdString,
	GrainTypeHash,
	GrainTypeString,
	PayloadBinary,
	PayloadJson,
	PayloadXml,
	ModifiedOn,
	Version
)
SELECT
	A.x,
	(N'GrainIdString_' + CAST(A.x AS NVARCHAR)),
	B.x,
	(N'GrainTypeString_' + CAST(B.x AS NVARCHAR)),
	NULL,
	N'{ "SomeObjectPayload": "' + (N'GrainIdString_' + CAST(A.x AS NVARCHAR)) + N'__' + (N'GrainTypeString_' + CAST(B.x AS NVARCHAR)) + N'" }',
	NULL,
	GETUTCDATE(),
	1
FROM
(	
	SELECT(deca * 10 + unit + 1) AS x
	FROM
	(
		SELECT 0 AS deca
		UNION ALL SELECT 1
		UNION ALL SELECT 2
		UNION ALL SELECT 3
		UNION ALL SELECT 4
		UNION ALL SELECT 5
		UNION ALL SELECT 6
		UNION ALL SELECT 7
		UNION ALL SELECT 8
		UNION ALL SELECT 9
	) AS da,
	(
		SELECT 0 AS unit
		UNION ALL SELECT 1
		UNION ALL SELECT 2
		UNION ALL SELECT 3
		UNION ALL SELECT 4
		UNION ALL SELECT 5
		UNION ALL SELECT 6
		UNION ALL SELECT 7
		UNION ALL SELECT 8
		UNION ALL SELECT 9
	) AS u
) AS A CROSS JOIN
(
	SELECT(deca * 10 + unit + 1) AS x
	FROM	
	(
		SELECT 0 AS deca
		UNION ALL SELECT 1
		UNION ALL SELECT 2
		UNION ALL SELECT 3
		UNION ALL SELECT 4
		UNION ALL SELECT 5
		UNION ALL SELECT 6
		UNION ALL SELECT 7
		UNION ALL SELECT 8
		UNION ALL SELECT 9
	) AS da,
	(
		SELECT 0 AS unit
		UNION ALL SELECT 1		
		UNION ALL SELECT 2
		UNION ALL SELECT 3
		UNION ALL SELECT 4
		UNION ALL SELECT 5
		UNION ALL SELECT 6
		UNION ALL SELECT 7
		UNION ALL SELECT 8
		UNION ALL SELECT 9
	) AS u	
) AS B;


DECLARE @GrainIdHash AS INT = 1;
DECLARE @GrainIdString AS NVARCHAR(512) = N'GrainIdString_1';
DECLARE @GrainTypeHash  AS INT = 1;
DECLARE @GrainTypeString AS NVARCHAR(512) = N'GrainTypeString_1';

DECLARE @PayloadBinary AS VARBINARY(MAX);
DECLARE @PayloadJson AS NVARCHAR(MAX);
DECLARE @PayloadXml AS XML;

DECLARE @GrainStateVersion AS INT = 0;

-- When Orleans is running in normal, non-split state, there will
-- be only one grain with the given ID and type combination only. This
-- grain saves states mostly serially if Orleans guarantees are upheld. Even
-- if not, the updates should work correctly due to version number.
--
-- In split brain situations there can be a situation where two or more
-- grains of with the given ID and type combination. When they try to INSERT
-- concurrently, the table needs to be locked pessimistically before one of
-- the grains gets @GrainStateVersion = 1 in return and the other grains will fail
-- to update storage. The following arrangement is made to reduce locking in normal operation.
--
-- If the version number explicitly returned is still the same, Orleans interprets it so the update did not succeed
-- and throws an InconsistentStateException.
-- 
-- See further information at See further at http://dotnet.github.io/orleans/Getting-Started-With-Orleans/Grain-Persistence.
BEGIN TRANSACTION;
SET XACT_ABORT, NOCOUNT ON;

DECLARE @ROWCOUNT AS INT = 0;
DECLARE @NewGrainStateVersion AS INT = @GrainStateVersion;


IF @GrainStateVersion != 0
BEGIN
    UPDATE Storage 
    SET	
	    PayloadBinary = @PayloadBinary,
	    PayloadJson = @PayloadJson,
	    PayloadXml = @PayloadXml,
	    ModifiedOn = GETUTCDATE(),
	    Version = Version + 1,
        @NewGrainStateVersion = Version + 1
    WHERE
	    GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
        AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
        AND GrainIdString = @GrainIdString AND @GrainIdString IS NOT NULL
        AND GrainTypeString = @GrainTypeString AND @GrainTypeString IS NOT NULL
        AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL;
END
ELSE
BEGIN
    INSERT INTO Storage
    (
	    GrainIdHash,
	    GrainIdString,
	    GrainTypeHash,
	    GrainTypeString,
	    PayloadBinary,
	    PayloadJson,
	    PayloadXml,
	    ModifiedOn,
	    Version
    )
    SELECT
	    @GrainIdHash,
	    @GrainIdString,
	    @GrainTypeHash,
	    @GrainTypeString,
	    @PayloadBinary,
	    @PayloadJson,
	    @PayloadXml,
	    GETUTCDATE(),
	    1
     WHERE NOT EXISTS
     (
        SELECT 1
        FROM Storage WITH(UPDLOCK, HOLDLOCK)
        WHERE
            GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
            AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
            AND GrainIdString = @GrainIdString AND @GrainIdString IS NOT NULL
            AND GrainTypeString = @GrainTypeString AND @GrainTypeString IS NOT NULL
            AND Version != 0
     );
    
    SET @NewGrainStateVersion = @@ROWCOUNT;
END

SELECT @NewGrainStateVersion AS Version;
COMMIT TRANSACTION;


INSERT INTO Storage
(
	GrainIdHash,
	GrainIdString,
	GrainTypeHash,
	GrainTypeString,
	PayloadBinary,
	PayloadJson,
	PayloadXml,
	ModifiedOn,
	Version
)
VALUES
(	
	@GrainIdHash,
	N'GrainIdString_1_duplicate',
	@GrainTypeHash,
	N'GrainTypeString_1_duplicate',
	NULL,
	N'{ "SomeObjectPayload": "GrainIdString_1__GrainTypeString_1_duplicate" }"',
	NULL,
	GETUTCDATE(),
	1	
);

-- The application code will deserialize the relevant result.
SELECT	
    PayloadBinary,
    PayloadXml,
    PayloadJson
FROM
    Storage
WHERE
    GrainIdHash = @GrainIdHash
    AND GrainTypeHash = @GrainTypeHash    
    AND GrainIdString = @GrainIdString
    AND GrainTypeString = @GrainTypeString;


-- With the INCLUDE columns the effect of the previous definition
-- should be more like this in "procedural SQL".
SELECT
    PayloadBinary,
    PayloadXml,
    PayloadJson
FROM
(
    SELECT
        PayloadBinary,
		PayloadXml,
		PayloadJson,
        GrainIdString,
        GrainTypeString
    FROM
        Storage
    WHERE
        GrainIdHash = @GrainIdHash
        AND GrainTypeHash = @GrainTypeHash        
) AS collidedRows
WHERE
	collidedRows.GrainIdString = @GrainIdString
	AND collidedRows.GrainTypeString = @GrainTypeString;