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

Откуда:
Сообщений: 185
Здравствуйте! постоянно вылетает взаимоблокировка.
Ситуация такая что в одну таблицу каждые 10 секунд пишутся данные. Вначале делается delete за определенный период а потом insert

Также пользователи постоянно смотрят в эту таблицу select'ом


Как быть? Заранее благодарен
Граф дедлока прилагаю

К сообщению приложен файл. Размер - 128Kb
28 июл 14, 10:18    [16364675]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
1. Смотрите на планы
2. Начинаете передавать даиту как жату,а не как строку
3. Пытаетесь переписать запрос убрав из негоь весь мусор (ето про селект)
28 июл 14, 10:34    [16364750]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Судя по IIF, скорее всего SQL Server 2012.
Поскольку жертвой оказался SELECT, можно попробовать поиграться с TIL.
Либо SET TRANSACTION ISOLATION LEVEL SNAPSHOT для транзакции с SELECT, либо
ALTER DATABASE [ЬнВфефИфыу] SET READ_COMMITTED_SNAPSHOT ON для базы.
28 июл 14, 11:04    [16364926]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Можно ещё попробовать
ALTER INDEX [IX_FACT_POWER_TG_DT_POWER] ON [dbo].[fact_power_tg] SET ( ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF )
28 июл 14, 11:11    [16364977]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Jovanny
Можно ещё попробовать
ALTER INDEX [IX_FACT_POWER_TG_DT_POWER] ON [dbo].[fact_power_tg] SET ( ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF )

ещеб не плохо было увидеть DDL таблицы
28 июл 14, 11:35    [16365121]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
serega063
Граф дедлока прилагаю
Граф в виде xml покажите.
28 июл 14, 11:43    [16365202]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
serega063
Member

Откуда:
Сообщений: 185
Maxx
ещеб не плохо было увидеть DDL таблицы

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[fact_power_tg](
	[id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
	[dt] [datetime] NOT NULL,
	[power] [float] NULL,
	[tg_id] [varchar](255) NULL,
	[tg_number] [varchar](255) NULL,
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]

GO

SET ANSI_PADDING OFF
GO
28 июл 14, 12:16    [16365420]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
serega063,

У вас запрос слева держит UPDATE LOCK на fact_power_tg, и он точно навешан не селектом.
28 июл 14, 12:16    [16365421]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
Maxx
Member [скрыт]

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

а де индекс то ?
28 июл 14, 12:20    [16365449]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
serega063,

Как починить:
1) Поставьте read commited snapshot
2) если RCS нельзя или начнет падать, то делайте удаление с блокировкой таблицы - with(tablock) или с уровнем изоляции serializable.
28 июл 14, 12:21    [16365460]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Jovanny
Можно ещё попробовать
ALTER INDEX [IX_FACT_POWER_TG_DT_POWER] ON [dbo].[fact_power_tg] SET ( ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF )


Сразу блокировка таблицы на любой запрос... Вы знаете толк в извращениях.
28 июл 14, 12:22    [16365468]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
serega063
Member

Откуда:
Сообщений: 185
Maxx
serega063,

а де индекс то ?

Извиняюсь
CREATE TABLE [dbo].[fact_power_tg](
	[id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
	[dt] [datetime] NOT NULL,
	[power] [float] NULL,
	[tg_id] [varchar](255) NULL,
	[tg_number] [varchar](255) NULL,
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]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Index [IX_FACT_POWER_TG_DT]    Script Date: 28.07.2014 12:28:24 ******/
CREATE NONCLUSTERED INDEX [IX_FACT_POWER_TG_DT] ON [dbo].[fact_power_tg]
(
	[dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_FACT_POWER_TG_DT_POWER]    Script Date: 28.07.2014 12:28:24 ******/
CREATE NONCLUSTERED INDEX [IX_FACT_POWER_TG_DT_POWER] ON [dbo].[fact_power_tg]
(
	[dt] ASC,
	[tg_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
28 июл 14, 12:29    [16365520]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
gandjustas
Jovanny
Можно ещё попробовать
ALTER INDEX [IX_FACT_POWER_TG_DT_POWER] ON [dbo].[fact_power_tg] SET ( ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF )


Сразу блокировка таблицы на любой запрос... Вы знаете толк в извращениях.

Зато верное средство от дедлоков. Если транзакции короткие, то вообще не заметно.
И не любые запросы, а только изменяющие данные.
28 июл 14, 12:29    [16365524]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
serega063
Member

Откуда:
Сообщений: 185
gandjustas
serega063,

Как починить:
1) Поставьте read commited snapshot
2) если RCS нельзя или начнет падать, то делайте удаление с блокировкой таблицы - with(tablock) или с уровнем изоляции serializable.


т.е. вот так?
DELETE FROM [dbo].[fact_power_tg] WHERE dt >= (CAST('28.07.2014 10:00:11' AS datetime)) and dt<=(CAST('28.07.2014 10:03:11' AS datetime))  WITH (TABLOCK) 
28 июл 14, 12:32    [16365546]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
И только затрагивающие индекс IX_FACT_POWER_TG_DT_POWER.
Кстати, у ТС так и стоит. Или может успел поменять.
28 июл 14, 12:32    [16365550]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
serega063
Member

Откуда:
Сообщений: 185
Jovanny
Кстати, у ТС так и стоит. Или может успел поменять.

Успел поменять ))
28 июл 14, 12:34    [16365565]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
serega063
Member

Откуда:
Сообщений: 185
Лучше вернуть обратно?
28 июл 14, 12:34    [16365566]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Дедлоки свидетельствуют о некорректной архитектуре запросов, надо не замазывать краской, а исправлять.

автор
Вначале делается delete за определенный период а потом insert

Разделите таблицу на две части - часто обновляемую и не обновляемую, составьте представление общих данные, из него читайте.
Уберите триггеры, перенесите бизнес-логику на уровень процедур.

Также сомнителен выбор NUNERIC в качестве ID и необходимость FLOAT, но это к делу не относится.
28 июл 14, 12:39    [16365586]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
serega063
Member

Откуда:
Сообщений: 185
Владислав Колосов
Дедлоки свидетельствуют о некорректной архитектуре запросов, надо не замазывать краской, а исправлять.

Разделите таблицу на две части - часто обновляемую и не обновляемую, составьте представление общих данные, из него читайте.
Уберите триггеры, перенесите бизнес-логику на уровень процедур.


В том то и проблема что данные нужно читать очень оперативно

Владислав Колосов
Также сомнителен выбор NUNERIC в качестве ID и необходимость FLOAT, но это к делу не относится.

Ну у Hibernate по умолчанию так, уж не стали переделывать
28 июл 14, 12:42    [16365609]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
serega063
gandjustas
serega063,

Как починить:
1) Поставьте read commited snapshot
2) если RCS нельзя или начнет падать, то делайте удаление с блокировкой таблицы - with(tablock) или с уровнем изоляции serializable.


т.е. вот так?
DELETE FROM [dbo].[fact_power_tg] WHERE dt >= (CAST('28.07.2014 10:00:11' AS datetime)) and dt<=(CAST('28.07.2014 10:03:11' AS datetime))  WITH (TABLOCK) 


Почти
DELETE FROM [dbo].[fact_power_tg] WITH (TABLOCK)  WHERE dt >= (CAST('28.07.2014 10:00:11' AS datetime)) and dt<=(CAST('28.07.2014 10:03:11' AS datetime))  


А блокировки на индексе не трогайте, просадите быстродейтсвие чтения в разы.


ЗЫ. Покажите весь "читающий" запрос, кто в нем вешает Update Lock и зачем?
28 июл 14, 12:45    [16365640]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Jovanny
gandjustas
пропущено...


Сразу блокировка таблицы на любой запрос... Вы знаете толк в извращениях.

Зато верное средство от дедлоков. Если транзакции короткие, то вообще не заметно.
И не любые запросы, а только изменяющие данные.


Именно на любые, Shared Lock на чтение никто не отменял.

У ТС проблема как раз в том, что запрос длинный и идут параллельно чтения\записи.
28 июл 14, 12:46    [16365656]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
serega063
Member

Откуда:
Сообщений: 185
gandjustas,
Да вроде не длинный select

select t2.dt, 
       IIF(( DATEDIFF(mi, dt, GETDATE()) ) >= 20, null, t2.power) as power, 
       t1.tg_number, 
       IIF(( DATEDIFF(mi, dt, GETDATE()) ) >= 15, -1, null) as [корректность]

from   (select 'TG1' as tg_number 
        union all 
        select 'TG2' as tg_number 
        union all 
        select 'TG3' as tg_number 
        union all 
        select 'TG5' as tg_number 
        union all 
        -- select 'TG6' as tg_number union all  
        select 'TG7' as tg_number 
        union all 
        select 'TG8' as tg_number 
       --select 'TG9' as tg_number union all  
       --select 'TG10' as tg_number  
       )t1 
       left join (select s1.* 
                  from   [dbo].[fact_power_tg] s1, 
                         (select tg_id, 
                                 MAX([dt]) dt 
                          from   [fact_power_tg] 
                          group  by tg_id)s2 
                  where  s1.tg_id = s2.tg_id 
                         and s1.dt = s2.dt)t2 
              on t1.tg_number = t2.tg_number 
28 июл 14, 12:53    [16365702]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
serega063,

Кстати рекомендовал бы переписать в джоине запрос
select s1.*
from fact_power_tg s1,
       (select tg_id, max(dt) dt from fact_power_tg group by tg_id) s2
where s1.tg_id = s2.tg_id


на
select /*конкретный набор полей*/ from
(select /*конкретный набор полей*/, rank () over (partition by tg_id order by dt desc) as rank from fact_power_tg)
where rank = 1


Ну и сделать индекс по (tg_id, dt desc), может и проблем с дедлоками меньше будет.
28 июл 14, 12:54    [16365708]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
serega063
gandjustas,
Да вроде не длинный select


А транцзакции? Уровень изоляции? Кто UpdateLock навесил?

Покажите Deadlock graph в виде XML, там эта информация должна быть. Если нет, то надо будет собрать через extended events.
28 июл 14, 12:57    [16365724]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с взаимоблокировкой  [new]
serega063
Member

Откуда:
Сообщений: 185
gandjustas
serega063
gandjustas,
Да вроде не длинный select


А транцзакции? Уровень изоляции? Кто UpdateLock навесил?

Покажите Deadlock graph в виде XML, там эта информация должна быть. Если нет, то надо будет собрать через extended events.


https://yadi.sk/d/F4xgB8MYY3Zyf
28 июл 14, 13:06    [16365773]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить