Managing SQL Server Database Concurrency
This course will dig into topics such as deadlocks, isolation levels, and more when managing SQL Server Database.
What you'll learn
Are you sometimes unsure about what type of database transaction or what isolation level makes sense for your application? Have you ever run into blocking problems or the dreaded deadlock? In this course, Managing SQL Server Database Concurrency, you will learn foundational knowledge of how SQL Server maximises the shared use of a database system while safeguarding the integrity of the data under its care and quickly delivering results to all applications that use it. First, you will learn how transactions are used and the various levels of isolation available. Next, you will discover how SQL Server uses locking to enable concurrency without affecting data integrity. Finally, you will explore how to analyze concurrency-affecting queries, including those that deadlock, and some techniques you can use to keep to avoid such issues. When you are finished with this course, you will have the skills and knowledge of concurrency management needed to explain database behavior, debug and fix blocking and deadlocking queries, and design queries that can do their jobs efficiently while minimizing their effects on other work being done simultaneously.
Table of contents
- Introducing Locking 3m
- Demo: Blocking in Action 3m
- Demo: Understanding Lock Granularity and Hierarchy 2m
- Reviewing SQL Server Locking Granularities 3m
- Introducing Locking Modes 4m
- Demo: Locking Modes in Action 1m
- Introducing Lock Escalation 2m
- Demo: Lock Escalation in Action 3m
- Module Summary and Additional Resources 1m
- Introducing Concurrency Optimization 3m
- Demo: Understanding Locking and Blocking 1m
- Common Lock Mode Compatibility 1m
- Demo: Lock Incompatibility and the Consequences 3m
- Introducing Deadlock Analysis Using SSMS 1m
- Demo: Analyzing Deadlocks Using Extended Events 6m
- Handling and Avoiding Deadlocks 2m
- Controlling Deadlocks with DEADLOCK_PRIORITY 1m
- Demo: Frameworks for Avoiding and Handling Deadlocks 3m
- Recapping Concurrency Optimization and Deadlock Handling 1m
About the author
Gerald Britton is a Pluralsight author and expert on Python programming practices and Microsoft SQL Server development and administration. A multiple-year of the Microsoft MVP award, Gerald has led introductory classes in Python and SQL for industry-sponsored events at Ryerson University, Toronto and the University of Toronto (his alma mater).