Understanding Database Locking and Blocking: 100% Demo - Part 5: Optimistic Concurrency


Length: 1 hour

Concurrency is a term used to mean “how a database server grants locks.” SQL Server offers two broad models of concurrency. SQL Server’s default is known as pessimistic concurrency, which assumes user transactions will often conflict with each other such that SQL Server must exclusively lock records whose data is being modified. In this case, users writing or changing data block others also trying to write or change data but allows the data to be read. Pessimistic concurrency ensures more thorough isolation for transactions to complete, but it may also provoke more blocks and deadlocks, more transaction log growth, and longer transaction wait times.

Alternatively, SQL Server also supports optimistic concurrency. In this case, SQL Server allows multiple users to both read and write data simultaneously. Optimistic concurrency reduces blocking and deadlocking and can speed up transaction processing. It provides these benefits by row versioning, which consumes a lot of extra space in tempdb and the support of additional background processes.

Join your host, Kevin Kline, and noted Microsoft MVP Torsten Strauss for this 100% demo session on optimistic concurrency. In this session you’ll see:

  • Examples of how to enable and scope optimistic concurrency
  • Inner workings of how SQL Server supports optimistic concurrency
  • Valuable examples of the DBCC PAGE command in action
  • Tips and tricks for ensuring top performance when using optimistic concurrency
  • And much more…

Whether you're a developer or DBA, this webinar helps to remove the mystery behind why some applications are plagued by poor concurrency. Learn the important benefits and restrictions of optimistic concurrency as you continue to advance your skills and level up as a SQL Server or Azure SQL technologist. Join us!

Interested in additional SentryOne webinars? Check our webinar library.

Watch now

Kevin Kline

Kevin Kline

Principal Program Manager, SentryOne

Kevin (@kekline) serves as Principal Program Manager at SentryOne. He is a founder and former president of PASS and the author of popular IT books like SQL in a Nutshell. Kevin is a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at blogs.sentryone.com/author/kevinkline. As a noted leader in the SQL Server community, Kevin blogs about Microsoft Data Platform features and best practices, SQL Server trends, and professional development for data professionals.

Torsten Strauß

Torsten Strauß

Microsoft Most Valuable Professional and Data Platform Senior Consultant
Torsten Strauß (MVP, MCSE Data Platform, MCSE Data Management and Analytics, MCT) has been working with Microsoft SQL Server since the turn of the millennium. He currently works as Database Engineer and Microsoft SQL Server Evangelist for BP Europe SE and as Principal Consultant for Sarpedon Quality Lab. Torsten has delivered more than 100 presentations about Microsoft SQL Server at national and international conferences and user groups in recent years.

Companies with the highest demands for database performance choose SentryOne