Which MS SQL Lock technique should I use?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

, +

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,

Posted 20-Mar-14 21:58pm

npdev13

Updated 20-Mar-14 22:01pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

npdev13 · Accepted Answer · 2014-03-27T21:49:00

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 :

SQL

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,