SQL Server Concurrency
Locking, Blocking and Row Versioning
| eBook | Free |
| Rating |  |
| Author | Kalen Delaney |
| Publisher | Self-publishing |
| Published | 2012 |
| Pages | 181 |
| Language | English |
| Format | Paper book / ebook (PDF) |
| ISBN-10 | 1906434905 |
| ISBN-13 | 9781906434908 |
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!
- Kalen Delaney (2 books)
Similar Books
Expert SQL Server Transactions and Locking 
by Dmitri Korotkevitch
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 devel...
Price: $24.23 | Publisher: Apress | Release: 2018
SQL Server 2017 Administration Inside Out
by William Assaf, Randolph West, Sven Aelterman, Mindy Curnutt
Dive into SQL Server 2017 administration - and really put your SQL Server DBA expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds - all you need to plan, implement, manage, and secure SQL Server 2017 in any production environment: on-premises, cloud, or hybrid. Four SQL Serve...
Price: $34.23 | Publisher: Microsoft Press | Release: 2018
SQL Server 2019 Administration Inside Out
by Randolph West, Melody Zacharias, William Assaf, Sven Aelterman, Louis Davidson, Joseph D'Antoni
Dive into SQL Server 2019 administration - and really put your SQL Server DBA expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds - all you need to plan, implement, manage, and secure SQL Server 2019 in any production environment: on-premises, cloud, or hybrid. Six experts th...
Price: $48.15 | Publisher: Microsoft Press | Release: 2020
A Developer's Guide to Data Modeling for SQL Server
by Eric Johnson, Joshua Jones
Effective data modeling is essential to ensuring that your databases will perform well, scale well, and evolve to meet changing requirements. However, if you're modeling databases to run on Microsoft SQL Server 2008 or 2005, theoretical or platform-agnostic data modeling knowledge isn't enough: models that don't reflect SQL...
Price: $6.15 | Publisher: Addison-Wesley | Release: 2008
Beginning Microsoft SQL Server 2008 Administration
by Chris Leiter, Dan Wood, Michael Cierkowski, Albert Boettger
SQL Server 2008 introduces many new features that will change database administration procedures; many DBAs will be forced to migrate to SQL Server 2008. This book teaches you how to develop the skills required to successfully administer a SQL Server 2008 database; no prior experience is required. The material covers system installation a...
Price: $5.17 | Publisher: Wrox | Release: 2009
Professional SQL Server 2008 Internals and Troubleshooting
by Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Jonathan Kehayias, Cindy Gross, Steven Wort
When faced with a variety of SQL Server issues, you need to be able to troubleshoot SQL Server as well as its underlying operating system. To that end, this author group of SQL Server experts presents you with invaluable advice that brings together - and simplifies - practical internal information concerning SQL Server along with troubles...
Price: $5.49 | Publisher: Wrox | Release: 2010
Microsoft SQL Server 2008 Management and Administration
by Ross Mistry, Hilary Cotter
If you need to deploy, manage, or secure Microsoft SQL Server 2008, this is the complete, fast-paced, task-based reference you've been searching for. Authored by a world-class expert on SQL Server in the enterprise, this book goes far beyond the basics, taking on the complex tasks that DBAs need to make the most of Microsoft's f...
Price: $9.87 | Publisher: SAMS Publishing | Release: 2008
Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes, 2nd Edition
by Ben Forta
Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes offers straightforward, practical answers when you need fast results. By working through the book's 30 lessons of 10 minutes or less, you'll learn what you need to know to take advantage of Microsoft SQL Server's T-SQL language.This handy pocket guide starts with ...
Price: $17.59 | Publisher: SAMS Publishing | Release: 2017