Ability to override DbPrincipal or set it to the connection's principal

[cmeeren]

I notice in this line here:

https://github.com/madelson/DistributedLock/blob/1a7a81ced42da04c2e094f7e8be96cbc630dd3c6/DistributedLock.SqlServer/SqlApplicationLock.cs#L88

that you use @DbPrincipal='public'.

We have several different services connecting to the same DB. Each service is independent (uses a separate schema in the DB and separate credentials in the connection string), and the locks one service takes should not impact the locks another service takes. The lock names used in each service may be the same as in other services - since they are independent, ideally lock names should not be necessary to coordinate.

My understanding is that currently, if two services take a lock with the same name, they "compete" for the lock (or whatever it's called). I would like each service to work in isolation. As far as I can tell, this would be the behavior if @DbPrincipal was set to the principal used in the connection (or allowed to be set manually).

Is this something you could implement?

[madelson]

@cmeeren I think you're right that if you pass a different dbPrincipal it would effectively give your locks a different keyspace. We could potentially add this as a new option for SqlDistributedLock and friends. For example, something like:

new SqlDistributedLock(name, connectionString, options: o => o.ScopeToDbPrincipal("..."));

Another potential direction would be to use the current dbprincipal (CURRENT_USER) as opposed to any arbitrary principal, something like options: o => o.ScopeToCurrentDbPrincipal().

That said, I'm not sure I'm convinced yet of the value of this feature. It seems that if you want to ensure uniqueness for one services's locks, you could simply prepend a unique prefix to each lock name created by the service. That also feels more straightforward and explicit than relying on the principal. This is probably what I would recommend for most library uses, although I would be interested to hear if this wouldn't work for your use-case.

[cmeeren]

Thanks for considering this!

Manual prefixing will certainly work. However, there are two important caveats:

• All services will have to know enough about the other services that they don't use the same prefix. Ideally, I don't want the services to have to coordinate this.
• The service's DB connection contains a unique per-service principal anyway, so the necessary information for a maintenance-free solution is already there; it just needs to be supported in DistributedLock.

The CURRENT_USER (or, synonymously, USER_NAME) solution would be the one I need. You could additionally support arbitrary principals; personally, I won't use that. (If you only added that, I would have to parse the connection strings to extract the principal name, which adds overhead and which I expect could fail if using integrated security.)

From a user perspective, I don't see any drawbacks to adding it. It would make DistributedLock more flexible without getting in anyone's way. I'm also guessing it would be fairly low-maintenance to add in DistributedLock.