Sql transaction sql server: BEGIN TRANSACTION (Transact-SQL) — SQL Server

Содержание

SQL. Ошибка. Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Введение

В этой статье мы разберем что такое дедлок, откуда берутся дедлоки и как решать проблемы дедлоков.

Данная ошибка возникает, когда два сеанса Sql Server обращаются к одним и тем же ресурсам, но в разном порядке. Следовательно, они оба ждут сеанса процесса, что является тупиком. В результате ни один из сеансов не может быть завершен, и SQL избавляется от тупика автоматически, выбирая один из сеансов в качестве жертвы и уничтожая его, позволяя продолжить другому сеансу. Как правило, жертвой является сеанс, чей откат наименее затратный.

Если SQL не знает как разобраться с подобной ситуацией взаимных блокировок и уходит в ожидание, то возникает дедлок (deadlock).

Основные способы избегания взаимоблокировок

1. Убедитесь, что процессы обращаются ко всем общим объектам в одном и том же порядке.

Рассмотрим процесс:

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

Изменить порядок операторов, в котором объекты и ресурсы базы данных должны быть доступны процессам – это хороший пример

2. Сделайте транзакции короткими и простыми

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

3. Убедитесь, что процессы используют минимально необходимый уровень изоляции транзакций.

Чем ниже уровень изоляции, тем меньше вероятность возникновения взаимоблокировок (хотя и выше вероятность нарушения целостности данных).

Самую высокую скорость выполнения и самую низкую согласованность имеет уровень read uncommitted (взаимоблокировки отсутствуют). На этом уровне каждая транзакция видит незафиксированные изменения другой транзакции (феномен грязного чтения). В SQL Server вы можете минимизировать конкуренцию за блокировку, одновременно защищая транзакции от грязного чтения или незафиксированных изменений данных, используя уровень read commited. Самую низкую скорость выполнения и самую высокую согласованность — serializable.

Установить уровень транзакции:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Проверить уровень транзакции :

select transaction_isolation_level from sys.dm_exec_sessions where session_id = Process_ID*

Если вы не можете избежать взаимоблокировок, есть возможность указать, какой процесс должен умереть при возникновении взаимоблокировки.

SQL Server выбирает жертву взаимоблокировки на основе двух факторов: DEADLOCK_PRIORITY, установленного для каждого сеанса, и объема работы, которую SQL Server должен выполнить для отката транзакции.

Параметр DEADLOCK_PRIORITY может быть установлен пользователем на HIGH, NORMAL, LOW или на целочисленное значение от -10 до 10. По умолчанию DEADLOCK_PRIORITY установлено на NORMAL (0)

SET DEADLOCK_PRIORITY HIGH; GO

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

SELECT session_id, DEADLOCK_PRIORITY FROM sys.dm_exec_sessions WHERE SESSION_ID = @@SPID

Подробнее об ошибке

HOWTO по дедлокам

Как понять есть ли дедлок сейчас в базе?

Через запрос:

SELECT * FROM sys.sysprocesses
WHERE blocked > 0

Через интерфейс:

Для этого в SQL SERVER перейдите в «Управление»> «Расширенные события»> «Сеансы»> «system_health»> «package0.event_file» и нажмите «Просмотреть целевые данные…»

Результат:

Как найти на какие именно таблицы висят дедлоки?

На рисунке выше график тупиков, состоящий из разделов ресурсов и процессов.

В разделе Ресурсы отображаются списки всех ресурсов, попавших в тупик, параметр objectname — таблица, в которой произошла взаимоблокировка.

* Примечание: Process ID указан в сообщении об ошибке

транзакций в SQL Server для начинающих

В этой статье мы поговорим об основных деталях транзакций в SQL Server.

Введение

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

Основная идея транзакций заключается в том, что когда каждый из операторов возвращает ошибку, все изменения
откат для обеспечения целостности данных. С другой стороны, если все операторы выполнены успешно, данные
изменения станут постоянными в базе данных. В результате, если у нас возникнут какие-либо отключения электроэнергии или другие
проблемы при снятии денег в банкомате, транзакции гарантируют стабильность нашего баланса. Это было бы
лучший способ выполнить все эти шаги через транзакцию, потому что четыре основных свойства транзакций
сделать все операции более точными и последовательными. Все эти свойства известны как КИСЛОТА
(атомарность, непротиворечивость, изоляция, устойчивость) в системах реляционных баз данных с первой буквой их
имена.

A tomicity: Все операции, включенные в выполненную транзакцию
успешно. В противном случае все операции отменяются в момент сбоя и все предыдущие операции
откатываются
С onsistency: Это свойство гарантирует, что все данные будут согласованными после
транзакция завершается в соответствии с определенными правилами, ограничениями, каскадами и триггерами
Изоляция: Все транзакции изолированы от других транзакций
Durable : модификация совершенных транзакций сохраняется в базе данных.

Предварительные требования

В этой статье мы создадим образец таблицы с помощью следующего запроса и заполним некоторые образцы данных.

110003

1999911110001

9000 2

9000 3

9000 3 9000 3 9000 2 9000 2

14 9000 3

9000 3

9000 2

9000 3 9000 3 9000 3

Создать таблицу человека (

Personid int Первичный ключ идентификация (1,1),

Lastname varchar (255),

FirstName varchar (255),

Адрес Varchar (255),

City Varchar (255),

AGE INT

)

Вставка в значения лица («Hayes», «Кори», «123 Wern Ddu Lane», «Lustleigh», 23)

Вставка в значения лица (» ‘ Macdonald», «Charlie», «23 Peachfield Road», «CEFN EINION», 45)

ВСТАВИТЬ В Person VALUES(‘Frost’,’Emma’,’85 Kingsway North’,’HOLTON’,26)

ВСТАВИТЬ В Person VALUES(‘Thomas’, ‘Tom’,’59 Dover Road’, ‘WESTER GRUINARDS’,51)

ВСТАВИТЬ В Person VALUES(‘Baxter’,’Cameron’,’106 Newmarket Road’,’HAWTHORPE’,46)

ВСТАВИТЬ В Person VALUES(‘Townsend’,’Imogen ‘,’100 Shannon Way’,’CHIPPENHAM’,20)

INSERT INTO Person VALUES(‘Preston’,’Taylor’,’14 Pendwyallt Road’,’BURTON’,19)

INSERT INTO Person VALUES(‘Townsend’,’Imogen’ ,’100 Шеннон Уэй’,’ЧИППЕНХЭМ’,18)

ВСТАВИТЬ В Person VALUES(‘Хан’,’Джейкоб’,’72 Ballifeary Road’,’BANCFFOSFELEN’,11)

Режимы транзакций в SQL Server

SQL Server может работать в трех различных режимах транзакций, а именно:

Транзакция Autocommit Режим является транзакцией по умолчанию для SQL Server. В этом режиме каждый
Оператор T-SQL оценивается как транзакция, и они фиксируются или откатываются в соответствии с их результатами.
Успешные операторы фиксируются, а неудачные операторы немедленно откатываются.
Режим неявной транзакции позволяет SQL Server запускать неявную
транзакция для каждого оператора DML, но нам нужно явно использовать команды фиксации или отката в конце
заявлений
Явная транзакция Режим позволяет точно определить транзакцию с началом и окончанием
точки сделки

Как определить неявную транзакцию в SQL Server

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

Совет: Функция @@TRANCOUNT возвращает количество операторов BEGIN TRANSACTION в текущем сеансе, и мы можем использовать эту функцию для подсчета количества открытых локальных транзакций в примерах

110003

119991110001

9000 2

9000 3

9000 3 9000 3 9000 2 9000 2

14 9000 3

9000 3

9000 2

1999911111000 3

14 9000 3

Установите ITICAIT_TRANSACTIONS на

Обновление

человек

SET

LASTNAME = ‘SAWYER’,

FirstName = ‘Tom’

, где

Personid = 2

Выберите

IIF (@@ Options & 2 = 2,

‘режим неявной транзакции на’,

‘режим неявной транзакции’

) в качестве «режим транзакции»

Select

@@ Trancount As OpentRansactions

COMMIT TRAN

SELECT

@@TRANCOUNT AS OpenTransactions

Оператор COMMIT TRANSACTION применяет изменения данных к базе данных, и измененные данные становятся постоянными.

Как определить явную транзакцию в SQL Server

Чтобы определить явную транзакцию, мы начинаем использовать команду BEGIN TRANSACTION , потому что
этот оператор определяет начальную точку явной транзакции. Он имеет следующий синтаксис:

НАЧАТЬ ТРАНЗАКЦИЮ [ {имя_транзакции | @tran_name_variable }

[С ПОМЕТКОЙ [‘описание’]]]

transaction_name Параметр используется для присвоения транзакциям определенного имени
@trans_var option — определяемая пользователем переменная, которая используется для хранения имени транзакции.
Параметр WITH MARK позволяет отметить конкретную транзакцию в файле журнала

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

Begin Tran

Обновление человека

SET LASTNAME = ‘Lucky’,

FirstName = ‘Luke’

, где Personid = 1

SELECT @@ Trancount As OpentRansactions

Как мы заявили в предыдущем разделе, оператор COMMIT TRAN применяет данные
изменения в базе данных, и измененные данные станут постоянными. Теперь давайте завершим открытую транзакцию с
Оператор COMMIT TRAN.

Begin Tran

Обновление человека

Set Lastname = ‘Lucky’,

FirstName = ‘Luke’

, где PersonId = 1

SELECT @@ Trancount As Opentransactions

Commit Tran

SELECT @@ Trancount As opentransactions

С другой стороны, оператор ROLLBACK TRANSACTION помогает отменить все изменения данных, которые
применяются транзакцией. В следующем примере мы изменим конкретную строку, но эта модификация данных
не будет сохраняться.

Begin Tran

Обновление человека

Set Lastname = ‘Donald’,

FirstName = ‘Duck’, где Personid = 2

Select * из человека, где Personid = 2

Rollback Tran

ВЫБЕРИТЕ * ОТ человека, ГДЕ PersonID=2

В следующей таблице показана структура явных транзакций в SQL Server.

НАЧАТЬ СДЕЛКУ	Начальная точка сделки
SQL-команды	Операторы DML и SELECT
СОВЕРШИТЬ ТРАНЗАКЦИЮ или ОТМЕНИТЬ ТРАНЗАКЦИЮ	Применить изменение данных к базе данных или Удалить изменение данных в базе данных

Сохранить баллы в транзакциях

Точки сохранения можно использовать для отката любой конкретной части транзакции, а не всей транзакции. Так
что мы можем откатить любую часть транзакции только между точкой сохранения и до отката.
команда. Чтобы определить точку сохранения в транзакции, мы используем СОХРАНИТЬ ТРАНЗАКЦИЮ синтаксис, а затем мы добавляем
имя точки сохранения. Теперь давайте проиллюстрируем пример использования точки сохранения. Когда мы выполним следующий запрос,
только оператор вставки будет зафиксирован, а оператор удаления будет отменен.

НАЧАТЬ ТРАНЗАКЦИЮ

INSERT INTO Person

VALUES(‘Mouse’, ‘Micky’,’500 South Buena Vista Street, Burbank’,’California’,43) FROM Person

ROLLBACK TRANSACTION InsertStatement

COMMIT

SELECT * FROM Person

Автоматический откат транзакций в SQL Server

Как правило, транзакции включают более одного запроса. Таким образом, если один из операторов SQL возвращает
error все модификации стираются, а остальные операторы не выполняются. Этот процесс называется
Автоматический откат Транзакция в SQL. Теперь давайте объясним этот принцип очень
простой пример.

BEGIN TRAN

INSERT INTO Person

VALUES(‘Bunny’, ‘Bugs’,’742 Evergreen Terrace’,’Springfield’,54)

UPDATE Person SET Age=’MiddleAge0003

SELECT * FROM Person

COMMIT TRAN

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

Отмеченные транзакции в SQL Server

SQL Server позволяет нам помечать и добавлять описание к конкретной транзакции в файлах журнала. Таким образом, мы можем
создать точку восстановления, которая не зависит от времени. Например, когда происходит случайное изменение данных
в базе данных, и мы не знаем точное время модификации данных, усилия по восстановлению данных могут быть предприняты
много времени. По этой причине помеченные транзакции могут быть полезным решением для определения точного времени данных.
модификации. Чтобы создать отмеченную транзакцию, нам нужно дать имя транзакции, а также нам нужно
добавить С ОТМЕТКОЙ синтаксис. В следующем запросе мы удалим некоторые строки, а также отметим
изменения в файле журнала.

BEGIN TRAN DeletePerson WITH MARK ‘MarkedTransactionDescription’

DELETE Person WHERE PersonID МЕЖДУ 3 И 4

COMMIT TRAN DeletePerson

В таблице logmarkhistory хранятся сведения о каждой отмеченной транзакции, которая была зафиксирована и
он находится в база данных msdb .

ВЫБЕРИТЕ * ИЗ msdb.dbo.logmarkhistory

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

STOPATMARK выполняет накат до отметки и включает отмеченную транзакцию в накат
STOPBEFOREMARK выполняет накат до отметки и исключает отмеченную транзакцию из наката

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

Восстановление данных из журнала транзакций SQL Server
Восстановление связанных баз данных, содержащих отмеченную транзакцию

Вывод

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

Автор
Последние сообщения

Esat Erkec

Esat Erkec — специалист по SQL Server, который начал свою карьеру более 8 лет назад в качестве разработчика программного обеспечения. Он является сертифицированным экспертом по решениям Microsoft для SQL Server.

Большая часть его карьеры была посвящена администрированию и разработке баз данных SQL Server. Его текущие интересы связаны с администрированием баз данных и бизнес-аналитикой. Вы можете найти его в LinkedIn.

Просмотреть все сообщения от Esat Erkec

Последние сообщения от Esat Erkec (посмотреть все)

tsql — Лучший способ работы с транзакциями в MS SQL Server Management Studio

спросил
13 лет, 11 месяцев назад

Изменено
16 дней назад

Просмотрено
182к раз

Допустим, у меня есть оператор SQL, который синтаксически и семантически корректен, поэтому он выполняется.

В Management Studio (или любом другом инструменте запросов) как я могу протестировать операторы SQL, и если я замечу, что они что-то сломали, откат (в отдельном запросе?) проще всего обернуть свой код в транзакцию, а затем выполнять каждую партию кода T-SQL построчно.

Например,

 Начать транзакцию
         -Выполните здесь несколько запросов T-SQL.
Откатить транзакцию -- ИЛИ зафиксировать транзакцию

Если вы хотите включить обработку ошибок, вы можете сделать это с помощью блока TRY…CATCH. В случае возникновения ошибки вы можете откатить транзакцию в блоке catch.

Например:

 ИСПОЛЬЗОВАТЬ AdventureWorks;
ИДТИ
НАЧАТЬ СДЕЛКУ;
НАЧАТЬ ПОПРОБУЙТЕ
    -- Генерировать ошибку нарушения ограничения.
    УДАЛИТЬ ИЗ Production.Product
    ГДЕ ProductID = 980;
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВИТЬ
    ВЫБРАТЬ
        ERROR_NUMBER() КАК ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() КАК ErrorState
        ,ERROR_PROCEDURE() КАК ErrorProcedure
        ,ERROR_LINE() КАК ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    ЕСЛИ @@TRANCOUNT > 0
        ОТКАТ СДЕЛКИ;
КОНЦЕВОЙ ЗАХВАТ;
ЕСЛИ @@TRANCOUNT > 0
    СОВЕРШИТЬ СДЕЛКУ;
ИДТИ

Подробнее см.