SQL Server Concurrency

  • Tables

  • ACID Transactions

  • Concurrency

  • OLTP vs OLAP

  • The CAP Theorem

Overview

Tables

Tables:

An Ancient Idea

 

An account of barley rations written in Cuneiform on clay tablet, written in year 4 of King Urukagina (c. 2350 BCE).

 

 

 

 

From Ngirsu, Iraq. British Museum, London. Image via Wikimedia Commons

Tables in Accounting


Luca Pacioli authors Summa de arithmetica geometria (1494), first known published work on double-entry bookkeeping. 

Transaction Log

 

Double-entry account ledger and daybook of Laban Morey Wheaton (1835).

 

 

 

 

 

 

Tomasek and Bauman, "Encoding Financial Records for Historical Research" in the Journal of the Text Encoding Initiative (2013).

National Bureau of Standards builds SEAC in 1950 - first computer to support two threads. 

Edgsar W. Dijkstra authors seminal paper on requirements of mutual exclusion locks (1965)

Edgar F. Codd proposes relational model of database management while at IBM Research (1970)

Chamberlin and Boyce publish paper on Structured English Query Language, the precursor to modern SQL (1974)

SQL Server (under the hood)

SQL Server Table:
a shared mutable data structure

B-Tree

Clustered vs Non-clustered

(B-Trees)

(Heap)

SQL Server Indexes

SQL Joins

Space Complexity Time Complexity
Nested loop join * 1 m log (n)
Merge join 1 log(m) + log(n)
Hash join n m + n

* with index seek on inner side

ACID Transactions

Atomicity - transaction is a single      
    indivisible unit of work, all or none
 

Consistency - maintain data integrity
    when transaction completes
 

Isolation - one transaction will not
    interfere with another
 

Durability - effects of transaction are 
    permanent and recoverable

Scope ensures transactions are Atomic 

BEGIN TRAN
CREATE/INSERT/SELECT/UPDATE/DELETE/DROP

...

COMMIT TRAN

Locks ensure transactions are

Consistent and Isolated

Transaction Log ensures updates are Durable

unicorn = transaction log

Concurrency

concurrency (kuh n-kur-uh nsee)-
the ability for multiple threads or processes to access or update shared data at the same time

Safety

 

Liveness

 

Performance

pessimistic vs optimistic

(Locks)

(Versioning)

  • Preventable read phenomena
    • ​dirty reads
    • non-repeatable reads
    • phantom reads
  • Lost updates
  • Excessive blocking
  • Deadlocks

Resource contention and data integrity

Transaction isolation levels

SQL Server can lock any of these resources

SQL Server uses these resource lock modes

Example query

lock vs lock-free

lock vs lock-free

lock vs lock-free

Read more

 

SQL Server Concurrency:
Locking, Blocking, and Row Versioning.

Kalen Delaney (2012)

 

OLTP vs OLAP

On-Line Transaction Processing

On-Line Analytical Processing

Relational databases are not designed for mixed workloads

The CAP Theorem

Database as a Distributed System

Q + A