April 16, 2020 at 6:32 am
Hello @jeff
I was referring Oracle in the context to explain how we "were" doing things until date & how the demand from new Solution provider to use WAIT(NOLOCK) with all queries executed against their tables, is getting us in to confusion as WAIT(NOLOCK) clearly documented as dirty-reading.
If possible we would prefer "Select a,b,c from xyz" in the place of "Select a,b,c from xyz WAIT(NOLOCK)" to make sure that only committed rows are selected. However, as I mentioned with my previous posts, I am reading statements from different sources about the Select statements putting exclusive locks on rows, blocking other transactions from accessing the currently queried row(s)
Thank you very much for the reply.
regards,
You used the term WITH(NOLOCK) in your first post. That's certainly not Oracle but that doesn't matter. The solution is to write the code to be fast enough that there will be no blocking and so no need for such hints, whether it be in Oracle, SQL Server, or any other database.
I'd also find a new "solution provider" if they're going to recommend doing otherwise.
And I've never heard of SELECTs putting Exclusive Locks on anything.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2020 at 10:23 am
Shared locks only affect data modification queries. They don't affect other reads, so you have to focus there. The key issue is ensuring that your queries are written well to take advantage of your structures and indexes. Also, that you have the right structures and indexes in place.
You're very focused on long running queries. What are your queries doing? In a transactional environment, most queries should be of fairly short duration because you're only ever retrieving a small subset of the data. Indexes assist the queries in their speed and all of this is dealt with easily by the lock management of the SQL Server OS. You get lots of small queries and lots of data updates and they're side by side and everything is cool. On the other hand, if you're looking at analysis systems that are doing massive scans of data, usually there's little updates to the data going on, so the concerns about conflict should be reduce.
If you're really looking at a hybrid environment, in addition to snapshot isolation, which I strongly believe that most people should just enable (yes, test, yes, ensure tempdb is configured well, and then enabled it), you can look to columnstore indexes to assist. If your environment is primarily transactional, classic OLTP, use standard clustered indexes for storage, and, as needed, add non-clustered columnstore indexes to help deal with the analytical queries.
Ensuring that you have the right indexing in place in support of the queries is probably even more important than focusing on the isolation level. However, also get the isolation level right (and read committed snapshot is your buddy there).
However, at this point, all of this sounds like speculation and fear, not knowledge. Get the structures and data in place and then monitor to understand the behaviors you're dealing with and then address those behaviors. Premature "optimizations" (and I can absolutely argue NOLOCK is not an optimization) of including unnecessary query hints based on bad internet information is not the way to design and build systems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2020 at 2:46 pm
Hello @Grant
While I respect all other inputs, trying to answer my half cooked questions (mostly due to my inexperience with the stack), your last post nails it. Yes, it is more like speculation and fear, as I am leaving a particular technology and embracing another one. Few things are pretty new for me, I must agree. I've worked approximately 23 years "Selecting" against Oracle database & determined to do stuffs the right way from the very beginning.
Basically we do material transactions, sales analysis, price differences, customer retentions etc against variables. Although we have materialized views (talking about Oracle), as I have mentioned earlier with one of my other replies, we are asked to deliver reports which require as on the moment details, for which we have to refer the transaction tables.
As we will be moving from Oracle EBS to MS SQL based 3rd party ERP suite, all I have to make sure that, the subsystems are NOT going to pull the main system through locks and other complications.
"Shared locks only affect data modification queries. They don't affect other reads, so you have to focus there." from your reply satisfies my primary concern.
Thanks once again Grant, Jeff and other who contributed towards the thread. As I am a beginner with MS SQL, I am pretty sure my acquired knowledge will definitely help me to understand and deal with it better in the coming days.
Thanks and regards,
rajesh
April 16, 2020 at 3:33 pm
Very happy to help out any time we can. We sure try to be friendly & helpful here (doesn't always work, but that's what we aim for).
So yes, focus on the structures and the queries and from what you're saying, I think you'll be ok. Of course you may hit snags, but those are best dealt with by adjustments to the structure and the code. Seldom will you need query hints to fix things. There are exceptions, but as the word says, they should be exceptional.
Microsoft has a lot of documentation and support for people moving off of Oracle to SQL Server (for some weird reason, ha), so I'd go there and do a search. You should find quite a lot of supportive material to help out. Also, I can suggest our Stairways series here (look up at the top of the page). There are series on almost any topic, but several on getting started aspects of SQL Server.
Good luck. Have fun. Welcome to #SQLFamily.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply