As you already know from Chapter 13, transactions and concurrency are related topics because concurrency problems can be solved using transactions. (This is true only for so-called pessimistic concurrency models.) For this reason, in the context of In-Memory OLTP and concurrency control, we will handle both database concepts in one section, starting with logging.
1. Logging Memory-Optimized Objects
Transaction logs are used to ensure that effects of all committed transactions can be recovered after a failure. In-Memory OLTP achieves this by writing all logging processes to persistent storage. (The same is true for checkpoint processes, but checkpoint processing of memory-optimized tables is beyond the scope of this book and thus will not be described.)
Operations on memory-optimized tables use the same transaction log that is used for operations on disk-based tables, and as always, the transaction log is stored on disk. In case of a system failure, the rows of data in memory-optimized tables can be re-created from the log. The information written to disk consists of transaction log streams. The log contains required information about committed transactions to redo the transactions. The changes are recorded as inserts and deletes of row versions marked with the table identifier they belong to. Index operations on memory-optimized tables are not logged. In other words, all indices are completely rebuilt on recovery.
The important property of transaction logging of memory-optimized tables is that this process generates significantly less data than the same process on disk-based tables. There are several reasons for this. First, log records are generated only at commit time. Second, data generated by dirty reads (i.e., uncommitted changes) are never written to disk. Finally, the system tries to group multiple log records into one large log record of up to 24KB. That way, fewer log records are written to the log.
NOTE Logging is one of only a few operations on memory-optimized database objects where the system writes to disk. Therefore, it is important to minimize the number of such operations. Creating a single log record for the entire transaction and reducing the size of the logged information reduces significantly the number of write operations.
2. Optimistic Multiversion Concurrency Control
When accessing memory-optimized tables, the Database Engine implements an optimistic multiversion concurrency control. (Transaction conflicts are uncommon in most applications that access data in memory-optimized tables. For this reason, the optimistic approach, which has less overhead than the pessimistic one, is used.) This means that rows in memory-optimized tables can have different versions. Transactions that access memory-optimized tables use this row versioning to obtain a transactional consistent snapshot of rows in the tables. Data read by any statement in the transaction will be the consistent version of the data that existed at the time the transaction started. Therefore, any modifications made by concurrently running transactions are not visible to statements in the current transaction, and concurrent transactions access potentially different versions of the same row. (The system can obtain this information using the Begin-TS and End-TS values from the header of each row.)
Transactions on memory-optimized tables assume that there are no conflicts with concurrent transactions. (This is a general property of all optimistic concurrency models.) Transactions do not take locks on memory-optimized tables to guarantee isolation of transactions. Therefore, write operations do not block any read or other write operations.
The Database Engine detects conflicts between concurrent transactions, as well as isolation violations. In that case, one of the conflicting transactions is aborted and must be restarted.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.