Prevent locking a database

Question

Post reply

Prevent locking a database

Pieter-423357

SSCommitted

Points: 1619
More actions
April 14, 2009 at 9:02 am

#208323

All,
I am having a server with a single database that allows users to transfer tables in and out. Typically my group would do some additional processing to the imported table before a user would get the results. Most users have setup a linked server and use SELECT INTO or SSIS to transfer tables into this database.
I am aware that SELECT INTO causes the meta-data to be locked and make it unavailable for review. It looks like SSIS is presenting the same obnoxious behavior. When several users are active and at least one is using SELECT INTO to transfer data into this database, the others are no longer able to even obtain a table listing, which is quite annoying.
What change can I make to still permit the data transfer ability, but not to lock up the meta-data?
Thanks for your help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mortalic SSCrazy Points: 2068 More actions · Answer 1

Depending on if it is t-sql or ssis there are several ways to handle locking.

The easiest is to use join hints:

select

columna,

columb

from tablename with (NOLOCK)

Keep in mind that this will mean the queries will look at uncommitted transactions if you do this.

You can also set the isolation level to READ UNCOMMITTED at the start of the query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

As for SSIS, check your properties on the SQL task, you will see an Isolation Level property that you can also set to READ UNCOMITTED.

For a good overview on what this is doing exactly take a look at this MS article:

http://msdn.microsoft.com/en-us/library/ms173763.aspx

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Pieter (4/14/2009)
I am aware that SELECT INTO causes the meta-data to be locked and make it unavailable for review.

Hi Pieter, there was a discussion about this quite recently which provided evidence that the problem has been resolved in current versions of SQL Server, certainly SQL2k5.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 3

Here's a complete article on..

"SQL Server 2005 Row Versioning-Based Transaction Isolation"

http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx

Tim White