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

Откуда:
Сообщений: 116
Добрый вечер.
Такой вопрос с блокировками. Может на примере лучше будет показать. Пусть есть две таблицы.
create table dbo.Tab1 (
	Tab1Id int identity(1, 1) primary key,
	DataOt datetime not null,
	DataDo datetime not null
)
go
create table dbo.Tab2 (
	Tab2Id int identity(1, 1) primary key,
	Tab1Id int references Tab1(Tab1Id),
	Data datetime not null
)
go


Пусть будет условие - дата из таблицы Tab2 должна лежать в диапазоне DataOt - DataDo с таблицы Tab1.
пусть есть две процедуры: z_tab1 (для добавления и изменения позиции в таблице Tab1) и z_tab2 (для добавления и изменения позиции в таблице Tab2).
+
create proc dbo.Z_Tab1 
	@Tab1Id int output,
	@DataOt datetime,
	@DataDo datetime
as
begin
	set nocount on
	
	begin try	
		begin tran
		
		if @DataDo < @DataOt raiserror('DataOd < DataOt', 16, 1);
		
		if ISNULL(@Tab1Id, 0) = 0 begin
			insert
				dbo.Tab1 (DataOt, DataDo)
			values
				(@DataOt, @DataDo)
				
			set @Tab1Id = SCOPE_IDENTITY()
		end else begin
			if exists(select 1 from dbo.Tab2 with (updlock, holdlock) where Tab1Id = @Tab1Id and not (Data between @DataOt and @DataDo)) raiserror('Существует позиция с датой выходящей за диапазон', 16, 1);
			
			update
				dbo.Tab1
			set
				DataOt = @DataOt,
				DataDo = @DataDo
			where
				Tab1Id = @Tab1Id
		end
		
		commit tran
	END TRY
	BEGIN CATCH
		if @@trancount > 0 rollback tran
		DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), @ErrorSeverity INT = ERROR_SEVERITY(), @ErrorState INT = ERROR_STATE()
    RAISERROR (@ErrorMessage, @ErrorSeverity,@ErrorState);
	END CATCH
end
go



+
create proc dbo.Z_Tab2
	@Tab2Id int output,
	@Tab1Id int,
	@Data datetime
as
begin
	set nocount on
	
	begin try	
		begin tran
		
		declare
			@DataOt datetime,
			@DataDo datetime
			
		select
			@DataOt = DataOt,
			@DataDo = DataDo
		from
			dbo.Tab1 with (updlock, holdlock) 
		where 
			Tab1Id = @Tab1Id 
			
		if @DataOt is null raiserror('Неверный параметр', 16, 1);
		
		if not (@Data between @DataOt and @DataDo) raiserror('Существует позиция с датой выходящей за диапазон', 16, 1);
		
		if ISNULL(@Tab2Id, 0) = 0 begin
			insert
				dbo.Tab2 (Tab1Id, Data)
			values
				(@Tab1Id, @Data)
				
			set @Tab2Id = SCOPE_IDENTITY()
		end else begin
			update
				dbo.Tab2
			set
				Data = @Data
			where
				Tab2Id = @Tab2Id and
				Tab1Id = @Tab1Id
				
			if @@ROWCOUNT <> 1 raiserror('Неверный параметр', 16, 1);
		end
		
		commit tran
	END TRY
	BEGIN CATCH
		if @@trancount > 0 rollback tran
		DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), @ErrorSeverity INT = ERROR_SEVERITY(), @ErrorState INT = ERROR_STATE()
    RAISERROR (@ErrorMessage, @ErrorSeverity,@ErrorState);
	END CATCH
end
go


Эти процедуры могут выполнятся одновременно. И так как они написаны (используя updlock, holdlock) все будет хорошо. Но процедура z_tab1 выполняется достаточно редко. И теперь вопрос. Можно ли что-то сделать, чтобы при одновременном (с разных потоков) выполнении процедуры z_tab2 с одним и тем же параметром Tab1Id не было бы ожидвания совобождения ресурса (позиции Tab1 с идентификатором Tab1Id) на этом куске кода:
		select
			@DataOt = DataOt,
			@DataDo = DataDo
		from
			dbo.Tab1 with (updlock, holdlock) 
		where 
			Tab1Id = @Tab1Id 

Но при этом если вдруг выполниться процедура z_tab1 с этим же параметром Tab1Id - чтобы ожидание все таки было.

В общем есть ли возможность чтобы была всегда достигнута челостность данных (дата из Tab2 должна быть в диапазоне дат из Tab1) но и, зная что 95% запросов - это запуск процедуры z_tab2, чтобы не было этой "самоблокировки".

Спасибо.

Сообщение было отредактировано: 21 окт 15, 02:30
21 окт 15, 00:30    [18305140]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка - оптимизация  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 345
А если убрать хинт
with (updlock, holdlock)
и выставить для всей транзакции в процедуре dbo.z_Tabl2 уровень изоляции REPEATABLE READ ?

Получится, что он будет держать только Shared блокировку на строке в dbo.Tab1, т.е. другие потоки смогут читать, но обновлять строку никто не сможет до окончания транзакции.
21 окт 15, 01:47    [18305258]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка - оптимизация  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
vdp
Можно ли что-то сделать, чтобы при одновременном (с разных потоков) выполнении процедуры z_tab2 с одним и тем же параметром Tab1Id не было бы ожидвания совобождения ресурса (позиции Tab1 с идентификатором Tab1Id)
vdp
Но при этом если вдруг выполниться процедура z_tab1 с этим же параметром Tab1Id - чтобы ожидание все таки было.
Можно
		select
			@DataOt = DataOt,
			@DataDo = DataDo
		from
			dbo.Tab1 with (repeatableread) 
		where 
			Tab1Id = @Tab1Id

vdp
есть ли возможность чтобы была всегда достигнута челостность данных (дата из Tab2 должна быть в диапазоне дат из Tab1)
Можно за счет увеличения объема хранения избавиться от управления блокировками. Примерно так:
create view dbo.vTabl1Tab2Integrity
with schemabinding
as
select
 t1.Tab1Id, t2Tab2Id, case when t2.Data between t1.DataOt and t1.DataDo then 1 else 1/0 end as dummy
from
 dbo.Tab1 t1 join
 dbo.Tab2 t2 on t2.Tab1Id = Tab1Id
go

create unique clustered index IX_vTabl1Table2Integrity__Tab1Id__Tab2Id on dbo.vTabl1Tab2Integrity (Tab1Id, Tab2Id);
go
21 окт 15, 02:15    [18305279]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка - оптимизация  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
А ещё вместо ваших IF...INSERT...ELSE...UPDATE...END и т.п. Вы можете использовать единственный MERGE.
И, возможно, все проблемы с блокировками исчезнут.
21 окт 15, 09:06    [18305514]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка - оптимизация  [new]
vdp
Member

Откуда:
Сообщений: 116
Всем спасибо!

То что нужно! За view отделаьное спасибо!

А еще такой вопрос: теперь с (repeatableread) ожидание освобождения ресурса будет либо в процедуре z_tab2 на
select
  @DataOt = DataOt,
  @DataDo = DataDo
from
  dbo.Tab1 with (repeatableread) 
where 
  Tab1Id = @Tab1Id

Либо в z_tab1 на
update
	dbo.Tab1
set
	DataOt = @DataOt,
	DataDo = @DataDo
where
	Tab1Id = @Tab1Id


А если бы в z_tab1 до этого update был ба кусок кода типа
select
  @DataOt = DataOt,
  @DataDo = DataDo
from
  dbo.Tab1 with (updlock, holdlock) 
where 
  Tab1Id = @Tab1Id


То все равно бы ожидание освобождения ресурса было только на update.
А возможность сделать так чтобы уже на этом select'е нужно было ждать освобождения ресурса есть? Что-нибудь вместо (updlock, holdlock)?
21 окт 15, 09:48    [18305698]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка - оптимизация  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
vdp
А возможность сделать так чтобы уже на этом select'е нужно было ждать освобождения ресурса есть? Что-нибудь вместо (updlock, holdlock)?
Оно и так будет за счет updlock. А holdlock тут не нужен.
21 окт 15, 10:29    [18305911]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка - оптимизация  [new]
vdp
Member

Откуда:
Сообщений: 116
Странно, у меня пролетает.

В первом окне делаю:
begin tran
select * from dbo.Tab1 with (repeatableread) where Tab1Id = 1


Во втором
begin tran
select * from dbo.Tab1 with (updlock) where Tab1Id = 1


И второй запрос выполняется нормально, не зависает.
21 окт 15, 10:33    [18305936]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка - оптимизация  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
vdp
И второй запрос выполняется нормально, не зависает.
При таком раскладе и не должен - U совместимо с S.
В первом запросе тоже нужно updlock. Но тогда вы не достигнете желаемого:
vdp
Можно ли что-то сделать, чтобы при одновременном (с разных потоков) выполнении процедуры z_tab2 с одним и тем же параметром Tab1Id не было бы ожидвания совобождения ресурса (позиции Tab1 с идентификатором Tab1Id)
21 окт 15, 10:55    [18306055]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка - оптимизация  [new]
vdp
Member

Откуда:
Сообщений: 116
Все понятно, спасибо!
21 окт 15, 10:55    [18306062]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить