private static string GetAggregationQuery(SqlServerStorage storage) { return(string.Format(@" DECLARE @RecordsToAggregate TABLE ( [Key] NVARCHAR(100) NOT NULL, [Value] SMALLINT NOT NULL, [ExpireAt] DATETIME NULL ) SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN DELETE TOP (@count) [{0}].[Counter] with (readpast) OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] INTO @RecordsToAggregate SET NOCOUNT ON ;MERGE [{0}].[AggregatedCounter] AS [Target] USING ( SELECT [Key], SUM([Value]) as [Value], MAX([ExpireAt]) AS [ExpireAt] FROM @RecordsToAggregate GROUP BY [Key]) AS [Source] ([Key], [Value], [ExpireAt]) ON [Target].[Key] = [Source].[Key] WHEN MATCHED THEN UPDATE SET [Target].[Value] = [Target].[Value] + [Source].[Value], [Target].[ExpireAt] = (SELECT MAX([ExpireAt]) FROM (VALUES ([Source].ExpireAt), ([Target].[ExpireAt])) AS MaxExpireAt([ExpireAt])) WHEN NOT MATCHED THEN INSERT ([Key], [Value], [ExpireAt]) VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt]); COMMIT TRAN", storage.GetSchemaName())); }
public IList <ServerDto> Servers() { return(UseConnection <IList <ServerDto> >(connection => { var servers = connection.Query <Entities.Server>( string.Format(@"select * from [{0}].Server", _storage.GetSchemaName())) .ToList(); var result = new List <ServerDto>(); foreach (var server in servers) { var data = JobHelper.FromJson <ServerData>(server.Data); result.Add(new ServerDto { Name = server.Id, Heartbeat = server.LastHeartbeat, Queues = data.Queues, StartedAt = data.StartedAt.HasValue ? data.StartedAt.Value : DateTime.MinValue, WorkersCount = data.WorkerCount }); } return result; })); }
public void Execute(CancellationToken cancellationToken) { foreach (var table in ProcessedTables) { Logger.DebugFormat("Removing outdated records from table '{0}'...", table); int removedCount = 0; do { _storage.UseConnection(connection => { SqlServerDistributedLock.Acquire(connection, DistributedLockKey, DefaultLockTimeout); try { removedCount = connection.Execute( String.Format(@" set transaction isolation level read committed; delete top (@count) from [{0}].[{1}] with (readpast) where ExpireAt < @now;", _storage.GetSchemaName(), table), new { now = DateTime.UtcNow, count = NumberOfRecordsInSinglePass }); } finally { SqlServerDistributedLock.Release(connection, DistributedLockKey); } }); if (removedCount > 0) { Logger.Trace(String.Format("Removed {0} outdated record(s) from '{1}' table.", removedCount, table)); cancellationToken.WaitHandle.WaitOne(DelayBetweenPasses); cancellationToken.ThrowIfCancellationRequested(); } } while (removedCount != 0); } cancellationToken.WaitHandle.WaitOne(_checkInterval); }
public override IDisposable AcquireDistributedLock(string resource, TimeSpan timeout) { return(new SqlServerDistributedLock( _storage, String.Format("{0}:{1}", _storage.GetSchemaName(), resource), timeout)); }
public override void ExpireJob(string jobId, TimeSpan expireIn) { QueueCommand(x => x.Execute( string.Format(@"update [{0}].Job set ExpireAt = @expireAt where Id = @id", _storage.GetSchemaName()), new { expireAt = DateTime.UtcNow.Add(expireIn), id = jobId })); }
private static string GetAggregationQuery(SqlServerStorage storage) { return string.Format(@" DECLARE @RecordsToAggregate TABLE ( [Key] NVARCHAR(100) NOT NULL, [Value] SMALLINT NOT NULL, [ExpireAt] DATETIME NULL ) SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN DELETE TOP (@count) [{0}].[Counter] with (readpast) OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] INTO @RecordsToAggregate SET NOCOUNT ON ;MERGE [{0}].[AggregatedCounter] AS [Target] USING ( SELECT [Key], SUM([Value]) as [Value], MAX([ExpireAt]) AS [ExpireAt] FROM @RecordsToAggregate GROUP BY [Key]) AS [Source] ([Key], [Value], [ExpireAt]) ON [Target].[Key] = [Source].[Key] WHEN MATCHED THEN UPDATE SET [Target].[Value] = [Target].[Value] + [Source].[Value], [Target].[ExpireAt] = (SELECT MAX([ExpireAt]) FROM (VALUES ([Source].ExpireAt), ([Target].[ExpireAt])) AS MaxExpireAt([ExpireAt])) WHEN NOT MATCHED THEN INSERT ([Key], [Value], [ExpireAt]) VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt]); COMMIT TRAN", storage.GetSchemaName()); }