SQL Server Concurrency
Locking, Blocking and Row Versioning
Book Details
| Author | Kalen Delaney |
| Publisher | Red Gate |
| Published | 2012 |
| Edition | 1 |
| Paperback | 181 pages |
| Language | English |
| ISBN-13 | 9781906434908 |
| ISBN-10 | 1906434905 |
| License | Open Access |
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 book is published as open-access, which means it is freely available to read, download, and share without restrictions.
If you enjoyed the book and would like to support the author, you can purchase a printed copy (hardcover or paperback) from official retailers.
Download and Read Links
Share This Book
[localhost]# find . -name "*Similar_Books*"
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-cri
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 develop
Protecting SQL Server Data
In Protecting SQL Server Data, John Magnabosco demonstrates how sensitive data, stored in SQL Server, can be protected using an efficient and maintainable encryption-based data architecture. He explains how to assess and categorize data elements according to sensitivity, regulate access to the various categories of data using database roles, views
Performance Tuning with SQL Server Dynamic Management Views
Dynamic Management Views (DMVs) are a significant and valuable addition to the DBA's troubleshooting armory, laying bare previously unavailable information regarding the under-the-covers activity of your database sessions and transactions. Why, then, aren't all DBAs using them? Why do many DBAs continue to ignore them in favour of "tried and truste
SQL Server Backup and Restore
In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool. Capturing backups using SSMS or simple scripts is perfectly fine for one-off backup operations, but any backups that form part of the recovery strategy for any given d
50 Ways to Avoid Find and Fix ASP.NET Performance Issues
Read the tips and tricks that top developers from the .NET and SQL Server communities use to boost code and query performance in their applications. 50 tips from the .NET and SQL Server communities for boosting performance in your .NET application; Learn the secrets of your fellow developers and read advice from MVPs and other experts; Covers perfo