SQL Server Concurrency

Locking, Blocking and Row Versioning

by Kalen Delaney

DescriptionTable of ContentsDetailsHashtagsReport an issue

Book Description

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 all processes can succeed without destroying data integrity.

Transactions are at the heart of concurrency. I explain their ACID properties, the transaction isolation levels that dictate 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!

This open book is licensed strictly for personal or educational use. You can download SQL Server Concurrency ebook for free in PDF format (9.6 MB).

Table of Contents

Chapter 1
Concurrency and Transactions
Chapter 2
Locking Basics
Chapter 3
Advanced Locking Concepts
Chapter 4
Controlling Locking
Chapter 5
Troubleshooting Pessimistic Concurrency
Chapter 6
Optimistic Concurrency
 

Book Details

Subject
Computer Science
Publisher
Red Gate
Published
2012
Pages
181
Edition
1
Language
English
ISBN13 Digital
9781906434908
ISBN10 Digital
1906434905
PDF Size
9.6 MB
License
For personal or educational use

Related Books

SQL Server Internals: In-Memory OLTP
The SQL Server 2016 In-Memory OLTP engine (a.k.a. Hekaton) is designed to exploit terabytes of available memory and high numbers of processing cores. It allows us to work with memory-optimized tables and indexes, and natively compiled stored procedures, in addition to the disk-based tables and indexes, and T-SQL stored procedures, that SQL Server h...
Defensive Database Programming with SQL Server
Resilient T-SQL code is code that is designed to last, and to be safely reused by others. The goal of defensive database programming, the goal of this book, is to help you to produce resilient T-SQL code that robustly and gracefully handles cases of unintended use, and is resilient to common changes to the database environment. Too often as deve...
SQL Server Hardware
SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS. Relational databases place heavy demands on their underlying hardware and many of these databases are mission-...
Data Science with Microsoft SQL Server 2016
R is one of the most popular, powerful data analytics languages and environments in use by data scientists. Actionable business data is often stored in Relational Database Management Systems (RDBMS), and one of the most widely used RDBMS is Microsoft SQL Server. Much more than a database server, it's a rich ecostructure with advanced analytic capab...
Migrating SQL Server Databases to Azure
SQL Server is Microsoft's relational database management system (RDBMS). SQL Server can now be hosted entirely in Microsoft Azure, either in a hosted virtual machine (VM) or as a hosted service. Hosting a virtual machine in Azure is known as infrastructure as a service (IaaS), and hosting a service in Azure is known as platform as a service (PaaS)....
Microsoft SQL Server Notes for Professionals
The Microsoft SQL Server Notes for Professionals book is compiled from Stack Overflow Documentation, the content is written by the beautiful people at Stack Overflow....