Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.
share|improve this answer
    
+1 Thank you for such a quick reply –  garik Feb 1 '12 at 0:00
1  
Such is the nature of transaction isolation, a trade between concurrency and consistency. Are you using the default read committed isolation level? Welcome to a world of inconsistency. –  Mark Storey-Smith Feb 1 '12 at 0:37
1  
@garik: snapshot isolation does not cause inconsistencies in selects. I wrote the article you are quoting, and I did not mean that. On the contrary, snapshot isolation prevents inconsistencies in selects. Can you elaborate what in the article is misleading? –  AlexKuznetsov Feb 1 '12 at 15:09
    
@AlexKuznetsov thank you, Alex. –  garik Feb 1 '12 at 20:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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