// http://rusanu.com/2010/03/26/using-tables-as-queues/
public async Task <WorkQueueItem> Take(QueueIds queueId, TimeSpan?timeLimit = null)
{
timeLimit = timeLimit ?? TimeSpan.FromMinutes(5);
// readpast пропускает заблокированные строки
// rowlock подсказывает блокировать строки, а не страницы
var sql =
$@"with cte as (
select top(1) *
from {AntiPlagiarismDb.DefaultSchema}.{nameof(db.WorkQueueItems)} with (rowlock, readpast)
where {WorkQueueItem.QueueIdColumnName} = @queueId
and ({WorkQueueItem.TakeAfterTimeColumnName} is NULL or {WorkQueueItem.TakeAfterTimeColumnName} < @now)
order by {WorkQueueItem.IdColumnName}
)
update cte SET {WorkQueueItem.TakeAfterTimeColumnName} = @timeLimit
output inserted.*";
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions {
IsolationLevel = IsolationLevel.RepeatableRead
}, TransactionScopeAsyncFlowOption.Enabled))
{
var taken = (await db.WorkQueueItems.FromSqlRaw(
sql,
new SqlParameter("@queueId", queueId),
new SqlParameter("@now", DateTime.UtcNow),
new SqlParameter("@timeLimit", DateTime.UtcNow + timeLimit)
).ToListAsync()).FirstOrDefault();
scope.Complete();
return(taken);
}
}
public async Task Add(QueueIds queueId, string itemId)
{
db.WorkQueueItems.Add(new WorkQueueItem {
QueueId = queueId, ItemId = itemId
});
await db.SaveChangesAsync().ConfigureAwait(false);
}
public override string ToString()
{
var optionalParameters = string.Empty;
if (Seasons != null)
{
optionalParameters += "&season=" + string.Join(",", Seasons.ToArray());
}
if (QueueIds != null)
{
optionalParameters += "&queue=" + string.Join(",", QueueIds.ToArray());
}
if (ChampionIds != null)
{
optionalParameters += "&champion=" + string.Join(",", ChampionIds.ToArray());
}
if (BeginIndex != -1)
{
optionalParameters += "&beginIndex=" + BeginIndex;
}
if (EndIndex != -1)
{
optionalParameters += "&endIndex=" + EndIndex;
}
if (BeginTime != -1)
{
optionalParameters += "&beginTime=" + BeginTime;
}
if (EndTime != -1)
{
optionalParameters += "&endTime=" + EndTime;
}
return(optionalParameters);
}
public IMessageQueryOption Ids(params string[] value)
{
if (value?.Any() ?? false)
{
QueueIds.AddRange(value);
}
return(this);
}
// https://habr.com/ru/post/481556/
public async Task <WorkQueueItem> TakeNoTracking(QueueIds queueId, TimeSpan?timeLimit = null)
{
timeLimit = timeLimit ?? TimeSpan.FromMinutes(5);
// skip locked пропускает заблокированные строки
// for update подсказывает блокировать строки, а не страницы
var sql =
$@"with next_task as (
select ""{IdColumnName}"" from {AntiPlagiarismDb.DefaultSchema}.""{nameof(db.WorkQueueItems)}""
where ""{QueueIdColumnName}"" = @queueId
and (""{TakeAfterTimeColumnName}"" is NULL or ""{TakeAfterTimeColumnName}"" < @now)
order by ""{IdColumnName}""
limit 1
for update skip locked
)
update {AntiPlagiarismDb.DefaultSchema}.""{nameof(db.WorkQueueItems)}""
set ""{TakeAfterTimeColumnName}"" = @timeLimit
from next_task
where {AntiPlagiarismDb.DefaultSchema}.""{nameof(db.WorkQueueItems)}"".""{IdColumnName}"" = next_task.""{IdColumnName}""
returning next_task.""{IdColumnName}"", ""{QueueIdColumnName}"", ""{ItemIdColumnName}"", ""{TakeAfterTimeColumnName}"";"; // Если написать *, Id возвращается дважды
try
{
var executionStrategy = new NpgsqlRetryingExecutionStrategy(db, 3);
return(await executionStrategy.ExecuteAsync(async() =>
{
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions {
IsolationLevel = IsolationLevel.RepeatableRead
}, TransactionScopeAsyncFlowOption.Enabled))
{
var taken = (await db.WorkQueueItems.FromSqlRaw(
sql,
new NpgsqlParameter <int>("@queueId", (int)queueId),
new NpgsqlParameter <DateTime>("@now", DateTime.UtcNow),
new NpgsqlParameter <DateTime>("@timeLimit", (DateTime.UtcNow + timeLimit).Value)
).AsNoTracking().ToListAsync()).FirstOrDefault();
scope.Complete();
return taken;
}
}));
} catch (InvalidOperationException ex)
{
log.Warn(ex);
return(null);
}
}
