Transact SQL – Atomicity and In-Memory Tables

 

Background

In this post, we touch on a couple of errors that one might encounter when trying to access In-Memory tables within a Transaction.

Error

Error Number Error Description
 Msg 41333 The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.
 Msg 41332 Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

 

 

Code

Here is how to reproduce the errors listed above.

Set Isolation Level

Set Isolation Level – Serializable

Code


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

begin tran

	select top 10 tblE.*

	from   [dbo].[oe_emp] tblE

rollback tran

 

Output – Textual


Msg 41333, Level 16, State 1, Line 20
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.


Output – Image

msg41333-setisolationlevelserializable

 

Explanation

  1. The error message states that the “The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.
  2. It seems that we have to be in Snapshot isolation when we access Memory Optimized Tables

 

Set Isolation Level

Set Isolation Level – Snapshot

Code

 


set nocount on;
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

begin tran

	begin try

		select top 10 tblE.*

		from   [dbo].[oe_emp] tblE

		print 'Successful '

	end try

	begin catch

		print 'Exception'
		print '========='
		print 'ErrorNumber    :- ' + cast(ERROR_NUMBER() as varchar(600))
		print 'ErrorState     :- ' + cast(ERROR_STATE() as varchar(600))
		print 'ErrorProcedure :- ' + cast(isNull(ERROR_PROCEDURE(), -1) as varchar(600))
		print 'ErrorLine      :- ' + cast(isNull(ERROR_LINE(), -1) as varchar(600))
		print 'ErrorMessage   :- ' + cast(ERROR_MESSAGE() as varchar(600))

		
	end catch

rollback tran

 


Exception
=========
ErrorNumber    :- 41332
ErrorState     :- 0
ErrorProcedure :- -1
ErrorLine      :- 10
ErrorMessage   :- Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.


Output – Image

msg41333-setisolationlevelsnapshot

 

Explanation

  1. So even we set our Transactions Isolation Level to SNAPSHOT, we are still getting an error
  2. Thankfully, a more specific error that states that “Memory Optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

 

 

Remediation

Query

Add Table Hint SNAPSHOT To In-Memory Table Reference

Objective

We added SNAPSHOT as a table hint when referencing In-Memory Tables

Code



set nocount on;

/*
	Add Table Hint, SNAPSHOT, to In-Memory Table References
*/
begin tran

	begin try

		
		select top 10 tblE.*

		from   [dbo].[oe_emp] tblE with (SNAPSHOT)

		print 'Successful '

	end try

	begin catch

		print 'Exception'
		print '========='
		print 'ErrorNumber    :- ' + cast(ERROR_NUMBER() as varchar(600))
		print 'ErrorState     :- ' + cast(ERROR_STATE() as varchar(600))
		print 'ErrorProcedure :- ' + cast(isNull(ERROR_PROCEDURE(), -1) as varchar(600))
		print 'ErrorLine      :- ' + cast(isNull(ERROR_LINE(), -1) as varchar(600))
		print 'ErrorMessage   :- ' + cast(ERROR_MESSAGE() as varchar(600))

		
	end catch

rollback tran


Explanation

  1. Add Table Hint (SNAPSHOT) to all In-Memory Table References

 

 

Add Table Hints SNAPSHOT To all In-Memory Table Reference

Objective

We added SNAPSHOT as a table hint to all our In-Memory Table

Code


set nocount on;

/*
	Add Table Hint to all In-Memory Table Object References with (SNAPSHOT)
*/
begin tran

	begin try

		
		select top 10 tblOEE.*

		from   [dbo].[oe_emp] tblOEE with (SNAPSHOT)

		inner join [dbo].[oe_dep] tblOED with (SNAPSHOT)

			on tblOEE.[oe_emp_ssn] = tblOED.[oe_dep_emp_ssn]

		print 'Successful '

	end try

	begin catch

		print 'Exception'
		print '========='
		print 'ErrorNumber    :- ' + cast(ERROR_NUMBER() as varchar(600))
		print 'ErrorState     :- ' + cast(ERROR_STATE() as varchar(600))
		print 'ErrorProcedure :- ' + cast(isNull(ERROR_PROCEDURE(), -1) as varchar(600))
		print 'ErrorLine      :- ' + cast(isNull(ERROR_LINE(), -1) as varchar(600))
		print 'ErrorMessage   :- ' + cast(ERROR_MESSAGE() as varchar(600))

		
	end catch

rollback tran



Explanation

  1. Add Table Hint (SNAPSHOT) to all In-Memory Table References
    • We added to oe_emp and oe_dep

 

Database Settings

Set Database Option “MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT” ON


set nocount on;
set XACT_ABORT on;

/*
	For our current database:
		Set Memory Optimized Elevate to Snapshot ON  
*/


ALTER DATABASE CURRENT 
	SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
go

begin tran

	begin try

		
		select top 10 tblOEE.*

		from   [dbo].[oe_emp] tblOEE --with (SNAPSHOT)

		inner join [dbo].[oe_dep] tblOED --with (SNAPSHOT)

			on tblOEE.[oe_emp_ssn] = tblOED.[oe_dep_emp_ssn]

		print 'Successful '

	end try

	begin catch

		print 'Exception'
		print '========='
		print 'ErrorNumber    :- ' + cast(ERROR_NUMBER() as varchar(600))
		print 'ErrorState     :- ' + cast(ERROR_STATE() as varchar(600))
		print 'ErrorProcedure :- ' + cast(isNull(ERROR_PROCEDURE(), -1) as varchar(600))
		print 'ErrorLine      :- ' + cast(isNull(ERROR_LINE(), -1) as varchar(600))
		print 'ErrorMessage   :- ' + cast(ERROR_MESSAGE() as varchar(600))

		
	end catch

rollback tran
go

/*
	Revert Database Setting
*/
ALTER DATABASE CURRENT 
	SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF  
go

Programmable Objects

Stored Procedure

In a follow-up post, we discuss how to effect the same covering when creating Stored Procedures.

 

Summary

In summary, to avoid the aforementioned errors, we can utilize Table Hints, specifically with (SNAPSHOT), on each query.

Or declaratively set the Database Option, MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, on each In-Memory Database.

 

References

  1. Database Features > In-Memory OLTP (In-Memory Optimization) > Memory-Optimized Tables
    Link
  2. Database Features > In-Memory OLTP (In-Memory Optimization) > Memory-Optimized Tables > Transactions with Memory-Optimized Tables
    Link

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s