If you are designing a multiple- user application that will both query and modify the same data, you need a good understanding of concurrency and consistency. Concurrency is the ability to have many simultaneous users operating at once. The more users who can work well simultaneously , the higher your concurrency. Consistency is the level at which the data in multiple-user scenarios exhibits the same behavior that it would if only one user were operating at a time. Consistency is expressed in terms of isolation levels. At the highest isolation level, Serializable, the multiple-user system behaves identically to what would exist if users submitted their requests seriallythat is, as if the system made every user queue up and run operations one at a time ( serially ). At the Serializable level you have a greater need to protect resources, which you do by locking and which reduces concurrency. (Locking is discussed in Chapter 13. You should also understand transactional concepts, which are presented in Chapter 10.)
In many typical environments, an ongoing struggle occurs between the OLTP demands on the database and the DSS demands. OLTP is characterized by relatively high volumes of transactions that modify data. The transactions tend to be relatively short and usually don't query large amounts of data. DSS is read- intensive , often with complex queries that can take a long time to complete and thus hold locks for a long time. The exclusive locks needed for data modification with OLTP applications block the shared locks used for DSS. And DSS tends to use many shared locks and often holds them for long periods, stalling the OLTP applications, which then must wait to acquire the exclusive locks required for updating. DSS also tends to benefit from many indexes and often from a denormalized database design that reduces the number of tables to be joined. Large numbers of indexes are a drag on OLTP because of the additional work to keep them updated. And denormalization means redundancya tax on the update procedures.
You need to understand the isolation level required by your application. Certainly, you do not want to request Serializable (or, equivalently, use the HOLDLOCK or SERIALIZABLE hint) if you need only Read Committed. And it might become clear that some queries in your application require only Read Uncommitted (dirty read), since they look for trends and don't need guaranteed precision. If that's the case, you potentially have some flexibility in terms of queries not requiring shared locks, which keeps them both from being blocked by processes modifying the database and from blocking those modifying processes. But even if you can live with a dirty-read level, you should use it only if necessary. This isolation level means that you might read data that logically never existed; this can create some weird conditions in the application. (For example, a row that's just been read will seem to vanish because it gets rolled back.) If your application can live with a dirty-read isolation level, it might be comforting to have that as a fallback. However, you should probably first try to make your application work with the standard isolation level of Read Committed and fall back to dirty read only if necessary.