SQL Server 2014 In-Memory OLTP: Speeding Up Writes with Hekaton Tables

The Lock Contention Scale Limit

Traditional relational engines use locking structures to protect data integrity during concurrent transactions. If user A is updating a row, user B must wait—which degrades write performance under high concurrent loads.

SQL Server 2014 addresses this by introducing Memory-Optimized Tables (Project Hekaton).

In-Memory Rule: Hekaton tables use lock-free, latch-free index structures. Concurrency conflicts are checked during transaction commit.

Lock-Free Multi-Version Concurrency Control (MVCC)

Instead of locks, Hekaton uses MVCC:

• ◆Row Versions: Every write creates a new version of the row with start and end timestamps.
• ◆Conflict Check: If two transactions modify the identical row, the second transaction is rolled back on commit, preventing block latency.

Creating a Memory-Optimized Table

-- Creating a memory-optimized table in SQL Server 2014
CREATE TABLE WebSession (
    SessionId INT NOT NULL PRIMARY KEY NONCLUSTERED,
    UserId INT NOT NULL,
    ExpiresAt DATETIME NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

By compiling queries into native C DLL files, Hekaton speeds up write performance for OLTP databases.