ReSQL.ru
     
powered by simpleCommunicator - 2.0.18     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вопрос по транзакциям
13 сообщений из 13, страница 1 из 1
Вопрос по транзакциям
    #40136342
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Здравствуйте!

Есть такой SQL-запрос
 
SQL
Код: 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.
DROP TABLE IF EXISTS dbo.MyTable1;
DROP PROCEDURE IF EXISTS dbo.MyProcedure1;
DROP TYPE IF EXISTS dbo.MyTableType1;

CREATE TABLE dbo.MyTable1
(
	SKU NVARCHAR(10),
	CONSTRAINT[uniqie_SKU] UNIQUE NONCLUSTERED (SKU ASC)
);

CREATE TYPE dbo.MyTableType1 AS TABLE
(    
    RowNumber INT,
	SKU nvarchar(10)
);

GO
CREATE PROCEDURE dbo.MyProcedure1
	@Type dbo.MyTableType1 READONLY
AS
BEGIN	
	DECLARE @Counter INT = 1;

	WHILE @Counter <= (SELECT MAX(RowNumber) FROM @Type)
		BEGIN
			BEGIN TRY
				BEGIN TRANSACTION
					INSERT dbo.MyTable1 (SKU) SELECT SKU FROM @Type WHERE RowNumber = @Counter;
				COMMIT TRANSACTION
			END TRY

			BEGIN CATCH
				ROLLBACK TRANSACTION
			END CATCH

			SET @Counter = @Counter + 1;
		END
END

GO

--Проверяем работу транзакции.
DECLARE @TableType dbo.MyTableType1;

INSERT INTO @TableType VALUES (1, 'SKU 1');
INSERT INTO @TableType VALUES (2, 'SKU 2');

EXECUTE dbo.MyProcedure1 @TableType;

SELECT * FROM dbo.MyTable1;

DELETE @TableType;

INSERT INTO @TableType VALUES (3, 'SKU 1');
INSERT INTO @TableType VALUES (4, 'SKU 3');

EXECUTE dbo.MyProcedure1 @TableType;

SELECT * FROM dbo.MyTable1;

Тут все ОК.

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

Пишу с использованием вложенных транзакции
 
SQL
Код: 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.
CREATE PROCEDURE dbo.MyProcedure1
	@Type dbo.MyTableType1 READONLY
AS
BEGIN	
	DECLARE @Counter INT = 1;
	DECLARE @Error_ BIT = 0;

	BEGIN TRANSACTION
	WHILE @Counter <= (SELECT MAX(RowNumber) FROM @Type)
		BEGIN
			BEGIN TRY
				BEGIN TRANSACTION
					INSERT dbo.MyTable1 (SKU) SELECT SKU FROM @Type WHERE RowNumber = @Counter;
				COMMIT TRANSACTION
			END TRY

			BEGIN CATCH
				SET @Error_ = 1;
				ROLLBACK TRANSACTION
			END CATCH

			SET @Counter = @Counter + 1;
		END

	IF @Error_ = 1
		ROLLBACK TRANSACTION
	ELSE
		COMMIT TRANSACTION

Выдает ошибку: "Запрос ROLLBACK TRANSACTION не имеет соответствующей инструкции BEGIN TRANSACTION."

Скажите, как правильно написать?
...
Рейтинг: 0 / 0
25.02.2022, 10:04
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136346
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
в SQL server нет вложенных транзакций (точнее они не совсем такие, как вы ожидаете)

rollback tran откатывает все открытые транзакции.

для понимания

Код: sql
1.
2.
3.
4.
5.
6.
7.
select @@trancount -- 0
begin tran
select @@trancount -- 1
begin tran
select @@trancount -- 2
rollback tran
select @@trancount -- 0
...
Рейтинг: 0 / 0
25.02.2022, 10:32
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136350
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
ferzmikk,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
CREATE PROCEDURE dbo.MyProcedure1
	@Type dbo.MyTableType1 READONLY
AS
BEGIN
SET XACT_ABORT ON;
DECLARE @TC INT = @@TRANCOUNT;

	BEGIN TRY
		INSERT dbo.MyTable1 (SKU) SELECT SKU FROM @Type;
	END TRY
	BEGIN CATCH
                 IF XACT_STATE() = -1 ROLLBACK TRANSACTION;
                 WHILE @@TRANCOUNT <> @TC BEGIN TRANSACTION; --хотя вот так делать не стоит, а лучше обрубать с ошибкой
                 PRINT ERROR_MESSAGE();
	END CATCH;
	
END;



для такой задачи даже транзакция явная не нужна
...
Рейтинг: 0 / 0
25.02.2022, 10:39
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136359
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
ferzmikk,

не существует транзакций "на уровне строк". Лучше бы Вам не фантазировать, а ознакомиться с документацией.
...
Рейтинг: 0 / 0
25.02.2022, 11:22
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136363
Gerasimenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gerasimenko
Участник
ferzmikk,

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

В данном конкретном случае очень удобно было бы использовать темповую таблицу
...
Рейтинг: 0 / 0
25.02.2022, 11:27
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136384
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Написал по другому: с использованием точки сохранения внутри транзакции.
 
SQL
Код: 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.
CREATE PROCEDURE dbo.MyProcedure1
	@Type dbo.MyTableType1 READONLY
AS
BEGIN	
	DECLARE @Counter INT = 1;
	DECLARE @Error_ BIT = 0;

	BEGIN TRANSACTION
	SAVE TRANSACTION a
	WHILE @Counter <= (SELECT MAX(RowNumber) FROM @Type)
		BEGIN
			BEGIN TRY
				SAVE TRANSACTION b
					INSERT dbo.MyTable1 (SKU) SELECT SKU FROM @Type WHERE RowNumber = @Counter;
				SAVE TRANSACTION c
			END TRY

			BEGIN CATCH
				SET @Error_ = 1;
				ROLLBACK TRANSACTION b
			END CATCH

			SET @Counter = @Counter + 1;
		END

	IF @Error_ = 1
		BEGIN
			ROLLBACK TRANSACTION a
		END
	
	COMMIT TRANSACTION	
END

Как бы работает.

В этом варианте есть то, что не учитывается?
...
Рейтинг: 0 / 0
25.02.2022, 12:00
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136392
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
У меня вопрос: нафига этот бред?

Что, религиозные убеждения не дозволяют написать Insert всей переданной таблицы?
...
Рейтинг: 0 / 0
25.02.2022, 12:16
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136395
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
aleks222,

Для данного примера это якобы бредовое решение. Специально упрощено, чтобы легко показать механизм решения. Но на самом деле сохраняется не в одну таблицу. Для каждой строки может быть либо Insert, либо Add, либо Update. И еще возвращает таблицу с полем ошибка (ведь в какой-то строке могла быть ошибка).
...
Рейтинг: 0 / 0
25.02.2022, 12:32
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136396
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Продолжайте изобретение непромокаемого пороха.
...
Рейтинг: 0 / 0
25.02.2022, 12:34
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136399
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
felix_ff,

Как вариант. Спасибо!
...
Рейтинг: 0 / 0
25.02.2022, 12:48
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136402
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
ferzmikk,

вы своим циклом по строкам напрасно жжете процессор.

а еще прикиньте что у вас в таблице будет распределение типа

Код: sql
1.
2.
3.
4.
5.
6.
declare @buffer dbo.MyTableType1;

insert into @buffer (rowNumber, SKU) values (100, '1'), (1000000, '2'), (2147483647, '3');

exec dbo.MyProcedure1
	@Type = @buffer;



сколько напрасных циклов приращения @counter у вас будет?
...
Рейтинг: 0 / 0
25.02.2022, 12:55
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136404
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
felix_ff,

Цикл то маленький.
...
Рейтинг: 0 / 0
25.02.2022, 13:01
| Ответить | Цитировать | Написать  
Вопрос по транзакциям
    #40136415
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
ferzmikk
felix_ff,

Цикл то маленький.


да ладно маленький, в таком варианте написания у вас проверка
@Counter <= (SELECT MAX(RowNumber) FROM @Type)
выполнится 2147483647 раз

если уж пишите цикл обрабатывающий каждую строку входной таблицы пишите или курсор или что то типа такого

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
DECLARE @RN INT = -2147483648;

	WHILE  (1=1) 
		BEGIN
                         SELECT @RN = MIN([RowNumber]) FROM @Type WHERE [RowNumber] > @RN;
                         IF @RN IS NULL BREAK;

			BEGIN TRY
				BEGIN TRANSACTION
					INSERT dbo.MyTable1 (SKU) SELECT SKU FROM @Type WHERE RowNumber = @RN;
				COMMIT TRANSACTION
			END TRY

			BEGIN CATCH
				ROLLBACK TRANSACTION
			END CATCH

		END
...
Рейтинг: 0 / 0
25.02.2022, 14:23
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вопрос по транзакциям
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (1): Анонимы (1)
Читали форум (2): Анонимы (1), Yandex Bot 1 мин.
Пользователи онлайн (7): Анонимы (5), Yandex Bot, Bing Bot 3 мин.
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
Start [/forum/topic.php?fid=46&tid=1683802&gotolast=1]:
 0ms
get settings:
 1ms
get forum list:
 6ms
check forum access:
 0ms
check topic access:
 0ms
track hit:
 24ms
get topic data:
 3ms
get forum data:
 0ms
get page messages:
 19ms
update_topic_read_status (1683802): 25.02.2022 14:23:15:
 0ms
get tp. blocked users:
 0ms
get online users:
 20ms
check new:
 1ms
others: 74ms
total:  148ms
созд. / загр.: 148ms (нв.: 1ms)
x
x
Закрыть


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