I have a case where I first want to run a direct query (using .Database.SqlQuery) to try to obtain an advisory lock. Then run another query using linq. When I run the linq query, it seems it uses another connection/session (or whatever) because the advisory lock is gone. Probably connection is released to connection pool or something.

If I start by doing ctx.Database.OpenConnection();, then the advisory lock seems to stay open.

But have I got this right? No downsides to explicit using OpenConnection? Connection seems to die when dbContext is disposed AFAIK.

var canLock = ctx.Database.SqlQuery<bool>($"SELECT pg_try_advisory_lock(42) as \"Value\"").FirstOrDefault();

if (!canLock)
    return;

var outboxMessages = ctx.Outbox.Where ...

I have a case where I first want to run a direct query (using .Database.SqlQuery) to try to obtain an advisory lock. Then run another query using linq. When I run the linq query, it seems it uses another connection/session (or whatever) because the advisory lock is gone. Probably connection is released to connection pool or something.

If I start by doing ctx.Database.OpenConnection();, then the advisory lock seems to stay open.

But have I got this right? No downsides to explicit using OpenConnection? Connection seems to die when dbContext is disposed AFAIK.

var canLock = ctx.Database.SqlQuery<bool>($"SELECT pg_try_advisory_lock(42) as \"Value\"").FirstOrDefault();

if (!canLock)
    return;

var outboxMessages = ctx.Outbox.Where ...

• entity-framework-core
• npgsql

Share Improve this question Follow edited Mar 21 at 13:45 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 21 at 10:56 MagnusMagnus 6988 bronze badges 11

• You don't need the same connection. A DbContext is a Unit-of-Work, not a database driver or connection and doesn't even use a connection until it has to either load data or persist all pending changes at once. – Panagiotis Kanavos Commented Mar 21 at 11:02
• obtain an advisory lock why? ORMs like EF or NHibernate work disconnected, using optimistic concurrency. Even ADO.NET DataTables work that way. ctx.Outbox are you trying to implement a database queue or transactional outbox? You don't need long-lived locks to append to a "queue" table – Panagiotis Kanavos Commented Mar 21 at 11:07
• Yes it is for a transactional outbox. There might be more than one instance of the service running at the same time and only one instance should process the outbox queue (i..e reading from the queue and publishing messages). – Magnus Commented Mar 21 at 11:55
• Start transaction explicitly and EF Core will not close connection. – Svyatoslav Danyliv Commented Mar 21 at 12:15
• @Magnus you don't need long-lived locks to manage concurrency. And long lived locks are a great way to freeze your application, especially as more servers are added. People have been trying the same bug since the 2000s, with the same consequences. And once again, DbContext isn't a connection, so you shouldn't be using ctx.Database for this job. If you use an already open connection with the DbContext, you can execute the SQL query you want directly – Panagiotis Kanavos Commented Mar 21 at 12:40

 |  Show 6 more comments

1 Answer 1

Sorted by: Reset to default 0

You don't need a long-running connection or transaction. Here's my code doing something similar when multiple users could be trying to retrieve the next item, a Submission in this case, to be processed. The two steps are 1) start a serializable transaction 2) execute a sql statement to lock the table. This is ok in my case because there aren't many users trying to work simultaneously. The locks are released when the method (retrieval) completes.

//Get currently assigned, un-processed submission, or next available submission to be processed.
using var transaction = await context.Database
    .BeginTransactionAsync(IsolationLevel.Serializable);
try
{
    //Lock the table while retrieving the next submission to process and assigning it to current user.
    //Prevents any other reads, to avoid conflicting assignments.
    context.Database.ExecuteSqlRaw(
        "SELECT TOP 0 Null FROM Offerings.Submissions WITH (TablockX)");
    submission = await context.Submissions
        .Include(s => s.OwnerUser)
        .Include(s => s.Session!.SessionType)
        .OrderByDescending(s => s.ProcessedByUserId).ThenBy(s => s.SubmittedDate)
        .FirstOrDefaultAsync(s =>
            s.Session!.MeetingId == meetingId
            && s.SubmittedDate.HasValue
            && (!s.PaperId.HasValue || !s.ProcessedDate.HasValue)
            && (s.ProcessedByUserId == userId || !s.ProcessedByUserId.HasValue)
            );

    //Assign this submission to the current user
    if (submission == null)
        transaction.Rollback();
    else
    {
        submission.ProcessedByUserId = userId;
        await context.SaveChangesAsync();
        transaction.Commit();
        //More efficient than including authors in the submission query.
        await context.Entry(submission)
            .Collection(s => s.SubmissionAuthors)
            .LoadAsync();
        await context.SubmissionAuthorAwards
            .Where(saa => saa.SubmissionId == submission.SubmissionId)
            .LoadAsync();
    }
}
catch (Exception)
{
    transaction.Rollback();
    submission = null;
}