SQL Server Concurrency (Paperback)

If you've designed your SQL code intelligently, and implemented a sensible indexing strategy, there's a good chance your queries will "fly", when tested in isolation. In the real world, however, where multiple processes can access the same data at the same time, SQL Server often has to make one process wait, sacrificing concurrency and performance, in order that in order that all can succeed, without destroying data integrity. Transactions are at the heart of concurrency. I explain their ACID properties, the transaction isolation levels that dictate the acceptable behaviors when multiple transactions access the same data simultaneously, and SQL Server's optimistic and pessimistic models for mediating concurrent access. Pessimistic concurrency, SQL Server's default, uses locks to avoid concurrency problems. I explain all the different locks and their compatibility. I show how to control locking with hints and bound connections, and how to troubleshoot excessive blocking and deadlocking. Optimistic concurrency uses row versions to support concurrency. I explain how row versioning works, cover SQL Server's two Snapshot-based isolation levels and offer troubleshooting tips for issues such as update conflicts. Your application can have world-class indexes and queries, but they won't help you if you can't get your data, because another application has it locked. That's why every DBA and developer must understand SQL Server concurrency, and how to troubleshoot any issues. I hope my book helps!

如果您設計了聰明的 SQL 代碼並實施了明智的索引策略，那麼在單獨測試時，您的查詢很有可能會"飛快"執行。然而，在現實世界中，當多個進程同時訪問相同的數據時，SQL Server 經常需要讓一個進程等待，以維護並發性和性能，以確保所有進程都能成功執行而不破壞數據完整性。事務是並發性的核心。我解釋了它們的 ACID 屬性，事務隔離級別，這些級別決定了多個事務同時訪問相同數據時的可接受行為，以及 SQL Server 的樂觀並發和悲觀並發模型。悲觀並發是 SQL Server 的默認模式，它使用鎖來避免並發問題。我解釋了所有不同的鎖及其相容性。我展示了如何使用提示和綁定連接來控制鎖定，以及如何解決過多的阻塞和死鎖問題。樂觀並發使用行版本來支持並發。我解釋了行版本控制的工作原理，介紹了 SQL Server 的兩個基於快照的隔離級別，並提供了解決更新衝突等問題的疑難解答技巧。您的應用程序可能擁有世界級的索引和查詢，但如果您無法獲取數據，因為另一個應用程序已經鎖定了它，那麼這些都無濟於事。這就是為什麼每個數據庫管理員和開發人員都必須了解 SQL Server 的並發性，以及如何解決任何問題。希望我的書能對您有所幫助！