我应该使用哪种MS SQL Lock技术？ [英] Which MS SQL Lock technique should I use?

问题描述

您好，



我有一个使用MS SQL 2008数据库的应用程序。



In数据库我有一个很长的storeprocedure，它正在做很多操作，比如读取一些表并检查一些特定的记录是否存在，如果没有退出那么它创建记录其他明智的更新它，这些类型的操作发生在不同的3-4表和许多其他检查正在进行。



这个storeprocedure从UI的不同SQL连接中经常调用（在1秒内调用10次）。现在我们有一个表X。其中需要检查特定记录然后需要更新它其他明智需要插入它。



但现在问题是因为这个storeprocedure调用非常快，所以表英寸×"有两个记录（一个需要插入，一个需要更新插入的记录）同时。因此，不要只插入一个记录，而是在表格中输入两个条目X



那么请你指导我如何使用不同的锁定来解决我的问题问题。



谢谢，

解决方案

你好，



我找到了我面临的问题的解决方案。



通过在sp和sp_releaseapplock的第一个语句处添加sp_getapplock语句sp它解决了我的问题。



我已添加如下：

  CREATE   PROCEDURE  [dbo]。[TestSP] 
  AS  
  BEGIN  
 
  BEGIN   TRANSACTION ; 
  EXEC  sp_getapplock  @ Resource  = '  TestSPLock'， @ LockMode  = ' 独家'; 
 
 //逻辑  SP 
 
  EXEC  sp_releaseapplock  @ Resource  = '  TestSPLock'; 
  COMMIT   TRANSACTION ; 
  END  

谢谢，

Hello,

I have one application which is using MS SQL 2008 database.

In the database I have one long storeprocedure which is doing many operation like reading some of the tables and check some specific record exists or not, if not exits then it create the record other wise update it, these type of operation happens on different 3-4 tables and many other checking going on.

And this storeprocedure called very frequently around (within 1 second called 10 times) from different SQL connection from UI. Now we have one table "X" in which need to check particular record exist then need to update it other wise need to insert it.

But now issue is because of this storeprocedure called very fast, so table "X" got two records (one need to insert and one need to update that inserted record) at the same time. so instead of insert one record only it making two entry in the table "X"

So can you please guide me how I should use different locking which can resolve my issue.

Thanks,

解决方案

Hello,

I have found the solution of the issue which I was facing.

By Adding the sp_getapplock statement at first statement of sp and sp_releaseapplock at last statement of sp it resolve my issue.

I've added it like below :

CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN

    BEGIN TRANSACTION;
    EXEC sp_getapplock @Resource = 'TestSPLock', @LockMode = 'Exclusive';

    //Logic of SP

    EXEC sp_releaseapplock @Resource = 'TestSPLock';
    COMMIT TRANSACTION;
END

Thanks,

这篇关于我应该使用哪种MS SQL Lock技术？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！