Уровни изоляции транзакций в PostgreSQL, MySQL, MSQL, Oracle с примерами на Go

Вступление

В данной статье обсудим проблемы, возникающие при конкурентной работе с данными, а также инструменты для их решения – атомарные инструкции, явные и неявные блокировки и уровни изолированности транзакций, реализованные в OLTP СУБД PostgreSQL, MySQL, SQL Server, Oracle с примерами на Go. Поговорим о деталях их реализации в указанных СУБД. На примере PostgreSQL проведем benchmark-тестирование производительности уровней изоляции с использованием инструмента pgbench.

Docker – compose с СУБД и код примеров можно найти в репозитории.

Содержание статьи

1. Транзакция

2. Состояние гонки

3. Атомарные инструкции

4. Явные блокировки

5. Проблемы конкурентного доступа и их решение

6. Dirty Write

7. Read Uncommitted

8. Dirty Read

9. Read Committed

10. Lost update

11. Non - Repeatable read

12. Phantom

13. Repeatable read/snapshot isolation

14. Skewed write

15. Serializable

16. Выводы

17. Тестирование производительности с помощью pgbench

18. Список используемых при подготовке статьи источников

1. Транзакция

Прежде всего определим, что в статье понимается под “транзакцией”.

В качестве описания транзакции возьмем определение, изложенное в SQL стандарте ISO/IEC 9075(ISO/IEC 9075:1999) (далее Стандарт): “An SQL-transaction is a sequence of executions of SQL-statements that is atomic with respect to recovery”. Что можно перевести как “SQL-транзакция выполняется как единое целое(атомарно), и если произойдет сбой или ошибки в процессе выполнения этой транзакции, система может восстановить данные до состояния, предшествующего началу транзакции”.

Данное определение также соответствует свойству A в аббревиатуре ACID – атомарности.

Чем, согласно определению, нам помогает транзакция? Не повторять одну и ту же, довольно распространенную логику, в приложении. Рассмотрим тривиальный пример такой логики – перевод денег с одного счета на другой.

Клиент переводит деньги со своего счета на счет продавца. В транзакцию упакованы запрос уменьшения суммы на счете клиента и начисления этой суммы на счет продавца. По определению, транзакция обеспечивает невозможность ситуации, когда деньги со счета клиента списались, а на счет продавца не зачислились.

Покажем в общем виде, как можно реализовать такую идею согласно Стандарту. Пункт 4.32 Стандарта определяет: “An SQL-transaction is terminated by a <commit statement>(success) or <rollback statement>(fail). МетодCommit реализуется тривиально как просто успешное выполнение серии запросов. Откуда следует, что реализовать нужно только метод Rollback – откат операций в случае ошибки. Что может быть реализовано сохранением зеркальных отображений всех произведенных операций(Т.е. INSERT -> DELETE, Increment -> Decrement ) в стеке и их “разворачиванием”(unfold) в момент вызова метода Rollback(При условии гарантированного его выполнения даже в случае различных сбоев – atomic with respect to recovery). И во многих системах даже есть встроенные средства, позволяющие это сделать – WAL, commit log, rollback segment.

И все - для последовательного доступа к данным этого механизма было бы достаточно. Вот только СУБД с данными работают конкурентно по понятным причинам производительности. И в случае такого доступа к данным ситуация значительно усложняется. Указанного механизма уже недостаточно – ведь после изменения данных одной транзакцией, другая, запущенная параллельно, имеет к ним доступ. Возникает состояние гонки.

2. Состояние гонки

Рассмотрим следующее состояние гонки(на начало транзакции amount = 100):

Begin Transaction
	SELECT  amount FROM  accounts WHERE  id = 1
	<логика приложения :new_amount = amount+50>
	UPDATE accounts SET amount = :new_amount
Commit

В случае последовательного выполнения amount = 200. В случае конкурентного - amount может быть как 200, так и 150.

Приведем аналогию с языком Go в ситуации, когда две горутины конкурентно обновляют одно значение счетчика:

var count int	
go func() {count+=10}()
go func() {count+=10}()

В результате при конкурентном выполнении на выходе может быть как 20, так и 10.

Хорошее объяснение, почему так происходит, дано в книге Томаса Кормена “Introduction to algorithms”:

Состояние гонки

Следующий псевдо – код иллюстрирует состояние гонки, возникающее, когда две логически параллельные инструкции обращаются к одной и той же ячейке памяти и по – крайней мере одна инструкция изменяет сохраненное в ней значение:

function RACE - EXAMPLE()
	x = 0
	parallel for i=1 to 2 
		x = x + 1
	print x

И его трассировка:

x – значение, хранящееся в памяти, r1, r2 – регистры процессора.
Step1: Один из процессоров устанавливает x в 0.
Step2, step 3: – процессор 1 загружает значение x из памяти в свой регистр 1(step2) и инкрементирует его(step3).
Step 4-6: процессор 2 загружает значение x в свой регистр 2(step4), инкрементирует его(step5), загружает измененное значение обратно в x(step6)
Step7: процессор 1 сохраняет значение из своего регистра r1 (1) обратно в x
В итоге напечатается 1, хотя при последовательном исполнении инструкций напечаталось бы 2.

3. Атомарные инструкции

С проблемой гонок чаще всего справляются путем использования атомарных инструкций процессора, в языке Go – использованием пакета atomic:

var count atomic.Int64
go func() { count.Add(10) }()
go func() { count.Add(10) }()

В СУБД есть похожий механизм, часто называемый “атомарным инкрементом/декрементом”. К примеру, в PostgreSQL следующий запрос:

UPDATE accounts SET amount = amount-100 WHERE id = 1 AND amount >= 100

Не создает состояния гонки и выполняется “атомарно”.

4. Явные блокировки

Если рассмотерть данный механизм на примере СУБД PostgreSQL, а именно по системному представлению pg_locks, то станет понятно, что это все – таки вид автоматической эксклюзивной блокировки на уровне строки(RowExclusiveLock, если быть точным), т.е. все – таки не аналог атомарной инструкции процессора, а скорее мьютекса в Go.

type MutexMap struct {
	mp map[string]int
	mu sync.RWMutex
}

func (m *MutexMap) Read(key string) (val int, ok bool) {
	m.mu.RLock()
	defer m.mu.RUnlock()
	val, ok = m.mp[key]
	return
}

func (m *MutexMap) Write(key string, val int) {
	m.mu.Lock()
	defer m.mu.Unlock()
	m.mp[key] = val
}

В СУБД для решения проблем конкурентного доступа также могут используются явные блокировки. В PostgreSQL SELECT FOR UPDATE соответствует эксклюзивной блокировке Lock(), а SELECT FOR SHARE – разделяемой Rlock(). В конце транзакции блокировки освобождаются неявно, (в коде блокировки освобождаются явно вызовом Unlock())

Замечу, что если забыть вызвать Unlock(), то это приведет к ситуации дедлока в соответствии с правилами А),Б). Покажем это на следующих примерах

type MutexMap struct {
	mp map[string]int
	mu sync.RWMutex
}

func (m *MutexMap) Read(key string) (val int, ok bool) {
	m.mu.RLock()
	//defer m.mu.RUnlock()
	val, ok = m.mp[key]
	return
}

func (m *MutexMap) Write(key string, val int) {
	m.mu.Lock() // deadlock!
	defer m.mu.Unlock()
	m.mp[key] = val
}

func main() {
	_mp := &MutexMap{mp: make(map[string]int)}
	_mp.Write("1", 1)
	val1, _ := _mp.Read("1")
	val2, _ := _mp.Read("1")

	_mp.Write("2", 1) // - deadlock!

	fmt.Println(val1, val2)
}

ype MutexMap struct {
	mp map[string]int
	mu sync.RWMutex
}

func (m *MutexMap) Read(key string) (val int, ok bool) {
	m.mu.RLock() // deadlock!
	defer m.mu.RUnlock()
	val, ok = m.mp[key]
	return
}

func (m *MutexMap) Write(key string, val int) {
	m.mu.Lock()
	//defer m.mu.Unlock()
	m.mp[key] = val
}

func main() {
	_mp := &MutexMap{mp: make(map[string]int)}
	_mp.Write("1", 1)
	val1, _ := _mp.Read("1") // - deadlock!
	fmt.Println(val1)
}

Но следует иметь в виду, что эти инструменты все - таки работают на разных уровнях абстракции. В различных СУБД применяются разные режимы блокировок со своими таблицами конфликтности. Но смысл дедлока при использовании разделяемых блокировок остается тем же. В дальнейшем нам этот пример поможет лучше понять работу SELECT FOR SHARE.

5. Проблемы конкурентного доступа и их решение

Рассмотрим теперь, какие проблемы конкурентного доступа при использовании транзакций помогают решить блокировки.

6. Dirty Write

Грязная операция записи(Dirty Write) возникает, если операция изменения не удерживает блокировку до конца транзакции. Из – за чего апдейты не группируются в рамках транзакции и выполняются независимо друг от друга, т.е. по сути отсутствует какая – либо изоляция транзакций друг от друга. При нескольких апдейтах в транзакции это может приводить к серьезным проблемам. Рассмотрим такой пример:

Два покупателя – X и Y одновременно отправили запрос на покупку машины. Последовательность операций в рамках каждой транзакции выглядит так:

Данная последовательность приведет к тому, что машину отправят Y, а счет отправят X. Это серьезная проблема. И хуже всего то, что при обычном, ненагруженном тестировании такая ошибка обычно не распознается. И при нагруженном может также не распознаться. Но хакеры то знают, как это протестировать

#!/bin/bash
host=https://habr.com/v1/users/$user/
curl 
	-X POST 
	-H "Content-Type: application/json" 
	"$host" &
curl 
	-X POST 
	-H "Content-Type: application/json" 
	"$host"

Какое бы straightforward solution можно предложить для решения проблемы dirty write? Атомарный апдейт тут не подходит, т.к. используются две таблицы. Можно попробовать упаковать это все в один запрос - к примеру, в PostgresSQL эту проблему можно решить с помощью CTE (Common Table Expressions) для группирования нескольких запросов в один и создания атомарности, например, так:

WITH updated_cars AS (
    UPDATE cars
        SET buyer = 2
        WHERE id = 3
        RETURNING id
), updated_car_invoices AS (
         UPDATE car_invoices
             SET buyer = 2
             WHERE car_id = 1
             RETURNING car_id
     )
SELECT * FROM updated_cars, updated_car_invoices;

Но не все СУБД поддерживают такой подход.

Можно попробовать использовать явные блокировки. Но в этом нет необходимости, т.к. разработчики СУБД уже позаботились об этом, изолировав транзакции друг от друга по умолчанию, чем предотвратили данную проблему.

7. Read Uncommitted

Транзакции всех из рассматриваемых СУБД обеспечивают гарантию для данного уровня изоляции, соответствующую Стандарту – отсутствие Dirty Write, т.е. выше описанная ситуация не происходит.

8. Dirty Read

Для дальнейшего тестирования уровней изоляции создадим следующую таблицу в указанных СУБД:

И вставим одну запись: INSERT INTO accounts(id,user_id,amount) VALUES(1,1,1000)

Рассмотрим следующий пример при использовании первого уровня изоляции – чтение незафиксированных данных(Read Uncommitted):

Вызовем Dirty Read Function командой read_uncommitted

Запустим код и посмотрим, как он себя поведет в разных СУБД.

Данный уровень вообще не поддерживается в Oracle(при выполнении запроса получаем ошибку – not supported), в PostgreSQL работает аналогично ReadCommitted(возможно, оставлен для обратной совместимости), Mysql, SQLServer ведут себя согласно Стандарту.

Если транзакция изменит значение amount счета и затем вызовет rollback на данном уровне изоляции в Mysql/SqlServer, но обновленное значение из – за состояния гонки кто – нибудь успеет прочитать, то это приведет к оперированию некорректными данными с возможными серьезными последствиями.

Такая проблема называется грязное чтение(Dirty Read) и, согласно Cтандарту, присутствует только на уровне Read Uncommitted. Многие СУБД, такие как PostgreSQL и Oracle не поддерживают этот уровень. В PostgreSQL можно установить уровень изоляции Read Uncommitted, но вести он себя будет как Read Committed. В Oracle попытка установить данный уровень изоляции вернет ошибку “not supported”.
Как уже было показано в разделе “состояние гонки”, без изменения уровня изоляции транзакции эту проблему можно решить, применив атомарный апдейт для одной таблицы, CTE для нескольких таблиц.
Но что, если атомарный апдейт нам по какой – то причине не подходит? Например, мы вычислили новый amount в приложении.
Очевидный вариант – использование явных локов.

Первый вариант – установить эксклюзивную блокировку SELECT FOR UPDATE(хинт UPDLOCK в SQL Server) в начале транзакции, которая будет держаться до ее завершения. Неоптимальное решение по ряду причин:

• Это плохо сказывается на производительности – операции чтения в других транзакциях сразу же встают в очередь, ожидая завершения всей транзакции.

• Если в итоге случился rollback, то и ждать не стоило

Отмечу такой момент, что в указанных СУБД SELECT FOR UPDATE хоть и называется “exclusive”, не блокирует обычный SELECT без использования FOR UPDATE/SHARE.

Второй вариант – разделяемая блокировка, SELECT FOR SHARE (хинт HOLDLOCK, ROWLOCK в SQL Server) чтение заблокируется, если операция update в другой транзакции уже установила блокировку, вплоть до завершения этой транзакции. И обратно – update заблокируется, если в другой транзакции SELECT FOR SHARE уже захватил блокировку. Проблему грязного чтения это решает, но имеет другой существенный недостаток. Рассмотрим следующий пример:

Вызовем Select For Share Deadlock Function командой read_uncommitted

В приведенном выше коде перед выполнением update блокировка успевает захватиться чтением в обоих транзакциями. Как следствие – deadlock. Все дело в том, что в момент операции Update транзакция пробует установить блокировку exclusive вместо share. Блокировка exclusive в свою очередь не может быть установлена, если есть блокировки share других транзакций. Поэтому возникает взаимное ожидание освобождения блокировок – deadlock. Т.е. по сути то же самое, что и в рассмотренных примерах блокировки мьютексов в Go коде. Во всех рассмотренных СУБД, поддерживающих данный уровень изоляции, предусмотрен механизм обнаружения дедлока и отката одной из транзакций с соответствующей ошибкой.

В целом этот вариант жизнеспособен, но есть ряд минусов:

• Необходимость повторной обработки запроса в случае дедлока.

• (касается и SELECT FOR UPDATE) Очень легко случайно где-нибудь в коде забыть добавить нужную блокировку

Вопрос – как сделать так, чтобы аномалии грязного чтения не возникало и какие еще механизмы кроме явных блокировок можно использовать? Переходим к следующему уровню изоляции.

9. Read Сommitted

Следующий уровень - чтение зафиксированных данных(Read Сommitted), согласно Стандарту, должен предотвращать проблему грязного чтения. При этом по возможности применяя механизм, который бы позволил не использовать чрезмерных локов.
И механизм этот - запоминание и возрат старых значений изменяемых в транзакции строк. Т.е. перед началом транзакции БД создает копию данных, которую и возращает при запросе другими транзакциями. Только после фиксации нового значения транзакции начинают получать его при чтении. Запустим пример грязного чтения на уровне изоляции read committed: Read Committed - Dirty Read

В итоге MySQL, PostgreSQL и Oracle не читают еще не зафиксированные данные из другой транзакции, при этом запись не блокирует чтение.

Забегая вперед, отмечу, что данный механизм это упрощенная версия механизма снимков данных, о котором будет рассказано в пункте уровень изоляции Repeatable Read/Snapshot Isolation. Но работают они по разному. На уровне изоляции Read Сommitted этот механизм работает, как было указано - перед началом транзакции БД создает копию данных, которую и возращает при запросе другими транзакциями. Если транзакция была зафиксирована, то повторный запрос в других транзакциях вернет новое значение – это и есть чтение зафикисированных данных.

Но SQLServer этот механизм в данном случае не использует. Все работает на блокировках.

Сравним использование SQL Server блокировок в данном случае, например, с PostgreSQL. PostgreSQL при выполнении операции Update устанавливает уровень блокировки(Row Exclusive), который согласно таблице блокировок не конфликтует с режимом, устанавливаемом командой SELECT(Access Share). SQL Server же для решения проблемы Dirty Read при выполнении команды UPDATE устанавливает уровень эксклюзивной блокировки, который уже будет конфликтовать с режимом, устанавливаемым командой SELECT.

И это приводит к тому, что если в транзакции на данном уровне изоляции выполнить Update, то даже запросы SELECT вне какой - либо транзакции встанут в очередь, ожидая освобождения блокировки, наложенной этой операцией Update. Звучит как неслабый оверхед. Вот такое простое решение проблемы Dirty Read.

В SQL Server также есть хинт NOLOCK, который приводит к тому, что запрос вообще не будет устанавливать никаких блокировок. Но тогда любой SELECT сможет прочитать еще незафиксированные значения, что приведет к Dirty Read.

is_read_committed_snapshot_on SQL Server

Для SqlServer есть настройка is_read_committed_snapshot_on, которая включается запросом ALTER DATABASE transaction_isolation SET READ_COMMITTED_SNAPSHOT ON;

Можно включить ее в коде, в файле read_committed.go, раскоментировав следующую строку

После ее включения поведение SQL Server становится таким же, как и у других СУБД, возвращается 1000, запись не блокирует чтение и при этом нет аномалии Dirty Read.

10. Lost Update

Очень часто в литературе в качестве примера аномалии потерянного обновления (Lost Update) приводят пример, когда транзакция 1 считала значение какого – то счетчика и перед выполнением команды Update его успела изменить другая транзакция – в итоге транзакция 1 перезапишет это изменение. Проверим, что во всех рассмотренных СУБД такая ситуация присутствует на уровне read committed:

Запустим следующий код: Lost Update Function командой read_committed

До выполнения транзакций accountSum = 1000.
В начале выполнения каждая транзакция считывает данное значение и затем прибавляет значение: в одной транзакции – 200, в другой – 500. В результате получаем значение 1500, в итоге первый Update – 200, был потерян.
Без изменения уровня изоляции данная проблема может быть решена как атомарным инкрементом, так и эксклюзивной блокировкой.

11. Non-Repeatable Read

Неповторяемым чтением(Non-Repeatable Read) называется ситуация, когда в рамках одной транзакции в результате внешних к транзакции операций Update/Delete БД два одинаковых запроса выводят разный результат.

Рассмотрим такой код: Non - Repeatable Read Function и вызовем данную функцию на уровне изоляции Read Committed

Получим такой результат:

Все СУБД приводят к одному результату – после коммита транзакции 2 транзакция 1 получает измененное в транзакции 2 значение. В некоторых случаях такое поведение нежелательно – к примеру, в сложных аналитических запросах, когда на выходе должен быть результат на определенный момент времени.

Какой механизм позволит избежать неповторяемого чтения? В некоторых СУБД можно использовать хранимые процедуры и в них создавать переменные. Не самое простое решение, дополнительно усложняющее код и требуещее поддержки.
Также всегда можно использовать для решения этой проблемы явные блокировки.

12. Phantom

В Стандарте последняя проблема конкурентного доступа носит название “Phantom”. И возникает, когда транзакция tx1 выполняет поиск и считывает диапазон строк, затем транзакция tx2 добавляет строку, т.е. делает операцию INSERT – если теперь tx1 снова выполнит поиск по тому же условию, результат будет отличаться.

Да, видом операции, приводящей к аномалии, и различаются эти две проблемы в Стандарте. Если подумать логически, то можно было бы включить Phantom в NonRepeatable – ведь новые строки тоже приводят к неповторяемому чтению.

Забегая снова вперед, механизм MVCC на следующем уровне изоляции помогает разом решить обе проблемы – и NonRepeatable Read и Phantom. Какой смысл тогда их разделять?
Если учитывать, что во времена составления Стандарта проблемы конкурентного доступа решались в основном блокировками, то это выглядит достаточно логично. Ведь простую блокировку на несуществующую строку не наложишь, SELECT FOR UPDATE тут не поможет. Хмм… какой же это может быть механизм, что бы придумать… Ну конечно же! Блокировки! Только более сложные, вроде блокировок диапазона или предикатных блокировок. А раз принципиально другой механизм, то и другая аномалия (:

Предикатная блокировка устанавлиется для предотвращения вставки строк в диапазон значений и исключения фантомов.

Вызовем Test Phantom Function командой read_committed

В данном коде считается сумма счета по полю user_id. В начале транзакции tx1 под это условие подходит 1 счет(account), в конце транзакции, после вставки нового счета с тем же user_id, уже 2 счета. В итоге – по сути неповторяемое чтение, но аномалия уже называется “фантом”.

Все СУБД выдают одинаковый результат – 1000, 2000

Согласно Стандарту, проблема Phantom должна решаться на уровне Serializable:

Но создатели ряда СУБД решили объединить решение обеих аномалий в одном уровне – ведь если используемый механизм позволяет это сделать, то почему бы не убить двух зайцев сразу?

13. Repeatable Read/Snapshot Isolation

И механизм этот – “оптимистическая блокировка” или в терминах СУБД MVCC(multiversion concurrency control) - многоверсионное управление конкурентным доступом.
Для работы данного механизма создается таблица, в которой ведется история апдейтов с счетчиком транзакций(transaction sequence). Когда транзакция запрашивает данные, она сравнивает свой счетчик с счетчиками других транзакций, которые вносили изменения в эти данные. Из этих счетчиков выбирается наибольший, но меньший по значению, чем счетчик текущей транзакции. При этом транзакция работает по принципу чтение никогда не блокирует запись, а запись — чтение.

По сути, таблица истории изменения строк, отсортированная по времени создания(также очень похоже на max heap по времени изменения, если говорить языком структур данных).

Но если мы говорим об оптимистичной блокировке, то возможны конфликты - ситуации, когда две параллельные транзакции отредактировали одни и те же строки в таблице и пытаются сделать commit. Как в GIT, когда два человека отредактировали одни и те же строки в файле и пытаются сделать merge изменений. Как решается такой конфликт?

Аномалия Lost Update – хороший пример такого конфликта. Запустим код TestLostUpdate на уровне изоляции Repeatable Read. Результаты будут следующими:

Postgresql выдают ошибку “ERROR: could not serialize access due to concurrent update”.
SQL Server выдает ошибку Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Интересно, что сколько бы я ни запускал тест, postgresql всегда выбирал в качестве “жертвы” роллбека транзакцию 1, а SQL Server – произвольно, то tx1, то tx2. Также тут интересно описание ошибки от SQL Server - deadlocked on lock resources, но постойте – почему именно deadlock? Потому, что в SQL Server на уровне Repeatable Read MVCC не использует. Как он работает на этом уровне, будет сказано далее, но по описанию ошибки уже можно догадаться. В PostgresSQL указанный конфликт, когда две транзакции редактируют одну строку решается просто – методом «first updater win»

В MySQL мы видим значение 1500, что не соответствует последовательному применению запросов: 1000 + 200 + 500 = 1700. Таким образом, на уровне Repeatable Read MySQL проблему Lost Update не предотвращает.

Справедливости ради надо сказать, что проблема Lost Update в Стандарте не упоминается. Эта проблема упоминается в другом труде - A Critique of ANSI SQL Isolation Levels, который будет рассмотрен далее

DELIMITER //
CREATE PROCEDURE UpdateAccount(
    IN account_id INT,
    IN old_version INT,
    IN amount_to_add INT
)
BEGIN
    DECLARE rows_changed INT;
    
    START TRANSACTION;

    UPDATE accounts
    SET amount = amount + amount_to_add, version = version + 1
    WHERE id = account_id AND version = old_version;

    SET rows_changed = ROW_COUNT();

    IF rows_changed = 0 THEN
        ROLLBACK;
        SELECT 'Update failed due to version mismatch or no matching account' AS result;
    ELSE
        COMMIT;
        SELECT 'Update successful' AS result;
    END IF;
END //
DELIMITER ;

Oracle вообще не поддерживает данный уровень изоляции. Вместо него используя уровень Serializable или Read only транзакции. Почему это так, описано, к примеру, Томом Кайтом в книге “Oracle для профессионалов”, разделы “Уровень изолированности REPEATABLE READ” и “Транзакции только для чтения”.

Согласно Кайту:

1) Read Only транзакции используются для продолжительных отчетов(но если они будут очень продолжительными, это может привести к ошибке ORA-1555 snapshot too old).

2) Serializable в Oracle дает дает хороший эффект, если:

- высока вероятность того, что данные не изменяет другой сеанс;

- необходима согласованность по чтению на уровне транзакций;

- транзакции будут непродолжительными

Протестируем Read Only транзакции в Oracle. К сожалению, настройка Read Only используемой мной библиотеки “database/sql” не поддерживает установку данного режима, на момент написания статьи кейс по ее фиксу был открыт.

Так что я использовал в этот раз консоль своей IDE для проверки, подойдет ли данный режим для решения проблемы Non-Repeatable Read. Сперва выполним такой SQL код:

SET TRANSACTION READ ONLY;
UPDATE ACCOUNTS SET AMOUNT = 1500 WHERE id = 1;
COMMIT;

Выполнение Update в данной транзакции приводит к ошибке “ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction”.

Транзакция действительно Read Only. Допустим, мы хотим выполнить несколько тяжелых аналитических SELECT - запросов. Могут ли внешние операции Update/Delete на них повлиять и привести к неповторяемому чтению?

Выполним следующие две транзакции:

Получим следующий результат:
tx1: 1000, 1000
tx2: 1500

Update не повлиял на результат выполнения Read Only транзакции.

Теперь проверим, как влияет Delete:

Получим следующий результат:
tx1: 1000, 1000

Таким образом, транзакции в режиме Read Only В Oracle решает проблему неповторяемого чтения.

Теперь протестируем как остальные СУБД справляются с неповторимым чтением.

Рассмотрим следующий код:

// NotRepeatableRead - start transaction, read account,
// then update account outside of transaction - transaction can see changes.
func NonRepeatableRead(ctx context.Context, db *sqlx.DB, txLevel sql.IsolationLevel, dbName string) (err error) {
	fmt.Println("----------------Non repeatable read-----------------")

	group, _ := errgroup.WithContext(ctx)
	group.Go(func() error {
		var tx1 *helper.Transaction
		if tx1, err = helper.CreateTransaction(ctx, db, txLevel, 1, dbName); err != nil {
			return err
		}
		defer func() {
			tx1.Close(err)
		}()
		//print amount in tx1 before update amount in tx2
		if err = tx1.PrintAmount(); err != nil {
			return err
		}
		time.Sleep(time.Millisecond * 300)
		//print amount after update in tx2 in tx1
		if err = tx1.PrintAmount(); err != nil {
			return err
		}
		//sql server - wait for commit
		time.Sleep(time.Millisecond * 500)

		return err
	})

	group.Go(func() error {
		var tx2 *helper.Transaction
		if tx2, err = helper.CreateTransaction(ctx, db, txLevel, 2, dbName); err != nil {
			return err
		}
		defer func() {
			tx2.Close(err)
		}()
		time.Sleep(time.Millisecond * 100)

		//update account in transaction 2
		if err = tx2.UpdateAccount(1500, false); err != nil {
			return err
		}

		fmt.Println("account updated")

		return err
	})

	err = group.Wait()
	if err != nil {
		fmt.Printf("waitgroup error: %s", err)
		return
	}
	return
}

Вопрос: какая транзакция закомиттится первой? Да, Mysql, PostgreSQL за счет таймаута делает commit сперва транзакции tx1. Но SQLServer выводит следующее:

----------------Non-Repeatable read-----------------
Transaction 1 created
Account sum in transaction 1: 1000
Transaction 2 created
Update account amount in transaction 2 to 1500
Account sum in transaction 1: 1000
Transaction 1 committed
Account updated
Transaction 2 committed

Т.о. на данном уровне изоляции SQL Server при чтении строки в одной транзакции блокирует эту строку на изменение в другой. Т.е. предотвращает неповторяемое чтение с помощью неявных разделяемых блокировок, что соответствует рассмотренному нами примеру с явными разделяемыми блокировками - SELECT FOR SHARE. Вот и ответ, почему SQL Server в случае Lost Update выдавал ошибку “was deadlocked on lock resources”. Т.е. тот же самый deadlock, который у нас происходил при решении проблемы грязного чтения методом SELECT FOR SHARE.

Проверим, как влияет Delete:

В обоих случаях результат будет одинаковый:

СУБД, поддерживающие уровень изоляции Repeatable Read, предотвращают аномалию Non – Repeatable Read, что логично.

В SQL Server помимо 4 описанных в Стандарте уровней изоляции есть еще пятый – Snapshot. И он как раз и соответствует уровню изоляции Repeatable Read в Mysql и PostgreSQL, т.е. использует MVCC.

Перед тестированием данного уровня проверим, предотвращают ли СУБД Phantom на данном уровне:

SQL Server на уровне изоляции Repeatable Read аномалию Phantom не предотвращает, Mysql, PostgreSQL – предотвращают.

Рассмотрим теперь, как с данными аномалиями справляется уровень изоляции SNAPSHOT в SQL Server:

На данном уровне изоляции SQL Server работает аналогично уровню изоляции Repeatable Read в Postgresql – не создает блокировок(сначала коммит tx2, затем tx1), выявляет update conflict в случае с Lost Update, не создает неповторяемого чтения и не позволяет читать фантомы.

MVCC приводит к бОльшему потреблению оперативной памяти СУБД и снижению производительности транзакций. Как сказано в документации SQL Server, “In order to be able to save multiple versions, significantly more RAM is required”. Также использование оптимистических уровней изоляции на системах с большим количеством изменений данных может привести к интенсивному использованию временной базы данных tempdb для хранения версий.

Кроме того, частые операции update/insert/delete в транзакциях могут приводить к частому вызову операции по очистке более не нужных версий строк, что также может влиять на производительность. К примеру, в PostgreSQL это входит в AUTO VACUUM процесс.
Также отмечу, что на данном уровне СУБД отслеживается изменение одной и той же строки в разных транзакциях. Но, как покажем далее, изменение разных строк, связанных определенным условием, также может приводить к состоянию гонки.

14. Skewed write

Требования к уровням изоляции транзакции были изложены еще в первой редакции Стандарта ISO/IEC 9075, выпущенной компанией ANSI в 1986 году. С тех пор Стандарт неоднократно менялся, выходили новые издания(и каждое новое издание платно), но требования к транзакциям не менялись. Тем не менее, уже первая редакция была подвергнута критике в труде “A Critique of ANSI SQL Isolation Levels”, опубликованном исследовательской группой из Microsoft.

В данном труде среди прочего расширялся набор аномалий до уже указанного Lost Update, также были введены аномалии Cursor Lost Update, Read Skew, Write Skew.
Разберем последнюю аномалию из этого списка – Write Skew или “Ассиметрии записи”, посмотрим, как она проявляется и все ли СУБД устраняют ее на уровне изоляции Serializable.

Эта асимметрия может происходить при чтении двумя транзакциями одних и тех же объектов с последующим обновлением некоторых из них. К примеру, рассмотрим пример: предположим, у пользователя есть несколько счетов в банке/торговых счетов с условием: пользователь может выводить с них сумму ровно 1000 при условии, что у него должно оставаться >= 1000 на всех счетах.

Создадим таблицу accounts со следующими строками:

Рассмотрим простой вложенный запрос, реализующий данное требование:

BEGIN TRANSACTION;
UPDATE accounts SET amount = amount-1000 WHERE id = 1 AND amount >=1000 AND  (
    SELECT SUM(amount) FROM accounts WHERE user_id = 1
  ) >= 2000;
COMMIT;

Смысл этого запроса, исходя из условий задачи, такой: Изменить сумму на счете пользователя если выполняется определенное условие агрегационного запроса.
Представим, что у нас есть два параллельных запроса, изменяющих суммы двух счетов одного и того же пользователя:

В Mysql такой запрос работать не будет, вместо него используется похожий по логике запрос:

BEGIN;
SELECT SUM(amount) INTO @total_amount FROM transaction_isolation.accounts WHERE user_id = 1;
#time pause
UPDATE transaction_isolation.accounts
SET amount = amount - 1000
WHERE id = 1 AND amount >= 1000 AND @total_amount >= 2000;
COMMIT;

В логике данного запроса может быть ошибка в зависимости от СУБД и уровня изоляции транзакции.
Перед тем, как разобрать данную ошибку, пересмотрим еще раз рассмотренные уровни изоляции и используемые для их реализации механизмы.

1. Уровень изоляции Read Committed в Mysql, PostgreSQL, Oracle и в SQL Server(с включенным Read_committed_snapshot). Т.к. запись не блокирует чтение, то запрос tx1 не заблокирует запрос tx2. И, хотя все упаковано в один запрос, это может привести к ситуации, когда оба запроса выполнятся перед фиксацией обоих и сумма на счете станет меньше 1000.

2. Уровень изоляции Repeatable Read в Mysql, PostgreSQL, Snapshot в SQL Server. Вероятность того, что на счете станет меньше 1000 еще больше, т.к. если транзакции начались одновременно, уже не важно, когда выполнится фиксация одной из них.

3. Уровень изоляции Read Committed(Read_committed_snapshot off) и Repeatable Read в SQL Server. На обоих уровнях изоляции запись блокирует чтение, поэтому после выполения одного из запросов второй запрос заблокируется при чтении, т.к. заблокированная строка – попадает в выборку данных. Ассиметрия записи не возникнет.

Проверим эти предположения. Данный запрос реализуется в следующем коде

Две конкурентно работающие транзакции выводят средства каждая со своего счета, принадлежащего одному пользователю, с проверкой общего агрегационного условия. В результате PostgreSQL, Mysql и Oracle дают списать полностью все денежные средства с обоих счетов пользователя, в то время, как при последовательном выполнении транзакций условие во второй транзакции бы не отработало.

На уровне Read Committed, результат также согласуется с предположением

Для формальности проверим, что на уровне Snapshot Isolation также списываются все денежные средства

Да, в данном случае предположение совпало с результатами теста. Вот только в мире СУБД порой не все так однозначно. В Mysql мы по сути разделили один запрос на два. Можно попробовать построить запрос по – другому запрос, к примеру, применить Update + JOIN:

BEGIN;
UPDATE transaction_isolation.accounts AS t1
    JOIN (
        SELECT SUM(amount) AS total_amount FROM transaction_isolation.accounts WHERE user_id = 1
    ) AS t2
SET t1.amount = t1.amount - 1000
WHERE t1.id = 1 AND t1.amount >= 1000 AND t2.total_amount >= 2000;
COMMIT;

Либо, используя CTE:

WITH total_amount AS (
    SELECT SUM(amount) AS total_amount
    FROM transaction_isolation.accounts
    WHERE user_id = 1
)
UPDATE transaction_isolation.accounts AS t1,total_amount AS t2
SET t1.amount = t1.amount - 1000
WHERE t1.id = 1 AND t1.amount >= 1000 AND t2.total_amount >= 2000

Предположим, какой будет результат у данного запроса на уровне изоляции Repeatable Read. После вычисления подзапроса и выполнения команды JOIN будет произведен Update строки с t1 = 1 и наложена блокировка на эту строку на запись. Затем аналогично выполнится другой запрос. При этом последовательность выполнения запросов и их фиксации не важна, если они оба начались одновременно, то, теоретически, результатом будет снова нулевой баланс на счетах пользователя.

Запустим код и получим следующий результат уровнях Read Committed и Repeatable Read:

Результат теста не согласуется с предположением – при таком построении запроса аномалия “Ассиметрия записи” не наблюдается, денежные средства не списываются в ноль. Задача выполнена, можно в прод. Можно предположить, что на запись блокируются также строки, попавшие под условие JOIN. Но все еще сложнее – на уровне Repeatable Read блокироваться на запись будут не только строки, имеющие отношение к запросу, но также все строки в таблице, а также любые операции INSERT. Разберемся, почему так происходит.

Проведем ряд тестов для следующих условий:

1. REPEATABLE READ, нет индекса на user_id

2. REPEATABLE READ, индекс на user_id

3. READ COMMITTED, индекс на user_id

Для большей наглядности поменяем значения id: 1→10, 2→20, 3 →30. user_id: 1→3, 2→4.

И соответственно поменяем условие запроса, не меняя сам запрос:

BEGIN;
UPDATE transaction_isolation.accounts AS t1
    JOIN (
        SELECT SUM(amount) AS total_amount FROM transaction_isolation.accounts WHERE user_id = 3
    ) AS t2
SET t1.amount = t1.amount - 1000
WHERE t1.id = 10 AND t1.amount >= 1000 AND t2.total_amount >= 2000;

1. REPEATABLE READ, нет индекса на user_id Запустим наш запрос с EXPLAIN. Результат будет следующим:

Представленные данные можно читать так: сперва происходит полная выборка по таблице accounts – 3 строки, затем создается временная таблица derived2 с применением агрегатной функции – 1 строка и затем выполняется update одной строки. Мы видим, что для вычисления агрегатной функции происходит выборка всех строк.

Теперь запустим сам запрос и посмотрим блокировки по таблице performance_schema.data_locks:

Глядя на эту таблицу, становится понятно, почему блокируется второй запрос. Были установлены блокировки типа S(shared) на все строки таблицы, не только на те, что попали в выборку SELECT SUM(amount) … user_id = 3. На строку с id = 10 при этом стоит эксклюзивная блокировка типа X(exclusive). Т.е. если в другой транзакции попробовать выполнить запрос SELECT * FROM accounts WHERE id = 10 FOR SHARE – он заблокируется.

Это реализация Repeatable Read SQL Server как она есть, только еще более радикальная – заблокированы на изменение вообще все строки таблицы. MVCC испарился. Вопрос – если MVCC испарился, то как быть с Phantom? Эта аномалия также появляется как на соответствующем уровне SQL Server?
Попробуем выполнить запрос INSERT INTO transaction_isolation.accounts (id, user_id, amount) VALUES (50, 3, 1000). Запрос заблокируется. Phantom не наблюдается.

Но также попробуем выполнить запрос INSERT INTO transaction_isolation.accounts (id, user_id, amount) VALUES (5, 4, 1000). Запрос с user_id = 4, никак не затрагивает изменение суммы пользователя с user_id = 3, id запроса = 5 – не больше максимального id в таблице(30) и не попадает ни в какой диапазон между затрагиваемыми запросом SUM строками(10-30). И тем не менее, запрос все равно заблокируется и будет ожидать завершения транзакции.
Вот так – без включения каких – либо настроек вроде Read_committed_snapshot СУБД “на лету” полностью меняет свое поведение, отключая(или не используя) MVCC и блокируя все, что только можно.

Кроме прочего, присутствует блокировка “ supremum pseudo-record” типа S(shared). Не буду вдаваться в описание того, что такое supremum pseudo-record, это часть механизма блокировок следующего ключа в mysql, который по сути является упрощенным мезанизмом предикатных блокировок. Главное здесь, что данная блокировка зависит от того, какой план запроса был выбран – а именно выборка всех строк, поэтому в данном случае блокировка от вставки новых записей была расширена на всю таблицу.

Попробуем улучшить план запроса и создадим индекс на поле user_id:

CREATE INDEX account_user_id ON accounts(user_id);

Выполним запрос еще раз на тех же исходных данных. Получим следующие результаты EXPLAIN:

И data_locks:

С индексом уже совсем другая картина – выборка только двух подходящих под условие строк, shared блокировки только подпадающих под условие строк, и более точная блокировка промежутка последней записи. Причем, согласно SHOW ENGINE INNODB STATUS(мониторинговый запрос в mysql):

RECORD LOCKS space id 45 page no 5 n bits 72 index account_user_id 
of table `transaction_isolation`.`accounts` 
trx id 9576 lock mode S locks gap before rec

Т.е. это блокировка от вставок в промежуток “до” записи(может быть и “после”, в зависимости от запроса). На самой записи с id = 30 уже блокировки нет.
Теперь запрос UPDATE transaction_isolation.accounts SET amount = 3000 WHERE id = 30 выполнится успешно. INSERT INTO transaction_isolation.accounts (id, user_id, amount) VALUES (50, 3, 1000) выполнится успешно, а вот INSERT INTO transaction_isolation.accounts (id, user_id, amount) VALUES (5, {any}, 1000) заблокируется. Неважно, какой будет user_id, если id будет < 30 – запрос заблокируется.

Установим теперь уровень изоляции READ COMMITTED, удалим индекс и запустим запрос при тех же исходных данных. Результаты будут следующие:

Несмотря на последовательное сканирование(seq scan) всех строк, S блокировки устанавливаются только на две записи и одна X на изменяемую строку. Никаких supremum, Insert не блокируется. Связано это вероятно с тем, что на данном уровне изоляции у Mysql нет цели не допустить фантомов и неповторяемого чтения.
Теперь строки, не имеющие отношение к запросу, не блокируются – проверить это можно запросом UPDATE transaction_isolation.accounts SET amount = 3000 WHERE id = 30.

И тем не менее устанавливаемые блокировки все равно не совсем соответствуют уровню изоляции. К примеру, S блокировка на строку с id = 20. Т.к. используются снимки данных, то изменение строк в процессе работы агрегационного запроса теоретически никак не должны влиять на консистентность транзакциии. Возможно, в каких – то случаях оптимизатор запросов Mysql решает не использовать снимки данных, переключаясь на блокировки.

И мы подходим к важному выводу – знать используемые механизмы для обеспечения уровней изоляции и согласованности данных полезно, но полагаться на одну логику при построении SQL запроса не стоит. MVCC теоретически служит для того, чтобы обеспечить гарантии уровня изоляции с меньшим количеством блокировок. Полностью заблокированная на любое изменение и вставку таблица явно по определению этому не соответствует.

Вот что говорит ChatGPT по поводу этого примера:

Да, с точки зрения MVCC, установка разделяемых блокировок в данном случае действительно избыточна и, строго говоря, не нужна для обеспечения изоляции REPEATABLE READ.

Почему это происходит?
В MySQL (InnoDB) исторически сложилось так, что при сложных запросах типа UPDATE JOIN с условиями, зависящими от подзапросов (особенно агрегаций), оптимизатор иногда накладывает блокировки для защиты данных, даже если, теоретически, MVCC мог бы справиться без них. Основные причины такой избыточной блокировки в MySQL связаны с:

1. Консервативным подходом: InnoDB предполагает, что агрегационные подзапросы могут быть зависимыми, и накладывает блокировки на строки, чтобы избежать потенциальных аномалий.

2. Оптимизацией производительности: Иногда блокировки могут предотвращать частые переконфликтования, которые затруднили бы завершение операций UPDATE JOIN без полной гарантии согласованности. Вывод: Да, в данном случае блокировки, накладываемые MySQL, технически избыточны. И хотя MVCC предоставляет возможность работать без них, InnoDB по умолчанию накладывает их для агрегационных и сложных подзапросов в обновлениях, вероятно, из-за консервативных решений в архитектуре движка.

Теперь посмотрим, как с аномалией Write Skew справляется самый строгий уровень изоляции – Serializable.

15. Serializable

Согласно определению Стандарта, при уровне изоляции Serializable конкурентно исполняемые транзакции должны всегда давать тот же результат, как если бы они выполнялись последовательно.
В рассматриваемых СУБД применяются два основных подхода к реализации уровня изоляции Serializable – двухфазная блокировка(two phase lock, 2PL) в Mysql и SQL Server и cериализуемая изоляция снимков состояния (serializable snapshot isolation, SSI) в PostgreSQL. В Oracle данный уровень изоляции использует MVCC, т.е. соответствует уровню изоляции Snapshot Isolation в SQL Server.

Двухфазная блокировка представляет собой комбинацию разделяемой блокировки на чтение и эксклюзивной на изменение, т.е. уже рассмотренный сценарий использования SELECT FOR SHARE + UPDATE. Таким образом, следующий запрос:

BEGIN
SELECT amount FROM accounts WHERE id = 1;
UPDATE  accounts SET amount = 1200 WHERE id = 1;
COMMIT;

Автоматически преобразуется в

BEGIN
SELECT amount FROM accounts WHERE id = 1 FOR SHARE;
UPDATE  accounts SET amount = 1200 WHERE id = 1;
COMMIT;

С соответствующей проблемой возможных взаимных блокировок, рассмотренной ранее:

Запустим следующий пример и посмотрим, как поведут себя СУБД:

Ожидаемо, использование механизма 2PL в MySQL и SQL Server привело к дедлоку и откату одной из транзакций. Это важно учитывать при построении логики приложения и предусмотреть повторение запроса. Например, в event-based системах аналогичная логика часто применяется в клиентах брокеров сообщений, таких как Kafka и RabbitMQ. При возникновении ошибки в обработчике события его выполнение повторяется несколько раз, прежде чем сообщение перемещается в dead letter exchange в RabbitMQ или dead letter queue в Kafka. В случае обработки в реальном времени можно классифицировать подобные ошибки как требующие повторного вызова API при их возникновении.

В Oracle же и PostgreSQL никакого дедлока нет. Перед тем, как пояснить, почему это так, проведем еще раз во всех СУБД все тесты, начиная с Dirty Read и заканчивая Skewed Write. Результаты будут следующими:

По результату тестов видно, что Dirty read устраняется во всех СУБД. При этом у всех СУБД, кроме Mysql, результат тот же, что и на уровне Read Committed. В Mysql же теперь происходит deadlock вместо возврата запомненного значения. Так же изменилось поведение Mysql для Lost Update и Skewed Write 1 по сравнению с Repeatable read.

Phantom не создается во всех СУБД, так же, как и nonrepeatable read.

Как уже было сказано в разделе Repeatable Read/Snapshot isolation, Oracle использует MVCC на уровне изоляции Serializable, соответственно обеспечивая те же гарантии, что и Mysql/PostgreSQL на уровне Repeatable Read и SQLServer на уровне Snapshot isolation. Т.е. не предотвращает аномалию “Ассиметрия записи” и не поддерживает требования Стандарта. Об этом также пишет Т. Кайт в книге “Oracle для профессионалов” : “Уровень изолированности SERIALIZABLE в Oracle не означает, что все пользовательские транзакции дают такой же результат, как и при последовательном выполнении”

PostgreSQL же использует на данном уровне механизм cериализуемой изоляции снимков состояния (serializable snapshot isolation, SSI). SSI – метод, разработанный в 2008, который вообще не устанавливает блокировок. Вместо этого перед завершением любой транзакции база данных проверяет, не была ли нарушена изоляция, к примеру, не возник ли Write Skew или Lost Update. В случае обнаружения такой ситуации в PostgreSQL одна из транзакций откатывается с ошибкой “Error: could not serialize access due to read/write dependencies among transactions”(SQLSTATE '40001'). Предполагается, что такой подход дает большой прирост производительности ввиду отсутствия необходимости каждый раз устанавливать блокировки.

16. Выводы

По результату рассмотрения уровней изоляции я сделал следующие выводы:

1. Read UnCommitted – ненадежный уровень, при котором могут возникнуть критические ошибки консистентности данных в случае rollback транзакции. Подходит для ограниченного круга сценариев

2. Read Committed является оптимальным в плане упрощения логики приложения и производительности. Но необходимо понимать, какие запросы могут порождать аномалии и применять для них дополнительные инструменты вроде атомарной записи/явных блокировок.

3. Уровень изоляции Repeatable Read/Snapshot. Достаточно сложный уровень изоляции, учитывая то, что он призван бороться как с ситуациями, когда две параллельные транзакции изменили одну запись, так и с фантомами. Может использовать достаточно много блокировок, особенно в сложных агрегатных запросах. В зависимости от того, насколько правильно структурирована БД и сам запрос, может приводить к совершенно разным блокировкам, вплоть до лока всей таблицы как это было показано в примере с Write Skew 2 и Mysql(это справедливо для всех уровней изоляции, но для Repeatable Read в особенности). Может приводить к усложнению логики приложения для обработки дедлока(если конечно вы не придерживаетесь мнения, что все ошибки приложения это проблемы клиента). В общем случае не препятствует возникновению проблемы write skew и служит конкретной цели - чтобы SELECT одних и тех же строк в рамках транзакции выдавал один и тот же результат. Необходимость такого лично в моей практике встречалась нечасто.

4. Уровень изоляции Serializable. Якобы silver bullet – уровень изоляции по принципу выстрелил и забыл включил и забыл о проблемах параллельных транзакций. Но если посмотреть детальнее, есть ряд проблем. Oracle, к примеру, использует MVCC на этом уровне, т.е. соответсвует Repeatable Read в Mysql/PostgreSQL и Snapshot Isolation в SQLServer. MySQL, SQLServer применяют достаточно простой по сравнению с MVCC механизм 2PL, который тем не менее также потенциально может приводит к большому числу дедлоков в ситуации “тяжелый SELECT → Update”, которая на практике встречается довольно часто. PostgreSQL использует SSI. Заявлено, что он надежный. Но тут нет такого контроля за ситуацией, как блокировки, и если все же что – то пойдет не так – разобраться будет сложнее, чем при использовании 2PL. И во всех СУБД также, как и при использовании MVCC, надо вычислять ошибки, вызванные параллельным доступом и по-хорошему, штатно их обрабатывать. Если на уровне Read Committed еще можно все ошибки БД трактовать как FATAL, то на уровне Serializable повторения запроса должны быть как стандартная практика.

По результату тестирования составим таблицу, какие уровни изоляции в какой СУБД реализованы и какие рассмотренные аномалии предотвращают:

17. Тестирование производительности с помощью pgbench

В плане был сравнительный анализ производительности разных БД с использованием инструмента вроде HammerDB, но не получилось получить удовлетворительный детерминированный результат. Поэтому было принято решение сузить масштаб тестирования до конкретного инструмента, заточенного под конкретную СУБД – pgbench postgreSQL.

Тестирование производилось в докер – контейнере в 4 – процессорной системе с 16 Гб RAM, ОС Linux.
Тестируемые уровни изоляции: read committed, repeatable read, serializable. Для каждого уровня изоляции запускалось 4 теста следующей командой:

pgbench --username=postgres -h localhost postgres -c 10 -j 4 -T 600 -s 10 -f default.sql

Где -c 10 – количество клиентов
-j 4 – количество потоков
-T (сек) – время теста
-s – коэффициент масштаба(scaling factor)
default.sql – стандартный, встроенный для pgbench:
set aid random(1, 100000 * :scale)
set bid random(1, 1 * :scale)
set tid random(1, 10 * :scale)
set delta random(-5000, 5000)

BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

Результат работы каждого теста выглядел примерно следующим образом:

pgbench (15.6 (Debian 15.6-1.pgdg120+2))
transaction type: default.sql
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 4
maximum number of tries: 1
duration: 600 s
number of transactions actually processed: 1587206
number of failed transactions: 0 (0.000%)
latency average = 3.780 ms
initial connection time = 16.458 ms
tps = 2793.321677 (without initial connection time)

Результаты работы тестов:

По результатам видно, что tps(transaction per second) у Read Committed в среднем выше на 32-35%. Производительность repeatable read находится примерно на одно уровне с serializable, что неудивительно, учитывая, что MVCC и SSI основаны на одном подходе – snapshot isolation. Но все же производительность serializable стабильно ниже.

Некоторые авторы утверждают, что
поддержка высоких уровней изоляции
транзакций обходится слишком дорого из-за
возникающих проблем с производительностью
и доступностью. Мы полагаем, что лучше уж
разработчикам заниматься решением проблем
с производительностью при возникновении узких
мест из-за злоупотребления транзакциями, чем
постоянно создавать код для обхода проблем,
связанных с их отсутствием
Джеймс Корбетт и др. Spanner:
глобально-распределенная база данных
от компании Google (2012)

18. Список используемых при подготовке статьи источников

1. OLTP

2. ACID

3. ISO/IEC 9075 history

4. Transaction Isolation in PostgreSQL

5. ISO/IEC 9075:1999

6. Мартин Клеппман. Высоконагруженные приложения. Программирование, масштабирование, поддержка. Транзакции

7. Том Кайт: Oracle для профессионалов, 2003 год

8. Oracle Essentials, 5th Edition

9. A Critique of ANSI SQL Isolation Levels

10. Serializable Isolation for Snapshot Databases

11. Phil Bernstein, Vassos Hadzilacos, and Nathan Goodman "Concurrency Control and Recovery in Database Systems"

12. Блокировки в PostgreSQL: 1. Блокировки отношений

13. Cormen Th., Leiserson Ch. et al. - Introduction to Algorithms, 4th Edition

14. Concurrency is not parallelism

15. Transaction Isolation in Mysql