Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Заблокировать строки для архивации / 25 сообщений из 43, страница 1 из 2
  1  все
26.05.2020, 11:45
    #39962100
Кнюпель
Кнюпель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Насколько правильно делать таким образом для архивации строк:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
set transaction isolation level serializable
begin tran

insert into [dbo].[History]
select * from [dbo].[HotTable] with (xlock) WHERE TypeId = @typeId

delete from [dbo].[HotTable] WHERE TypeId = @typeId

commit



В данной транзакции надо гарантировать, что из HotTable будет удалено только то, что уехало в историю. Т.е. надо недопустить что-бы посредине транзакции кто-то добавил запись с текущим @typeId.
Дополнительный вопрос - нормально ли использовать курсор для таких вещей, в HotTable содержится много typeId, их всех надо отправить в архив, но транзакционная согласованность нужна только по каждому конкретному типу. Я вижу курсор как единственный путь пройтись по всем типам (где я их получу используя SELECT DISTINCT)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.05.2020, 11:59
    #39962116
Гавриленко Сергей Алексеевич
Гавриленко Сергей Алексеевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
delete + output
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.05.2020, 12:03
    #39962118
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель
В данной транзакции надо гарантировать, что из HotTable будет удалено только то, что уехало в историю. Т.е. надо недопустить что-бы посредине транзакции кто-то добавил запись с текущим @typeId.


UPD: перечитал, что вам нужно. serializable вам не нужен

Да, для этого нужно использовать serializable, но код можно упростить (убрав явную транзакцию и объединив insert и delete)

Код: sql
1.
2.
3.
4.
5.
delete 
output deleted.*
into [dbo].[History]
from [dbo].[HotTable]  
WHERE TypeId = @typeId


лучше перечислить список полей как в output так и into



Кнюпель
Дополнительный вопрос - нормально ли использовать курсор для таких вещей, в HotTable содержится много typeId, их всех надо отправить в архив, но транзакционная согласованность нужна только по каждому конкретному типу. Я вижу курсор как единственный путь пройтись по всем типам (где я их получу используя SELECT DISTINCT)


Курсор (или любой другой цикл) по @typeId поможет сократить время транзакции, но может увеличить общее время всей операции переноса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.05.2020, 12:26
    #39962136
a_voronin
a_voronin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
msLex,

а какие за и против того, чтобы просто триггер навесить на таблицу?

https://stackoverflow.com/questions/11890868/how-to-store-historical-records-in-a-history-table-in-sql-server
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.05.2020, 12:38
    #39962153
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель,

Я бы сделал так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
-- Собираем типы для удаления
CREATE TABLE #TypeID (ID INT);
INSERT #TypeID VALUES (1),(2),(4),(100);

delete 
output deleted.*
into [dbo].[History]
from [dbo].[HotTable]  
inner join #TypeID ON ID = TypeId;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.05.2020, 14:11
    #39962237
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
a_voronin
msLex,

а какие за и против того, чтобы просто триггер навесить на таблицу?

https://stackoverflow.com/questions/11890868/how-to-store-historical-records-in-a-history-table-in-sql-server


Лично у меня негативное (частично иррационально) отношение к триггерам в принципе.

Из главных минусов
- "размазывание" логики работы.
- раньше (то ли до 2005 то ли до 2008) таблицы deleted и inserted в триггерах брались из лога транхакций, что могло очень сильно сказаться на производительности.

Из плюсов
- При возможности обновления таблицы из многих мест, либо от туда, куда мы не можем "влезть", триггер лучшее решение


Если говорить про логирование изменений, то это наверно единственный случай, когда я для себя лично приемлю триггер.
Но с появлением temporal tables, эта польза стала для меня значительно менее существенной.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.05.2020, 20:04
    #39962475
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
А какая версия сервера? Может темпоральными таблицами обойтись и не изобретать велосипед?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 07:13
    #39962637
Кнюпель
Кнюпель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
msLex

Код: sql
1.
2.
3.
4.
5.
delete 
output deleted.*
into [dbo].[History]
from [dbo].[HotTable]  
WHERE TypeId = @typeId




Это в какой версии такой синтаксис есть? На азуре не компилится
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 07:15
    #39962639
Кнюпель
Кнюпель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Владислав Колосов


Я бы сделал так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
-- Собираем типы для удаления
CREATE TABLE #TypeID (ID INT);
INSERT #TypeID VALUES (1),(2),(4),(100);

delete 
output deleted.*
into [dbo].[History]
from [dbo].[HotTable]  
inner join #TypeID ON ID = TypeId;


Это подвесит все транзакции пока история копируется, может выйти сильно долго, пусть по каждому типу отдельно делается, общее время архивации некритично
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 08:06
    #39962654
a_voronin
a_voronin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель
Владислав Колосов


Я бы сделал так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
-- Собираем типы для удаления
CREATE TABLE #TypeID (ID INT);
INSERT #TypeID VALUES (1),(2),(4),(100);

delete 
output deleted.*
into [dbo].[History]
from [dbo].[HotTable]  
inner join #TypeID ON ID = TypeId;


Это подвесит все транзакции пока история копируется, может выйти сильно долго, пусть по каждому типу отдельно делается, общее время архивации некритично


Если вы делаете массовое удаление и хотите сохранить целостность, то какой бы способ вы не выбрали, блокировка будет. Уменьшайте размер порции или отказывайтесь от гарантии целостности.

Вы можете кинуть удаленные в буфер, очередь и т.п. Потом перенести в history.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 11:40
    #39962732
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель,

чтобы не подвешивало запросы на чтение в момент удаления, используйте подсказку readpast при чтении. Одним запросом удаление будет быстрее и по ресурсам экономнее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 11:57
    #39962737
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель
надо гарантировать, что из HotTable будет удалено только то, что уехало в историю.
Кнюпель
пройтись по всем типам (где я их получу используя SELECT DISTINCT)
Ну, вообще-то, это эквивалентно delete from HotTable.
Судя по названию, таблица сильно нагружена и самый простой способ достичь желаемого, не мешая другим добавлять строки это:
Код: sql
1.
2.
3.
4.
set transaction isolation level snapshot;
begin tran;
delete output ... into History (...) from HotTable;
commit;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 12:40
    #39962756
Кнюпель
Кнюпель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
invm
самый простой способ достичь желаемого, не мешая другим добавлять строки это


не совсем так, во время перемещения каждого конкретного типа в историю надо очень даже мешать другим добавлять эти типы, иначе все рассогласуется. Так что оптимистические блокировки вряд-ли тут полезны
Также эта конструкция у меня не желает работать ругаясь на
The target table 'dbo.History' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint...
Видимо ей ключ не нравится, но он мне полезен в обоих таблицах, убирать его ради работы данной конструкции я не хочу

Все-же тот начальный вариант который я указывал - он насколько рабочий? Я вроде не вижу изьянов, другие транзацкции не должны ничего суметь добавить пока копируется в историю, и дэдлоков вроде получить тут нельзя
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 13:08
    #39962775
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель
во время перемещения каждого конкретного типа в историю надо очень даже мешать другим добавлять эти типы
Было бы интересно узнать из-за чего такое ограничение.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 13:26
    #39962783
Кнюпель
Кнюпель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
invm
Было бы интересно узнать из-за чего такое ограничение.

по очевидной причине - если после первого стайтмента insert into select... вклиниться другая транзакция и добавит новую запись данного типа - то следующий стейтмент delete ее просто удалит, в итоге получим потерянную запись. Другие типы добавлять можно, я для этого и собираюсь курсор использовать
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 13:31
    #39962788
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель
по очевидной причине - если после первого стайтмента insert into select... вклиниться другая транзакция и добавит новую запись данного типа - то следующий стейтмент delete ее просто удалит, в итоге получим потерянную запись
Я вам уже показал пример со snapshot, который работает корректно.
Даже ваш первоначальный код будет работать правильно на snapshot.

А по-хорошему нужен признак "готово к архивации".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 13:39
    #39962796
Кнюпель
Кнюпель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
invm
Я вам уже показал пример со snapshot, который работает корректно.
Даже ваш первоначальный код будет работать правильно на snapshot.

а в чем конкретно преимущество снапшота - он позволит добавлять записи данного типа без подвисания на блокировке?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 13:52
    #39962802
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель,

не совсем понятно, вы не помешаете добавлению удаляемого типа в Вашем примере, добавление просто станет в очередь и этот тип появится после удаления в исходной таблице.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 13:57
    #39962806
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель,

Кажется, понял... Ваш вариант в первом сообщении не будет работать по той же причине, о которой вы написали ниже. Вам надо или блокировать таблицу в целом, или использовать snapshot или использовать output выражение для получения ключей, переписанных в историческую таблицу. Удаление производить по этой таблице ключей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 13:58
    #39962807
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель
а в чем конкретно преимущество снапшота - он позволит добавлять записи данного типа без подвисания на блокировке?
Да.
К тому же на serializable может заблокироваться таблица, а не строки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 14:25
    #39962820
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
invm,

если ТС не будет использовать DELETE ... OUTPUT, он писал, что это не подходит, то уровень изоляции снимка ему также не поможет. Остается вариант INSERT ... OUTPUT #t; DELETE .. FROM #t WHERE;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 14:29
    #39962827
a_voronin
a_voronin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Кнюпель

Также эта конструкция у меня не желает работать ругаясь на
The target table 'dbo.History' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint...


output_table
Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

If column_list is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

output_table cannot:

Have enabled triggers defined on it.

Participate on either side of a FOREIGN KEY constraint.

Have CHECK constraints or enabled rules.

https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15

Вам никто не мешает отправить результат в табличную переменную или времянку, потом вставить следующим запросом уже без ограничений.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 14:48
    #39962839
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Владислав Колосов
если ТС не будет использовать DELETE ... OUTPUT, он писал, что это не подходит, то уровень изоляции снимка ему также не поможет
Это почему? snapshot дает согласованность на начало транзакции.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 14:57
    #39962841
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
invm
Владислав Колосов
если ТС не будет использовать DELETE ... OUTPUT, он писал, что это не подходит, то уровень изоляции снимка ему также не поможет
Это почему? snapshot дает согласованность на начало транзакции.


В снимке находятся версии только прочитанных страниц, если между чтением select * from [dbo].[HotTable] и удалением delete from [dbo].[HotTable] появятся новые строки на новых страницах, то они же будут удалены?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 15:17
    #39962858
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Заблокировать строки для архивации
Владислав Колосов
В снимке находятся версии только прочитанных страниц

Snapshot выдает данные на момент начала транзакции, какие "прочитанные страницы?"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Заблокировать строки для архивации / 25 сообщений из 43, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 181ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1686063]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 48ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 66ms
get tp. blocked users:
 2ms
others: 16ms
total:  181ms
x
x
Закрыть


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