Solved

Using transaction locks in SQL Server 2012

Posted on 2013-12-17
3
485 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:ScottPletcher
Comment Utility
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
Comment Utility
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:
ScottPletcher earned 500 total points
Comment Utility
>> 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now