ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Заблокировать строки для архивации
25 сообщений из 43, страница 1 из 2
  1  все
Заблокировать строки для архивации
    #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:45
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #39962116
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
delete + output
...
Рейтинг: 0 / 0
26.05.2020, 11:59
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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:03
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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:26
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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, 12:38
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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, 14:11
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #39962475
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
А какая версия сервера? Может темпоральными таблицами обойтись и не изобретать велосипед?
...
Рейтинг: 0 / 0
26.05.2020, 20:04
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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:13
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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, 07:15
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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, 08:06
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #39962732
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Кнюпель,

чтобы не подвешивало запросы на чтение в момент удаления, используйте подсказку readpast при чтении. Одним запросом удаление будет быстрее и по ресурсам экономнее.
...
Рейтинг: 0 / 0
27.05.2020, 11:40
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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, 11:57
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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, 12:40
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #39962775
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Кнюпель
во время перемещения каждого конкретного типа в историю надо очень даже мешать другим добавлять эти типы
Было бы интересно узнать из-за чего такое ограничение.
...
Рейтинг: 0 / 0
27.05.2020, 13:08
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #39962783
Кнюпель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кнюпель
Участник
invm
Было бы интересно узнать из-за чего такое ограничение.

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

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

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

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

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

если ТС не будет использовать DELETE ... OUTPUT, он писал, что это не подходит, то уровень изоляции снимка ему также не поможет. Остается вариант INSERT ... OUTPUT #t; DELETE .. FROM #t WHERE;
...
Рейтинг: 0 / 0
27.05.2020, 14:25
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #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:29
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #39962839
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Владислав Колосов
если ТС не будет использовать DELETE ... OUTPUT, он писал, что это не подходит, то уровень изоляции снимка ему также не поможет
Это почему? snapshot дает согласованность на начало транзакции.
...
Рейтинг: 0 / 0
27.05.2020, 14:48
| Ответить | Цитировать | Написать  
Заблокировать строки для архивации
    #39962841
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
invm
Владислав Колосов
если ТС не будет использовать DELETE ... OUTPUT, он писал, что это не подходит, то уровень изоляции снимка ему также не поможет
Это почему? snapshot дает согласованность на начало транзакции.


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

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


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