Select * into blocking select against information_schema

  • 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.

  • TrailKing, is the second transaction running after the first transaction has completed?

    😀

  • Yes - the blocked spid runs just fine after the select * into completes

  • 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.

    😀

  • 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.

  • 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

    😀

  • 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?

  • The underlying table being locked is the "sysobjects" table, which feeds the INFORMATION_SCHEMA view, and others.

    😀

  • 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.

  • 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

  • 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.

    😀

  • 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

  • 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

  • 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.

  • 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