February 12, 2012 at 7:34 am
Comments posted to this topic are about the item Read Uncommitted, locks, and transactions
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
February 12, 2012 at 9:49 am
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
February 12, 2012 at 6:52 pm
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:
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
February 13, 2012 at 1:37 am
Good question, no complaints from me 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 13, 2012 at 1:39 am
Nice question, i learned something again.
February 13, 2012 at 1:46 am
Great question. I especially liked how you explicitly included the rationale for (and dangers of) using NOLOCK.
Thanks!
February 13, 2012 at 2:12 am
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
February 13, 2012 at 2:42 am
I liked this question - partially at least because I could recall the answer from double checking the answer to the last truncate question.
February 13, 2012 at 2:43 am
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?
February 13, 2012 at 2:48 am
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.
February 13, 2012 at 3:08 am
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!
February 13, 2012 at 3:26 am
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.
February 13, 2012 at 4:20 am
Great question!!!!
Thanks Craig!!!!!
February 13, 2012 at 4:37 am
This was removed by the editor as SPAM
February 13, 2012 at 5:02 am
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