В любой реляционной базе данных транзакции играют важную роль в обеспечении целостности и согласованности данных. Однако, когда несколько транзакций выполняются одновременно, это может привести к различным проблемам конкурентности. Чтобы решить эти проблемы, SQL Server предоставляет различные уровни изоляции транзакций.
Проблемы конкурентности
Прежде чем погрузиться в детали уровней изоляции транзакций, давайте ознакомимся с некоторыми распространенными проблемами конкурентности:
- Потеря обновления и грязная запись: Это происходит, когда две транзакции получают доступ к одной и той же записи и обе ее обновляют. Одна транзакция перезаписывает изменения, внесенные другой, что приводит к потере данных.
- Грязное чтение: Грязное чтение происходит, когда транзакция читает данные, которые были изменены другой транзакцией, но еще не зафиксированы. Это может привести к несогласованным или неправильным данным.
- Неповторяющееся чтение: В этой ситуации транзакция читает одни и те же данные несколько раз в пределах одной транзакции, но полученные значения каждый раз разные. Это может привести к несогласованным или непредсказуемым результатам.
- Фантомное чтение: Фантомное чтение происходит, когда транзакция многократно читает набор строк, но результат изменяется между чтениями из-за других одновременных транзакций, изменяющих данные.
Уровни изоляции транзакций
Для решения этих проблем конкурентности SQL Server предоставляет различные уровни изоляции транзакций:
- Чтение незафиксированных данных: Это самый низкий уровень изоляции, при котором транзакции могут читать незафиксированные данные. Он позволяет грязное чтение, неповторяющееся чтение и фантомное чтение.
- Чтение зафиксированных данных: Это уровень изоляции по умолчанию в SQL Server. Он гарантирует, что транзакции читают только зафиксированные данные, предотвращая грязное чтение. Однако он все еще позволяет неповторяющееся чтение и фантомное чтение.
- Повторяемое чтение: Этот уровень изоляции гарантирует, что в пределах одной транзакции один и тот же запрос всегда вернет один и тот же набор результатов. Он предотвращает грязное чтение и неповторяющееся чтение, но фантомное чтение все еще может происходить.
- Сериализуемое: Это самый высокий уровень изоляции, который обеспечивает строгую согласованность данных. Он предотвращает грязное чтение, неповторяющееся чтение и фантомное чтение. Для этого он устанавливает блокировки на данные, что может повлиять на конкурентность.
Эксперименты в SQL Server
Чтобы лучше понять эти проблемы конкурентности и уровни изоляции транзакций, давайте проведем некоторые эксперименты с использованием SQL Server.
Потеря обновления:
В этом сценарии две параллельные транзакции пытаются одновременно обновить одну и ту же запись. Одна транзакция перезаписывает изменения, внесенные другой, что приводит к потере обновления. Вот пример:
CREATE TABLE BankAccounts (
AccountId INT IDENTITY (1, 1),
BalanceAmount INT
);
-- Сессия 1: Работодатель
BEGIN TRANSACTION;
SELECT @CustomerBalance = BalanceAmount FROM BankAccounts WHERE AccountId = 1;
SET @CustomerBalance = @CustomerBalance + @BalanceDifference;
UPDATE BankAccounts SET BalanceAmount = @CustomerBalance WHERE AccountId = 1;
COMMIT;
-- Сессия 2: Веб-реселлер
BEGIN TRANSACTION;
SELECT @CustomerBalance = BalanceAmount FROM BankAccounts WHERE AccountId = 1;
SET @CustomerBalance = @CustomerBalance + @BalanceDifference;
UPDATE BankAccounts SET BalanceAmount = @CustomerBalance WHERE AccountId = 1;
COMMIT;
Грязное чтение:
В этом сценарии транзакция читает данные, которые были изменены другой транзакцией, но еще не зафиксированы. Вот пример:
-- Сессия 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT COUNT(DISTINCT LastName) DistinctLastNameBeforeBeginTran FROM Person.Person WHERE FirstName = 'Aaron';
UPDATE Person.Person SET LastName = 'Hotchner' WHERE FirstName = 'Aaron';
SELECT COUNT(DISTINCT LastName) DistinctLastNameInTransaction FROM Person.Person WHERE FirstName = 'Aaron';
ROLLBACK TRANSACTION;
SELECT COUNT(DISTINCT LastName) DistinctLastNameAfterRollback FROM Person.Person WHERE FirstName = 'Aaron';
-- Сессия 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(DISTINCT LastName) SecondSessionResults FROM Person.Person WHERE FirstName = 'Aaron';
Неповторяющееся чтение:
В этом сценарии транзакция читает одни и те же данные несколько раз в пределах одной транзакции, но полученные значения каждый раз разные. Вот пример:
-- Сессия 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT TOP 5 FirstName, MiddleName, LastName, Suffix FROM Person.Person ORDER BY LastName;
WAITFOR DELAY '00:00:10.000';
SELECT TOP 5 FirstName, MiddleName, LastName, Suffix FROM Person.Person ORDER BY LastName;
COMMIT TRANSACTION;
-- Сессия 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
UPDATE Person.Person SET Suffix = 'Clothes' WHERE LastName = 'Abercrombie' AND FirstName = 'Kim';
COMMIT TRANSACTION;
Фантомное чтение:
В этом сценарии транзакция многократно читает набор строк, но результат изменяется между чтениями из-за других одновременных транзакций,