使用SQL Server应用程序锁解决锁定要求 [英] Using SQL Server application locks to solve locking requirements
问题描述
我有一个基于Dynamics CRM 2011的大型应用程序，该应用程序在各个地方都有必须根据某些条件查询记录的代码，如果不存在，则创建该记录，否则进行更新。
I have a large application based on Dynamics CRM 2011 that in various places has code that must query for a record based upon some criteria and create it if it doesn't exist else update it.
我所谈论的这种事情的一个例子类似于：
An example of the kind of thing I am talking about would be similar to this:
stk_balance record = context.stk_balanceSet.FirstOrDefault(x => x.stk_key == id);
if(record == null)
{
record = new stk_balance();
record.Id = Guid.NewGuid();
record.stk_value = 100;
context.AddObject(record);
}
else
{
record.stk_value += 100;
context.UpdateObject(record);
}
context.SaveChanges();
就CRM 2011的实施而言（尽管与该问题并不严格相关），可以从同步或异步插件。问题是代码不是线程安全的，在检查记录是否存在与创建记录不存在之间，另一个线程可能会进入并首先执行相同操作，从而导致记录重复。
In terms of CRM 2011 implementation (although not strictly relevant to this question) the code could be triggered from synchronous or asynchronous plugins. The issue is that the code is not thread safe, between checking if the record exists and creating it if it doesn't, another thread could come in and do the same thing first resulting in duplicate records.
由于系统的体系结构，常规锁定方法不可靠，使用多个线程的各种服务都可能都使用相同的代码，并且这些多个服务也在多台计算机之间进行负载平衡。
Normal locking methods are not reliable due to the architecture of the system, various services using multiple threads could all be using the same code, and these multiple services are also load balanced across multiple machines.
在试图找到解决这个问题的方法时，它不会增加大量额外的复杂性，并且不会损害没有单点故障或单点故障的想法我遇到了使用SQL Server应用程序锁的想法。
In trying to find a solution to this problem that doesn't add massive amounts of extra complexity and doesn't compromise the idea of not having a single point of failure or a single point where a bottleneck could occur I came across the idea of using SQL Server application locks.
我想到了以下课程：
public class SQLLock : IDisposable
{
//Lock constants
private const string _lockMode = "Exclusive";
private const string _lockOwner = "Transaction";
private const string _lockDbPrincipal = "public";
//Variable for storing the connection passed to the constructor
private SqlConnection _connection;
//Variable for storing the name of the Application Lock created in SQL
private string _lockName;
//Variable for storing the timeout value of the lock
private int _lockTimeout;
//Variable for storing the SQL Transaction containing the lock
private SqlTransaction _transaction;
//Variable for storing if the lock was created ok
private bool _lockCreated = false;
public SQLLock (string lockName, int lockTimeout = 180000)
{
_connection = Connection.GetMasterDbConnection();
_lockName = lockName;
_lockTimeout = lockTimeout;
//Create the Application Lock
CreateLock();
}
public void Dispose()
{
//Release the Application Lock if it was created
if (_lockCreated)
{
ReleaseLock();
}
_connection.Close();
_connection.Dispose();
}
private void CreateLock()
{
_transaction = _connection.BeginTransaction();
using (SqlCommand createCmd = _connection.CreateCommand())
{
createCmd.Transaction = _transaction;
createCmd.CommandType = System.Data.CommandType.Text;
StringBuilder sbCreateCommand = new StringBuilder();
sbCreateCommand.AppendLine("DECLARE @res INT");
sbCreateCommand.AppendLine("EXEC @res = sp_getapplock");
sbCreateCommand.Append("@Resource = '").Append(_lockName).AppendLine("',");
sbCreateCommand.Append("@LockMode = '").Append(_lockMode).AppendLine("',");
sbCreateCommand.Append("@LockOwner = '").Append(_lockOwner).AppendLine("',");
sbCreateCommand.Append("@LockTimeout = ").Append(_lockTimeout).AppendLine(",");
sbCreateCommand.Append("@DbPrincipal = '").Append(_lockDbPrincipal).AppendLine("'");
sbCreateCommand.AppendLine("IF @res NOT IN (0, 1)");
sbCreateCommand.AppendLine("BEGIN");
sbCreateCommand.AppendLine("RAISERROR ( 'Unable to acquire Lock', 16, 1 )");
sbCreateCommand.AppendLine("END");
createCmd.CommandText = sbCreateCommand.ToString();
try
{
createCmd.ExecuteNonQuery();
_lockCreated = true;
}
catch (Exception ex)
{
_transaction.Rollback();
throw new Exception(string.Format("Unable to get SQL Application Lock on '{0}'", _lockName), ex);
}
}
}
private void ReleaseLock()
{
using (SqlCommand releaseCmd = _connection.CreateCommand())
{
releaseCmd.Transaction = _transaction;
releaseCmd.CommandType = System.Data.CommandType.StoredProcedure;
releaseCmd.CommandText = "sp_releaseapplock";
releaseCmd.Parameters.AddWithValue("@Resource", _lockName);
releaseCmd.Parameters.AddWithValue("@LockOwner", _lockOwner);
releaseCmd.Parameters.AddWithValue("@DbPrincipal", _lockDbPrincipal);
try
{
releaseCmd.ExecuteNonQuery();
}
catch {}
}
_transaction.Commit();
}
}
我会在我的代码中使用它来创建一个SQL服务器应用程序锁使用我正在查询的唯一密钥作为这样的锁名
I would use this in my code to create a SQL Server application lock using the unique key I am querying for as the lock name like this
using (var sqlLock = new SQLLock(id))
{
//Code to check for and create or update record here
}
现在这种方法似乎可行，但是我绝不是任何SQL Server专家，并且对将其放置在生产代码附近还是很警惕。
Now this approach seems to work, however I am by no means any kind of SQL Server expert and am wary about putting this anywhere near production code.
我的问题确实包含3个部分
My question really has 3 parts
1。
SQL Server应用程序锁是否完全不适合此目的？
Are SQL Server application locks completely unsuitable for this purpose?
是否一次可以拥有最多数量的应用程序锁（具有不同的名称）？
Is there a maximum number of application locks (with different names) you can have at a time?
如果存在性能方面的考虑，如果可能会创建大量的锁吗？
一般方法还有什么问题？
Are there performance considerations if a potentially large number of locks are created? What else could be an issue with the general approach?
2。
如果这样可以使用SQL Server应用程序锁，我是否已正确使用了它们？
If SQL Server application locks are usable like this, have I actually used them properly?
是否有使用SQL Server达到相同结果的更好方法？
Is there a better way of using SQL Server to achieve the same result?
在上面的代码中正在连接到Master数据库并在其中创建锁。这有可能引起其他问题吗？我应该在其他数据库中创建锁吗？
In the code above I am getting a connection to the Master database and creating the locks in there. Does that potentially cause other issues? Should I create the locks in a different database?
3。是否可以使用不使用SQL Server应用程序锁的完全替代方法？
我无法使用存储过程来创建和更新记录（在CRM 2011中不受支持）。
I can't use stored procedures to create and update the record (unsupported in CRM 2011).
我不想添加单个故障点。
I don't want to add a single point of failure.
推荐答案
您可以轻松完成此操作。
You can do this much easier.
//make sure your plugin runs within a transaction, this is the case for stage 20 and 40
//you can check this with IExecutionContext.IsInTransaction
//works not with offline plugins but works within CRM Online (Cloud) and its fully supported
//also works on transaction rollback
var lockUpdateEntity = new dummy_lock_entity(); //simple technical entity with as many rows as different lock barriers you need
lockUpdateEntity.Id = Guid.parse("well known guid"); //well known guid for this barrier
lockUpdateEntity.dummy_field=Guid.NewGuid(); //just update/change a field to create a lock, no matter of its content
//--------------- this is untested by me, i use the next one
context.UpdateObject(lockUpdateEntity);
context.SaveChanges();
//---------------
//OR
//--------------- i use this one, but you need a reference to your OrganizationService
OrganizationService.Update(lockUpdateEntity);
//---------------
//threads wait here if they have no lock for dummy_lock_entity with "well known guid"
stk_balance record = context.stk_balanceSet.FirstOrDefault(x => x.stk_key == id);
if(record == null)
{
record = new stk_balance();
//record.Id = Guid.NewGuid(); //not needed
record.stk_value = 100;
context.AddObject(record);
}
else
{
record.stk_value += 100;
context.UpdateObject(record);
}
context.SaveChanges();
//let the pipeline flow and the transaction complete ...
For more background info refer to http://www.crmsoftwareblog.com/2012/01/implementing-robust-microsoft-dynamics-crm-2011-auto-numbering-using-transactions/
这篇关于使用SQL Server应用程序锁解决锁定要求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
