Read Uncommitted, locks, and transactions

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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

  • Paul White

    SSC Guru

    Points: 150442

    Good question, no complaints from me 🙂

  • palotaiarpad

    SSCertifiable

    Points: 5615

    Nice question, i learned something again.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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

  • call.copse

    SSCoach

    Points: 17261

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

  • cengland0

    SSCertifiable

    Points: 6102

    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?

  • Ian_McCann

    SSCertifiable

    Points: 5154

    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.

  • paul.knibbs

    SSCoach

    Points: 15270

    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!

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    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

  • paul s-306273

    SSChampion

    Points: 10615

    Craig - interesting question.

    Thanks.

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

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