Azure SQL Database: Non-blocking transactions | Azure Friday

Managing concurrent access to a resource, be it a row, a table, or a single value, is easy if no one is trying to modify the underlying data. But what happens if someone changes data while someone else is reading it? Even better, what could happen if two or more users try to change the same data at the same time? Well, there are a couple of options. The easiest is to just ask everyone to queue up, the other is to have multiple versions of the data available over time so that no one must wait for the changes to be finished. Let's explore both the options with Lara Rubbelke and Davide Mauri on Azure Friday.
Chapters
00:00 - Introduction
01:50 - DEMO: Locking behavior
03:20 - DEMO: Default "read committed snapshot" behavior
05:20 - Migrating from SQL Server to Azure SQL DB
06:42 - DEMO: snapshot isolation level
12:12 - Wrap-up
Recommended resources
■ Transaction locking and row versioning guide - aka.ms/azfr/726/01
■ SET TRANSACTION ISOLATION LEVEL (Transact-SQL) - aka.ms/azfr/726/02
■ Azure SQL Database - aka.ms/azfr/726/03
■ Create a Pay-as-You-Go account (Azure) - aka.ms/azfr/726/payg
■ Create a free account (Azure) - aka.ms/azfr/726/free
Connect with us
■ Lara Rubbelke | @SQLGal - / sqlgal
■ Davide Mauri | @MauriDB - / mauridb
■ Azure Friday | @AzureFriday - / azurefriday
#MicrosoftAzure #AzureFriday #AzureSQL

[@danielegiovanetti9258]

Davide you are fantastic! Thanks Azure Friday for sharing these precious info

[@cvetkovicslobodan]

Great presentation Davide!
What about if the second transaction is also updating data, and the first one is committed before the second one?
I would assume the second operation fails as data is changed in the meantime (e.g. by comparing sequence numbers at the beginning and at the commit phase)? The solution for a second transaction is probably a retry.

[@timheikell3938]

Davide, you're famous! ;)

[@DavideMauri]

Hey Tim! :)

[@PeriMCS]

I thought that this was the only allowed setting in Azure SQL. How can we turn it off? Just curious.

[@DavideMauri]

alter database set read_committed_snapshot off

[@jacobdreyerlund]

Is this also enabled by default for azure sql managed instance?