Chapter 8. Resolving transaction issues
This chapter covers
- Transactions, locks, blocks, and deadlocks
- Sessions, connections, and requests
- Methods to minimize contention and improve concurrency and performance
A query may run fast when it runs alone, taking advantage of all available resources without the impact of other running queries. But in the real world it needs to interact with other running queries, each requiring resources and coordination to reduce contention.
To enable queries to run concurrently, resources need to be shared, and data needs to be protected so it’s able to give consistent and correct values. Therefore, there’s often a trade-off between data consistency and concurrency. Many factors can affect both consistency and concurrency, and we’ll discuss these throughout this chapter.
To protect the consistency of data, SQL Server issues locks against resources such as a row or page of data within a database table. These locks can lead to blocking, resulting in reduced performance, concurrency, and scalability. Circular blocking between queries can lead to a deadlock, which results in SQL Server terminating one of the queries and rolling back its changes.