When I arrived in this land made of the finest fabric that Microsoft could find, I had a LOT of assumptions. I assumed that some things would work the same, and these were errors I bumped into regularly. I have written on creating tables, identity and sequences, and metadata and temp tables. None of these did I expect to be different. Those entries were truly about things that you will bump into, and if you don’t read something from someone about this stuff, you will stumble upon these problems like a Lego in the carpet at 2 am…it will hurt, it will change your path and demeanor, but there won’t be any real lasting effect.

Ironically, today’s topic is kind of the opposite. I expected things to be far different in Fabric, but it isn’t really that different, at least not in behavior, except when it is. Since this system is Parquet file based, I didn’t think there would be locking, blocking, etc. I sort of expected it would be sort of locked down when writing data, maybe just single threaded per file, but highly concurrent when reading. Reads would most likely work like time travel and read previous data where it existed. And transactions? Would there be transactions? I guessed not before I got started with it.

Was I wrong? Turns out I was. Things are not the same exactly, but they are closer than I expected to broad ACID compliance than I expected. Microsoft Fabric supports ACID compliance as noted in this post, but that support does not imply how it is supported, only that it is supported in some fashion.

“Fabric Data Warehouse supports ACID-compliant transactions. Each transaction is atomic, consistent, isolated, and durable (ACID). All operations within a single transaction are treated atomically, all succeeding or all failing. If any statement in the transaction fails, the entire transaction is rolled back.”

This is great news of course, because if a process is half complete and it isn’t properly idempotent, you can be in a terrible mess.

I talked about Consistency in part 1, and with no constraint checking, this aspect is limited in implementation. Most of the consistency it checks is nullability and datatypes. Fine, this is a reporting oriented platform, and checking constraints is probably better done in bulk, even if it means work for us. (A perfect version of this would be to give us constraints that were verified on demand instead of during modifications. I am working on a shareable version of that, but it will be a while yet before I get that all straight.

Durability is built in, when you store data, you can be assured it will be there if the equipment it was stored on still is. And if not, this is reporting data, make sure you have your history data somewhere safe and you can rebuild.

But Atomicity and Isolation are interesting in how they work for you.

In this blog I will cover transactions and isolation when modifying/creating data in a table. Later I will look at schema locks

Atomicity

Like other entries in this series, I don’t know and (for all practical purposes), I don’t care how things are implemented internally. Just like in a typical relational engine, we start to care when we are tuning queries to work with an engine. The first step is to know what is possible.

One thing to note, I do every one of my examples using explicit transactions (BEGIN TRANSACTION and COMMIT/ROLLBACK TRANSACTION). All statement are executed in a transaction, and if you don’t explicitly start a transaction, one starts when your statement starts, and is automatically committed when it is done. This allows your transaction to be undone if any error, no matter what the error is.

Objects

In SQL Server, most everything can be added to an atomic process (a transaction, which is what I will call it like anyone else). What about a Fabric Data Warehouse?

BEGIN TRANSACTION;
EXEC ('CREATE SCHEMA LouisTest;'); --has to be in its own batch
SELECT *
FROM   sys.schemas
WHERE  name = 'LouisTest';
ROLLBACK TRANSACTION;
SELECT *
FROM   sys.schemas
WHERE  name = 'LouisTest';

This returns:

The first time I tried that, I definitely didn’t expect it, but just like SQL Server’s T-SQL, you can do almost everything within a transaction. There are however, a few things you can’t do:

• Savepoints
• Named transaction
• Distributed Transactions

Which do make sense as the first two would rely on the SQL Server transaction log, and Distributed Transactions outside a Workspace would also be a lot more complicated than those within.

Looking at the list in documentation, you can rollback any command that I would expect), even one that frequently is bandied around the internet. TRUNCATE TABLE.

CREATE TABLE TestTruncate
(
    Value INT NOT NULL
);
INSERT INTO TestTruncate
SELECT Value
FROM   GENERATE_SERIES(1,100000);
GO
SELECT COUNT(*) AS Row_Count
FROM  TestTruncate; 

Then you can see the rollback here:

BEGIN TRANSACTION;
TRUNCATE TABLE TestTruncate;
SELECT COUNT(*) AS Row_Count
FROM  TestTruncate;
ROLLBACK;
SELECT COUNT(*) AS Row_Count
FROM  TestTruncate; 

And then:

TRUNCATE TABLE is something that seems to work pretty much the same as in SQL Server with one caveat (that I don’t completely understand yet!) From the link earlier in this paragraph:

In Fabric SQL database, truncating a table deletes all mirrored data from Fabric OneLake for that table.

One major caveat, in the documentation about fabric and transactions, it is stated as a list the things that can be done in a transaction, not the things that cannot. This means there are things that may not work (and some that may. For example, CREATE SCHEMA or CREATE PROCEDURE were not listed. But I showed the schema worked, and:

BEGIN TRANSACTION;
GO
CREATE PROCEDURE Test
AS
   BEGIN
    SELECT 'Go Vols!' as [Football Time In TN];
   END;
GO
EXECUTE Test;
GO
ROLLBACK TRANSACTION;
GO
EXECUTE Test; 

This returns:

Followed by:

Msg 2812, Level 16, State 62, Line 110
Could not find stored procedure 'Test'.

Very useful, so just like in SQL Server, be sure to use transactions everywhere you are modifying data that you might not want to lose (especially when dropping an object, as I will show in the next entry in this series.)

Isolation

Isolation is an interesting topic because it could mean to force single threaded activity to keep other people out while you are using some data, or it could be multi-user with some form of multi-version concurrency control based (MVCC) or it could be basic locks. Each behave differently for the user, but do the same thing. One user’s results should never be alterable by another connection’s action in any way that it has not allowed.

So if one connection is updating all the rows in a table, a query that starts before, during, or after that process should not get a different result than they would have when they started, unless they have allowed it. The default isolation level for most RDBMS’ READ COMMITTED, does allow this to occur, though whether it can occur is based on how it is all implemented.

This is a really light coverage of concurrency, but hopefully enough to set the stage on what I am going to try out. The first lesson is that you have to be relatively careful about just trying things if they matter.

Can you set your Isolation Level?

As a SQL Server programmer, you “know” that the default isolation level is READ COMMITTED, or you just use it with this isolation level, most of the blissfully uncaring of what that really even means. It is good enough. But once in a while, you need to one of the other isolation levels to protect some important data.

So you use the SET TRANSACTION ISOLATION LEVEL command to set it. And in Fabric, these all seem to work.

SET TRAN ISOLATION LEVEL READ COMMITTED;
SET TRAN ISOLATION LEVEL REPEATABLE READ;
SET TRAN ISOLATION LEVEL SERIALIZABLE;
SET TRAN ISOLATION LEVEL SNAPSHOT;

Good deal, they must work as in SSMS you see this cheerful message Commands completed successfully.

Well, not so fast:

--Set isolation level back to what we thought
--was the default.
SET TRAN ISOLATION LEVEL READ COMMITTED;

Then check it:

SELECT 
    session_id,
    CASE transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted (NOLOCK)'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable Read'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END AS isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID; 

This returns:

Wait, what? I said read committed. Well, it turns out that any attempt to change the isolation level is just ignored. From the documentation:

“If you use T-SQL to change your isolation level, the change is ignored at query execution time and snapshot isolation is applied.”

This I really do not like. I am quite with SNAPSHOT being the only alternative. That’s fine. But what is this “ignoring” business? Quite annoying and concerning as a user.

What we do get?

At this point, we have established that we are in SNAPSHOT isolation level, so just by using the definition of SNAPSHOT isolation, we know that each connection should get it’s own steady state of the data. So let’s create a table and some data.

CREATE TABLE TestIsolation
(
    TestIsolationId bigint NOT NULL IDENTITY,
    Value CHAR(100) --just to make sure there is some actual data size involved
);
INSERT INTO TestIsolation
SELECT CAST(REPLICATE(cast(value as varchar(100)),100) as char(100))
FROM   GENERATE_SERIES(1,100000); 

Not a tremendous amount of data, but it will work for this test.

The less deterministic version of IDENTITY is really annoying for demos as I showed back in part 2. Since the value is different, you can’t really make demos return the same values each time. I am using ROW_NUMBER() and GENERATE_SERIES to load test tables for that reason but it works fine here even if the values are so huge!

Effects on readers

As you would expect with SNAPSHOT ISOLATION, writers don’t have an effect on readers (at least not when modifying data with simple INSERT, UPDATE, DELETE. Some stuff to try a bit later.

--Connection A
BEGIN TRANSACTION
UPDATE TestIsolation
SET   Value = 'Changed'

This returns:

(100000 rows affected)

Next I will change to a different connection and execute the following:

--Connection B
SELECT *
FROM   TestIsolation
WHERE  Value = 'Changed'; 

There are no results returned, but it is not blocked. You can still query the data as much as you wish on connection B. For example, execute:

--on connection B
SELECT MIN(TestIsolationId) AS MinId, 
       MAX(TestIsolationId) AS MaxId,
       AVG(LEN(Value)) AS AVG_LEN_VALUE
FROM TestIsolation; 

And it will return this (with different min and max values)

Effects on colliding writers

When two users try to modify the same row, there is always something that must be done. Blocking, or rollbacks, either one. So I will try on Connection B to update one row. (Make sure you have rolled back any connections you have started.)

Note: this example continues from the previous section where Connection A has updated all the rows in the table to 'Changed' in a transaction. No back to regularly scheduled text.

--Connection B
BEGIN TRANSACTION
UPDATE TestIsolation
SET    Value = 'Changed In a Different Way'
WHERE  TestIsolationId = 1765411053929334432; 

Something unexpected occurs… It seems like worked. I know, you probably expected blocking or perhaps that snapshot isolation error. I did to the first few times. Execute the query that does MinId AND MaxId again and you get an indication that the data has changed.

This was not what I was expecting as a SQL Server programmer. So I obviously need to check back here in Connection A, am I still in a transaction?

SELECT @@Trancount; 

The first time I tried this I received this error:

Msg 0, Level 20, State 0, Line 235
The connection is broken and recovery is not possible. The
connection is marked by the server as unrecoverable. No
attempt was made to restore the connection.

I find this error on my screen rather often when I am working on a lot of different windows. It may not be completely related to isolation, but if you are following along and get the same error, I just retried from the start.

I was still in a transaction, but I will start again. Just to be sure I am not still in a transaction, I will execute:

ROLLBACK; --since we were in a 
          --different transaction already 

So I execute an update on all of the rows:

--connection A
BEGIN TRANSACTION;
UPDATE TestIsolation
SET   Value = 'Changed'; 

Then see what is in the table

--Connection A
SELECT MIN(TestIsolationId) AS MinId, 
       MAX(TestIsolationId) AS MaxId,
       AVG(LEN(Value)) AS AVG_LEN_VALUE
FROM TestIsolation; 

The output as you can see now, is that the average length is now 7, which corresponds with the length of 'Changed'.

Connection B, updated in the last section, still sees the data differently:

So this is a block-less type of isolation (SNAPSHOT in some RDBMS implementations tend to use a lock to prevent connections from modifying the same rows. SQL Server, MySQL, and PostgreSQL all do.

But after I commit B.

--Connection B
COMMIT TRANSACTION

And I come back to A, when I try to execute

--Connection A
SELECT *
FROM TestIsolation; 

I get 100000 rows returned, each still saying Value = Changed. So I can see all 100000 of the rows just fine. But when I commit:

COMMIT;

You get the following error you may recognize if you have used SNAPSHOT isolation level in SQL Server, or perhaps when working with In-Memory data structures in SQL Server.

Msg 24556, Level 16, State 2, Line 294
Snapshot isolation transaction aborted due to update conflict.
Using snapshot isolation to access table 'TestIsolation' directly
or indirectly in database 'wh_monitoring' can cause update
conflicts if rows in that table have been deleted or updated
by another concurrent transaction. Retry the transaction.

Not a huge deal, and it is similar to a deadlock in that your transaction is invalidated and you have to do the work again.

What about non-colliding operations?

In the previous section, I was working with connections that were both trying to modify the same rows. But that isn’t the only scenarios you might come across with this data.

Using this query from an earlier section, I will get 2 rows that are definitely not the same:

--Connection A
SELECT MIN(TestIsolationId) AS MinId,
       MAX(TestIsolationId) AS MaxId
FROM TestIsolation;

So first, on connection A I will change one, then the other (the differences between the rows can be complex with the large identity values, but one ends in 234433, the other with 334432…kind of like drawing tickets with only the last digits being difrerent):

--Connection A:
BEGIN TRANSACTION;
UPDATE TestIsolation
SET Value = 'UPDATED BY Connection A'
WHERE TestIsolationId = 1765411053929234433;

and

--Connection A:
BEGIN TRANSACTION;
UPDATE TestIsolation
SET Value = 'UPDATED BY Connection B'
WHERE TestIsolationId = 1765411053929334432;

Then commit one or the other.

--Connection B
COMMIT;

Then the other:

--Connection A
COMMIT;

And you will see something that would not work well for an OLTP type system, but is fine for a reporting system that is being loaded, not typically interacted with a row at a time:

Msg 24556, Level 16, State 2, Line 346
Snapshot isolation transaction aborted due to update conflict.
Using snapshot isolation to access table 'TestIsolation' directly
or indirectly in database 'wh_monitoring' can cause update
conflicts if rows in that table have been deleted or updated
by another concurrent transaction. Retry the transaction.

It turns out any updates to the table while you are in a transaction will cause that error, no matter the row. Now, this isn’t terribly surprising if you have considered how this works in SQL Server. Every row in your table has to be touched, so it is simpler (and faster for the type of work you are doing in a Fabric Data Warehouse), to just handle it as if you modify a row, then you can’t commit it if any other connection has made changes.

What about a delete?

A delete is a write of sorts, and with no indexes, it does have to scan all the rows to see if there is a match. So as expected, you will see the same sort of behavior.

--Connection A:
BEGIN TRANSACTION;
DELETE TestIsolation
WHERE TestIsolationId = --1765411053929234433;

and

--Connection A:
BEGIN TRANSACTION;
UPDATE TestIsolation
SET Value = 'UPDATED BY Connection B'
WHERE TestIsolationId = 1765411053929334432;

Then commit one or the other.

--Connection B
COMMIT; 

Then the other:

--Connection A
COMMIT;

You get the same snapshot error message as noted before. Obviously if you have history to maintain, you can’t avoid updates/deletes, but it is important to realize there is a concurrency cost when you change existing data (either in substance of existence).

What about inserts?

Finally, inserts. Logically, you probably think that this would be an issue (I am 100% admitting I expected it to be,) especially knowing what we know from the previous section, but execute the following statements:

--Connection A
BEGIN TRANSACTION;
INSERT INTO TestIsolation
SELECT CAST(REPLICATE(cast(value as varchar(100)),100) as char(100))
FROM   GENERATE_SERIES(1,10000);

and then:

--Connection B
BEGIN TRANSACTION;
INSERT INTO TestIsolation
SELECT CAST(REPLICATE(cast(value as varchar(100)),100) as char(100))
FROM   GENERATE_SERIES(1,10000);

Then commit one:

--Connection B
COMMIT TRANSACTION;

Then the other:

--Connection A
COMMIT TRANSACION;

Now you can see that this number increases over the original rows you had in the table, which if you are following al:

SELECT COUNT(*)
FROM   TestIsolation;

So multiple simultaneous inserts are not an issue, which is really nice when you are loading a lot of data (and shouldn’t be terribly surprising either, as it is a distributed system, and it doesn’t have any constraints to check to make sure the data matches.

Inserts and Updates/Deletes

What about where you are updating data in one connection, and inserting in another? So on Connection A I will add 10000 rows, and then update all the rows, and delete a few rows:

--Connection A
BEGIN TRANSACTION;
INSERT INTO TestIsolation
SELECT CAST(REPLICATE(CAST(Value AS varchar(100)),100) as char(100))
FROM   GENERATE_SERIES(1,10000);

Then go to Connection B:

--Connection B:
BEGIN TRANSACTION;
UPDATE TestIsolation
SET Value = 'UPDATED BY Connection B';
--And then:
DECLARE @RandomTestIsolationId bigint
SELECT  @RandomTestIsolationId = TestIsolationId
FROM    TestIsolation
ORDER   BY NEWID();
DELETE FROM TestIsolation 
WHERE  TestIsolationId < @RandomTestIsolationId;
--check the rowcount
SELECT COUNT(*)
FROM TestIsolation;

It will always be more than 0 (since the < means at least 1 row will be left from the delete operation). In one test I had 9322 rows remaining. Now you have 2 transactions, one with inserts, the other with some updates and deletes.

--Connection A
COMMIT TRANSACTION;
SELECT COUNT(*)
FROM TestIsolation; 

I still have 129999 rows on Connection A. The one’s I have deleted on Connection B are still in the table. Commit Connection B

--Connection B
COMMIT;
SELECT COUNT(*)
FROM  TestIsolation;

After I commit the rows, and since there was no overlap of accessing data sets, no isolation issues and I got back: 19322. So the rows I deleted are now gone, and if you check on another connection outside of an active transaction, you will see the same number.

Summary

Whew, another long post that covers a topic that will hopefully help you understand that concurrency in Fabric Data Warehouse and how it differs from SQL Server’s T-SQL implementation. It is different than anything else I have used, but for the types of loads that Fabric Data Warehouse is used for, it is great.

The biggest thing to understand is that two transactions changing rows in the same table will cause errors that require you to run an update or delete again, even if you aren’t modifying the same rows.