Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am maintaining a queue in a database table. An SSIS package checks the queue every two minutes. If it finds anything then it runs a job. I am experiencing some sort of table level locking.

The query that checks the queue is not a simple SELECT statement; it has an INNER JOIN too. How can I avoid the table level locking? Would it help to create a view from my query and then configure the SSIS package to check the view instead of querying the queue table inner joined with another table?

All suggestions are welcome.

share

migration rejected from stackoverflow.com Oct 15 '13 at 16:49

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Mark Storey-Smith, Jon Seigel, Michael - sqlbot, RolandoMySQLDBA, Max Vernon Oct 15 '13 at 16:49

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
@user1711287 Are you using Service Broker ? –  Kin Aug 14 '13 at 14:48
comments disabled on deleted / locked posts

1 Answer 1

It might be a good idea to look into using a different isolation level for your database. SQL server uses pessimistic locking by default. If that's causing you problems, you might want to look into an isolation level which has a row level versioning approach ( e.g. Snapshot isolation ).

This article provides some good information about row level versioning in SQL Server : http://technet.microsoft.com/en-us/library/ms179599(v=SQL.105).aspx

share
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.