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!