Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 переименовать таблицу  [new]
valv
Member

Откуда:
Сообщений: 118
добрый день.

надо заменить таблицу на вид с точно таким же названием.
теоретически, это элементарно:
begin tran
  exec sp_rename 'table1', 'table1_old'
  exec sp_rename 'view1', 'table1'
commit

но возникла проблема боевом сервере:
непрерывные запросы к таблице не дают её переименовать.
запросы типа "select * from table1 where id = 123" идут с IIS клинта напрямую, а остановить работу ISS нельзя.
перевести БД в single_user тоже нельзя.
сервер обязан работать 24/7. ~600 соединений, ~150 запросов в секунду.
убивать соединения можно. пробовал убивать соединения блокирующие таблицу, но пока 600 раз выполняется команда kill, успевают создасться новые соединения.

MS SQL 2016 SP2

как переименовать?
спасибо
22 авг 19, 12:34    [21955327]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
aleks222
Member

Откуда:
Сообщений: 855
begin tran
select top(0) * from table1 with(tablockx, holdlock)
exec sp_rename 'table1', 'table1_old'
exec sp_rename 'view1', 'table1'
commit
22 авг 19, 13:16    [21955378]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
valv,

Можете попробовать подставить свои выражения, рано или поздно изменение проскочит:
/*
Автор: Колосов В.В.
Назначение: удаление индекса под рабочей нагрузкой
*/
set lock_timeout 1000;



while exists 
	(
	select * from sys.indexes where name = 'ix_web_log' and object_id = object_id('dbo.web_log')
	)
begin
	begin try
		drop index ix_web_log on dbo.web_log;
	end try
	begin catch
	end catch;
	waitfor delay '00:00:00.3';
end
:
22 авг 19, 13:19    [21955384]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
Gator
Member

Откуда: Москва
Сообщений: 14982
valv
но возникла проблема боевом сервере:
непрерывные запросы к таблице не дают её переименовать.
запросы типа "select * from table1 where id = 123" идут с IIS клинта напрямую, а остановить работу ISS нельзя.
перевести БД в single_user тоже нельзя.
сервер обязан работать 24/7. ~600 соединений, ~150 запросов в секунду.
Такие вопросы всегда решаются административно. В нужное время назначается технологический интервал и об этом сообщается всемзаинтересованым.
22 авг 19, 13:32    [21955414]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
Gator
Member

Откуда: Москва
Сообщений: 14982
Gator, а ещё мне жаль ваш сервер. Без профилактики, бедняга.
22 авг 19, 13:33    [21955415]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
valv
как переименовать?
Запустить и ждать пока не выполнится.
Сервер не дурак - новые соединения, желающие доступа к таблице, будут ждать завершения транзакции с sp_rename.

Если желаете ускорить, то можете уже после запуска sp_rename отстреливать сессии, удерживающие несовместимые блокировки на нужную таблицу.
22 авг 19, 14:00    [21955465]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
valv
Member

Откуда:
Сообщений: 118
aleks222
begin tran
select top(0) * from table1 with(tablockx, holdlock)
exec sp_rename 'table1', 'table1_old'
exec sp_rename 'view1', 'table1'
commit
к сожалению, нет.
запросы с уровнем "грязное чтение" (hint "nolock") так не блокируются.
они накладывают SCH-S лок, который мешает переименованию.

invm
valv
как переименовать?
Запустить и ждать пока не выполнится.
Сервер не дурак - новые соединения, желающие доступа к таблице, будут ждать завершения транзакции с sp_rename.

Если желаете ускорить, то можете уже после запуска sp_rename отстреливать сессии, удерживающие несовместимые блокировки на нужную таблицу.
отстреливать сессии - это перспективный путь, но слишком их много.
не успеваю, IIS сервера создают их быстрее чем ms sql server их убивает.
как бы замедлить создание новых сессий?
22 авг 19, 14:19    [21955501]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
valv
отстреливать сессии - это перспективный путь, но слишком их много.
не успеваю
Вы внимательно прочитали, что я написал? Особенно второе предложение?
22 авг 19, 14:27    [21955515]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
valv
Member

Откуда:
Сообщений: 118
invm
valv
отстреливать сессии - это перспективный путь, но слишком их много.
не успеваю
Вы внимательно прочитали, что я написал? Особенно второе предложение?

Вы о
invm
Запустить и ждать пока не выполнится.
Сервер не дурак - новые соединения, желающие доступа к таблице, будут ждать завершения транзакции с sp_rename.
?
это неверно. запросы с уровнем "грязное чтение" (hint "nolock") не ждут завершения транзакции, а свободно читают из таблицы.
22 авг 19, 14:35    [21955534]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
valv
invm
пропущено...
Вы внимательно прочитали, что я написал? Особенно второе предложение?

Вы о
invm
Запустить и ждать пока не выполнится.
Сервер не дурак - новые соединения, желающие доступа к таблице, будут ждать завершения транзакции с sp_rename.
?
это неверно. запросы с уровнем "грязное чтение" (hint "nolock") не ждут завершения транзакции, а свободно читают из таблицы.

и как же они умудряются получить свой SCH-S ?
22 авг 19, 14:44    [21955559]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
valv
Member

Откуда:
Сообщений: 118
TaPaK
valv
это неверно. запросы с уровнем "грязное чтение" (hint "nolock") не ждут завершения транзакции, а свободно читают из таблицы.

и как же они умудряются получить свой SCH-S ?

см. таблицу совместимости локов.
sch-s совместим со всеми, за исключением sch-m.
а его то и нужно получить для переименования.
22 авг 19, 15:08    [21955598]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
valv
TaPaK
пропущено...

и как же они умудряются получить свой SCH-S ?

см. таблицу совместимости локов.
sch-s совместим со всеми, за исключением sch-m.
а его то и нужно получить для переименования.


чукча не читатель?

автор
новые соединения, желающие доступа к таблице, будут ждать завершения транзакции с sp_rename.
22 авг 19, 15:18    [21955611]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
valv
Member

Откуда:
Сообщений: 118
TaPaK
valv
пропущено...

см. таблицу совместимости локов.
sch-s совместим со всеми, за исключением sch-m.
а его то и нужно получить для переименования.


чукча не читатель?

автор
новые соединения, желающие доступа к таблице, будут ждать завершения транзакции с sp_rename.

чувачок не догоняет?

запросы которые читают с (nolock) не будут ждать. проверено. MS SQL 2016.
22 авг 19, 15:24    [21955621]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
valv
TaPaK
пропущено...


чукча не читатель?

пропущено...

чувачок не догоняет?

запросы которые читают с (nolock) не будут ждать. проверено. MS SQL 2016.

неа не догоняет

CREATE TABLE dbo.A
(
	ID INT
)

CREATE TABLE dbo.B
(
	ID INT
)

-- connection 1
WHILE 1 = 1
BEGIN 
	SELECT TOP 0 * FROM dbo.b (NOLOCK)
END

-- connection 2
BEGIN TRAN
  exec sp_rename 'a', 'a_old'
  exec sp_rename 'b', 'a'
COMMIT



DROP TABLE dbo.a
DROP TABLE dbo.b
22 авг 19, 15:30    [21955632]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
valv
запросы которые читают с (nolock) не будут ждать. проверено. MS SQL 2016.
Плохо проверяли.

1.Создаем таблицу
use tempdb;
go

create table dbo.t (id int);
insert into dbo.t values (1), (2), (3);

2. В первой сессии запускаем чтение
use tempdb;
go

begin tran;
select count(*) from dbo.t with (tablock, repeatableread);
--commit;

3. Во второй сессии запускаем переименование
use tempdb;
go

begin tran;
exec sp_rename 'dbo.t', 't2', 'object'
--rollback;

4. В третьей сессии запускаем "грязное" чтение
use tempdb;
go

begin tran;
select count(*) from dbo.t with (nolock);
--commit;


Впрочем, уговаривать не буду - продолжайте страдать.
22 авг 19, 15:34    [21955638]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
valv,

дело не в этом, рано или поздно снимется последняя блокировка стабильности и будет наложена блокировка изменения, которая до этого стояла в очереди.
22 авг 19, 15:34    [21955640]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Владислав Колосов
valv,

дело не в этом, рано или поздно снимется последняя блокировка стабильности и будет наложена блокировка изменения, которая до этого стояла в очереди.

тс думает что сервер смотрит, О! стоит сессия с запросом sch-m и прилетает новая с sch-s, и сервер: та пусть ещё подождёт, этому же только спросить....
22 авг 19, 15:37    [21955642]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
valv
Member

Откуда:
Сообщений: 118
TaPaK
valv
пропущено...

чувачок не догоняет?

запросы которые читают с (nolock) не будут ждать. проверено. MS SQL 2016.

неа не догоняет

CREATE TABLE dbo.A
(
	ID INT
)

CREATE TABLE dbo.B
(
	ID INT
)

-- connection 1
WHILE 1 = 1
BEGIN 
	SELECT TOP 0 * FROM dbo.b (NOLOCK)
END

-- connection 2
BEGIN TRAN
  exec sp_rename 'a', 'a_old'
  exec sp_rename 'b', 'a'
COMMIT



DROP TABLE dbo.a
DROP TABLE dbo.b

в данном примере таблица будет переименована в момент между итерациями
SELECT TOP 0 * FROM dbo.b (NOLOCK)
в нашей ситуации, несколько сотен запросов параллельно обращаются к таблице.
не существует момента времени когда таблица не блокируются.

собственно, в этом и вопрос.
как создать миг, когда можно наложить sch-m и переименовать.
22 авг 19, 15:41    [21955647]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
о, действительно так и думает
22 авг 19, 15:50    [21955654]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
valv
Member

Откуда:
Сообщений: 118
Владислав Колосов
valv,

дело не в этом, рано или поздно снимется последняя блокировка стабильности и будет наложена блокировка изменения, которая до этого стояла в очереди.

Владислав,
возможно существует решение без "рано или поздно",
и кто нибудь знает и посоветует?
транзакция с sp_rename - она же не безобидный select, она препятствует нормальной работе.
должен же быть способ освободить одну единственную таблицы на миллисекунду без того чтобы останавливать всю БД?
22 авг 19, 15:53    [21955659]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
valv
убивать соединения можно. пробовал убивать соединения блокирующие таблицу, но пока 600 раз выполняется команда kill, успевают создасться новые соединения.


ну попробуйте ломом:
блокировка логина, отстрел коннектов, переименовка, разблокировка логина
22 авг 19, 16:02    [21955667]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
valv
Member

Откуда:
Сообщений: 118
komrad
valv
убивать соединения можно. пробовал убивать соединения блокирующие таблицу, но пока 600 раз выполняется команда kill, успевают создасться новые соединения.


ну попробуйте ломом:
блокировка логина, отстрел коннектов, переименовка, разблокировка логина

Картинка с другого сайта. на буржуинском форуме тоже самое посоветовали...
фактически, это то же самое как single_user restriction или остановка IIS. БД какое-то время не будет работать.
беда...
22 авг 19, 16:14    [21955687]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
valv
komrad
пропущено...


ну попробуйте ломом:
блокировка логина, отстрел коннектов, переименовка, разблокировка логина

Картинка с другого сайта. на буржуинском форуме тоже самое посоветовали...
фактически, это то же самое как single_user restriction или остановка IIS. БД какое-то время не будет работать.
беда...


потому что не бывает изменения схемы в онлайне. Люди планируют окна за полгода вперёд и тд и тп
22 авг 19, 16:25    [21955704]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
valv,
ну можно попробовать право на селект отобрать вместо блокировки
22 авг 19, 16:28    [21955709]     Ответить | Цитировать Сообщить модератору
 Re: переименовать таблицу  [new]
valv
Member

Откуда:
Сообщений: 118
komrad
valv,
ну можно попробовать право на селект отобрать вместо блокировки

гениально!!!
komrad, спасибо большое.
уже тестирую.
22 авг 19, 17:00    [21955761]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить