Concurrency and Consistency Tradeoffs

If you're designing a multiuser 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 multiuser 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 multiuser system behaves just as it would if users submitted their requests serially. At the Serializable level you have a greater need to protect resources, which you do by locking, which in turn reduces concurrency. (Locking is discussed in Chapter 14. You should also understand transactional concepts, which are covered in Chapter 12.)

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 and thus stalls the OLTP applications, which 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 redundancy—a 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) because they look for trends and don't need guaranteed precision. If that's the case, you might have some flexibility in terms of queries not requiring shared locks, which keeps them 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.