ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
12 сообщений из 12, страница 1 из 1
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639286
Фотография Mandarin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mandarin
Участник
Всем привет!
Версия 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
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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
...
Рейтинг: 0 / 0
03.05.2018, 12:09
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639330
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Mandarin,

пример 1 и 3 - это одно и то же.
...
Рейтинг: 0 / 0
03.05.2018, 12:42
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639336
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
Mandarin,

Есть ли индексы на f2?
У вас с таким кодом могут открытые транзакции остаться, в инете полно "шаблонов" для хранимок, типо http://jackworthen.com/2015/10/29/a-good-stored-procedure-template/
...
Рейтинг: 0 / 0
03.05.2018, 12:46
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639340
Фотография Mandarin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mandarin
Участник
aleksrov,

Индексы в таблицах есть, и кластерные (первичный ключ, int автоинкрементный) и не кластерные.
...
Рейтинг: 0 / 0
03.05.2018, 12:51
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639456
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
MandarinВозможно я ошибаюсь, подскажите как правильно?


1. Чем крупнее транзакция - тем больше вероятность смертельных блокировок.
2. Поэтому, если целостность не нужна - транзакция тоже не нужна.
3. Все остальное решается по графу смертельной блокировки.
4. Если не решается - изучаем понятие критической секции - ваяем критическую секцию.
...
Рейтинг: 0 / 0
03.05.2018, 14:47
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639604
Фотография Mandarin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mandarin
Участник
aleks222MandarinВозможно я ошибаюсь, подскажите как правильно?


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

Если целостность не нужна, как отказаться от транзакции в хранимой процедуре (Пример 1)
Если совсем отказаться от транзакции нельзя, то какой уровень транзакции надо задать чтобы максимально снизить возможность блокировок?
...
Рейтинг: 0 / 0
03.05.2018, 17:55
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639632
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
Mandarin,

Уровень изоляции ставится в зависимости от требований системы (т.е. к примеру допускаются ли фантомные строки), а не для того чтобы избавиться от блокировок.
Смотрите граф дедлока в общем.
...
Рейтинг: 0 / 0
03.05.2018, 19:07
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639838
Фотография Mandarin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mandarin
Участник
aleksrov,

Предположим посмотрел я граф дедлоков, увидел там что proc1 блокирует proc2, это я и без графа знал, дальше то что ? :)
...
Рейтинг: 0 / 0
04.05.2018, 10:11
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639901
Фотография Yasha123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yasha123
Участник
Mandarinaleksrov,

Предположим посмотрел я граф дедлоков, увидел там что proc1 блокирует proc2, это я и без графа знал, дальше то что ? :)
неправильно посмотрел.
в графе есть resource list,
где перечислены ресурсы, и к каждому указан owner и waiter
...
Рейтинг: 0 / 0
04.05.2018, 11:02
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39639906
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Mandarin,

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

Измените архитектуру базы так, чтобы максимально избежать обновлений строк, которые участвуют в поиске, например.
...
Рейтинг: 0 / 0
04.05.2018, 11:18
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39640044
Фотография Mandarin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mandarin
Участник
Владислав Колосов,

Спасибо за советы, буду пробовать.
Подскажите ещё один момент, если я вызываю одну хранимую процедуру из другой, то каждый вызов хранимки будет в отдельной транзакции?
...
Рейтинг: 0 / 0
04.05.2018, 14:01
| Ответить | Цитировать | Написать  
Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
    #39640210
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
MandarinВладислав Колосов,

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

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

Это воля "программизда".
...
Рейтинг: 0 / 0
04.05.2018, 16:45
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите пожалуйста понять, как работают транзакции в хранимых процедурах.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]