2

I just finished reading an excellent article on isolation levels here. Our company will soon start development on a rewrite and expansion of our current product. My desire is to have an OLTP database and a separate, more denormalized, reporting database. Assuming we're somewhat disciplined and most of our ad-hoc and reporting type queries actually go to the reporting database, does it sound appropriate that our OLTP database have a default isolation level of Read Committed (we won't need a more stringent isolation level for OLTP) and our reporting database be Snapshot Isolation (probably RCSI)?

My thinking is that if our OLTP database is actually a true OLTP database and not serving double-duty as a reporting DB, we won't need snapshot isolation, and the associated overhead it entails. But snapshot isolation would be desirable on the reporting database so that readers are not blocked by the constant flow of data coming in, and reading the last saved version of a row would be acceptable.

4
  • 1
    READ COMMITTED SNAPSHOT should be the default for any new OLTP application on SQL Server. Using locking READ COMMITTED here would be a gross premature optimization, and risks running into concurrency problems later. Oct 9, 2017 at 16:09
  • @DavidBrowne-Microsoft - Thank you. Can you tell me why, in a bit more detail, this is the case with any new OLTP? Maybe point me to an article. Oct 9, 2017 at 16:16
  • @RandyMinder, READ_COMMITTED_SNAPSHOT is enabled by default in Azure SQL DB, which is a good indication that you could enable it by default without much worry for an on-premise or VM-hosted installation. Oct 9, 2017 at 17:42
  • @JohnEisbrener - Very good to know. Thank you. Oct 9, 2017 at 17:43

2 Answers 2

8

Just to add to the other answer.

SQL Server supports two different flavors of READ COMMITTED, legacy locking READ COMMITTED and READ COMMITTED SNAPSHOT. If you've ever built and supported a high-volume OLTP application on locking READ COMMITTED you know why RCSI was introduced (in addition to making it easy to port Oracle applications to SQL Server).

Locking READ COMMITTED is tricky to get concurrency and is prone to deadlocking, as readers block writers and writers block readers. Deadlocks are a kind of bug in your application, but they're famously hard to find in testing. So you have a chance to make a choice that reduces the number of hard-to-find-in-testing bugs. That's worth a lot. RCSI also increases the concurrency of your application, enabling you to scale up to use multiple cores more easily.

So, RCSI increases the scalability and reliability of your application, at the cost of a little extra bookkeeping for UPDATES and DELETES (and INSERTS if you have a trigger), and an extra 14 bytes per row.

RCSI is simpler to program to over all, the main thing is that you sometimes need to opt in to a locking read using the UPDLOCK table hint when you want to read a row and immediately update it and need to ensure that no other session does the same thing concurrently.

4

I wouldn't assume that snapshot or READ COMMITTED SNAPSHOT necessarily will increase your overhead that might lead to performance degradation -- the reduced wait times may increase throughput.

The danger with RCS and SNAPSHOT has more to do with how the app is coded -- if your programmers assume the blocking semantics of READ COMMITTED when the queries execute in Snapshot mode, you could be in for trouble. For example

UPDATE foo SET bar = 1 + (SELECT MAX(bar) from foo);

will likely cause problems in high throughput situations because multiple readers will get the same MAX(bar) value at the same time.

However, you will likely still need some form of non-OLAP operational reports and queries, and snapshot mode can be a great boon for performance there.

My point is that you can design and develop your OLTP system for RCS mode, and should consider it. After all, that's pretty much how most OLTP systems in Oracle are written.

1
  • ... that query is only "safe" (in the sense it would return a unique value) under SERIALIZATION. Which would likely cause major blocking problems, if any of the rows are scheduled to be updated. The need for gapless sequences is somewhat rare, and you normally want a more involved process anyways. Oct 9, 2017 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.