September 13, 2010 at 10:10 am
Hello Everyone - I have a blocking issue and need a little bit of help from the experts. My description of the issue is below and if furthur information is needed please let me know.
Our application allows customers to build sql ad-hoc sql statments to query information and to create a table from the result of the query. An example of that type of query is below.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * INTO MONTY2 FROM (SELECT "rl63_at_405", "at_176", SUM(dt."rl1267_at_21034") "rl1267_at_21034" FROM (SELECT rl63_activities.name "rl63_at_405", OTBD.name "at_176", rl1267_ELksVw.UniqueLinkClicks "rl1267_at_21034" FROM otbd_forms OTBD WITH (NoLock) LEFT OUTER JOIN action rl63_activities WITH (NoLock) ON (rl63_activities.activity_id = OTBD.activity_id) INNER JOIN ELksVw rl1267_ELksVw WITH (NoLock) ON (rl1267_ELksVw.email_id = OTBD.outbound_id) WHERE (OTBD.outbound_id = 19801)) "DT" GROUP BY "rl63_at_405", "at_176" ) createDT
The blocking occurs when the below statement is run after the select * into.
SELECT c.column_name, c.data_type, c.character_maximum_length, c.numeric_precision, c.is_nullable, c.numeric_scale, c.column_name, ISNULL(( SELECT 'YES' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcc ON pkcc.constraint_name = pkc.constraint_name WHERE pkc.table_name = c.table_name AND pkc.constraint_type = 'PRIMARY KEY' AND pkcc.column_name = c.column_name), 'NO') AS 'is_primary_key' FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.table_name = 'Bobo'
Can someone explain to me why this blocking is occuring? I know that somehow the creation of the table is involved but can not pinpoint why. I tried reproducing the error on my test machine but no luck.
I have not been able to find out why the blocking is happening. Any help would be appreciated.
September 13, 2010 at 10:40 am
TrailKing, is the second transaction running after the first transaction has completed?
😀
September 13, 2010 at 10:42 am
Yes - the blocked spid runs just fine after the select * into completes
September 13, 2010 at 10:48 am
Ok, in our shop we do the following when working with large tables and transactions.
--This section strictly creates the table, and nothing else.
SELECT ColumnOne, ColumnTwo, ColumnThree... INTO TableName_Destination
FROM TableName_Source
WHERE 1 = 0
--This section copies the required data into the newly created table, with no metadata locks.
INSERT INTO TableName_Destination
SELECT ColumnOne, ColumnTwo, ColumnThree...
FROM TableName_Source
The reason for the (WHERE 1 = 0) is to reduce locking on the metadata tables. The typical locking required when using (WHERE 1= 0) on the metadata tables is less than a second. This should alleviate your problems.
😀
September 13, 2010 at 10:52 am
Thanks CoolWebs for the reply but can you explain to me why the blocking is occuring. I have tried researching the cause but nothing in google or BOL has pointed me in the right direction.
September 13, 2010 at 10:59 am
Before I answer without all of the correct facts, please see the link to a similar discussion below. It's very similar to your situation even though it's dealing with temporary tables.
http://www.sqlservercentral.com/Forums/Topic984394-391-1.aspx#bm984500
😀
September 13, 2010 at 11:27 am
Took a look at the link and although it is similar to my issue the posters did not explain the reasons for their recommendations. What I am looking for is an explanantion of why the information_schema is locked by the select * into statement?
September 13, 2010 at 11:35 am
The underlying table being locked is the "sysobjects" table, which feeds the INFORMATION_SCHEMA view, and others.
😀
September 13, 2010 at 11:40 am
Outstanding!! CoolWeb would you by any chance have a link to something so I can read up on this behavior. This issue has driven me bonkers for about 3 weeks trying to research and recreate.
September 13, 2010 at 11:48 am
A couple of things looking at this would lead me to access patterns for the underlying objects.
I would do the inner joins first and the the outer joins.
You also have a join on a view which may be accessing several more tables.
Is there any way you could add a where clause to the first subquery (not the innermost subquery).
Also, could you provide DDL and sample data so people could reproduce your issue? An execution plan of the first query would prove most helpful here. I imagine there are plenty of scans and potentially locks happening in this query (don't believe everything you have heard about the NOLOCK hint - NOLOCK is usually a bad thing).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 13, 2010 at 12:19 pm
Since sysobjects contains "one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure" (SQL Books Online). Then the fundamental cause is that your create table statement is wrapped in the transaction of copying the data from the source table as well. This is what ACID is all about.
So my solution utilized transaction management to work around the lock on the sysobjects table. That should be all there is to know about this particular problem.
😀
September 13, 2010 at 12:57 pm
Thanks a lot CoolWebs for the info and your patience. Your explanation sheds light on the issue I am seeing.
CirquedeSQLeil - A couple of things. First, the view definition is below. It contains 334 million records with an estimated size of 400 + GB. I have changed the table names but the sql text should still be sound. Also, why do you say nolock is usually a bad thing?
select
Lnks.designer_resource_id LinkId,
Lnks.url,
Lnks.title,
ems.history_record_date,
Lnks.microsite_id,
Lnks.microsite_page_id,
1 as LinkCount,
(select COUNT(*)
from chmm with (nolock)
where Lnks.designer_resource_id = chmm.designer_resource_id and
chmm.message_id = ems.message_id and
metric_type = 1 and
message_id is not null and
outbound_id is not null) as LinkClicks,
(CASE WHEN (select COUNT(*)
from chmm with (nolock)
where Lnks.designer_resource_id = chmm.designer_resource_id and
chmm.message_id = ems.message_id and
metric_type = 1 and
message_id is not null and
outbound_id is not null) >= 1 THEN 1 ELSE 0 END) UniqueLinkClicks,
ems.audience_member_id,
ems.email_id,
ems.domain
from
des_res Lnks with (nolock) inner join des_page Page with (nolock) on Lnks.des_page_id = Page.des_page_id inner join
Otbd_Forms Forms with (nolock) on Forms.des_page_id = Page.des_page_id inner join
ches ems with (nolock) on ems.email_id = Forms.outbound_id
where
Lnks.resource_type_id = 1 and
ems.delivery_date is not null
September 13, 2010 at 1:01 pm
Have a read at this article:
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 13, 2010 at 1:28 pm
I see your point and was aware of the possible issues of nolock or read uncommitted. Fortunately, we do not always use it and when we do it is for a count of records 99% and we have not encountered any issues as of yet because the users just want to know if there are millions of records or a couple of hundred. This fact weighed against blocking a update while a count(*) is running is a no-brainer. When there are issues I try to add a covering index or something else to help SQL return results quickly.
September 13, 2010 at 2:36 pm
That's good.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply