Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using transaction locks in SQL Server 2012

Posted on 2013-12-17
3
Medium Priority
?
523 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 70

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

722 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