Skip to main content

Über dieses Buch

Master SQL Server’s Concurrency Model so you can implement high-throughput systems that deliver transactional consistency to your application customers. This book explains how to troubleshoot and address blocking problems and deadlocks, and write code and design database schemas to minimize concurrency issues in the systems you develop.
SQL Server’s Concurrency Model is one of the least understood parts of the SQL Server Database Engine. Almost every SQL Server system experiences hard-to-explain concurrency and blocking issues, and it can be extremely confusing to solve those issues without a base of knowledge in the internals of the Engine. While confusing from the outside, the SQL Server Concurrency Model is based on several well-defined principles that are covered in this book.
Understanding the internals surrounding SQL Server’s Concurrency Model helps you build high-throughput systems in multi-user environments. This book guides you through the Concurrency Model and elaborates how SQL Server supports transactional consistency in the databases. The book covers all versions of SQL Server, including Microsoft Azure SQL Database, and it includes coverage of new technologies such as In-Memory OLTP and Columnstore Indexes.

What You'll LearnKnow how transaction isolation levels affect locking behavior and concurrency
Troubleshoot and address blocking issues and deadlocks
Provide required data consistency while minimizing concurrency issues
Design efficient transaction strategies that lead to scalable code
Reduce concurrency problems through good schema design
Understand concurrency models for In-Memory OLTP and Columnstore Indexes
Reduce blocking during index maintenance, batch data load, and similar tasks

Who This Book Is For
SQL Server developers, database administrators, and application architects who are developing highly-concurrent applications. The book is for anyone interested in the technical aspects of creating and troubleshooting high-throughput systems that respond swiftly to user requests.



Chapter 1. Data Storage and Access Methods

It is impossible to grasp the SQL Server concurrency model without understanding how SQL Server stores and accesses the data. This knowledge helps you to comprehend various aspects of locking behavior in the system, and it is also essential when troubleshooting concurrency issues.
Dmitri Korotkevitch

Chapter 2. Transaction Management and Concurrency Models

Transactions are the key concept in data management systems; they guarantee the consistency and durability of the data in the database. It is impossible to implement a database system without proper transaction management in place.
Dmitri Korotkevitch

Chapter 3. Lock Types

This chapter will discuss the key concept in SQL Server concurrency—locks. It will provide an overview of the major lock types in SQL Server, explain their compatibility, and, finally, demonstrate how different transaction isolation levels affect the lifetime of the locks in the system.
Dmitri Korotkevitch

Chapter 4. Blocking in the System

Blocking is, perhaps, one of the most common concurrency problems encountered in the system. When blocking occurs, multiple queries block each other, which increases the execution time of queries and introduces timeouts. All of that negatively affects the user experience with the system.
Dmitri Korotkevitch

Chapter 5. Deadlocks

A deadlock is a special blocking case that occurs when multiple sessions—or sometimes multiple execution threads within a single session—block each other. When it happens, SQL Server terminates one of the sessions, allowing the others to continue.
Dmitri Korotkevitch

Chapter 6. Optimistic Isolation Levels

Optimistic transaction isolation levels were introduced in SQL Server 2005 as a new way to deal with blocking problems and address concurrency phenomena in a system. With optimistic transaction isolation levels, queries read “old” committed versions of rows while accessing data modified by the other sessions, rather than being blocked by the incompatibility of shared (S) and exclusive (X) locks.
Dmitri Korotkevitch

Chapter 7. Lock Escalation

Although row-level locking is great from a concurrency standpoint, it is expensive. In memory, a lock structure uses 64 bytes in 32-bit and 128 bytes in 64-bit operating systems. Keeping information about millions of row- and page-level locks would use gigabytes of memory.
Dmitri Korotkevitch

Chapter 8. Schema and Low-Priority Locks

SQL Server uses two additional lock types called schema locks to prevent table and metadata alterations during query execution. This chapter will discuss schema locks in depth along with low-priority locks, which were introduced in SQL Server 2014 to reduce blocking during online index rebuilds and partition switch operations.
Dmitri Korotkevitch

Chapter 9. Lock Partitioning

SQL Server, as with other modern database engines, is designed to work on servers with a large number of CPUs. It has many optimizations that help the engine to scale and efficiently work in such environments.
Dmitri Korotkevitch

Chapter 10. Application Locks

This chapter will discuss another SQL Server locking feature called application locks, which place locks on an application resource identified by name. Application locks allow you to serialize access to T-SQL code, similar to critical sections and mutexes in client applications.
Dmitri Korotkevitch

Chapter 11. Designing Transaction Strategies

A properly implemented transaction strategy would benefit every system. This chapter will provide a set of generic guidelines on the subject and discuss how you can improve concurrency in a system.
Dmitri Korotkevitch

Chapter 12. Troubleshooting Concurrency Issues

System troubleshooting is both an art and a science. It is also a very big and complex topic. If I had to write a book covering all aspects of system troubleshooting, it would have more pages than the one you are currently reading.
Dmitri Korotkevitch

Chapter 13. In-Memory OLTP Concurrency Model

The In-Memory OLTP technology, introduced in SQL Server 2014, can significantly improve the performance and throughput of OLTP systems. The key technology component—memory-optimized tables—stores the data in-memory, utilizing lock- and latch-free multi-versioning concurrency control.
Dmitri Korotkevitch

Chapter 14. Locking in Columnstore Indexes

Columnstore indexes are a type of index that stores data on a per-column rather than per-row basis. This storage format benefits query processing in data warehousing, reporting, and analytics environments where, although queries typically read a very large number of rows, they work with just a subset of the columns from a table.
Dmitri Korotkevitch


Weitere Informationen

Premium Partner

BranchenIndex Online

Die B2B-Firmensuche für Industrie und Wirtschaft: Kostenfrei in Firmenprofilen nach Lieferanten, Herstellern, Dienstleistern und Händlern recherchieren.



Best Practices für die Mitarbeiter-Partizipation in der Produktentwicklung

Unternehmen haben das Innovationspotenzial der eigenen Mitarbeiter auch außerhalb der F&E-Abteilung erkannt. Viele Initiativen zur Partizipation scheitern in der Praxis jedoch häufig. Lesen Sie hier  - basierend auf einer qualitativ-explorativen Expertenstudie - mehr über die wesentlichen Problemfelder der mitarbeiterzentrierten Produktentwicklung und profitieren Sie von konkreten Handlungsempfehlungen aus der Praxis.
Jetzt gratis downloaden!