0

I'm executing a method that I only want to execute one time to avoid some race conditions. Unfortunately, the sp_getapplock always returns 0 in that it retrieved the lock.

public void Any(MenuScheduleUpdateLocationRequest req)
{
  using (var trans = Db.OpenTransaction()) { 

  var hasLock = Db.SqlScalar<int>($"exec sp_getapplock @Resource='location-{req.LocationId}',@LockMode='Exclusive',@LockOwner='Transaction',@LockTimeout=0");
  if (hasLock!=0) {
   // should mean someone else has a lock and we just should abort
   return;
 }
 // the rest of the code
  trans.commit(); // releases the lock
}

The pasted code above always returns 0. I've tested this by creating several hangfire jobs and having them execute this code at the same time. The method takes about a minute to run and creates duplicate records which wouldn't happen if the lock worked.

Service Stack Service using ORMLite's IDBConnectionFactory standard registration in the app host:

 var factory = new OrmLiteConnectionFactory(ConfigUtils.GetConnectionString("umbracoDbDSN"), 
 SqlServer2017Dialect.Provider);
container.Register<IDbConnectionFactory>(factory);

SQL Azure database, azure app service (windows).

I don't really know if this is an ormlite issue, SQL provider, or a misunderstanding on my part.

lucuma
  • 18,247
  • 4
  • 66
  • 91
  • Have you used SQL Profiler to check if SQL Server returns `0`? I don't see how it's an OrmLite issue unless it's swallowing an Exception which you can avoid with `OrmLiteConfig.ThrowOnError=true`. Otherwise you can try [calling the SP via ADO .NET SqlServer classes directly](https://stackoverflow.com/a/7542564/85785). – mythz Jun 14 '20 at 06:09
  • In addition, are you sure the `Resource`-name of your lock has the uniqueness you require? Maybe you generate names (`req.LocationId`) that are (basically) "different every time" and thus you always create a new lock and thus you never block on an existing one. (again, use the SQL Profiler to see what is actually happening). – Christian.K Jun 14 '20 at 08:53
  • 2
    I know nothing about ormlite but I suspect `SqlScalar` returns a scalar value from a single-row, single-column result set but none is returned by `sp_getapplock`. Try `DECLARE @result int;exec @result = sp_getapplock @Resource='location-{req.LocationId}',@LockMode='Exclusive',@LockOwner='Transaction',@LockTimeout=0;SELECT @result;` – Dan Guzman Jun 14 '20 at 10:50
  • One thing i noticed in testing is that inside the same transaction the sp_getapplock will always return 0 (success) no matter how many times you call it as long as the lock was set inside that transaction. You have to test it against different transactions. @DanGuzman that seemed to have worked in this case. Thank you. – lucuma Jun 14 '20 at 19:52
  • 1
    @lucuma, when you invoke the same `sp_getapplock` more than once in a session/transaction, the subsequent executions only increment the lock reference count and return 0. The lock will be released after `sp_releaseapplock` is executed repeatedly (once for each `sp_getapplock`) or by a transaction commit/rollback. – Dan Guzman Jun 14 '20 at 21:27

0 Answers0