October 28, 2019 at 12:00 am
Comments posted to this topic are about the item Transaction Isolation Levels
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/
October 28, 2019 at 11:00 am
Great article,
I've had to use READ UNCOMMITTED to fix so many locking and blocking issues (it's not a fix all and should be used only by experienced adults under supervision)
It's nice to see articles that can show my dev team a few tricks that makes a DBA a lifesaver 🙂
MVDBA
October 28, 2019 at 11:45 am
I am always surprised that the default isolation level for SSIS package objects is Serializable. Granted, it could be useful or even necessary in some cases. Isolation Level settings other than ReadCommitted should be concious decisions.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
October 28, 2019 at 1:43 pm
This was a good read. Short and straight to the point. As primary a application developer, the different isolation levels have a lot of application in different areas based on the app itself as well, I would imagine. Being able to read "dirty" uncommitted data in some cases may be required for performance reasons. It is part of the reason why a lot of other database systems (like NoSQL DBs) have differing principles than the default SQL Server behavior.
In the case of many NoSQL DBs, they even perform clustering with a sort of "eventual consistency", which means that reading will be performed on the closest server even if the data hasn't reached it yet.
It would definitely be interesting to do some performance tests with "read uncommitted" and see how badly I could break a test script. I am assuming that if you read from a table where an insert has occurred and then the transaction fails, executing the select again will not return the uncommitted record? I assume that may break the ACID durability principle, as the insert was reversed but still read initially...
October 28, 2019 at 1:55 pm
I use, and tell my team to use, the table hint "With (nolock)" to set read uncommitted. Has the advantage of being only applicable to that query and makes you be aware of the fact that you may have uncommitted data ( acceptable to us as developers 99% of the time).
October 28, 2019 at 2:27 pm
It would definitely be interesting to do some performance tests with "read uncommitted" and see how badly I could break a test script. I am assuming that if you read from a table where an insert has occurred and then the transaction fails, executing the select again will not return the uncommitted record? I assume that may break the ACID durability principle, as the insert was reversed but still read initially...
at devcon 2008 Itzik ben-gan (an absolute sql legend) showed a demo with 2 query windows open - one trying to alter data in a loop, the other querying it using nolock.
most of the time he hit a phantom row or a missing row within15 seconds... it was quite an artificial test and you will never hit a table that hard in a production environment.
Getting round the phantom rows issue can be managed by getting your clustered index correct.. I had an issue where I had clustered "date modified" - the rows were jumping around all over the place
MVDBA
October 28, 2019 at 2:42 pm
Very nice article. Straight, to the point and broken down into simple terms.
Great job aveek22.
AS a budding DBA, I have found SQL Server Central to be a great learning source.
Thank you to the team and contributors that keep this going.
Aubrey W Love
aka: PailWriter
https://www.aubreywlove.com/
October 28, 2019 at 2:53 pm
Thank you for your comments, @mvdba.
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/
October 28, 2019 at 2:55 pm
Totally agree with you @bryant-mcclellan
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/
October 28, 2019 at 2:58 pm
Thanks, @ReportThrall. It is in my to-do list to compare the performance between different transaction isolations and publish it. I will keep you posted.
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/
October 28, 2019 at 3:00 pm
That's correct, @grahamwade-69740. As per my understanding, I think the NOLOCK hint is applied to a table executing the query, whereas the READ UNCOMMITTED is at a session-level. Please correct me if wrong.
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/
October 28, 2019 at 3:02 pm
Thank you @pailwriter. It's my pleasure to be helpful to the community.
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/
October 28, 2019 at 3:04 pm
aveek22: That's as I understand it too.
October 28, 2019 at 3:18 pm
Nolock / read uncommitted is very popular nowadays, computer programs nowadays are just assumed to be "glitchy", and management loves deliverables that work "most of the time."
October 28, 2019 at 3:22 pm
Haha! Well said @patrickmcginnis59-10839. Reminded me of one of my old bosses.
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply