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

Откуда:
Сообщений: 22
Установлен Microsoft SQL Server 2014 - 12.0.2370.0 (X64).
Установили новое ПО от поставщика, сами не разрабатывем.
В день получаем кучу deadlock от хранимой процедуры. Официальный ответ от поставщика, запросы выполнять последовательно, а не параллельно. Звучит смешно паралелльные вычисления сделать последовательными. После анализа заметили откуда растут блоки.
Есть хранимая процедура, которая ставит блок на всю таблицу. Когда приходит несколько парралельных процессов, бывают случаи, что процесс 1 блокирует процесс 2, а процесс 2 блокирует процесс 1.
Обращаюсь к гуру, можно ли как то это исправить. Текст хранимки ниже, возможно у нее стоят неверные типы блокировок. Но для это звучит смешно, что в мире параллельных вычисление MSSQL не может разрулить их. Суть хранимки, при получении запроса от клиента, записывать значения в таблицу.

USE UPSLT
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER procedure TS_LOCK_PROCESS_LOCK
        @lockType int,
        @lockKey nvarchar(128),
        @accessMode nchar,
        @lockLifeSpan int,
        @hostName nvarchar(32),
        @moduleName nvarchar(65),
        @methodName nvarchar(128),
        @lockOwner nvarchar(12),
		@curWriteLockOwner nvarchar(12) output
as
begin
    delete from TSDBA.TS_PROCESS_LOCK where EXPIRATION_TIME < GETUTCDATE()

    declare @result int
    set @result = 0
    begin tran
        select @result=-count(1) from TSDBA.TS_PROCESS_LOCK with (tablock, serializable) 
            where LOCK_TYPE = @lockType and LOCK_KEY = @lockKey and (@accessMode = 'W' or ACCESS_MODE = 'W')
        if (@result = 0)
        begin
		    declare @utcNow datetime
			set @utcNow = GETUTCDATE()
		    declare @expTime datetime
		    set @expTime = DATEADD(s, @lockLifeSpan, @utcNow)
            insert into TSDBA.TS_PROCESS_LOCK
                (LOCK_TYPE, LOCK_KEY, ACCESS_MODE, LOCK_TIME, EXPIRATION_TIME, HOST_NAME, MODULE_NAME, METHOD_NAME, LOCK_OWNER) values
                (@lockType, @lockKey, @accessMode, @utcNow,   @expTime,        @hostName, @moduleName, @methodName, @lockOwner)
            set @result = @@identity
        end
		else
		   select @curWriteLockOwner=LOCK_OWNER from TSDBA.TS_PROCESS_LOCK where LOCK_TYPE = @lockType and LOCK_KEY = @lockKey and ACCESS_MODE = 'W'
    commit tran
	
    return @result
end
16 авг 17, 11:23    [20727487]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
komap,

имхо DELETE в транзацию тоже с tablock
16 авг 17, 11:36    [20727516]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
komap,

а так логика не подразумевает паралельные вычисления. Всё равно будет ждать один другого
16 авг 17, 11:36    [20727518]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
Владислав Колосов
Member

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

у поставщика руки не оттуда растут... Если он хочет ввести бизнес-логику последовательной обработки, надо надо использовать либо applock, либо service broker с количеством обработчиков 1. Всё остальное приведет к неприятным "засорам", как правило. Если случай вырожденный т.е. таблица единственная при выполнении процедуры, можно и таблок. Но это не ваш случай, как я понимаю.
16 авг 17, 11:51    [20727565]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
tazzman
Member

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

А таблица большая? В память влезет? Может попробовать сделать её in-memory чтобы операции проходили с максимальной производительностью?
16 авг 17, 12:31    [20727745]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
tazzman
komap,

А таблица большая? В память влезет? Может попробовать сделать её in-memory чтобы операции проходили с максимальной производительностью?

да, памяти доставить, ядер накидать, так все с дедлоками борятся, пусть побыстрее будет, может сервер не заметит
16 авг 17, 12:35    [20727771]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
aleks222
Guest
komap
Официальный ответ от поставщика, запросы выполнять последовательно, а не параллельно. Звучит смешно паралелльные вычисления сделать последовательными.


Страдальцы.
Ну сами не можете - попросите поставшика захерачить критическую секцию с помощью sp_getapplock.

ЗЫ. Судя по изобилию хинтов - поставщик уже наступал на грабли.

ЗЗЫ. with (tablock, serializable) - бесполезная, в контексте процедуры, хрень.
Там надо with (tablockx, holdlock).
16 авг 17, 12:56    [20727875]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
komap
Member

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

до 100 строк. приходит запрос от сервера, ставится блок на часть данных (о том что они заняты), как только запрос выполнен, данные из ьаблицы удаляются.
16 авг 17, 13:00    [20727888]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
komap
tazzman,

до 100 строк. приходит запрос от сервера, ставится блок на часть данных (о том что они заняты), как только запрос выполнен, данные из ьаблицы удаляются.


Вы бы поискали нового поставщика... Иначе до конца дней будете с толкача заводить.
16 авг 17, 13:23    [20727992]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
Почему
select @result=-count(1) from TSDBA.TS_PROCESS_LOCK with (tablock, serializable)
where LOCK_TYPE = @lockType and LOCK_KEY = @lockKey and (@accessMode = 'W' or ACCESS_MODE = 'W')
if (@result = 0)

не заменить на:

if not exists(select * from ....)

?
16 авг 17, 14:00    [20728122]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
LSV
Почему
select @result=-count(1) from TSDBA.TS_PROCESS_LOCK with (tablock, serializable)
where LOCK_TYPE = @lockType and LOCK_KEY = @lockKey and (@accessMode = 'W' or ACCESS_MODE = 'W')
if (@result = 0)

не заменить на:

if not exists(select * from ....)

?

потому что они его ещё и возвращают
16 авг 17, 14:02    [20728125]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
komap
Официальный ответ от поставщика, запросы выполнять последовательно, а не параллельно. Звучит смешно паралелльные вычисления сделать последовательными.
Ну не умеет ваш поставщик писать серверный код для MSSQL. Либо смиритесь, либо меняйте поставщика.
komap
можно ли как то это исправить.
Можно. Вариантов много. Проще всего и с минимальным вмешательством в чужой код вот так:
ALTER procedure TS_LOCK_PROCESS_LOCK
        @lockType int,
        @lockKey nvarchar(128),
        @accessMode nchar,
        @lockLifeSpan int,
        @hostName nvarchar(32),
        @moduleName nvarchar(65),
        @methodName nvarchar(128),
        @lockOwner nvarchar(12),
		@curWriteLockOwner nvarchar(12) output
as
begin
    set xact_abort on

    declare @result int
    set @result = 0
    begin tran
        delete from TSDBA.TS_PROCESS_LOCK with (tablockx) where EXPIRATION_TIME < GETUTCDATE()

        select @result=-count(1) from TSDBA.TS_PROCESS_LOCK
            where LOCK_TYPE = @lockType and LOCK_KEY = @lockKey and (@accessMode = 'W' or ACCESS_MODE = 'W')
        if (@result = 0)
        begin
		    declare @utcNow datetime
			set @utcNow = GETUTCDATE()
		    declare @expTime datetime
		    set @expTime = DATEADD(s, @lockLifeSpan, @utcNow)
            insert into TSDBA.TS_PROCESS_LOCK
                (LOCK_TYPE, LOCK_KEY, ACCESS_MODE, LOCK_TIME, EXPIRATION_TIME, HOST_NAME, MODULE_NAME, METHOD_NAME, LOCK_OWNER) values
                (@lockType, @lockKey, @accessMode, @utcNow,   @expTime,        @hostName, @moduleName, @methodName, @lockOwner)
            set @result = @@identity
        end
		else
		   select @curWriteLockOwner=LOCK_OWNER from TSDBA.TS_PROCESS_LOCK where LOCK_TYPE = @lockType and LOCK_KEY = @lockKey and ACCESS_MODE = 'W'

    commit tran
	
    return @result
end
16 авг 17, 14:15    [20728179]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
komap,

Предъявить претензии разработчику по поводу некачественного кода.
16 авг 17, 14:35    [20728302]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
aleks222
Guest
invm
komap
Официальный ответ от поставщика, запросы выполнять последовательно, а не параллельно. Звучит смешно паралелльные вычисления сделать последовательными.
Ну не умеет ваш поставщик писать серверный код для MSSQL. Либо смиритесь, либо меняйте поставщика.
komap
можно ли как то это исправить.
Можно. Вариантов много. Проще всего и с минимальным вмешательством в чужой код вот так:
ALTER procedure TS_LOCK_PROCESS_LOCK
        @lockType int,
        @lockKey nvarchar(128),
        @accessMode nchar,
        @lockLifeSpan int,
        @hostName nvarchar(32),
        @moduleName nvarchar(65),
        @methodName nvarchar(128),
        @lockOwner nvarchar(12),
		@curWriteLockOwner nvarchar(12) output
as
begin
    set xact_abort on

    declare @result int
    set @result = 0
    begin tran
        delete from TSDBA.TS_PROCESS_LOCK with (tablockx) where EXPIRATION_TIME < GETUTCDATE()

        select @result=-count(1) from TSDBA.TS_PROCESS_LOCK
            where LOCK_TYPE = @lockType and LOCK_KEY = @lockKey and (@accessMode = 'W' or ACCESS_MODE = 'W')
        if (@result = 0)
        begin
		    declare @utcNow datetime
			set @utcNow = GETUTCDATE()
		    declare @expTime datetime
		    set @expTime = DATEADD(s, @lockLifeSpan, @utcNow)
            insert into TSDBA.TS_PROCESS_LOCK
                (LOCK_TYPE, LOCK_KEY, ACCESS_MODE, LOCK_TIME, EXPIRATION_TIME, HOST_NAME, MODULE_NAME, METHOD_NAME, LOCK_OWNER) values
                (@lockType, @lockKey, @accessMode, @utcNow,   @expTime,        @hostName, @moduleName, @methodName, @lockOwner)
            set @result = @@identity
        end
		else
		   select @curWriteLockOwner=LOCK_OWNER from TSDBA.TS_PROCESS_LOCK where LOCK_TYPE = @lockType and LOCK_KEY = @lockKey and ACCESS_MODE = 'W'

    commit tran
	
    return @result
end


Незамутненный бред. Вы не этот самый "поставщик"?

1. Нахера
delete from TSDBA.TS_PROCESS_LOCK with (tablockx) where EXPIRATION_TIME < GETUTCDATE()
под транзакцию?
Ежели чуть-чуть подумать, то конкурентное удаление никому не нужно.
Правильно - вынести из транзакции, изолировать этот стейтмент. И запретить его параллельное исполнение.

exec @loc = sp_getapplock ...
if @loc = 0 begin
 delete from TSDBA.TS_PROCESS_LOCK where EXPIRATION_TIME < GETUTCDATE()
 sp_releaseapplock 
end;


2. with (tablockx) без holdlock - бесполезная фигня.

3. Кластерный индекс по EXPIRATION_TIME.
16 авг 17, 15:16    [20728502]     Ответить | Цитировать Сообщить модератору
 Re: DeadLockи (tablock, serializable)  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
aleks222
Незамутненный бред. Вы не этот самый "поставщик"?
Дарагуля (с), думаешь прибавив две двойки к нику стал в четыре раза умнее? Как показывает практика с точностью до наоборот
И раньше-то особо умом не блистал, а сейчас вообще...
aleks222
delete from TSDBA.TS_PROCESS_LOCK with (tablockx) where EXPIRATION_TIME < GETUTCDATE()
под транзакцию?
Ежели чуть-чуть подумать, то конкурентное удаление никому не нужно.
Правильно - вынести из транзакции, изолировать этот стейтмент. И запретить его параллельное исполнение.
Ежели чуть-чуть подумать, а не строить из себя умного и не раздувать щеки в приступе очередного самолюбования, то станет ясно, что delete с tablockx в транзакции делает ровно то, что и твой говнокод с "забавным" побочным эффектом. Только проще и надежней.
aleks222
with (tablockx) без holdlock - бесполезная фигня.
Тебе уже неоднократно предлагалось - учи матчасть. Так нет же, предпочитаешь выставляться идиотом...
aleks222
Кластерный индекс по EXPIRATION_TIME
Гениально. Как раз то что не хватало для таблицы из 100 строк. Это сразу решит все проблемы.
16 авг 17, 15:39    [20728606]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить