Part 3 of 5 · 1 min read
Why the two conflict on one system
An analytic query's long scan competes for the same resources that operational transactions depend on to stay fast.
Think of that same cashier's counter again. Now an auditor is also standing there. The auditor slowly flips through every receipt from the past year, totaling up annual sales, right at the counter, while customers are still trying to check out.
In plain words: an OLTP workload depends on consistently low latency; a customer checking out should never wait seconds for a response. An OLAP query, by its nature, scans a large fraction of the data, work that takes real time and consumes real resources, memory, disk I/O, and often locks. Running both on the same database at the same time means a long-running analytic scan can compete for exactly the resources an operational transaction needs to stay fast, slowing down or blocking the very checkouts the business depends on. This is the core reason organizations separate the two rather than running analytic queries directly against their operational database.
A worked example. An analyst runs a query scanning three years of order history directly against the bookstore's live operational database, during business hours. Customers checking out at that exact moment experience slower response times, since the scan is consuming I/O and lock resources the checkout transactions also need.
The key lines. No new SQL; the conflict is about shared resource contention between two workloads, not a syntax difference.
Resource contention (OLTP versus OLAP)
Resource contention between OLTP and OLAP workloads happens when a long-running analytic scan competes with operational transactions for the same memory, disk I/O, or locks, degrading the low latency the operational workload depends on.
Example: a three-year revenue scan run directly against the live operational database, slowing down customers' checkouts happening at the same time, is resource contention between OLAP and OLTP.