Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вопрос по транзакциям / 13 сообщений из 13, страница 1 из 1
25.02.2022, 10:04
    #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:32
    #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:39
    #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, 11:22
    #40136359
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по транзакциям
ferzmikk,

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

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

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

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

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

Как вариант. Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.02.2022, 12:55
    #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, 13:01
    #40136404
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по транзакциям
felix_ff,

Цикл то маленький.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.02.2022, 14:23
    #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
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вопрос по транзакциям / 13 сообщений из 13, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 639ms
Закрыть
Start [/forum/topic.php?fid=46&mobile=1&tid=1683802]:
 0ms
get settings:
 25ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 44ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 249ms
get tp. blocked users:
 1ms
others: 286ms
total:  639ms
x
x
Закрыть


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