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

Откуда:
Сообщений: 49
Добрый день!

Есть таблица:

CREATE TABLE [dbo].[T_DL_REQUEST](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BATCH_ID] [uniqueidentifier] NULL,
	[STATUS] [varchar](50) NOT NULL,
	[THREAD_ID] [uniqueidentifier] NULL
)


В ней большое количество строк, каждая из которых является заданием на выполнение. Задания сгруппированы в пакеты с помощью BATCH_ID. Есть параллельные процессы, которые "расхватывают" себе задачи пакетами.

Делают они это так:
UPDATE T_DL_REQUEST
SET STATUS = 'IN_PROGRESS'
          , THREAD_ID = @ThreadId
WHERE BATCH_ID = (SELECT TOP 1 BATCH_ID
                                  FROM T_DL_REQUEST
                                  WHERE STATUS = 'WAITING')


Так вот часто возникают тупиковые блокировки:
Transaction (Process ID 74) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Как можно модифицировать запрос, чтобы избежать блокировок?
Спасибо.
13 авг 13, 00:45    [14699552]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
aleks2
Guest
UPDATE top(1) T_DL_REQUEST
SET STATUS = 'IN_PROGRESS'
          , THREAD_ID = @ThreadId
WHERE STATUS = 'WAITING';


Ваш КО.
13 авг 13, 06:07    [14699698]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
forrfor
Member

Откуда:
Сообщений: 49
aleks2
UPDATE top(1) T_DL_REQUEST
SET STATUS = 'IN_PROGRESS'
          , THREAD_ID = @ThreadId
WHERE STATUS = 'WAITING';


Ваш КО.


Товарищ капитан, Ваш запрос обновляет одну запись, а мой целый пакет. :)
13 авг 13, 08:53    [14699881]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Гость333
Member

Откуда:
Сообщений: 3683
forrfor
Есть таблица:

CREATE TABLE [dbo].[T_DL_REQUEST](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BATCH_ID] [uniqueidentifier] NULL,
	[STATUS] [varchar](50) NOT NULL,
	[THREAD_ID] [uniqueidentifier] NULL
)

И на таблице нет ни одного индекса?
13 авг 13, 09:42    [14700090]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
forrfor
Member

Откуда:
Сообщений: 49
Гость333
forrfor
Есть таблица:

CREATE TABLE [dbo].[T_DL_REQUEST](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BATCH_ID] [uniqueidentifier] NULL,
	[STATUS] [varchar](50) NOT NULL,
	[THREAD_ID] [uniqueidentifier] NULL
)

И на таблице нет ни одного индекса?


Есть-есть, я для простоты все лишнее поубирал.
13 авг 13, 10:02    [14700192]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Glory
Member

Откуда:
Сообщений: 104751
forrfor
Есть-есть, я для простоты все лишнее поубирал.

Ну тогда нужен их список и граф дедлока
Ибо для предложенной структуры вся таблица будет блокироваться целиком. Что не может привести к взаимоблокировке
13 авг 13, 10:31    [14700407]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Граф взаимоблокировки, как я понимаю, тоже лишний и убран для простоты.
13 авг 13, 10:32    [14700415]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
forrfor
Есть-есть, я для простоты все лишнее поубирал.


вы тренируете наше телепатические способности?
13 авг 13, 10:34    [14700444]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
forrfor
Товарищ капитан, Ваш запрос обновляет одну запись, а мой целый пакет. :)

невже :)
SELECT TOP 1 BATCH_ID
                                  FROM T_DL_REQUEST
                                  WHERE STATUS = 'WAITING'

с каких пор TOP 1 начал возращать больше 1 записи ?
13 авг 13, 11:42    [14700953]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
StarikNavy
Member

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

у ТС возращается топ1 для id_пакета, а у КО "UPDATE top(1)"
13 авг 13, 11:53    [14701053]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Maxx
Member [скрыт]

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

не заметил,что BATCH_ID не есть ПК
13 авг 13, 12:17    [14701255]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
forrfor,

Вариантов много может быть, посмотрите deadlock graph. Если два одинаковых (котрые вы привели) запроса приводят к дедлоку, то скорее всего это из-за подзапроса. Он ставит Shared блокировку. Если будет UPDATE или EXCLUSIVE то дедлока не будет. Можно добавить хинт, но дедлок граф посмотреть нужно, ибо гадать что произошло не самый лучший вариант

UPDATE T_DL_REQUEST
SET STATUS = 'IN_PROGRESS'
          , THREAD_ID = @ThreadId
WHERE BATCH_ID = (SELECT TOP 1 BATCH_ID
                                  FROM T_DL_REQUEST WITH (UPDLOCK)
                                  WHERE STATUS = 'WAITING')
13 авг 13, 14:52    [14702646]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
forrfor
Member

Откуда:
Сообщений: 49
Вот полное описание таблицы:

CREATE TABLE [dbo].[T_DL_REQUEST](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CREATED] [datetime] NOT NULL,
	[ORIG] [varchar](50) NOT NULL,
	[DEST] [varchar](50) NOT NULL,
	[DATE] [date] NOT NULL,
	[BATCH_ID] [uniqueidentifier] NULL,
	[SESSION_ID] [uniqueidentifier] NULL,
	[STATUS] [varchar](50) NOT NULL,
	[URL] [varchar](300) NOT NULL,
	[TAB_INDEX] [int] NULL,
	[PROCESSED] [datetime] NULL,
	[HTML] [varchar](max) NULL,
	[PARSE_RESULT] [varchar](50) NULL,
	[PRICE] [int] NULL,
	[TEST] [varchar](max) NULL,
	[THREAD_ID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_T_DL_REQUEST] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


А вот граф:

Картинка с другого сайта.

Индексы следующие:
1) ID - кластерный, уникальный
2) STATUS - некластерный, неуникальный
3) BATCH_ID - некластерный, неуникальный
4) THREAD_ID- некластерный, неуникальный
13 авг 13, 15:37    [14703040]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
forrfor
UPDATE T_DL_REQUEST
SET STATUS = 'IN_PROGRESS'
          , THREAD_ID = @ThreadId
WHERE BATCH_ID = (SELECT TOP 1 BATCH_ID
                                  FROM T_DL_REQUEST
                                  WHERE STATUS = 'WAITING')
Так вот часто возникают тупиковые блокировки
Очевидно же. Намерение нет в нём.
К тому же, для данной задачи необходимо показать её ещё в индексах. А коль вы не показали, значит вы не очень понимаете чем пользуетесь.
И зря вы проигнорировали aleks2, запрос то у него не такой какой вам нужен, а вот подход правильный.

Ваш запрос не то что путает сервер - он путает любого.
Ясность мысли, ясность запросов, ясность в работе.
13 авг 13, 16:02    [14703203]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
forrfor
Member

Откуда:
Сообщений: 49
Mnior
forrfor
UPDATE T_DL_REQUEST
SET STATUS = 'IN_PROGRESS'
          , THREAD_ID = @ThreadId
WHERE BATCH_ID = (SELECT TOP 1 BATCH_ID
                                  FROM T_DL_REQUEST
                                  WHERE STATUS = 'WAITING')
Так вот часто возникают тупиковые блокировки
Очевидно же. Намерение нет в нём.
К тому же, для данной задачи необходимо показать её ещё в индексах. А коль вы не показали, значит вы не очень понимаете чем пользуетесь.
И зря вы проигнорировали aleks2, запрос то у него не такой какой вам нужен, а вот подход правильный.

Ваш запрос не то что путает сервер - он путает любого.
Ясность мысли, ясность запросов, ясность в работе.


Привел граф и информацию по индексам.

А какой подход в запросе aleks2? Забрать все записи со статусе ожидания? В чем подход? Тут такое дело, что даже если немного изменить "запрос, не такой как мне нужен", то подход меняется с ног на голову.
13 авг 13, 17:20    [14703813]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
forrfor
А какой подход в запросе aleks2?
Показать серверу все карты что намеревается делаться и как оно взаимосвязано.

У вас проблема в том что происходит две вещи, которые совершенно непонятно как завязаны. Даже нам, тем более серверу.

forrfor
Задания сгруппированы в пакеты
Параллельные процессы "расхватывают" себе задачи пакетами
Судя по запросу, процессы захватывают пакет, а не задачи.

Совершенно непонятно как они сгруппированы.
К примеру есть такая ситуация:
IDBATCHSTATUSTHREAD
11WAITINGNULL
21IN_PROGRESS1
31WAITING2
41IN_PROGRESS2
52WAITINGNULL
Как это интерпретировать? И почему ваш запрос перебивает для пакета 1 остальные забранные задачи этого пакета?

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

Скорее вам нужна таблица пакетов, определять состояние пакета. А также точнее и надёжнее определить процесс "захватывания" задачи (чтобы не было случайного передёргивания с наложением).
13 авг 13, 19:55    [14704410]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
invm
Member

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

Сделайте примерно так:
create view dbo.vT_DL_REQUEST_Batches
with schemabinding
as
select
 BATCH_ID,
 count_big(*) as c
from
 dbo.T_DL_REQUEST
where
 STATUS = 'WAITING';
go

create unique clustered index IX_vT_DL_REQUEST_Batches__BATCH_ID on dbo.vT_DL_REQUEST_Batches (BATCH_ID);
go
А запрос модифицируйте:
with x as
(
 select top (1)
  BATCH_ID
 from
  dbo.vT_DL_REQUEST_Batches with (noexpand, updlock, rowlock, readpast)
)
update t
 set
  STATUS = 'IN_PROGRESS'
from
 x join
 dbo.T_DL_REQUEST t on t.BATCH_ID = x.BATCH_ID;

Но все-таки лучше, как уже предлагали, завести таблицу пакетов.
13 авг 13, 22:18    [14704761]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
forrfor
Member

Откуда:
Сообщений: 49
Mnior
Скорее вам нужна таблица пакетов, определять состояние пакета.


Да, Вы правы. Это было бы логичнее. Изначально я отказался от этого из-за необходимости делать дополнительный join к большой таблице (пакетов будет очень много). Я хорошенько подумаю над этим еще раз.
Просто в моей схеме подразумевается, что если задачи внутри пакета будут "захватываться", то все сразу. Так зачем вводить понятие статуса пакета?
Спасибо, что Вы глубоко прониклись смыслом.
14 авг 13, 01:15    [14705480]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
forrfor
Member

Откуда:
Сообщений: 49
invm
forrfor,

Сделайте примерно так:
create view dbo.vT_DL_REQUEST_Batches
with schemabinding
as
select
 BATCH_ID,
 count_big(*) as c
from
 dbo.T_DL_REQUEST
where
 STATUS = 'WAITING';
go

create unique clustered index IX_vT_DL_REQUEST_Batches__BATCH_ID on dbo.vT_DL_REQUEST_Batches (BATCH_ID);
go



Понял. Только добавить group by BATCH_ID, да?

invm
[/src]А запрос модифицируйте:
with x as
(
 select top (1)
  BATCH_ID
 from
  dbo.vT_DL_REQUEST_Batches with (noexpand, updlock, rowlock, readpast)
)
update t
 set
  STATUS = 'IN_PROGRESS'
from
 x join
 dbo.T_DL_REQUEST t on t.BATCH_ID = x.BATCH_ID;

Но все-таки лучше, как уже предлагали, завести таблицу пакетов.


Спасибо за идею использовать with и from для операции update. Я правильно понял, что преимущество в том, что здесь одна операция вместо двух (select и update) в описанном в начале варианте? Именно это и поможет избежать тупиковой блокировки.
14 авг 13, 01:23    [14705500]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
aleks2
Guest
forrfor
Спасибо за идею использовать with и from для операции update. Я правильно понял, что преимущество в том, что здесь одна операция вместо двух (select и update) в описанном в начале варианте? Именно это и поможет избежать тупиковой блокировки.


with - НЕ пилюля от всех проблем, а только лишь форма записи запроса.

Это тот же самый сапрос, что и был.
14 авг 13, 06:11    [14705627]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
разве ето не проще,чем городить view+ cte ?
declare @batch_id int
set @bact_id =(SELECT TOP 1 BATCH_ID
                                  FROM T_DL_REQUEST
                                  WHERE STATUS = 'WAITING')

UPDATE T_DL_REQUEST
SET STATUS = 'IN_PROGRESS'
          , THREAD_ID = @ThreadId
WHERE BATCH_ID = @batch_id
14 авг 13, 09:39    [14705942]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
forrfor
Member

Откуда:
Сообщений: 49
aleks2
forrfor
Спасибо за идею использовать with и from для операции update. Я правильно понял, что преимущество в том, что здесь одна операция вместо двух (select и update) в описанном в начале варианте? Именно это и поможет избежать тупиковой блокировки.


with - НЕ пилюля от всех проблем, а только лишь форма записи запроса.

Это тот же самый сапрос, что и был.

Я не говорю что от всех проблем. Но вы мне посоветовали, а теперь говорите, что это не пилюля от всего. А от проблемы моей взаимоблокировки пилюля или не пилюля?
14 авг 13, 10:21    [14706147]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
forrfor
Member

Откуда:
Сообщений: 49
Maxx
разве ето не проще,чем городить view+ cte ?
declare @batch_id int
set @bact_id =(SELECT TOP 1 BATCH_ID
                                  FROM T_DL_REQUEST
                                  WHERE STATUS = 'WAITING')

UPDATE T_DL_REQUEST
SET STATUS = 'IN_PROGRESS'
          , THREAD_ID = @ThreadId
WHERE BATCH_ID = @batch_id

Ну это по сути и есть то, что я написал в первом посте. Только в этом варианте возможно, что между выбором батча и апдейтом статуса просочится другой процесс и возмет тот же батч. В моем же случае это будет тупиковая блокировка, что лучше, т.к. произойдет ошибка и процесс начнет искать себе другой пакет.
14 авг 13, 10:25    [14706179]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Maxx
Member [скрыт]

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

заврените все в танзакцию..никто не "просочится"
14 авг 13, 10:27    [14706186]     Ответить | Цитировать Сообщить модератору
 Re: Тупиковые блокировки  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Maxx, опять. Подумайте сначала. Вы совершаете несколько логических ошибок.

forrfor
Просто в моей схеме подразумевается, что если задачи внутри пакета будут "захватываться", то все сразу. Так зачем вводить понятие статуса пакета?
Ну дык тем более. Захватываются не задачи а пакет во всеми потрахами (задачами).
Если вы захватываете задачи, значит нельзя захватывать пакет, а только задачи по одной.

Нужно понятие объекта. Потому что локируются объекты (как целая сущность).
Чтобы сменить статус у объекта, нужно его залокировать - значит другие будут ждать. Если не будет сущности и разливаться на несколько элементов - то и нет понятия как локировать и начинаются проблемы (дедлоки).

Это не проблемы SQL, это основы мышления.
Надо чётко разделять понятия.

invm , согласен. Правильно сформулировать не "таблица", а индекс (кстати кластерный или уникальный?).
И зачем rowlock? Есть прецеденты?

Ну и это не решает проблемы нормализации. И ситуаций что я описал - частичный захват, потеря ранее захваченного.
И если будет другой запрос, к примеру отпускание задачи, без использования агрегатки, то опять будут проблемы. Ибо агрегатка захватывается после изменения, а не до.

Хотя я считаю это багом MS.
Т.е. лучше всегда считывать (ну хотя бы налагать локи) на все базовые индексы (кластерные), а потом уже делать что надо.
Не зря же любое изменение индекса начинает с его захвата.
14 авг 13, 11:48    [14706702]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить