Read Uncommitted, locks, and transactions

  • Comments posted to this topic are about the item Read Uncommitted, locks, and transactions


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Nice question Craig, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (2/12/2012)


    Nice question Craig, thanks!

    Thanks. I was concerned that I wouldn't explain the components well enough. Hopefully the scenario is clear and this doesn't end up as an anti-NOLOCK discussion instead of one regarding troubleshooting the differences between DELETE and TRUNCATE.

    Also kind of surprised, this wasn't supposed to be available until tomorrrw. :hehe:


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Good question, no complaints from me 🙂

  • Nice question, i learned something again.

  • Great question. I especially liked how you explicitly included the rationale for (and dangers of) using NOLOCK.

    Thanks!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Evil Kraig F (2/12/2012)


    Koen Verbeeck (2/12/2012)


    Nice question Craig, thanks!

    Thanks. I was concerned that I wouldn't explain the components well enough. Hopefully the scenario is clear and this doesn't end up as an anti-NOLOCK discussion instead of one regarding troubleshooting the differences between DELETE and TRUNCATE.

    It is explained well enough - at least according to me - and the MSDN article linked to also explains it all.

    Your question did say the end users decided they could live with incomplete or missing data, so an anti-lock discussion is not necessary: the DBA/report developer did his job in my opinion. He informed the users about the consequences and they accepted them.

    Evil Kraig F (2/12/2012)


    Also kind of surprised, this wasn't supposed to be available until tomorrrw. :hehe:

    That's because is used DBCC_TIMEWARP to solve this question 😀 :hehe: 😉

    Ok seriously now, in the weekend questions usually appear a few hours too early.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I liked this question - partially at least because I could recall the answer from double checking the answer to the last truncate question.

  • This was a good question. I like how it was written in the terms of a real problem to solve.

    What other ways can you change the entire contents of a table and still have the reports available without dirty data?

    I've thought of creating a view that points to the table. Then, you can populate another table with the new information and then update the view to point to the new table. Once all the queries are done running on the old dataset table, the old table can then be deleted. I've never tried this so I'm curious what the experts on this forum have to say about this. Does the view get locked if a query is running it? Any negative sides to my proposed solution?

  • Thank you.

    Great question and I really appreciate the time and effort you took in creating the scenario, rather than just posting up the code and asking what's going on.

  • Managed to get it right by a process of elimination--the other three answers looked wrong (especially the one about TRUNCATE not being logged!), so got the right one by default!

  • cengland0 (2/13/2012)


    This was a good question. I like how it was written in the terms of a real problem to solve.

    What other ways can you change the entire contents of a table and still have the reports available without dirty data?

    I've thought of creating a view that points to the table. Then, you can populate another table with the new information and then update the view to point to the new table. Once all the queries are done running on the old dataset table, the old table can then be deleted. I've never tried this so I'm curious what the experts on this forum have to say about this. Does the view get locked if a query is running it? Any negative sides to my proposed solution?

    I didn't check it, but I fully expect an ALTER VIEW statement to require an exclusive schema lock. Anything less would be extremely worrying. (And if anyone wants to test, it's fairly easy - just start a transaction in a window, then run ALTER VIEW, and don't commit or rollback yet; from another window, you can now run sp_lock to see the locks being held by the uncommitted transaction.

    For the situation depicted by Craig, the best solution would probably be to use one of the SNAPSHOT isolation levels.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Great question!!!!

    Thanks Craig!!!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • This was removed by the editor as SPAM

  • Craig - interesting question.

    Thanks.

Viewing 15 posts - 1 through 15 (of 53 total)

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