Solved

Using transaction locks in SQL Server 2012

Posted on 2013-12-17
3
500 Views
Last Modified: 2016-02-10
Hi Experts,
I have written a stored procedure that executes an SSIS package in my SQL Server 2012 database.  The SSIS package loads data from a file into a table when executed.  If there is data in the table, it deletes the existing data and then writes the new data to the table.  This was a great solution when only one user was using the stored procedure.  Now more users are using this feature, and could possibly use it simultaneously.  I need to somehow lock the execution of the stored procedure, if another user is in the middle of running it.  How can I do this?  Do I need to add syntax in my stored procedure that would prevent this from happening?

Thanks in advance,
mrotor
0
Comment
Question by:mainrotor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39724856
Rather than locking out the stored proc, you could add the username as a clustering key to the table.  When a new user runs the proc, only rows with that username are deleted.  That should allow multiple users to use the proc at the same time.  It's a lot of code and a real hassle to restrict procs to 1-up use.
0
 

Author Comment

by:mainrotor
ID: 39724880
That sounds like a good idea.  How would I add the username as a clustering key to the table?  How would I delete rows with only that user's name on it?  
Would I have to recreate my SSIS package (since that's where the deletion happens)?

thanks,
mrotor
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39725115
>> How would I add the username as a clustering key to the table? <<

Good q :-).  It depends; hopefully it's possible.

Check "SELECT ORIGINAL_LOGIN()" for each user and see what it's value is.  If that doesn't come in, maybe you can use "HOST_NAME()".  Or if you have their unique AD name.

Anything that gives you an absolutely unique string for that user.

Then, where your package currently does this:

DELETE FROM dbo.table_name

You change it to:

DELETE FROM dbo.table_name WHERE user_name_column = @current_user_name

or the equivalent.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question