In the last blog post, I tried out a simple example to show the difference between the three transaction isolation levels supported by memory-optimized tables. I only used user-transactions in T-SQL, and only showed the behavior of transactions consisting of SELECT statements, when INSERT/UPDATE/DELETE activity was taking place in other sessions running concurrently. But of course, things are not always *that* simple, and there are differences regarding:
1. Transactions that do concurrent INSERT/UPDATE/DELETE in multiple sessions
2. Transactions running in or using compiled stored procedures (which must be defined as atomic and declare their isolation level)
3. Autocommit transactions (i.e. single atomic SQL statements)
Today, I’ll expand the discussion to cover some additional cases.
One thing I did mention in the previous post is WHEN a user transaction fails. The previous post originally mentioned (as a simplification) that transactions with memory-optimized tables “…fail the user transaction at commit time, if there’s a conflict detected. SQL Server’s SNAPSHOT against “traditional” tables fail during user transactions at the statement where the conflict is detected.”
That’s not always true with concurrent *actions* in a user transaction. User transactions can fail at different points in a transactions lifetime, that is:
1. Normal Processing phase – during the lifetime of a transaction
2. Preparation phase – transaction decides whether it can commit or must abort
According to the definition in the whitepaper “High-Performance Concurrency Control Mechanisms for Main-Memory Databases” (sections 2 and 3), there are a total of 3 transaction processing phases:
Tx created
1. Normal Processing
2. Preparation
3. Postprocessing
Tx terminated
The preparation phase concludes with writes to the transaction log, if the transaction commits. The postprocessing phase consists of timestamp fix up, whether the transaction commits or aborts. It’s also good to remember that, in a T-SQL user transaction, the transaction begins with the first statement after “BEGIN TRANSACTION” that touches data, rather than during the T-SQL “BEGIN TRANSACTION” statement.
All of the conditions I looked at last week failed in the preparation phase.
It’s obvious that, if two concurrent INSERTS with the same primary key happen, one of them must fail. If the later insert happens during a transaction, that transaction fails at commit time (in the preparation phase), rather than normal processing time.
begin tran insert dbo.t1 with (snapshot) values(5, ‘Fred’)
— insert row with same primary key in another session and commit other session (insert, commit in other session works)
commit — Msg 41325, Level 16, State 0, Line 66 The current transaction failed to commit due to a serializable validation failure.
However, WRITE conflicts (transaction updates or deletes a row already updated/deleted by another session AFTER the transaction starts) fail at normal processing time. This is what we’re used to with conflict detection in SNAPSHOT isolation level with “ordinary” SQL Server tables. The previous post is rephrased from the simplified original “…fail at commit time” to “…CAN fail at commit time” to indicate that write conflicts fail the way that we’re “used to”.
begin tran
select * from dbo.t2 with (snapshot) — starts the transaction
— update row 2 in another transaction and commit other session
update dbo.t1 with (snapshot) set c2 = ‘Sam’ where c1 = 2
— Msg 41302, Level 16, State 110, Line 120
— The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.
— Msg 3998, Level 16, State 1, Line 120
— Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
commit — transaction already rolled back (above)
begin tran
select * from dbo.t2 with (snapshot) — starts the transaction
— update row 2 in another transaction and commit
delete dbo.t1 with (snapshot) where c1 = 2
— Msg 41302, Level 16, State 111, Line 130
— The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.
— Msg 3998, Level 16, State 1, Line 130
— Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
— The statement has been terminated.
commit — transaction already rolled back (above)
Just to confirm what I mentioned before, the following shows that the transaction starts when the first data is touched, rather than at BEGIN TRANSACTION. If I remove the SELECT from (unrelated) table dbo.t2 and update and commit row 2 in another session before accessing data at all, the transaction is consistent (you’re just updated already updated/committed data after the transaction starts) and the UPDATE succeeds.
begin tran
— update row 2 in another transaction and commit
update dbo.t1 with (snapshot) set c2 = ‘Sam’ where c1 = 2 — tx starts here, 2 is already updated/commited to new value
commit — OK
In traditional SNAPSHOT isolation, 2 INSERTers with the same primary key or 2 UPDATE/DELETEers when both sessions are in transactions cause the transaction with the last action to wait, but with multi-version memory-optimized tables, we don’t want to wait, and rather than wait transactions can take dependencies on other transactions, continuing but assuming the transactions “before” them will commit. These dependencies are resolved by waiting, if needed, during the preparation phase, rather than doing normal processing. A best practice with memory-optimized tables is to keep transactions short, to minimize waits for transaction dependencies. Refer to the whitepaper for a description of conditions that can cause transaction dependencies.
There are places where reading or writing inside a transaction can cause other sessions to wait, however. In repeatable read isolation level and above, but not in snapshot isolation level, a SELECT statement on a memory-optimized table WITH A T-SQL STATEMENT within a T-SQL user transaction will cause another session attempting to DROP the table to wait.
begin tran
select * from dbo.t1 with (snapshot)
— drop table t1 in the other session, it works
select * from dbo.t1 with (snapshot) — invalid object name dbo.t1
commit — OK
begin tran
select * from dbo.t1 with (repeatableread)
— drop table t1 in the other session, this waits
select * from dbo.t1 with (repeatableread) — OK
commit — OK, (and drop works after this tx committed)
However, reads from within a repeatable read isolation level COMPILED PROCEDURE and writes (INSERT/UPDATE/DELETE) from within a COMPILED PROCEDURE at any isolation level allow a DROP TABLE from another session statement to work, and cause a validation error at preparation time. Writes in a T-SQL statement at any isolation level cause the same validation error.
begin tran
insert into dbo.t1 with (snapshot) values(5, ‘Fred’)
— drop table t1 in the other session – the drop statement hangs
commit — OK, (and drop works after this tx committed)
go
create procedure dbo.insert_t1(@c1 int, @c2 varchar(10))
with native_compilation, schemabinding, execute as owner
as begin atomic with (transaction isolation level=snapshot, language=N’us_english’)
insert into dbo.t1 values(@c1, @c2)
end
go
begin tran
execute dbo.insert_t1 5, ‘Fred’
— drop procedure dbo.insert_t1 and dbo.insert_t1 and table t1 in the other session (this works without hanging/waiting)
—
— note that we must drop the procedure (and any other procedures that reference table t1)
— first, because compiled procedures are schemabound.
— attempting to drop the table without dropping the procedure would fail with a schemabinding error.
commit — Msg 41305, Level 16, State 0, Line 211 The current transaction failed to commit due to a repeatable read validation failure.
So we’ve succeeded in muddying the waters a bit by illustrating that:
1. With memory-optimized tables, transactions can fail during normal procession as well as the preparation phase
2. Transactional behavior can cause different behavior in other sessions depending on whether we use a compiled procedure or use a T-SQL statement.
With memory-optimized tables, these are not your mother’s/father’s traditional transactions. AND…we haven’t covered all the differences and edge-cases yet, either.
Cheers, Bob