Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
Всем привет!
Версия SQL:

Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
Aug 15 2017 10:23:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Есть многопользовательская OLTP система. Часто возникают конфликты транзакций.
Текст примерно такой: "Транзакция (идентификатор процесса XXX) вызвала взаимоблокировку ресурсов блокировка с другим процессом и стала жертвой взаимоблокировки. Запустите транзакцию повторно."

Скорее всего это из-за того, что я не правильно понимаю как работают транзакции в хранимых процедурах, по этому SQL в хранимках пишу не правильно.

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

Блокировки появляются в "сложных" хранимках, где есть несколько update. Ниже я привёл три примера. Первый пример - как есть сейчас. Остальные, наверное, как должно быть.
Обёртку в транзакции я сделал не для того, чтобы сохранялась целостность операции, а для того чтобы избежать блокировок. Возможно я ошибаюсь, подскажите как правильно?

Пример 1
CREATE PROCEDURE myProc
AS
BEGIN
 -- без явного указания транзакций
 SET NOCOUNT ON;

 update myTable set 
  f1 = 1
 where f2 = 1

 update myTable set 
  f1 = 2
 where f2 = 2

 update myTable set 
  f1 = 3
 where f2 = 3

END


Пример 2
CREATE PROCEDURE myProc
AS
BEGIN
 -- одна транзакция на всю процедуру
 SET NOCOUNT ON;
 
 begin tran 

  update myTable set 
   f1 = 1
  where f2 = 1

  update myTable set 
   f1 = 2
  where f2 = 2

  update myTable set 
   f1 = 3
  where f2 = 3
 
 commit

END


Пример 3
CREATE PROCEDURE myProc
AS
BEGIN
 -- отдельная транзакция для каждого update
 SET NOCOUNT ON;

 begin tran 
  update myTable set 
   f1 = 1
  where f2 = 1
 commit

 begin tran
  update myTable set 
   f1 = 2
  where f2 = 2
 commit

 begin tran
  update myTable set 
   f1 = 3
  where f2 = 3
 commit

END
3 май 18, 12:09    [21383959]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
Mandarin,

пример 1 и 3 - это одно и то же.
3 май 18, 12:42    [21384089]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Mandarin,

Есть ли индексы на f2?
У вас с таким кодом могут открытые транзакции остаться, в инете полно "шаблонов" для хранимок, типо http://jackworthen.com/2015/10/29/a-good-stored-procedure-template/
3 май 18, 12:46    [21384103]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
aleksrov,

Индексы в таблицах есть, и кластерные (первичный ключ, int автоинкрементный) и не кластерные.
3 май 18, 12:51    [21384119]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
aleks222
Member

Откуда:
Сообщений: 847
Mandarin
Возможно я ошибаюсь, подскажите как правильно?


1. Чем крупнее транзакция - тем больше вероятность смертельных блокировок.
2. Поэтому, если целостность не нужна - транзакция тоже не нужна.
3. Все остальное решается по графу смертельной блокировки.
4. Если не решается - изучаем понятие критической секции - ваяем критическую секцию.
3 май 18, 14:47    [21384595]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
aleks222
Mandarin
Возможно я ошибаюсь, подскажите как правильно?


1. Чем крупнее транзакция - тем больше вероятность смертельных блокировок.
2. Поэтому, если целостность не нужна - транзакция тоже не нужна.
3. Все остальное решается по графу смертельной блокировки.
4. Если не решается - изучаем понятие критической секции - ваяем критическую секцию.


Если целостность не нужна, как отказаться от транзакции в хранимой процедуре (Пример 1)
Если совсем отказаться от транзакции нельзя, то какой уровень транзакции надо задать чтобы максимально снизить возможность блокировок?
3 май 18, 17:55    [21385542]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Mandarin,

Уровень изоляции ставится в зависимости от требований системы (т.е. к примеру допускаются ли фантомные строки), а не для того чтобы избавиться от блокировок.
Смотрите граф дедлока в общем.
3 май 18, 19:07    [21385708]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
aleksrov,

Предположим посмотрел я граф дедлоков, увидел там что proc1 блокирует proc2, это я и без графа знал, дальше то что ? :)
4 май 18, 10:11    [21386516]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Mandarin
aleksrov,

Предположим посмотрел я граф дедлоков, увидел там что proc1 блокирует proc2, это я и без графа знал, дальше то что ? :)

неправильно посмотрел.
в графе есть resource list,
где перечислены ресурсы, и к каждому указан owner и waiter
4 май 18, 11:02    [21386707]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
Mandarin,

проблема не в транзакции, а в порядке и типе наложения блокировок. Сервер пишет "транзакция", чтобы вы, при желании, могли бы понять - какая именно транзакция не была зафиксирована из-за конфликта блокировок. Ищите, где потеряли, а не там, где светло.

Измените архитектуру базы так, чтобы максимально избежать обновлений строк, которые участвуют в поиске, например.
4 май 18, 11:18    [21386741]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
Владислав Колосов,

Спасибо за советы, буду пробовать.
Подскажите ещё один момент, если я вызываю одну хранимую процедуру из другой, то каждый вызов хранимки будет в отдельной транзакции?
4 май 18, 14:01    [21387390]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.  [new]
aleks222
Member

Откуда:
Сообщений: 847
Mandarin
Владислав Колосов,

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


Процедура не обязана выполняться в транзакции.

Это воля "программизда".
4 май 18, 16:45    [21388091]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить