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

Откуда:
Сообщений: 19
Доброе время суток, я делаю работу по MS sql и столкнулась с такой проблемой, мне необходимо запустить 2 транзакции одновременно, так что бы они начали по очереди взаимоблокировать доступ друг друга к таблице, я изучаю эскалацию блокировок, и мне необходимо реализовать такой опыт. Проблема в том, что из клиентского приложения такое сделать не получается, так как пока не выполнится 1 транзакция не могу начать выполнять другу, подскажите пожалуйста как этого можно добиться.
31 мар 17, 02:47    [20353001]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
o-o
Guest
cat94
мне необходимо запустить 2 транзакции одновременно, так что бы они начали по очереди взаимоблокировать доступ друг друга к таблице, я изучаю эскалацию блокировок, и мне необходимо реализовать такой опыт.

Вы уж разберитесь, дедлоки вам надо получать или эскалацию. Для эскалации не надо конфликтных транзакций, надо более 5000 локов на statement
31 мар 17, 07:18    [20353067]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18326
В менеджмент студио можно открыть транзакцию Begin tran и не комитить.

Т.е. открываете два окна. В одном пишете Begin Tran; Update Table1. Во втором Begin Tran; Update Table2.
Затем в первом Update table2;Commit tran. И во втором Update table1;Commit tran. Вот вам и дедлок.
31 мар 17, 08:23    [20353124]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
тут прям действительно неясно, блокировки, эскалацию или дедлок получить
31 мар 17, 09:45    [20353292]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
o-o
Guest
— Сыру хочешь?
— Ха! Спрашиваешь!
— Или котлету?
— Или котлету!
— А чего больше?
— А всего побольше, сыру побольше и котлет побольше!
31 мар 17, 09:57    [20353353]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
cat94
Member

Откуда:
Сообщений: 19
Прошу прощения если запутала, суть эксперимента в том, что бы запустив 2 транзакции параллельно и узнать как влияет уменьшение или увеличения объемов блокировки (эскалации) на время выполнения этих транзакций, для этого то мне и нужно запустить их одновременно, я понимаю что проще всего запустить это на двух локальных машинах, но такой возможности нет.
31 мар 17, 19:27    [20355859]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
cat94,

Плохо понимаете. Две транзакции можно открыть и с одной локальной машины. Вам необходимо две сессии подключения к сиквелу. Запустите два экземпляра клиента который ваш запрос выполняет.
31 мар 17, 20:35    [20355967]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
cat94
Member

Откуда:
Сообщений: 19
Подскажите пожалуйста, что я делаю не так , выполняю запрос

declare @start datetime  
set @start = getdate()
 UPDATE tb1 with (TABLOCK) SET name = 'Оля' where name = 'УРК';
  UPDATE tb1 with (TABLOCK) SET name = 'УРК' where name = 'Оля';
   UPDATE tb1 with (TABLOCK) SET name = 'Оля' where name = 'УРК';
select datediff(ms,@start,getdate())


и меняя TABLOCK, ROWLOCK, PAGLOCK время выполнения не меняется.
16 апр 17, 23:00    [20406193]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18326
cat94
Подскажите пожалуйста, что я делаю не так , выполняю запрос

declare @start datetime  
set @start = getdate()
 UPDATE tb1 with (TABLOCK) SET name = 'Оля' where name = 'УРК';
  UPDATE tb1 with (TABLOCK) SET name = 'УРК' where name = 'Оля';
   UPDATE tb1 with (TABLOCK) SET name = 'Оля' where name = 'УРК';
select datediff(ms,@start,getdate())



и меняя TABLOCK, ROWLOCK, PAGLOCK время выполнения не меняется.
Так измерять эффективность запроса нельзя в принципе.
Кроме того запись о блокировке обычно занимает значительно меньше времени, чем изменения данных.
16 апр 17, 23:11    [20406208]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18326
Самое простое как измерить отношение разницы в планах, это запустить 2 запроса, и менджмент студия покажет процент сколько потрачено на каждый запрос.
16 апр 17, 23:14    [20406216]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18326
Ну и главное, хинты TABLOCK, ROWLOCK, PAGLOCK это логические блокировки - не физические.
Не будет он в транзакционный лог копировать данные.

В этом, наверное, у вас вопрос.
16 апр 17, 23:19    [20406229]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
cat94
Member

Откуда:
Сообщений: 19
Deff, Я просто пытаюсь построить зависимость производительсноти от уровня блокировок, но видимо выбрала неудачный способ. Подскажите пожалуйста как и где лучше изменять уровень эскалации и ещё вопросик можно ли где нибуть посмотреть сколько тратиться ресурсов (памяти) на выполнение того или иного запроса.
17 апр 17, 00:06    [20406298]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18326
cat94
Deff, Я просто пытаюсь построить зависимость производительсноти от уровня блокировок, но видимо выбрала неудачный способ.
Смотрите. MSSQL сам выбирает, какой способ блокировки ему выбирать. Чаще всего это PAGLOCK.
Логические хинты нужны, если пользователю виднее, чем серверу.
Например, вы хотите забрать всю таблицу монопольно, для каких-то действий.
Или у вас по бизнес процессам часто разные транзакции обращаются к записям одной странице, и подолгу лочаться.
Тогда можно указать, чтобы лочилась не страница, и только запись. Вы выиграете в скорости на блокировках.
Но правило остается навсегда: "требуется указание хинтов" = "плохое проектирование базы".

Ну и важный момент, если вы укажете ROWLOCK, а изменять надо очень много записей/страниц, вот тут вы можете проиграть как раз на скорости блокировки.
MSSQL, кстати, говорит, что может отменить решение пользователя, и уйти на свою блокировку. Но если вы точно знаете, что меняете одну запись, то можете поставить ROWLOCK. Потерь в скорости не будет.
17 апр 17, 00:33    [20406312]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
cat94
Member

Откуда:
Сообщений: 19
Deff, Так а есть ли способ оценить этот выигрышный во времени, то как я говорила выше ставя блокировку в запросе, время не меняется, оценивая время тем способом что я применила.
17 апр 17, 00:47    [20406318]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36965
Deff
Но правило остается навсегда: "требуется указание хинтов" = "плохое проектирование базы".
Это такой наивный идеал, который имеет смысл впаривать новичкам. В реальной жизни при первой же ночной побудке в праздничные выходные нужный план (особенно, если других вариантов не предусматривается) легчайше прибивается хинтами. Никому не будет охота ждать, когда сервер в следующий заблудится в трех статистиках и не начнет (к примеру) лупать сканами две времянки по 100к только потому, что при построении плана на предыдущей итерации они были пустыми.
Deff
MSSQL, кстати, говорит, что может отменить решение пользователя, и уйти на свою блокировку.
Ссылку можно, где он так говорит?
Deff
Но если вы точно знаете, что меняете одну запись, то можете поставить ROWLOCK.
А если я меняю 1к из 1ккк?

Сообщение было отредактировано: 17 апр 17, 00:59
17 апр 17, 00:51    [20406320]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
cat94
Member

Откуда:
Сообщений: 19
Я выполняю запроса

declare @start datetime  
set @start = getdate()
  UPDATE tb1 with (ROWLOCK) SET name = 'Оля' where name = 'УРК';
select datediff(ms,@start,getdate())


И время затраченное на это равняется примерно 3500 мм это 2 миллиона записей он вынимает около 800 тысяч.
потом выполню:

declare @start datetime  
set @start = getdate()
 UPDATE tb1 with (TABLOCK) SET name = 'УРК' where name = 'Оля';
select datediff(ms,@start,getdate())


и время составляет теже 3500 мм, хотя должно быть меньше, он же не записывает все эти 800 тысяч записей в журнал блокировок.
17 апр 17, 01:01    [20406323]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36965
cat94
хотя должно быть меньше
Откуда уверенность, что "журнал блокировок" (что бы это ни было) является узким местом при выполнении вашего запроса?
17 апр 17, 01:58    [20406346]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
cat94
Member

Откуда:
Сообщений: 19
Гавриленко Сергей Алексеевич, Возможно это не так называется, но когда я ставлю блокировку она куда то заноситься, и я думаю что есть разница между тем, что бы занести миллион блокировок в одном запросе или одну.
17 апр 17, 02:06    [20406347]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36965
cat94
Гавриленко Сергей Алексеевич, Возможно это не так называется, но когда я ставлю блокировку она куда то заноситься, и я думаю что есть разница между тем, что бы занести миллион блокировок в одном запросе или одну.
Примерно такая же разница, как если перетаскать тонну цемента на своем горбу на расстояние километра и сказать "ух ты, как тяжело" 20 раз после каждого 50кг мешка, или один раз в конце.

Сообщение было отредактировано: 17 апр 17, 02:15
17 апр 17, 02:10    [20406351]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1733
cat94
Я выполняю запроса

declare @start datetime  
set @start = getdate()
  UPDATE tb1 with (ROWLOCK) SET name = 'Оля' where name = 'УРК';
select datediff(ms,@start,getdate())


И время затраченное на это равняется примерно 3500 мм это 2 миллиона записей он вынимает около 800 тысяч.
потом выполню:

declare @start datetime  
set @start = getdate()
 UPDATE tb1 with (TABLOCK) SET name = 'УРК' where name = 'Оля';
select datediff(ms,@start,getdate())


и время составляет теже 3500 мм, хотя должно быть меньше, он же не записывает все эти 800 тысяч записей в журнал блокировок.

А кто сказал что при ROWLOCK Это всегда будут только построчное наложение lock

BEGIN TRAN
SELECT COUNT(*) FROM ein.[Document] AS d WITH(ROWLOCK, SERIALIZABLE)

sp_lock 56

56	28	0	        0	DB	                                	S	GRANT
56	28	725577623	0	TAB	                                	S	GRANT

Сервер не дурак...
BEGIN TRAN
SELECT COUNT(*) FROM ein.[Document] AS d WITH(ROWLOCK, SERIALIZABLE)
WHERE d.Id_Document = 1

56	28	0	        0	DB	                                	S	GRANT
56	28	725577623	1	KEY	(8194443284a0)                          S	GRANT
56	28	725577623	1	PAG	1:411872                        	IS	GRANT
56	28	725577623	0	TAB	                                	IS	GRANT
17 апр 17, 10:37    [20406849]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1733
P.S. Например
BEGIN TRAN

SELECT NULL FROM ein.[Document] AS d WITH(ROWLOCK, SERIALIZABLE)
WHERE d.Id_Document <= 1700


уже кладет S на таблицу, а не построчно

а
BEGIN TRAN

SELECT NULL FROM ein.[Document] AS d WITH(ROWLOCK, SERIALIZABLE)
WHERE d.Id_Document <= 1600

уже накладывает RangeS-S на ключи индекса и IS на страницы и таблицу
17 апр 17, 10:42    [20406858]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
cat94
и я думаю что есть разница между тем, что бы занести миллион блокировок в одном запросе или одну.
Конечно есть. Только в вашем примере эта разница не видна на фоне затрат на изменение данных.
Хотите увидеть - update не должен реально изменять данные. Например так:
use tempdb;
go

create table dbo.t (id int identity primary key, v int);
go

insert into dbo.t
select top (1000000)
 1
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

declare @v int;

set statistics time on;

update dbo.t with (rowlock) set v = 2 where v = 0 option (maxdop 1);
update dbo.t with (tablock) set v = 2 where v = 0 option (maxdop 1);

set statistics time off;
go

drop table dbo.t;
go

/*
(1000000 row(s) affected)

 Время работы SQL Server:
   Время ЦП = 280 мс, затраченное время = 281 мс.

(0 row(s) affected)

 Время работы SQL Server:
   Время ЦП = 47 мс, затраченное время = 43 мс.

(0 row(s) affected)
*/
17 апр 17, 12:55    [20407347]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
cat94
Member

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

Спасибо за скрипт, все наглядно , а такой ещё вопросик почему когда добавляешь блокировку на уровне страницы, время такое же
как и на уровне таблице, ведь на уровне страницы он должен блокировать не такое уж и большое количество записей в отличие от таблицы.

update dbo.t with (paglock) set v = 2 where v = 0 option (maxdop 1);


или это какая то особенность сервера? Я конечно читала что он перепрыгивает с уровня записи до уровня таблицы минуя страницу, но тут же я задаю уровень.
29 апр 17, 17:00    [20446140]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
cat94
Member

Откуда:
Сообщений: 19
declare @v int;

set statistics time on;

update dbo.t with (rowlock) set v = 2 where v = 0 option (maxdop 1);
update dbo.t with (paglock) set v = 2 where v = 0 option (maxdop 1);
update dbo.t with (tablock) set v = 2 where v = 0 option (maxdop 1);

set statistics time off;
go

/*
 Время работы SQL Server:
   Время ЦП = 312 мс, затраченное время = 305 мс.

(строк обработано: 0)

 Время работы SQL Server:
   Время ЦП = 31 мс, затраченное время = 41 мс.

(строк обработано: 0)

 Время работы SQL Server:
   Время ЦП = 47 мс, затраченное время = 41 мс.

(строк обработано: 0)
*/
29 апр 17, 17:02    [20446143]     Ответить | Цитировать Сообщить модератору
 Re: Конфликтные транзакции  [new]
invm
Member

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

Страниц в такой таблице мало, около 4000. Поэтому влияние на время выполнения несущественное.
29 апр 17, 17:46    [20446185]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить