· 8 years ago · Jan 14, 2018, 08:32 PM
1/*
2Implementation notes:
3
41) The general idea is that data is read and written through Orleans specific queries.
5 Orleans operates on column names and types when reading and on parameter names and types when writing.
6
72) The implementations *must* preserve input and output names and types. Orleans uses these parameters to reads query results by name and type.
8 Vendor and deployment specific tuning is allowed and contributions are encouraged as long as the interface contract
9 is maintained.
10
113) The implementation across vendor specific scripts *should* preserve the constraint names. This simplifies troubleshooting
12 by virtue of uniform naming across concrete implementations.
13
145) ETag for Orleans is an opaque column that represents a unique version. The type of its actual implementation
15 is not important as long as it represents a unique version. In this implementation we use integers for versioning
16
176) For the sake of being explicit and removing ambiguity, Orleans expects some queries to return either TRUE as >0 value
18 or FALSE as =0 value. That is, affected rows or such does not matter. If an error is raised or an exception is thrown
19 the query *must* ensure the entire transaction is rolled back and may either return FALSE or propagate the exception.
20 Orleans handles exception as a failure and will retry.
21
227) The implementation follows the Extended Orleans membership protocol. For more information, see at:
23 http://dotnet.github.io/orleans/Runtime-Implementation-Details/Runtime-Tables.html
24 http://dotnet.github.io/orleans/Runtime-Implementation-Details/Cluster-Management
25 https://github.com/dotnet/orleans/blob/master/src/Orleans/SystemTargetInterfaces/IMembershipTable.cs
26*/
27
28-- These settings improves throughput of the database by reducing locking by better separating readers from writers.
29-- SQL Server 2012 and newer can refer to itself as CURRENT. Older ones need a workaround.
30DECLARE @current NVARCHAR(256);
31DECLARE @snapshotSettings NVARCHAR(612);
32
33SELECT @current = (SELECT DB_NAME());
34SET @snapshotSettings = N'ALTER DATABASE ' + @current + N' SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE ' + @current + N' SET ALLOW_SNAPSHOT_ISOLATION ON;';
35
36EXECUTE sp_executesql @snapshotSettings;
37
38-- This table defines Orleans operational queries. Orleans uses these to manage its operations,
39-- these are the only queries Orleans issues to the database.
40-- These can be redefined (e.g. to provide non-destructive updates) provided the stated interface principles hold.
41CREATE TABLE OrleansQuery
42(
43 QueryKey VARCHAR(64) NOT NULL,
44 QueryText VARCHAR(8000) NOT NULL,
45
46 CONSTRAINT OrleansQuery_Key PRIMARY KEY(QueryKey)
47);
48
49-- For each deployment, there will be only one (active) membership version table version column which will be updated periodically.
50CREATE TABLE OrleansMembershipVersionTable
51(
52 DeploymentId NVARCHAR(150) NOT NULL,
53 Timestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE(),
54 Version INT NOT NULL DEFAULT 0,
55
56 CONSTRAINT PK_OrleansMembershipVersionTable_DeploymentId PRIMARY KEY(DeploymentId)
57);
58
59-- Every silo instance has a row in the membership table.
60CREATE TABLE OrleansMembershipTable
61(
62 DeploymentId NVARCHAR(150) NOT NULL,
63 Address VARCHAR(45) NOT NULL,
64 Port INT NOT NULL,
65 Generation INT NOT NULL,
66 SiloName NVARCHAR(150) NOT NULL,
67 HostName NVARCHAR(150) NOT NULL,
68 Status INT NOT NULL,
69 ProxyPort INT NULL,
70 SuspectTimes VARCHAR(8000) NULL,
71 StartTime DATETIME2(3) NOT NULL,
72 IAmAliveTime DATETIME2(3) NOT NULL,
73
74 CONSTRAINT PK_MembershipTable_DeploymentId PRIMARY KEY(DeploymentId, Address, Port, Generation),
75 CONSTRAINT FK_MembershipTable_MembershipVersionTable_DeploymentId FOREIGN KEY (DeploymentId) REFERENCES OrleansMembershipVersionTable (DeploymentId)
76);
77
78-- Orleans Reminders table - http://dotnet.github.io/orleans/Advanced-Concepts/Timers-and-Reminders
79CREATE TABLE OrleansRemindersTable
80(
81 ServiceId NVARCHAR(150) NOT NULL,
82 GrainId VARCHAR(150) NOT NULL,
83 ReminderName NVARCHAR(150) NOT NULL,
84 StartTime DATETIME2(3) NOT NULL,
85 Period INT NOT NULL,
86 GrainHash INT NOT NULL,
87 Version INT NOT NULL,
88
89 CONSTRAINT PK_RemindersTable_ServiceId_GrainId_ReminderName PRIMARY KEY(ServiceId, GrainId, ReminderName)
90);
91
92CREATE TABLE OrleansStatisticsTable
93(
94 OrleansStatisticsTableId INT IDENTITY(1,1) NOT NULL,
95 DeploymentId NVARCHAR(150) NOT NULL,
96 Timestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE(),
97 Id NVARCHAR(250) NOT NULL,
98 HostName NVARCHAR(150) NOT NULL,
99 Name NVARCHAR(150) NOT NULL,
100 IsValueDelta BIT NOT NULL,
101 StatValue NVARCHAR(1024) NOT NULL,
102 Statistic NVARCHAR(512) NOT NULL,
103
104 CONSTRAINT StatisticsTable_StatisticsTableId PRIMARY KEY(OrleansStatisticsTableId)
105);
106
107CREATE TABLE OrleansClientMetricsTable
108(
109 DeploymentId NVARCHAR(150) NOT NULL,
110 ClientId NVARCHAR(150) NOT NULL,
111 Timestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE(),
112 Address VARCHAR(45) NOT NULL,
113 HostName NVARCHAR(150) NOT NULL,
114 CpuUsage FLOAT NOT NULL,
115 MemoryUsage BIGINT NOT NULL,
116 SendQueueLength INT NOT NULL,
117 ReceiveQueueLength INT NOT NULL,
118 SentMessages BIGINT NOT NULL,
119 ReceivedMessages BIGINT NOT NULL,
120 ConnectedGatewayCount BIGINT NOT NULL,
121
122 CONSTRAINT PK_ClientMetricsTable_DeploymentId_ClientId PRIMARY KEY (DeploymentId , ClientId)
123);
124
125CREATE TABLE OrleansSiloMetricsTable
126(
127 DeploymentId NVARCHAR(150) NOT NULL,
128 SiloId NVARCHAR(150) NOT NULL,
129 Timestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE(),
130 Address VARCHAR(45) NOT NULL,
131 Port INT NOT NULL,
132 Generation INT NOT NULL,
133 HostName NVARCHAR(150) NOT NULL,
134 GatewayAddress VARCHAR(45) NOT NULL,
135 GatewayPort INT NOT NULL,
136 CpuUsage FLOAT NOT NULL,
137 MemoryUsage BIGINT NOT NULL,
138 SendQueueLength INT NOT NULL,
139 ReceiveQueueLength INT NOT NULL,
140 SentMessages BIGINT NOT NULL,
141 ReceivedMessages BIGINT NOT NULL,
142 ActivationCount INT NOT NULL,
143 RecentlyUsedActivationCount INT NOT NULL,
144 RequestQueueLength BIGINT NOT NULL,
145 IsOverloaded BIT NOT NULL,
146 ClientCount BIGINT NOT NULL,
147
148 CONSTRAINT PK_SiloMetricsTable_DeploymentId_SiloId PRIMARY KEY (DeploymentId , SiloId),
149 CONSTRAINT FK_SiloMetricsTable_MembershipVersionTable_DeploymentId FOREIGN KEY (DeploymentId) REFERENCES OrleansMembershipVersionTable (DeploymentId)
150);
151
152-- The design criteria for this table are:
153--
154-- 1. It can contain arbitrary content serialized as binary, XML or JSON. These formats
155-- are supported to allow one to take advantage of in-storage processing capabilities for
156-- these types if required. This should not incur extra cost on storage.
157--
158-- 2. The table design should scale with the idea of tens or hundreds (or even more) types
159-- of grains that may operate with even hundreds of thousands of grain IDs within each
160-- type of a grain.
161--
162-- 3. The table and its associated operations should remain stable. There should not be
163-- structural reason for unexpected delays in operations. It should be possible to also
164-- insert data reasonably fast without resource contention.
165--
166-- 4. For reasons in 2. and 3., the index should be as narrow as possible so it fits well in
167-- memory and should it require maintenance, isn't resource intensive. For this
168-- reason the index is narrow by design (ideally non-clustered). Currently the entity
169-- is recognized in the storage by the grain type and its ID, which are unique in Orleans silo.
170-- The ID is the grain ID bytes (if string type UTF-8 bytes) and possible extension key as UTF-8
171-- bytes concatenated with the ID and then hashed.
172--
173-- Reason for hashing: Database engines usually limit the length of the column sizes, which
174-- would artificially limit the length of IDs or types. Even when within limitations, the
175-- index would be thick and consume more memory.
176--
177-- In the current setup the ID and the type are hashed into two INT type instances, which
178-- are made a compound index. When there are no collisions, the index can quickly locate
179-- the unique row. Along with the hashed index values, the NVARCHAR(nnn) values are also
180-- stored and they are used to prune hash collisions down to only one result row.
181--
182-- 5. The design leads to duplication in the storage. It is reasonable to assume there will
183-- a low number of services with a given service ID operational at any given time. Or that
184-- compared to the number of grain IDs, there are a fairly low number of different types of
185-- grain. The catch is that were these data separated to another table, it would make INSERT
186-- and UPDATE operations complicated and would require joins, temporary variables and additional
187-- indexes or some combinations of them to make it work. It looks like fitting strategy
188-- could be to use table compression.
189--
190-- 6. For the aforementioned reasons, grain state DELETE will set NULL to the data fields
191-- and updates the Version number normally. This should alleviate the need for index or
192-- statistics maintenance with the loss of some bytes of storage space. The table can be scrubbed
193-- in a separate maintenance operation.
194--
195-- 7. In the storage operations queries the columns need to be in the exact same order
196-- since the storage table operations support optionally streaming.
197CREATE TABLE Storage
198(
199 -- These are for the book keeping. Orleans calculates
200 -- these hashes (see RelationalStorageProvide implementation),
201 -- which are signed 32 bit integers mapped to the *Hash fields.
202 -- The mapping is done in the code. The
203 -- *String columns contain the corresponding clear name fields.
204 --
205 -- If there are duplicates, they are resolved by using GrainIdN0,
206 -- GrainIdN1, GrainIdExtensionString and GrainTypeString fields.
207 -- It is assumed these would be rarely needed.
208 GrainIdHash INT NOT NULL,
209 GrainIdN0 BIGINT NOT NULL,
210 GrainIdN1 BIGINT NOT NULL,
211 GrainTypeHash INT NOT NULL,
212 GrainTypeString NVARCHAR(512) NOT NULL,
213 GrainIdExtensionString NVARCHAR(512) NULL,
214 ServiceId NVARCHAR(150) NOT NULL,
215
216 -- The usage of the Payload records is exclusive in that
217 -- only one should be populated at any given time and two others
218 -- are NULL. The types are separated to advantage on special
219 -- processing capabilities present on database engines (not all might
220 -- have both JSON and XML types.
221 --
222 -- One is free to alter the size of these fields.
223 PayloadBinary VARBINARY(MAX) NULL,
224 PayloadXml XML NULL,
225 PayloadJson NVARCHAR(MAX) NULL,
226
227 -- Informational field, no other use.
228 ModifiedOn DATETIME2(3) NOT NULL,
229
230 -- The version of the stored payload.
231 Version INT NULL
232
233 -- The following would in principle be the primary key, but it would be too thick
234 -- to be indexed, so the values are hashed and only collisions will be solved
235 -- by using the fields. That is, after the indexed queries have pinpointed the right
236 -- rows down to [0, n] relevant ones, n being the number of collided value pairs.
237);
238CREATE NONCLUSTERED INDEX IX_Storage ON Storage(GrainIdHash, GrainTypeHash);
239
240-- This ensures lock escalation will not lock the whole table, which can potentially be enormous.
241-- See more information at https://www.littlekendra.com/2016/02/04/why-rowlock-hints-can-make-queries-slower-and-blocking-worse-in-sql-server/.
242ALTER TABLE Storage SET(LOCK_ESCALATION = DISABLE);
243
244-- A feature with ID is compression. If it is supported, it is used for Storage table. This is an Enterprise feature.
245-- This consumes more processor cycles, but should save on space on GrainIdString, GrainTypeString and ServiceId, which
246-- contain mainly the same values. Also the payloads will be compressed.
247IF EXISTS (SELECT 1 FROM sys.dm_db_persisted_sku_features WHERE feature_id = 100)
248BEGIN
249 ALTER TABLE Storage REBUILD PARTITION = ALL WITH(DATA_COMPRESSION = PAGE);
250END
251
252INSERT INTO OrleansQuery(QueryKey, QueryText)
253VALUES
254(
255 'UpdateIAmAlivetimeKey','
256 -- This is expected to never fail by Orleans, so return value
257 -- is not needed nor is it checked.
258 SET NOCOUNT ON;
259 UPDATE OrleansMembershipTable
260 SET
261 IAmAliveTime = @IAmAliveTime
262 WHERE
263 DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
264 AND Address = @Address AND @Address IS NOT NULL
265 AND Port = @Port AND @Port IS NOT NULL
266 AND Generation = @Generation AND @Generation IS NOT NULL;
267');
268
269INSERT INTO OrleansQuery(QueryKey, QueryText)
270VALUES
271(
272 'InsertMembershipVersionKey','
273 SET NOCOUNT ON;
274 INSERT INTO OrleansMembershipVersionTable
275 (
276 DeploymentId
277 )
278 SELECT @DeploymentId
279 WHERE NOT EXISTS
280 (
281 SELECT 1
282 FROM
283 OrleansMembershipVersionTable
284 WHERE
285 DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
286 );
287
288 SELECT @@ROWCOUNT;
289');
290
291INSERT INTO OrleansQuery(QueryKey, QueryText)
292VALUES
293(
294 'InsertMembershipKey','
295 SET XACT_ABORT, NOCOUNT ON;
296 DECLARE @ROWCOUNT AS INT;
297 BEGIN TRANSACTION;
298 INSERT INTO OrleansMembershipTable
299 (
300 DeploymentId,
301 Address,
302 Port,
303 Generation,
304 SiloName,
305 HostName,
306 Status,
307 ProxyPort,
308 StartTime,
309 IAmAliveTime
310 )
311 SELECT
312 @DeploymentId,
313 @Address,
314 @Port,
315 @Generation,
316 @SiloName,
317 @HostName,
318 @Status,
319 @ProxyPort,
320 @StartTime,
321 @IAmAliveTime
322 WHERE NOT EXISTS
323 (
324 SELECT 1
325 FROM
326 OrleansMembershipTable
327 WHERE
328 DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
329 AND Address = @Address AND @Address IS NOT NULL
330 AND Port = @Port AND @Port IS NOT NULL
331 AND Generation = @Generation AND @Generation IS NOT NULL
332 );
333
334 UPDATE OrleansMembershipVersionTable
335 SET
336 Timestamp = GETUTCDATE(),
337 Version = Version + 1
338 WHERE
339 DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
340 AND Version = @Version AND @Version IS NOT NULL
341 AND @@ROWCOUNT > 0;
342
343 SET @ROWCOUNT = @@ROWCOUNT;
344
345 IF @ROWCOUNT = 0
346 ROLLBACK TRANSACTION
347 ELSE
348 COMMIT TRANSACTION
349 SELECT @ROWCOUNT;
350');
351
352INSERT INTO OrleansQuery(QueryKey, QueryText)
353VALUES
354(
355 'UpdateMembershipKey','
356 SET XACT_ABORT, NOCOUNT ON;
357 BEGIN TRANSACTION;
358
359 UPDATE OrleansMembershipVersionTable
360 SET
361 Timestamp = GETUTCDATE(),
362 Version = Version + 1
363 WHERE
364 DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
365 AND Version = @Version AND @Version IS NOT NULL;
366
367 UPDATE OrleansMembershipTable
368 SET
369 Status = @Status,
370 SuspectTimes = @SuspectTimes,
371 IAmAliveTime = @IAmAliveTime
372 WHERE
373 DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
374 AND Address = @Address AND @Address IS NOT NULL
375 AND Port = @Port AND @Port IS NOT NULL
376 AND Generation = @Generation AND @Generation IS NOT NULL
377 AND @@ROWCOUNT > 0;
378
379 SELECT @@ROWCOUNT;
380 COMMIT TRANSACTION;
381');
382
383INSERT INTO OrleansQuery(QueryKey, QueryText)
384VALUES
385(
386 'UpsertReminderRowKey','
387 DECLARE @Version AS INT = 0;
388 SET XACT_ABORT, NOCOUNT ON;
389 BEGIN TRANSACTION;
390 UPDATE OrleansRemindersTable WITH(UPDLOCK, ROWLOCK, HOLDLOCK)
391 SET
392 StartTime = @StartTime,
393 Period = @Period,
394 GrainHash = @GrainHash,
395 @Version = Version = Version + 1
396 WHERE
397 ServiceId = @ServiceId AND @ServiceId IS NOT NULL
398 AND GrainId = @GrainId AND @GrainId IS NOT NULL
399 AND ReminderName = @ReminderName AND @ReminderName IS NOT NULL;
400
401 INSERT INTO OrleansRemindersTable
402 (
403 ServiceId,
404 GrainId,
405 ReminderName,
406 StartTime,
407 Period,
408 GrainHash,
409 Version
410 )
411 SELECT
412 @ServiceId,
413 @GrainId,
414 @ReminderName,
415 @StartTime,
416 @Period,
417 @GrainHash,
418 0
419 WHERE
420 @@ROWCOUNT=0;
421 SELECT @Version AS Version;
422 COMMIT TRANSACTION;
423');
424
425INSERT INTO OrleansQuery(QueryKey, QueryText)
426VALUES
427(
428 'UpsertReportClientMetricsKey','
429 SET XACT_ABORT, NOCOUNT ON;
430 BEGIN TRANSACTION;
431 UPDATE OrleansClientMetricsTable WITH(UPDLOCK, ROWLOCK, HOLDLOCK)
432 SET
433 Timestamp = GETUTCDATE(),
434 Address = @Address,
435 HostName = @HostName,
436 CpuUsage = @CpuUsage,
437 MemoryUsage = @MemoryUsage,
438 SendQueueLength = @SendQueueLength,
439 ReceiveQueueLength = @ReceiveQueueLength,
440 SentMessages = @SentMessages,
441 ReceivedMessages = @ReceivedMessages,
442 ConnectedGatewayCount = @ConnectedGatewayCount
443 WHERE
444 DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
445 AND ClientId = @ClientId AND @ClientId IS NOT NULL;
446
447 INSERT INTO OrleansClientMetricsTable
448 (
449 DeploymentId,
450 ClientId,
451 Address,
452 HostName,
453 CpuUsage,
454 MemoryUsage,
455 SendQueueLength,
456 ReceiveQueueLength,
457 SentMessages,
458 ReceivedMessages,
459 ConnectedGatewayCount
460 )
461 SELECT
462 @DeploymentId,
463 @ClientId,
464 @Address,
465 @HostName,
466 @CpuUsage,
467 @MemoryUsage,
468 @SendQueueLength,
469 @ReceiveQueueLength,
470 @SentMessages,
471 @ReceivedMessages,
472 @ConnectedGatewayCount
473 WHERE
474 @@ROWCOUNT=0;
475 COMMIT TRANSACTION;
476');
477
478INSERT INTO OrleansQuery(QueryKey, QueryText)
479VALUES
480(
481 'UpsertSiloMetricsKey','
482 SET XACT_ABORT, NOCOUNT ON;
483 BEGIN TRANSACTION;
484 UPDATE OrleansSiloMetricsTable WITH(UPDLOCK, ROWLOCK, HOLDLOCK)
485 SET
486 Timestamp = GETUTCDATE(),
487 Address = @Address,
488 Port = @Port,
489 Generation = @Generation,
490 HostName = @HostName,
491 GatewayAddress = @GatewayAddress,
492 GatewayPort = @GatewayPort,
493 CpuUsage = @CpuUsage,
494 MemoryUsage = @MemoryUsage,
495 ActivationCount = @ActivationCount,
496 RecentlyUsedActivationCount = @RecentlyUsedActivationCount,
497 SendQueueLength = @SendQueueLength,
498 ReceiveQueueLength = @ReceiveQueueLength,
499 RequestQueueLength = @RequestQueueLength,
500 SentMessages = @SentMessages,
501 ReceivedMessages = @ReceivedMessages,
502 IsOverloaded = @IsOverloaded,
503 ClientCount = @ClientCount
504 WHERE
505 DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
506 AND SiloId = @SiloId AND @SiloId IS NOT NULL;
507
508 INSERT INTO OrleansSiloMetricsTable
509 (
510 DeploymentId,
511 SiloId,
512 Address,
513 Port,
514 Generation,
515 HostName,
516 GatewayAddress,
517 GatewayPort,
518 CpuUsage,
519 MemoryUsage,
520 SendQueueLength,
521 ReceiveQueueLength,
522 SentMessages,
523 ReceivedMessages,
524 ActivationCount,
525 RecentlyUsedActivationCount,
526 RequestQueueLength,
527 IsOverloaded,
528 ClientCount
529 )
530 SELECT
531 @DeploymentId,
532 @SiloId,
533 @Address,
534 @Port,
535 @Generation,
536 @HostName,
537 @GatewayAddress,
538 @GatewayPort,
539 @CpuUsage,
540 @MemoryUsage,
541 @SendQueueLength,
542 @ReceiveQueueLength,
543 @SentMessages,
544 @ReceivedMessages,
545 @ActivationCount,
546 @RecentlyUsedActivationCount,
547 @RequestQueueLength,
548 @IsOverloaded,
549 @ClientCount
550 WHERE
551 @@ROWCOUNT=0;
552 COMMIT TRANSACTION;
553');
554
555INSERT INTO OrleansQuery(QueryKey, QueryText)
556VALUES
557(
558 'GatewaysQueryKey','
559 SELECT
560 Address,
561 ProxyPort,
562 Generation
563 FROM
564 OrleansMembershipTable
565 WHERE
566 DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
567 AND Status = @Status AND @Status IS NOT NULL
568 AND ProxyPort > 0;
569');
570
571INSERT INTO OrleansQuery(QueryKey, QueryText)
572VALUES
573(
574 'MembershipReadRowKey','
575 SELECT
576 v.DeploymentId,
577 m.Address,
578 m.Port,
579 m.Generation,
580 m.SiloName,
581 m.HostName,
582 m.Status,
583 m.ProxyPort,
584 m.SuspectTimes,
585 m.StartTime,
586 m.IAmAliveTime,
587 v.Version
588 FROM
589 OrleansMembershipVersionTable v
590 -- This ensures the version table will returned even if there is no matching membership row.
591 LEFT OUTER JOIN OrleansMembershipTable m ON v.DeploymentId = m.DeploymentId
592 AND Address = @Address AND @Address IS NOT NULL
593 AND Port = @Port AND @Port IS NOT NULL
594 AND Generation = @Generation AND @Generation IS NOT NULL
595 WHERE
596 v.DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
597');
598
599INSERT INTO OrleansQuery(QueryKey, QueryText)
600VALUES
601(
602 'MembershipReadAllKey','
603 SELECT
604 v.DeploymentId,
605 m.Address,
606 m.Port,
607 m.Generation,
608 m.SiloName,
609 m.HostName,
610 m.Status,
611 m.ProxyPort,
612 m.SuspectTimes,
613 m.StartTime,
614 m.IAmAliveTime,
615 v.Version
616 FROM
617 OrleansMembershipVersionTable v LEFT OUTER JOIN OrleansMembershipTable m
618 ON v.DeploymentId = m.DeploymentId
619 WHERE
620 v.DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
621');
622
623INSERT INTO OrleansQuery(QueryKey, QueryText)
624VALUES
625(
626 'DeleteMembershipTableEntriesKey','
627 DELETE FROM OrleansMembershipTable
628 WHERE DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
629 DELETE FROM OrleansMembershipVersionTable
630 WHERE DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
631');
632
633INSERT INTO OrleansQuery(QueryKey, QueryText)
634VALUES
635(
636 'ReadReminderRowsKey','
637 SELECT
638 GrainId,
639 ReminderName,
640 StartTime,
641 Period,
642 Version
643 FROM OrleansRemindersTable
644 WHERE
645 ServiceId = @ServiceId AND @ServiceId IS NOT NULL
646 AND GrainId = @GrainId AND @GrainId IS NOT NULL;
647');
648
649INSERT INTO OrleansQuery(QueryKey, QueryText)
650VALUES
651(
652 'ReadReminderRowKey','
653 SELECT
654 GrainId,
655 ReminderName,
656 StartTime,
657 Period,
658 Version
659 FROM OrleansRemindersTable
660 WHERE
661 ServiceId = @ServiceId AND @ServiceId IS NOT NULL
662 AND GrainId = @GrainId AND @GrainId IS NOT NULL
663 AND ReminderName = @ReminderName AND @ReminderName IS NOT NULL;
664');
665
666INSERT INTO OrleansQuery(QueryKey, QueryText)
667VALUES
668(
669 'ReadRangeRows1Key','
670 SELECT
671 GrainId,
672 ReminderName,
673 StartTime,
674 Period,
675 Version
676 FROM OrleansRemindersTable
677 WHERE
678 ServiceId = @ServiceId AND @ServiceId IS NOT NULL
679 AND GrainHash > @BeginHash AND @BeginHash IS NOT NULL
680 AND GrainHash <= @EndHash AND @EndHash IS NOT NULL;
681');
682
683INSERT INTO OrleansQuery(QueryKey, QueryText)
684VALUES
685(
686 'ReadRangeRows2Key','
687 SELECT
688 GrainId,
689 ReminderName,
690 StartTime,
691 Period,
692 Version
693 FROM OrleansRemindersTable
694 WHERE
695 ServiceId = @ServiceId AND @ServiceId IS NOT NULL
696 AND ((GrainHash > @BeginHash AND @BeginHash IS NOT NULL)
697 OR (GrainHash <= @EndHash AND @EndHash IS NOT NULL));
698');
699
700INSERT INTO OrleansQuery(QueryKey, QueryText)
701VALUES
702(
703 'InsertOrleansStatisticsKey','
704 BEGIN TRANSACTION;
705 INSERT INTO OrleansStatisticsTable
706 (
707 DeploymentId,
708 Id,
709 HostName,
710 Name,
711 IsValueDelta,
712 StatValue,
713 Statistic
714 )
715 SELECT
716 @DeploymentId,
717 @Id,
718 @HostName,
719 @Name,
720 @IsValueDelta,
721 @StatValue,
722 @Statistic;
723 COMMIT TRANSACTION;
724');
725
726INSERT INTO OrleansQuery(QueryKey, QueryText)
727VALUES
728(
729 'DeleteReminderRowKey','
730 DELETE FROM OrleansRemindersTable
731 WHERE
732 ServiceId = @ServiceId AND @ServiceId IS NOT NULL
733 AND GrainId = @GrainId AND @GrainId IS NOT NULL
734 AND ReminderName = @ReminderName AND @ReminderName IS NOT NULL
735 AND Version = @Version AND @Version IS NOT NULL;
736 SELECT @@ROWCOUNT;
737');
738
739INSERT INTO OrleansQuery(QueryKey, QueryText)
740VALUES
741(
742 'DeleteReminderRowsKey','
743 DELETE FROM OrleansRemindersTable
744 WHERE
745 ServiceId = @ServiceId AND @ServiceId IS NOT NULL;
746');
747
748
749INSERT INTO OrleansQuery(QueryKey, QueryText)
750VALUES
751(
752 'WriteToStorageKey',
753 '-- When Orleans is running in normal, non-split state, there will
754 -- be only one grain with the given ID and type combination only. This
755 -- grain saves states mostly serially if Orleans guarantees are upheld. Even
756 -- if not, the updates should work correctly due to version number.
757 --
758 -- In split brain situations there can be a situation where there are two or more
759 -- grains with the given ID and type combination. When they try to INSERT
760 -- concurrently, the table needs to be locked pessimistically before one of
761 -- the grains gets @GrainStateVersion = 1 in return and the other grains will fail
762 -- to update storage. The following arrangement is made to reduce locking in normal operation.
763 --
764 -- If the version number explicitly returned is still the same, Orleans interprets it so the update did not succeed
765 -- and throws an InconsistentStateException.
766 --
767 -- See further information at http://dotnet.github.io/orleans/Getting-Started-With-Orleans/Grain-Persistence.
768 BEGIN TRANSACTION;
769 SET XACT_ABORT, NOCOUNT ON;
770
771 DECLARE @NewGrainStateVersion AS INT = @GrainStateVersion;
772
773
774 -- If the @GrainStateVersion is not zero, this branch assumes it exists in this database.
775 -- The NULL value is supplied by Orleans when the state is new.
776 IF @GrainStateVersion IS NOT NULL
777 BEGIN
778 UPDATE Storage
779 SET
780 PayloadBinary = @PayloadBinary,
781 PayloadJson = @PayloadJson,
782 PayloadXml = @PayloadXml,
783 ModifiedOn = GETUTCDATE(),
784 Version = Version + 1,
785 @NewGrainStateVersion = Version + 1,
786 @GrainStateVersion = Version + 1
787 WHERE
788 GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
789 AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
790 AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
791 AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
792 AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
793 AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
794 AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
795 AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
796 OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));
797 END
798
799 -- The grain state has not been read. The following locks rather pessimistically
800 -- to ensure only one INSERT succeeds.
801 IF @GrainStateVersion IS NULL
802 BEGIN
803 INSERT INTO Storage
804 (
805 GrainIdHash,
806 GrainIdN0,
807 GrainIdN1,
808 GrainTypeHash,
809 GrainTypeString,
810 GrainIdExtensionString,
811 ServiceId,
812 PayloadBinary,
813 PayloadJson,
814 PayloadXml,
815 ModifiedOn,
816 Version
817 )
818 SELECT
819 @GrainIdHash,
820 @GrainIdN0,
821 @GrainIdN1,
822 @GrainTypeHash,
823 @GrainTypeString,
824 @GrainIdExtensionString,
825 @ServiceId,
826 @PayloadBinary,
827 @PayloadJson,
828 @PayloadXml,
829 GETUTCDATE(),
830 1
831 WHERE NOT EXISTS
832 (
833 -- There should not be any version of this grain state.
834 SELECT 1
835 FROM Storage WITH(XLOCK, ROWLOCK, HOLDLOCK, INDEX(IX_Storage))
836 WHERE
837 GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
838 AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
839 AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
840 AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
841 AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
842 AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
843 AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
844 ) OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));
845
846 IF @@ROWCOUNT > 0
847 BEGIN
848 SET @NewGrainStateVersion = 1;
849 END
850 END
851
852 SELECT @NewGrainStateVersion AS NewGrainStateVersion;
853 COMMIT TRANSACTION;'
854);
855
856
857INSERT INTO OrleansQuery(QueryKey, QueryText)
858VALUES
859(
860 'ClearStorageKey',
861 'BEGIN TRANSACTION;
862 SET XACT_ABORT, NOCOUNT ON;
863 DECLARE @NewGrainStateVersion AS INT = @GrainStateVersion;
864 UPDATE Storage
865 SET
866 PayloadBinary = NULL,
867 PayloadJson = NULL,
868 PayloadXml = NULL,
869 ModifiedOn = GETUTCDATE(),
870 Version = Version + 1,
871 @NewGrainStateVersion = Version + 1
872 WHERE
873 GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
874 AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
875 AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
876 AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
877 AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
878 AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
879 AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
880 AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
881 OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));
882
883 SELECT @NewGrainStateVersion;
884 COMMIT TRANSACTION;'
885);
886
887
888INSERT INTO OrleansQuery(QueryKey, QueryText)
889VALUES
890(
891 'ReadFromStorageKey',
892 '-- The application code will deserialize the relevant result. Not that the query optimizer
893 -- estimates the result of rows based on its knowledge on the index. It does not know there
894 -- will be only one row returned. Forcing the optimizer to process the first found row quickly
895 -- creates an estimate for a one-row result and makes a difference on multi-million row tables.
896 -- Also the optimizer is instructed to always use the same plan via index using the OPTIMIZE
897 -- FOR UNKNOWN flags. These hints are only available in SQL Server 2008 and later. They
898 -- should guarantee the execution time is robustly basically the same from query-to-query.
899 SELECT
900 PayloadBinary,
901 PayloadXml,
902 PayloadJson,
903 Version
904 FROM
905 Storage
906 WHERE
907 GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
908 AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
909 AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
910 AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
911 AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
912 AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
913 AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
914 OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));'
915);