1

Im trying to use the postgresql's ROW LOCK in EF Core. I have a .net core application and postgresql database.

In GetUserContainer function, I build my queries using the FromSqlRaw, as below

var query = db.UserContainers.FromSqlRaw($"SELECT *, xmin FROM tableName {rowlock}")
     .Where(uc => uc.Id == id);

Where rowLock is a function parameter whose value can be "FOR UPDATE" or "FOR SHARE".

I use the below code to read the UserContainers and update

await Task.WhenAll(
   Task.Run(async () =>
   {
    using (var ctx = DbFixture.GetCtx()){
    var repo = new PgUserRepository(ctx);
    var usertoupdate = await repo.GetUserContainer(containerId, 
    dealershipId, eTag, true);
    repo.UpdateUserContainer(usertoupdate, usertoupdate.eTag);
  await repo.Commit();
   }}),
  Task.Run(async () =>
  {
  var message = await TypeOneChannel.Reader.WaitToReadAsync();
  using (var ctx = DbFixture.GetCtx()){
   var repo = new PgUserRepository(ctx);
  repo.Invoking(async (userContainer) => 
  await repo.GetUserContainer(containerId, dealershipId, eTag, true))
  .Should()
 .Throw<Exception>("Because the row is exclusively locked by other task");
 }}));

Note: the snippets of functions used in the above code are as below

 public UserContainer UpdateUserContainer(UserContainer item)
    {
       return db.UserContainers.Update(item).Entity;
    }

 public async Task Commit()
    {
       await db.SaveChangesAsync();
    }

As per the code and the row lock concept, I expect, the getusercontainer in the second task to either fail or wait until the row lock is released, as it is locked by first task.

Im trying to ensure an ACID transaction for updating the user from application side. Postgresql documentation (ROW LOCK) makes it clear that it is possible in SQL, however I need to implement this in C#.

Is there anything I'm doing wrong/missing important details? Or any suggestions to achieve this with a new approach are welcome.

Trupti
  • 108
  • 2
  • 14
  • Why do you want to throw an exception? Just use a transaction around your user select and update. – Ian Kemp Sep 21 '20 at 16:54
  • This is unit test method so I was trying to assert() that the update on the second user fails as the first user has locked the row. Nevertheless, now I have abandoned this idea of using "FOR UPDATE" to lock the row and instead relying on xmin of postgresql to ensure concurrency checks. – Trupti Sep 24 '20 at 13:47
  • Unfortunately, I had to go back to using "FOR UPDATE" in the EFCore: db.UserContainers.FromSqlRaw($"SELECT *, xmin FROM tableName FOR UPDATE". I believe the documentation and the numerous blog posts I had been reading since a week. However they are all about executing the "FOR UPDATE" on the database side and not from the application side using linq. I'm saturated with the web content I read so far. Has anyone does this before? Please help. – Trupti Sep 29 '20 at 08:52

0 Answers0