Reading and writing the common data

Question

I have a couple tables and application service that intensively inserts, updates its. From the other hand some client application have to read data using those tables. Sometimes client application gets timeout exceptions because of locks this shared data. What the common or maybe non-ordinal solution to separate intensively modified data and fast reading the same one. By the way, data can be consistent but can be delayed in time for 10-20 seconds (for example). OLAP cannot be used.

Answer 1

Use Snapshot or Read Committed Snapshot isolation for your reads.

When a row versioning-based isolation level is enabled, the Database Engine maintains versions of each row that is modified. Applications can specify that a transaction use the row versions to view data as it existed at the start of the transaction or query instead of protecting all reads with locks. By using row versioning, the chance that a read operation will block other transactions is greatly reduced.

Alternatively:

• Read less. If the nature of the application and data is such that you can tolerate a 10-20 seconds delay, cache the response and returned the cached result until your next successful read.
• Use replication to distribute data to a set of read-only slaves.
• Fix your application. Timeout implies long running transaction; occasionally necessary but typically a design flaw.