Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Подскажите, когда произойдет коммит транзакции? / 19 сообщений из 19, страница 1 из 1
22.04.2021, 10:24
    #40064654
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
Всем привет.

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

Если я сделаю insert в таблицу1, коммит этой транзакции произойдет после выполнения хранимки?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 11:07
    #40064669
Ведущий профессионал
Ведущий профессионал
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
teCa
Всем привет.

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

Если я сделаю insert в таблицу1, коммит этой транзакции произойдет после выполнения хранимки?
Коммит произойдёт после команды COMMIT. Если мы в режиме автокоммита, то транзакцией является одиночная инструкция "insert в таблицу1".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 11:41
    #40064677
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
Ведущий профессионал
teCa
Всем привет.

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

Если я сделаю insert в таблицу1, коммит этой транзакции произойдет после выполнения хранимки?
Коммит произойдёт после команды COMMIT. Если мы в режиме автокоммита, то транзакцией является одиночная инструкция "insert в таблицу1".


ноуп. хотите сказать что insert в таблицу2 тригерный вывалится из транзакции автокоммита?

действие транзакции будет распостраняться и на вставку триггера в таблицу2 и на запуск хранимой процедуры из триггера на второй таблице.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 11:47
    #40064682
Ведущий профессионал
Ведущий профессионал
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
felix_ff
Ведущий профессионал
пропущено...
Коммит произойдёт после команды COMMIT. Если мы в режиме автокоммита, то транзакцией является одиночная инструкция "insert в таблицу1".


ноуп. хотите сказать что insert в таблицу2 тригерный вывалится из транзакции автокоммита?

действие транзакции будет распостраняться и на вставку триггера в таблицу2 и на запуск хранимой процедуры из триггера на второй таблице.
Да, действие транзакции будет распространяться на всё. Напишите деление на ноль в конце ХП ради примера. Там произойдёт сбой при выполнении. И Вы увидите, что вся транзакция откатилась целиком.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 11:58
    #40064685
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
teCa,

неявные и явные транзакции фиксируются при выполнении COMMIT, автоматические - после завершения вставки или обновления. Триггер выполняется до выполнения фиксации. Как только закончат работы триггеры таблицы1 произойдет автоматическая фиксация всех изменений для текущего подключения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 12:17
    #40064691
Ведущий профессионал
Ведущий профессионал
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
Эксперимент провести не сложно:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
GO
CREATE PROCEDURE PTest16788
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @n INT = 3/0;
END;
GO
CREATE TABLE TTest16788
(
	Val1 INT
);
GO
CREATE TRIGGER TTest16788_AI
ON TTest16788
AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON;
	
	EXEC PTest16788;
END;
GO



Затем выполняете:
Код: sql
1.
INSERT TTest16788 VALUES(567);



Получаете сообщение об ошибке. Не смотря на то, что триггер был AFTER INSERT, сбой в хранимой процедуре привёл к откату транзакции целиком и вставка записи в таблицу также откатилась.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 12:30
    #40064696
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
Хранимку поместил в джоб, а в последний триггер поместил инструкцию запуска джоба. Костыль конечно, но мне нужно, что-бы хранимка отработала после фиксации изменений, всё завязать на триггеры не удалось(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 12:57
    #40064706
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
teCa,

Если произойдет откат, то процедура ничего не обнаружит в случае джоба, результат работы процедуры откатится в случае триггера. Итог будет одинаков, но при выполнении процедуры в триггере удержание блокировок будет более длительным.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 13:04
    #40064710
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
teCa
Хранимку поместил в джоб, а в последний триггер поместил инструкцию запуска джоба.
И получите ошибку, если это задание уже работает.

Все уже давно придумано - http://rusanu.com/2009/08/05/asynchronous-procedure-execution/
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 17:50
    #40064851
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
invm
teCa
Хранимку поместил в джоб, а в последний триггер поместил инструкцию запуска джоба.
И получите ошибку, если это задание уже работает.

Все уже давно придумано - http://rusanu.com/2009/08/05/asynchronous-procedure-execution/


Так и получилось:(

Если пользователь изменяет несколько атрибутов джоба, MSSQL делает это последовательно 1 изменение - 1 версия:
авторVersion 60 2021-04-22 10:48:32.947
Version 61 2021-04-22 10:48:32.963

в таблицу2 данные записываются в полном объеме, но как и предполагали, при записи версии 61 происходит ошибка, те джоб уже запущен.

Никогда не работал с асинхронным выполнением процедур, по данной статье сложно понять принципы работы, но ясно в какую сторону следует копать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2021, 23:56
    #40064971
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
teCa,

Вам нужно поведение атомарной транзакции, в mssql в явном виде такой конструкции нет.
Но ее поведение возможно смоделировать с некоторой пляской.

создаете линкованный сервер на самого себя
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
exec master.dbo.sp_addlinkedserver @server = N'LOOPBACK', @srvproduct = N'', @provider = N'SQLNCLI11', @datasrc = 'Data Source=(local);';

exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LOOPBACK', @userself = N'True', @locallogin = NULL, @rtmlogin = NULL, @rmtpassword = NULL;

exec master.dbo.sp_serveroption @server = N'LOOPBACK', @optname = N'data access', @optvalue=N'true';
exec master.dbo.sp_serveroption @server = N'LOOPBACK', @optname = N'rpc', @optvalue=N'true';
exec master.dbo.sp_serveroption @server = N'LOOPBACK', @optname = N'rpc out', @optvalue=N'true';
exec master.dbo.sp_serveroption @server = N'LOOPBACK', @optname = N'connect timeout', @optvalue=N'0';
exec master.dbo.sp_serveroption @server = N'LOOPBACK', @optname = N'query timeout', @optvalue=N'0';
exec master.dbo.sp_serveroption @server = N'LOOPBACK', @optname = N'remote proc transaction promotion', @optvalue=N'false';




в случае когда вам нужно запустить какую либо логику вне контекста существующей транзакции используете линк.
Код: sql
1.
2.
3.
4.
5.
6.
7.
create or alter procedure [dbo].[log]
   @spid int,
   @tran_id bigint,
   @rc bigint
as
insert into [dbo].[log] ([spid], [tran_id], [rows], [date], [log_tran_id]) values (@spid, @tran_id, @rc, GETDATE(), CURRENT_TRANSACTION_ID());
go



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
begin tran

declare @spid int, @rc bigint, @tran_id bigint;

set @spid = @@SPID;
set @tran_id = CURRENT_TRANSACTION_ID();
begin try
    insert into [dbo].[test_table] values (1), (2), (3);
    set @rc = ROWCOUNT_BIG();

    throw 50000, 'SOME EXTREMAL EXCEPTION', 16;
    if XACT_STATE() = 0 COMMIT TRANSACTION;
end try
begin catch
    exec [LOOPBACK].[db_name].[dbo].[log] @spid = @SPID, @tran_id = @tran_id, @rc = @rc;
    if XACT_STATE() <> 0 ROLLBACK TRANSACTION;
end catch;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 07:48
    #40065013
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
скип
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 13:03
    #40065165
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
felix_ff,

Пробую запустить хранимку на линкованном сервере, процесс инсерта в таблицу с триггером подвисает, инсерт ждет ответа от OLEDB, в то время, как выполнение хранимки заблокировано процессом вставки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 13:16
    #40065178
Ведущий профессионал
Ведущий профессионал
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
teCa
felix_ff,

Пробую запустить хранимку на линкованном сервере, процесс инсерта в таблицу с триггером подвисает, инсерт ждет ответа от OLEDB, в то время, как выполнение хранимки заблокировано процессом вставки.
Надо смотреть, какие блокировки поставлены. Блокировки удерживаются до завершение транзакции. Если другой процесс вступает в конфликт с этими блокировками, то всё повиснет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 13:40
    #40065194
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
teCa,

смотрите информацию по sys.dm_os_waiting_tasks where session_id in (<spid основного процесса>, <spid процесса линка>) какие ресурсы на ожидании.

архитекрутно вы не должны в процессе отдельной транзакции затрагивать данные к которым есть обращение из уже открытой транзакции.
я привел в пример процедурку логирования, но у меня там только вставка данных для лога. если у вас процедура запускающаяся на линке по логике сложнее, то меняйте архитектуру.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 14:50
    #40065225
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
Не совсем понятно, чего добивается автор. Триггер-триггер-процедура зачем?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 15:18
    #40065236
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
Владислав Колосов
Не совсем понятно, чего добивается автор. Триггер-триггер-процедура зачем?


Я пытаюсь организовать логирование изменений в джобах:

1. Триггер на таблице sysjobs мониторит изменение версии задания, если версия меняется, происходит запись в таблицу prepare_jobaudit. (id,login,job_id,actions,version_number,modif_datetime,status)
2. Далее, должна отработать хранимая процедура, которая сохранит в таблице лога "слепок" новых настроек задания из таблиц sysjobs+sysjobsteps

 
Код: 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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
insert into [admin].[dbo].[JobsAudit] ([Login]
	  ,[date]
      ,[job_id]
      ,[originating_server_id]
      ,[name]
      ,[enabled]
      ,[description]
      ,[start_step_id]
      ,[category_id]
      ,[owner_sid]
      ,[notify_level_eventlog]
      ,[notify_level_email]
      ,[notify_level_netsend]
      ,[notify_level_page]
      ,[notify_email_operator_id]
      ,[notify_netsend_operator_id]
      ,[notify_page_operator_id]
      ,[delete_level]
      ,[date_created]
      ,[date_modified]
      ,[version_number]
      ,[step_id]
      ,[step_name]
      ,[subsystem]
      ,[command]
      ,[flags]
      ,[additional_parameters]
      ,[cmdexec_success_code]
      ,[on_success_action]
      ,[on_success_step_id]
      ,[on_fail_action]
      ,[on_fail_step_id]
      ,[server]
      ,[database_name]
      ,[database_user_name]
      ,[retry_attempts]
      ,[retry_interval]
      ,[os_run_priority]
      ,[output_file_name]
      ,[step_uid])

SELECT @login
	  ,@date
	  ,@job_id
      ,b.[originating_server_id]
      ,b.[name]
      ,b.[enabled]
      ,b.[description]
      ,b.[start_step_id]
      ,b.[category_id]
      ,b.[owner_sid]
      ,b.[notify_level_eventlog]
      ,b.[notify_level_email]
      ,b.[notify_level_netsend]
      ,b.[notify_level_page]
      ,b.[notify_email_operator_id]
      ,b.[notify_netsend_operator_id]
      ,b.[notify_page_operator_id]
      ,b.[delete_level]
      ,b.[date_created]
      ,b.[date_modified]
      ,b.[version_number]
      ,[step_id]
      ,[step_name]
      ,[subsystem]
      ,[command]
      ,[flags]
      ,[additional_parameters]
      ,[cmdexec_success_code]
      ,[on_success_action]
      ,[on_success_step_id]
      ,[on_fail_action]
      ,[on_fail_step_id]
      ,[server]
      ,[database_name]
      ,[database_user_name]
      ,[retry_attempts]
      ,[retry_interval]
      ,[os_run_priority]
      ,[output_file_name]
      ,[step_uid]
  FROM [msdb].[dbo].[sysjobs] b left join [msdb].[dbo].[sysjobsteps] a on a.job_id=b.job_id where b.job_id=@job_id


1. Триггер1 - нужен для фиксации факта изменения в задании
2. Триггер2 - на таблице prepare_jobaudit должен после фиксации изменений в таблицах sysjobs и sysjobsteps и вызвать процедуру, которая сохранит новые настройки в логе.

Сейчас проблема как раз в том, как вызвать процедуру сразу после коммита изменений в таблицах sysjobs и sysjobsteps.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 15:27
    #40065239
Ведущий профессионал
Ведущий профессионал
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
teCa
Владислав Колосов
Не совсем понятно, чего добивается автор. Триггер-триггер-процедура зачем?


Я пытаюсь организовать логирование изменений в джобах:

1. Триггер на таблице sysjobs мониторит изменение версии задания, если версия меняется, происходит запись в таблицу prepare_jobaudit. (id,login,job_id,actions,version_number,modif_datetime,status)
2. Далее, должна отработать хранимая процедура, которая сохранит в таблице лога "слепок" новых настроек задания из таблиц sysjobs+sysjobsteps

 
Код: 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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
insert into [admin].[dbo].[JobsAudit] ([Login]
	  ,[date]
      ,[job_id]
      ,[originating_server_id]
      ,[name]
      ,[enabled]
      ,[description]
      ,[start_step_id]
      ,[category_id]
      ,[owner_sid]
      ,[notify_level_eventlog]
      ,[notify_level_email]
      ,[notify_level_netsend]
      ,[notify_level_page]
      ,[notify_email_operator_id]
      ,[notify_netsend_operator_id]
      ,[notify_page_operator_id]
      ,[delete_level]
      ,[date_created]
      ,[date_modified]
      ,[version_number]
      ,[step_id]
      ,[step_name]
      ,[subsystem]
      ,[command]
      ,[flags]
      ,[additional_parameters]
      ,[cmdexec_success_code]
      ,[on_success_action]
      ,[on_success_step_id]
      ,[on_fail_action]
      ,[on_fail_step_id]
      ,[server]
      ,[database_name]
      ,[database_user_name]
      ,[retry_attempts]
      ,[retry_interval]
      ,[os_run_priority]
      ,[output_file_name]
      ,[step_uid])

SELECT @login
	  ,@date
	  ,@job_id
      ,b.[originating_server_id]
      ,b.[name]
      ,b.[enabled]
      ,b.[description]
      ,b.[start_step_id]
      ,b.[category_id]
      ,b.[owner_sid]
      ,b.[notify_level_eventlog]
      ,b.[notify_level_email]
      ,b.[notify_level_netsend]
      ,b.[notify_level_page]
      ,b.[notify_email_operator_id]
      ,b.[notify_netsend_operator_id]
      ,b.[notify_page_operator_id]
      ,b.[delete_level]
      ,b.[date_created]
      ,b.[date_modified]
      ,b.[version_number]
      ,[step_id]
      ,[step_name]
      ,[subsystem]
      ,[command]
      ,[flags]
      ,[additional_parameters]
      ,[cmdexec_success_code]
      ,[on_success_action]
      ,[on_success_step_id]
      ,[on_fail_action]
      ,[on_fail_step_id]
      ,[server]
      ,[database_name]
      ,[database_user_name]
      ,[retry_attempts]
      ,[retry_interval]
      ,[os_run_priority]
      ,[output_file_name]
      ,[step_uid]
  FROM [msdb].[dbo].[sysjobs] b left join [msdb].[dbo].[sysjobsteps] a on a.job_id=b.job_id where b.job_id=@job_id


1. Триггер1 - нужен для фиксации факта изменения в задании
2. Триггер2 - на таблице prepare_jobaudit должен после фиксации изменений в таблицах sysjobs и sysjobsteps и вызвать процедуру, которая сохранит новые настройки в логе.

Сейчас проблема как раз в том, как вызвать процедуру сразу после коммита изменений в таблицах sysjobs и sysjobsteps.
Не понятно, зачем именно после коммита. Почему не до?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 15:30
    #40065242
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, когда произойдет коммит транзакции?
teCa,

Нужно делать таблицы аудита отдельно для sysjobjs, sysjobsteps, sysjobschedules и т.д.
Во-первых, это решит вашт проблемы.
Во-вторых, не обязательно изменять разные параметры задания одной логической операцией.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Подскажите, когда произойдет коммит транзакции? / 19 сообщений из 19, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 144ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1684780]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 59ms
get tp. blocked users:
 2ms
others: 11ms
total:  144ms
x
x
Закрыть


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