One in a series of posts about Microsoft’s In-Memory OLTP Functionality

One thing to keep in mind about querying Hekaton tables is that the isolation level you use is quite important. Queries must be run at the snapshot, repeatable read or serializable isolation level. In the previous post, we executed a select query. Let’s add an explicit isolation level.

set transaction isolation level read committed;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

And this query runs just fine. We execute at the read committed level using snapshot isolation.

But change that “read committed” to just about anything else (read uncommitted, repeatable read) and the statement will produce an error.

Msg 10794, Level 16, State 80, Line 3
The transaction isolation level 'READ UNCOMMITTED' is not supported with memory optimized tables.


Msg 41333, Level 16, State 1, Line 3
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 41333, Level 16, State 1, Line 3
Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.


Things also start to break down when you use an explicit transaction (instead of the autocommit transaction above).

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

commit;

Msg 41368, Level 16, State 0, Line 5
Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).


The error message give the solution: use the with (snapshot) table hint.

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee with (snapshot)
order by LastName, FirstName;

commit;

There is a database-level setting to allow omitting the table hint.

alter database Hekaton
set memory_optimized_elevate_to_snapshot = on;

And now the table hint can be left out of the query.

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

commit;

Things get a bit more interesting when queries access both Hekaton tables and disk-based tables. Suppose we have an on-disk Customer table and we want to note which Employees have certain relationships to a given Customer.

create table CustomerEmployeeRelationship
(
	RelationshipId int not null identity(1,1) primary key clustered,
	CustomerId int not null,
	EmployeeId int not null,
	RelationshipTypeId int not null
);

Side note: Trying create a foreign key back to the Employee table won’t work, another limitation of Hekaton tables.

alter table dbo.CustomerEmployeeRelationship
add constraint fk_CustomerEmployeeRelationship_EmployeeId
foreign key (EmployeeId) references dbo.Employee (EmployeeID);


Msg 10794, Level 16, State 10, Line 57
The feature 'FOREIGN KEY' is not supported with memory optimized tables.
Msg 1750, Level 16, State 0, Line 57
Could not create constraint or index. See previous errors.


Let’s find customer-employee relationships of a certain type:

select rel.CustomerId, rel.EmployeeId
from dbo.Employee emp
inner join dbo.CustomerEmployeeRelationship rel
on emp.EmployeeID = rel.EmployeeId
where rel.RelationshipTypeId = 7;

That works fine and well, but some combinations of isolation levels don’t mix and match.

set transaction isolation level repeatable read;

select rel.CustomerId, rel.EmployeeId
from dbo.Employee emp with (repeatableread)
inner join dbo.CustomerEmployeeRelationship rel
on emp.EmployeeID = rel.EmployeeId
where rel.RelationshipTypeId = 7;


Msg 41333, Level 16, State 1, Line 74
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.