Lock, Block and Two Smoking Barrels

2016

TL; DR

Enter the world of SQL Server concurrency to experience the age old problem of Locking, Blocking and Deadlocking. Let's investigate pessimistic isolation and whether optimistic concurrency could be the answer to all our problems or the last resort.

Session Details

SQL Server is a high performance relational engine and provides a highly scalable database platform but due to its complexity (and bad programming practices) can be prone to serious concurrency problems, unexpected behaviors, lost updates and much more! In SQL Server 2005, two optimistic concurrency mechanisms were introduced and touted as the solution to all our problems. Now in SQL Server 2012 and 2014 even more have followed, but many challenges and problems still remain.

Let’s take a long look into the world of SQL Server concurrency and investigate Pessimistic and Optimistic isolation understanding how they work, when you should use them, and more importantly when they can go very wrong. Don't be staring down the wrong end of SQL Server's two Smoking Barrels and join me for this revealing and thought provoking presentation.

3 things you'll get out of this session

Mark Broadbent's previous sessions

2 Fast 2 Furious - designing for speed, concurrency, and correctness (20 min edition)

Learn how to design for speed, concurrency, and correctness and avoid common database development pitfalls.

Introduction to continuous integration and delivery with Azure DevOps (20 min edition)

Learn how to use Azure DevOps for your development and CICD processes

SQL Server Always On for Dummies

In this session, we will explain how to plan for, deploy, and manage Availability Groups from the ground up in easy to understand examples and demos.

Persistence is Futile-Implementing Delayed Durability

In this session, we will investigate how SQL Server implements transactional durability in order to understand how Delayed Durability bends the rules to remove transactional bottlenecks and achieve improved throughput.

Ye Olde Cluster Curiosity Shoppe-Clustering Tips & Tricks

Welcome to the strange and mysterious world of SQL Server Failover Clustering and enter Ye Olde Cluster Curiosity Shoppe where we will reveal a miscellany of top tips, tricks and advice gained from nearly two decades of installing and troubleshooting

Moves Like Jagger: Upgrading to SQL Server 2012

Upgrading SQL Server is one of the most challenging and risky operations that you will perform as a DBA. Make the wrong move, and you could be heading for the exit. In this diverse session, we'll take an in-depth look at moving to SQL Server 2012.

Enter the Dragon: SQL 2012 on Server Core

In this session we shall demonstrate configuration, installation and administration of SQL Server 2012 on Server 2008R2 Core. We shall discuss Command line and PowerShell configuration, Installation of SQL, Remote Administration and Clustering.

SQL Server Clustering for Dummies

Are you uncomfortable with the idea of SQL Server Clustering? Do you currently use it and are scared to go near your Clustered Instances and their physical hosts? Stop worrying and learn the "Dark Art" of SQL Server Cluster administration.

READPAST & Furious: Transactions, Locking and Isolation

In this session we will investigate SQL Server concurrency and reveal some very surprising and thought provoking behaviors.

Thinking outside the Box. Learning a little about a lot

Generalize your skills so you can specialize on SQL Server.