Anyone who works with SQL Server, whether as a programmer, an administrator or even a user and wants to understand how the database engine handles multiple sessions and what causes excessive stalling, should read this book. It is also for SQL Server experts who need to understand how to diagnose and resolve issues with blocking and deadlocking.
Your queries may "fly" when evaluated separately if your SQL code was written intelligently and an effective indexing strategy was put in place. However, in the real world, when numerous processes can access the same data concurrently, SQL Server frequently needs to make one process wait, sacrificing concurrency and efficiency, in order that all can succeed, without compromising the integrity of the data. Concurrency is built on transactions. I describe these objects' ACID features, the levels of transaction isolation that specify the permitted actions when several transactions access the same data concurrently, and the optimistic and pessimistic concurrent access models used in SQL Server. Locks are used in pessimistic concurrency, which is SQL Server's default, to prevent concurrent issues. I describe every kind of lock and how it works with others. I demonstrate how to prevent excessive blocking and deadlocking, as well as how to control locking using hints and bound connections. Row versions are used to allow optimistic concurrency. I describe how row versioning functions, go over the two snapshot-based isolation levels available in SQL Server, and provide troubleshooting advice for problems like update conflicts. Even if your application has top-notch indexes and queries, they won't help you if another application has locked your data, making it impossible for you to access it. Because of this, each DBA and developer needs to be familiar with SQL Server concurrency and how to debug any problems. I hope my book is useful.
