Problem
A friend recently asked me why you can’t use a warehouse or lakehouse to replace an operational database like SQL Server or Azure SQL Database. So, I feel compelled to state that a Microsoft Fabric data warehouse does not handle concurrency like SQL Server or Azure SQL Database. Some people might read this statement and say, “Well, duh,” while others may ask, “What do you mean? Tell me more.” Keep reading, because you might learn something no matter which group you fall into.
Solution
I will demonstrate why you cannot successfully use a Fabric warehouse to handle even a set of low-concurrency transactions that affect the same table. First, we’ll define concurrency and its application to a relational database management system like SQL Server. Then, we’ll focus on how concurrency functions in a warehouse and, for the most part, in a lakehouse. The goal is to understand how concurrency works in a warehouse, why you can’t replace your existing operational database with one, or at least why it would be a bad idea.
Why Concurrency Matters
High concurrency matters when multiple connections execute INSERT, UPDATE, DELETE, and SELECT statements simultaneously on the same rows and tables. To maintain order, SQL Server enforces locks, as determined by the isolation level, to prevent connections from colliding. Isolation levels, or more precisely, locks, function like traffic lights, ensuring that vehicles do not crash into one another at intersections. SQL Server offers four standard and two special isolation levels; however, we will focus on just a couple.
Default Isolation Level
By default, SQL Server enables Read Committed isolation (RCI), where writers block readers. Microsoft describes RCI as a pessimistic isolation level since it only reads committed data. For an Azure SQL Database, a setting known as Read Committed Snapshot isolation (RCSI) is enabled by default. You can also enable RCSI on SQL Server. When RCSI is enabled, writers do not block readers, which is more optimistic. This can be beneficial or detrimental depending on the workload.
Snapshot Isolation
Like RCSI, SQL Server provides Snapshot isolation (SI), which uses row versioning. One key difference is that SI ensures transaction-level read consistency, whereas RCSI offers it at the statement level. For example, let’s take a query with two select statements. With RCSI, the second statement might read different data if another connection has already updated it. In contrast, with SI, you read the data from the start of the transaction. Picture SQL Server taking a snapshot of the data when you start the transaction, hence the name Snapshot isolation.
Concurrency with SQL Server
I mention SI rather than the other five because it’s the isolation level used in a Fabric warehouse. Before focusing on the warehouse, let’s review a simple example of how SI works in SQL Server, specifically for selecting and updating data.
I’m using SQL Server 2022 Developer Edition and SQL Server Management Studio 21 preview 6 for the demo.
Create Demo Tables
The following code creates a database with two tables. For SI to work, we must turn it on with SET ALLOW_SNAPHSHOT_ISOLATION ON.
/*
* MSSQLTips.com
* Create two tables with three rows.
*/
USE [master];
GO
IF DB_ID('ConcurrencyDemo') IS NOT NULL
BEGIN
ALTER DATABASE ConcurrencyDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ConcurrencyDemo;
END;
GO
CREATE DATABASE ConcurrencyDemo;
GO
ALTER DATABASE ConcurrencyDemo SET RECOVERY SIMPLE;
GO
/*
* This command allows the database to use snapshot isolation
*/
ALTER DATABASE ConcurrencyDemo SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
USE ConcurrencyDemo;
GO
CREATE TABLE dbo.Table1
(
Column1 INT,
Column2 VARCHAR(10)
);
GO
CREATE TABLE dbo.Table2
(
Column1 INT,
Column2 VARCHAR(10)
);
GO
INSERT INTO dbo.Table1
(
Column1,
Column2
)
VALUES
(1, 'red'),
(2, 'red'),
(3, 'red');
GO
INSERT INTO dbo.Table2
(
Column1,
Column2
)
VALUES
(1, 'green'),
(2, 'green'),
(3, 'green');
GOWriters and Readers
Imagine we have two connections, A and B. The screenshot below illustrates how to arrange the windows in SSMS for a clearer view.
In Connection A, I’m performing an update; however, I have not yet committed the transaction.
/*
* MSSQLTips.com
* Connection A: Don't commit the transaction
*/
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
UPDATE dbo.Table1
SET Column2 = 'pink'
WHERE Column1 = 1;When I select the same row from Connection B, am I blocked?
/*
* MSSQLTips.com
* Connection B: We will select the same row as Connection A
*/
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
SELECT Column1,
Column2
FROM dbo.Table1
WHERE Column1 = 1;
GOResults:
Column1 Column2
-------- ----------
1 redThe answer is no because in SI, writers don’t block readers. The data returned in Connection B reflects what was committed at the start of the transaction. Even if I run the SELECT in Connection B multiple times after the first connection commits, SQL Server returns the original snapshot. Now let’s roll back any open transactions in both connections.
ROLLBACK TRANSACTION;Updating the Same Row
How about updating the same row in both connections? Once again, let’s run the update in Connection A without committing the transaction.
/*
* MSSQLTips.com
* Connection A: Don't commit the transaction yet
*/
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
UPDATE dbo.Table1
SET Column2 = 'pink'
WHERE Column1 = 1;Then, in Connection B, we’ll update the same row and commit the transaction.
/*
* MSSQLTips.com
* Connection B: Commit the transaction
*/
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
UPDATE dbo.Table1
SET Column2 = 'black'
WHERE Column1 = 1;
COMMIT TRANSACTION;At this point, SQL blocks Connection B.
Next, I’ll commit Connection A, and SQL returns the error message below in Connection B:
Msg 3960, Level 16, State 6, Line 23
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.Table1’ directly or indirectly in database ‘ConcurrencyDemo’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
For SQL Server, the first transaction to start wins the commit. Paul White refers to this as a write snapshot conflict. Let’s remember this point for later.
Updating a Different Row
If I attempted to update a different row in Connection B, our transaction would have been committed because SI uses row versioning instead of table versioning.
Concurrency in a Fabric Warehouse
By default, a warehouse in Fabric uses Snapshot isolation (SI), and even if you try, you cannot change the isolation level; it’s ignored. Perhaps we’ll have that ability in the future. However, a major difference exists between SQL Server and Fabric’s definition of SI. Allow me to show you using an example.
Create Demo Tables
For this demo, I created a warehouse named MSSQLTips_DW. As in our first example, we will use the script below to create two tables with three rows.
/*
* MSQLTips.com
*/
USE [MSSQLTips_DW];
GO
DROP TABLE IF EXISTS dbo.Table1;
DROP TABLE IF EXISTS dbo.Table2;
GO
CREATE TABLE dbo.Table1
(
Column1 INT,
Column2 VARCHAR(10)
);
GO
CREATE TABLE dbo.Table2
(
Column1 INT,
Column2 VARCHAR(10)
);
GO
INSERT INTO dbo.Table1
(
Column1,
Column2
)
VALUES
(1, 'red'),
(2, 'red'),
(3, 'red');
GO
INSERT INTO dbo.Table2
(
Column1,
Column2
)
VALUES
(1, 'green'),
(2, 'green'),
(3, 'green');
GOWriters and Readers
Next, I’ll execute the update statement in Connection A without committing it.
/*
* MSSQLTips.com
* Connection A: Update Column2 without a commit
*/
USE [MSSQLTips_DW];
GO
BEGIN TRANSACTION;
UPDATE dbo.Table1
SET Column2 = 'pink'
WHERE Column1 = 1;Over in Connection B, I’ll execute the select statement with an explicit transaction.
/*
* MSSQLTips.com
* Connection B: Select the same row as Connection A
*/
USE [MSSQLTips_DW];
GO
BEGIN TRANSACTION;
SELECT Column1,
Column2
FROM dbo.Table1
WHERE Column1 = 1;
COMMIT TRANSACTION;Results:
Column1 Column2
-------- --------
1 redIn Connection A, commit the transaction to keep things flowing.
/*
* MSSQLTips.com
* Connection A: Commit the transaction
/*
COMMIT TRANSACTION;In this example, Connection B returned the original row value of red for Column2. So far, the warehouse behaves like SQL Server when reading data—writers don’t block readers.
Updating a Different Row
Now, let’s conduct a second experiment in which Connection B updates a row different from Connection A. For SQL Server, there was no blocking due to row versioning.
We will begin by running the code below in Connection A.
/*
* MSSQLTips.com
* Connection A: Update Column2 without a commit
*/
BEGIN TRANSACTION;
UPDATE dbo.Table1
SET Column2 = 'pink'
WHERE Column1 = 1;For Connection B, run the code below, including the commit.
/*
* MSSQLTips.com
* Connection B: Update a different row than Connection A
*/
BEGIN TRANSACTION;
UPDATE dbo.Table1
SET Column2 = 'yellow'
WHERE Column1 = 2;
COMMIT TRANSACTION;That behavior differed from SQL Server because we committed Connection B without being blocked by Connection A. The first notable difference between SQL Server and a warehouse is that the transaction that commits first wins. Now, let’s commit Connection A.
/*
* MSSQLTips.com
* Connection A: commit the transaction
*/
COMMIT TRANSACTION;Results:
Msg 24556, Level 16, State 2, Line 55
Snapshot isolation transaction aborted due to update conflict. Using snapshot isolation to access table ‘Table1’ directly or indirectly in database ‘MSSQLTips_DW’ can cause update conflicts if rows in that table have been deleted or updated by another concurrent transaction. Retry the transaction.
The SQL engine returns the error message above, indicating an update conflict; this is the primary difference between the two platforms I wanted to highlight.
The Big Difference
Versioning appears to happen at the warehouse’s table or file level, likely due to the Delta Parquet files, rather than at the row level. Perhaps Microsoft should refer to it as a table-level Snapshot instead of just Snapshot. This is only one reason why a Fabric warehouse does not handle concurrency as robustly as an OLTP database, such as SQL Server or Postgres, and why we shouldn’t replace an operational database with one. However, I never expected a warehouse to handle high concurrency like SQL Server can.
Summary
Fabric represents the latest and greatest innovation in Microsoft’s data analytics world, and I enjoy working with lakehouses and warehouses. Although the data enthusiast who inspired this article is from the business side, I’ve noticed several posts where data experts discuss importing AdventureWorks or WideWorldImporters into a lakehouse or warehouse.
I’m not the data police; you can do whatever you want. However, if I import AdventureWorks into Excel, break each table into separate worksheets, and write an article or record a video about it without stating that using Excel as an operational database is a terrible idea, it might imply that I’m suggesting you can use Excel as such. Misusing a tool sends the wrong message to the general population, who only want to understand these new technologies. On the upside, these events inspired me to write an article about it.
Next Steps
- If you haven’t checked it out yet, Koen Verbeeck has an article titled “2025 Microsoft Fabric Community Conference Announcements” covering all the latest updates from FabCon. Additionally, Koen has a wealth of articles on various Fabric topics.
- While writing this article, I came across a piece by Andy Setter titled “Transactions and Isolation Levels in Fabric Warehouse.” In it, he outlines how transactions behave in a Fabric warehouse and offers a handy script for monitoring locks using a custom function.
- To stay updated with all the latest Fabric news, consider following the Fabric Community. You can check out the newest blog posts and forums for each workload.
