December 9, 2024 at 11:09 pm
Great discussion, which brings out on of the most important truisms in database processing - maintaining data integrity and concurrency is really, really hard if you want both. It still drives me crazy when some IT professionals (I'm looking at you, developers) don't even give it a thought. I've been preaching this gospel for over 40 years and it's still the same. No end of grasshoppers who have yet to learn of its importance.
December 10, 2024 at 1:14 am
The responses are as expected... some hardcore "NEVER"s.
To those people, I ask, if you want a simple report, say the number of products purchased by day for the previous calendar month, you're saying that it's incorrect to use WITH (NOLOCK) on such a production report?
On what grounds?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2024 at 1:50 am
Jeff, with RCSI enabled, it’s barely if ever necessary.
December 10, 2024 at 3:11 am
On what grounds?
Uh, because the data is right, and the implementation is right.
You set up a straw man, where this condition applies, and you don't care about this, and that doesn't matter, just so you can use or justify a solution that has integrity holes.
Why not do it right so you don't have to work around the problems created by the software?
December 10, 2024 at 4:04 am
On what grounds?
Uh, because the data is right, and the implementation is right.
You set up a straw man, where this condition applies, and you don't care about this, and that doesn't matter, just so you can use or justify a solution that has integrity holes.
Why not do it right so you don't have to work around the problems created by the software?
Strawman? No. It's a practical question for a commonly used query. Tell me how using WITH (NOLOCK) will jeopardize anything in the case that I've provided.
They real point is, there is no panacea. "It Depends" is the only answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2024 at 4:05 am
Jeff, with RCSI enabled, it’s barely if ever necessary.
Agreed... all you have to do there is accept an extra 14 bytes per row and extra usage of TempDB. Since you're here, add an extra load on top of the performance issues that have occurred with 2019 and still not fixed in 2022 and you have a winning package, right? 😀
Tell me how the example I posed with cause any integrity problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2024 at 8:13 am
Jeff. I'll take a crack at your question:
In your example, are you assuming the date column is indexed and would be used? What if that is not the case, the rows for the past month share records with current month , and updates occur on other rows that cause page splits ? I believe you might get something called a ghost read in these cases.
And all subsequent replies were oblivious to what I mentioned earlier that nolock is not even acted on if you have a AG environment.
Also if the business case is that items can be returned, your counts for previous month(s) can change. If you have a program where you get discount from the manufacturer of a product if you sell over (i dont know) 2000 items in the month, then accuracy starts to matter before you get to the point wher you close the books and no longer accept refunds for some month.
----------------------------------------------------
December 10, 2024 at 8:47 am
The article itself is unhelpful overall, though I appreciate the author's enthusiasm and desire to share the 'common sense' idea that a rough result returned quickly is sometimes preferable to a fully 'correct' result delivered slowly.
It is certainly desirable to make an informed choice of isolation level. This requires accurate knowledge of the trade-offs being made under each of the levels available in SQL Server. Sadly, this article does little to advance the reader in that direction. In fact, it may move people the opposite way.
Every isolation level has its advantages and disadvantages. The default for non-cloud SQL Server editions is locking read committed, which provides surprisingly few guarantees and allows several behaviours people most often associate with read uncommitted:
There's no typo there. All those are possible under locking read committed, which appears to be the level the article's author considers determined to return 'perfect results'.
In addition to all of the above, read uncommitted also allows:
That said, every isolation level has its place.
If the data (and by implication all its associated secondary structures like indexes) is truly unchanging, there is no possibility of concurrency phenomena. This is a less common scenario (even 'historical' information can be subject to audit and revision), but not exactly unknown. In any case, an informed choice of read uncommitted isolation in that scenario is perfectly defensible.
Side note: Truly static data may be archived in a read-only database (not just filegroup). In that case, SQL Server is smart enough not to take any locks or other concurrency precautions. The requested isolation level makes little difference in that scenario.
Many people argue that read committed snapshot isolation (RCSI) is a better default than locking read committed, since it (mostly) provides a statement-level point in time view of the committed and consistent state of the database. Indeed, RCSI is the default isolation level on cloud SQL Server editions.
I agree with this position on balance, but that doesn't mean there aren't significant drawbacks to RCSI as well. The 14-byte per row versioning overhead and (somewhat overstated these days) impact on tempdb (or the persistent version store if Accelerated Database Recovery is in use) are relatively well-known, as is the overhead on all update and delete statements (and some inserts) as they need to create versions.
Many are aware that the RCSI 'point in time' view as of the start of the current statement means the data can be somewhat out of date. Other transactions can change and commit data we are reading under RCSI--we will data as it was. This is not usually a huge concern but may be more so for long-running statements, depending on the circumstances and requirements.
RCSI also requires careful handling when applying to code that previously relied on the blocking behaviour of locking read committed for correctness.
One must be especially careful with RCSI in triggers and any other code used to enforce a complex data integrity constraint or business rule. It's no good using out-of-date committed data for integrity! Appropriate use of READCOMMITTED locks is necessary here.
What is less commonly appreciated is the effect of RCSI on the background ghost and ghost version clean up processes. Cleanup under non-versioning isolation levels can proceed while a statement is in progress. RCSI delays the start of ghost processing to at least the end of the currently executing RCSI statement.
That's bad enough, but a long-running or accidentally left open active RCSI transaction prevents ghost and ghost version cleanup from the point the open transaction started. The accumulation of ghosts and ghosted versions can dramatically slow down other active sessions because they have to skip over the ghosts. Remember, we're talking about RCSI here, not snapshot isolation (SI).
For a data-changing statement, the situation is even worse because SQL Server must request a lock on the ghosts to ensure correct behaviour, even under RCSI. Anyone interested can find a simple one-column one-table example of 123,456 single row updates that go from 12 seconds under locking read committed to half an hour under RCSI in my recent article, Improved RCSI Ghost Cleanup in SQL Server 2022. It's an extreme illustration, but hopefully informative.
All that aside, the only isolation level that guarantees results as if the executing transaction were the only database activity during its execution is SERIALIZABLE. Most people don't want to deal with the issues associated with using that highest level of logical isolation when it isn't necessary.
Everything below serializable is a compromise, but it's important to accurately know what those compromises are.
Anyone interested in more of my thoughts on this topic can find the index to my series at SQL Server Isolation Levels: The Series.
December 10, 2024 at 11:44 am
Jeff - He he aren’t you the clever one 😉 but my example was just plucked out of the air and nothing at all to do with system throughput. The point I was making is that use of NOLOCK depends on the nature of the data. There is absolutely NO point in taking a lock when there is zero value for the production staff in doing that. By minimising contention when you don’t need it you are reserving system resources in your system for when you really need a lock. You could run another few million transactions a second in your amazing example and take a couple of CPUs away. Understand your data requirements and only use the resources you need when you need them.
December 10, 2024 at 4:13 pm
@paul-2 White
Excellent post Paul. Snapshot isolation is useful for a consistent (but not up to date) result without locking. RSCI is logically equivalent to MVCC which has the same drawbacks.
Which leaves the question, if serializable is the correct level of locking and concurrency why haven't DBMS / TMS providers been able to implement it yet without impacting throughput? After all, with faster processors and all data in memory then surely queuing one transaction after another must be possible with acceptable performance? It is an interesting and perhaps provocative question. Is there a better mathematical solution to the serialisation /locking /performance problem?
December 10, 2024 at 5:27 pm
A new server CPU from AMD has 192 cores. I wonder how many cores can be effectively used by serialized transactions.
PS I think some of our legacy apps would use 1 of the 192. 🙂
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
December 10, 2024 at 5:32 pm
Excellent post Paul. Snapshot isolation is useful for a consistent (but not up to date) result without locking. RSCI is logically equivalent to MVCC which has the same drawbacks.
Thanks for the post, SSC-Addicted. I am curious as to why you say MVCC has the drawback of a not up to date result. I know this technology pretty well (but not RSCI as well), and it was my impression that datum became immediately available once a transaction commited.
To me, your subsequent question is philosophical and not answerable. MVCC, properly implement, does not impact performance very much. Any serializable requirement may impact performance a bit, or more, depending on use case, because of the serial nature of time. That is a hard one to get around, with math or anything else. (But if you figure a way to do it, please share - I could use it! <g>)
December 10, 2024 at 5:52 pm
The problem with MVCC is that you only see the data as it was at the beginning of the transaction. If performing updates, other data which are relevant to validation can change during your transaction (no locks taken), Thus you can get the wrong answer,
There is a very detailed explanation of how to overcome the shortcomings of MVCC in Oracle in Toon Koopelaars and Lex de Haan's book Applied Mathematics for Database Professionals. It is not for the fainthearted. But it is also a bit of a distraction from the real worth of the book.
The book is absolutely worth reading as for me the best explanation of the mathematics behind the relational model for the non-mathematician (like myself), Taking you from the theoretical background in logic and set theory through to a full SQL implementation based on the theory. It really is and outstanding piece of work and thoroughly recommended.
An for serialisation as standard for updates, it seems a reasonable requirement to ask for, It would free us up to concentrate on getting the logic right rather than having to worry about things that the DBMS / Transaction Control System should deal with quietly behind the scenes.
December 10, 2024 at 8:00 pm
Thanks Will. I can't believe it, but you are making me go out and buy that book! <g>
If the book clears up my misunderstanding, that will be great. But I thought that only showing you the data as it was at the beginning of the transaction was exactly what MVCC is supposed to do - to give you a consistent view of the data at a single point in time. If, in your example, data changed that could affect validation after the transaction started, it should not be seen. If that update is in the same transaction, of course it will be seen.
But I will read the book, since I am that kind of person. Thanks.
December 10, 2024 at 10:23 pm
"if serializable is the correct level of locking and concurrency why haven't DBMS / TMS providers been able to implement it yet without impacting throughput? After all, with faster processors and all data in memory then surely queuing one transaction after another must be possible with acceptable performance"
In effect, serializable does do that, with operations queuing on the lock.
But... so why not have one lock and queue everything? Well, consider hybrid processing where both SQL and a driver language form your application (e.g., C#).
Example:
There's a possibility that another execution thread (in C#) will simultaneously process account 123, leading to a race condition and a mess. To combat that, we can:
The select statement in serializable will lock the records - and other transactions (at least at serializable level) cannot even read that account balance until the transaction is completed, giving independence between the transactions.
But performance would be terrible if you couldn't process multiple accounts simultaneously, and with a 200 ms delay, we're limited to 8 transactions per second!
So serializable means 99% of the time, things can execute in parallel (e.g., updating different accounts) and 1% of the time, they're queued behind the read lock (e.g, updating the same account). It's the correctness guarantees with waiting for other systems that limits throughput.
Of course, Serializable would mean another transaction across the whole table (e.g., reporting) would wait for a 'break in the traffic' when there's no locks, or could halt the processing of all transactions because it would lock everything. Thus, reporting often is at a different isolation (e.g., READ COMMITTED, SNAPSHOT, or WITH NOLOCK).
Personally, these days, I'd:
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply