SQL Hacks

Chapter 9. Locking and Performance

In applications requiring high query throughput, high concurrency rates, and/or large result sets, you need to ensure good database design. However, systems with good database design can still suffer from performance problems. Before you spend money on hardware architecture improvements you should look at your approach to queries, and how queries and result sets are passed between applications and database systems. You can employ some useful techniques to reduce overhead and improve throughput.

You need to be aware of the behavior of concurrent transactions under your selected isolation level, in order to ensure query correctness. Careful selection of transaction isolation level can also produce significant performance changes.

You should also consider how result sets are transferred from database to application. By minimizing transfer sizes you promote effective queries. It also means that your application code has to deal only with the information it actually needs.

Determine Your Isolation Level

Your isolation level dictates how locks are obtained in a transaction. A higher isolation level results in fewer concurrency problems at the expense of performance.

If two transactions are competing for the same resource, the database system needs rules to tell it how to resolve the competition. The rules depend on the transaction isolation level. You need to understand these rules, as they can affect system behavior.

Autocommit

To be able to investigate ...