Solved

Using transaction locks in SQL Server 2012

Posted on 2013-12-17
3
495 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

861 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