[I attended this presentation. The original links to material have been removed as these no longer exist]
Other products on the market with in memory technology are Oracle TimesTen, IBM SolidDB and Volt VoltDB, but SAP Hana is not a direct competitor.
This new technology is not the same as the old functionality of pinning a table in memory. In the current core engine there is the need for concurrency control, locks and latches and interpreted query plans.
In the 1980’s the database field explorer had the use of many different concurrency control (CC) mechanisms. The technique (due to Jim Gray) known as 2 phase locking emerged as the standard. There are a variety of lock types including X(eXclusive) and S(shared). There are two simple rules to follow. Before access, the query must acquire an appropriate lock types from the lock manager and once a query releases a lock, no further locks can be acquired. Jim Gray won the ACM Turing Award for his work on transaction processing.
A comparison between the two engines and the required 3 components.
In SQL Server 2014 it uses lock free data structures invented by Maurice Herlihy at Brown University, who was elected to National Academy of Engineering. The first phase is to create a memory optimised table. Then the concurrency control components are:
Optimistic
Conflicts
are assumed to be rare
Transactions
run to completion without blocking or setting locks
Conflicts
are detected during a validation phase
MultiversionUpdating a row creates a new version of the row
Timestamps
Each
row version has an associated time range
Transactions
use their begin timestamps to select correct version
Timestamps
are also used to create a total order
for transactions to obtain equivalent of a serial order.
Regular T-SQL access ‘Interop’
Queries
can access and update both Hekaton and disk resident tables
Interpreted
execution limits performance
Complied
Stored Procedures ‘Native’T-SQL => C code => machine code (dll)
MUCH faster, but some language surface restrictions with V1
The slide deck is no longer available from its original source.
More details on the new SQL Server feature was in these articles listed below (links are now archived and not available so they have been removed):
Getting Started with SQL Server 2014 In-Memory OLTP
Hardware Considerations for In-Memory OLTP in SQL Server 2014
Architectural Overview of SQL Server 2014’s In-Memory OLTP Technology
