Insert .... Select that blocks nothing

  • Comments posted to this topic are about the item Insert .... Select that blocks nothing

  • We also use snapshot isolation level locking. It works nicely, but...

    There is always a but:

    a) It will take up 14 bytes on each row on each table in the database.

    b) There is more activity on TempDB.

    BOL warns about a (dead) locking issue, but we've not had any problems so far.

    Best regards,

    Henrik Staun Poulsen

    http://www.stovi.com

  • Does the "WITH (NOLOCK)" query hint not work as well for this (except for the dirty reads)?

  • I'm a little puzzled by this.

    There seem to be two points.

    Firstly, we have a long-running SELECT which must not prevent modifications to the tables being queried. In practice, shared locks are usually transient and affect data at the row level so are unlikely to interfere with modifications - unless the modifications escalate to the page or table level. But obviously snapshot isolation is a good solution to this potential problem (though it has costs of its own, as Henrik points out).

    The second part is more controversial. We have a long-running INSERT which must not prevent other processes performing SELECTs on the table into which we are inserting.

    But surely this is exactly what snapshot isolation achieves ?

    The solution offered - doing the inserts in chunks - actually solves nothing. As it stands, it simply lengthens the time taken to do the whole thing by re-evaluating the Common Table Expression every time we go round the loop - but there will still be a lock on the table for the entire duration of the loop.

    Perhaps the code provided is missing a COMMIT after each chunk of inserts. This might improve matters - I still can't really see the point.

    In my experience, you get the best database concurrency by implementing the fastest and most efficient queries and judiciously choosing the most suitable transaction isolation level. I'd need to see hard evidence that the kind of trick the author describes is actually effective before I adopted it.

  • David, you have a couple of interesting points. To add to yours,

    1 - if we're re-evaluating the CTE every time we do the loop, and we can't specify the order from within the CTE, (and we know that without an ORDER BY, the results are never guaranteed to be the same) how can you be sure that you aren't writing the same data (or some of the same data) into the resulting table in each loop? Wouldn't it be better in this instance to extract the data in a specific order into a temp table and then SELECT from that in increments?

    2 - Why are we pulling 5 million rows every time? Should we not just be inserting rows that changed? Surely there aren't 5 million new transactions every time we run this?

    3 - I'm assuming that we're pulling from the transactional server and INSERTing into the reporting one? Why is a ten-minute wait a problem? Is it a 24-7 reporting environment? (side comment, not sure ten minutes can't be improved upon by some of the gurus around here either, I know 5 million seems like a large number, but still ...)

    All in all, a good article about the benefits of the Isolation level, but perhaps there are improvements to be made at the macro level first to solve this specific problem?

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • To answer some questions:

    1) do understand how SQLServer does Read Committed with a shared lock (dirty read, phantom read, repeatable read) before you read on. Read books by Thomas Kyte.

    2) CTE is captured in memory which is much faster if you have enough memory (you should, unless budget, inheritance issues).

    3) the 5 million records came from 25 tables joins, you cannot figure out which ones have changed in any time interval. Also I stated "from business community". As DBA does not get into business requirement background was, (BSA and developer should do so) DBA fixes inefficient queries and tells them the right things to do. You made a point, there is only so much DBA can push back business community.

    4) Ideally yes, the best practice is reporting server is a different one from transactional server. Too many companies directly report on transactional data (for budget, refresh, timing or various reasons).

    Select ... with (nolock) = Read Uncommitted came along with side effects of dirty read, phantom read, repeatable read. If that meets your business requirements, you can do so.

    Too many people are having this problem but they did not understand. This script illustrate the core of the issue (isolation level, any users can run query all day long, they should block as brief as possible, they should make transaction short and fast). Snapshot isolation actually reduces lock/blocking or deadlock.

    Snapshot isolation does row versioning which use tempdb so you have to clear the way ahead making sure tempdb is on a fast drive and has enough space.

    Insert uses exclusive lock. Insert in small pieces is to allow rowlock not escalated, which the transaction is completed fast and providing breathing room to allow other queries to go thru in between (or block yours).

    If you want to see how this code actually performs and solves the problem, all you need is to run the code (replace with a large select) on a server that the select tables and insert tables are busily used. Monitor the activities and record timing etc.

    If you don't have the environment, just use begin tran to hold up queries. Do a simple script with begin tran Select and hold up commit, do another session with begin tran select and hold up commit, another session with begin tran insert hold up commit,. .... reverse the above orders, do the same again. You should see all.

    I rest my case without charging to your credit card.

  • Thanks for the script.

Viewing 7 posts - 1 through 6 (of 6 total)

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