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

Откуда:
Сообщений: 524
День добрый!
Интересует такой вопрос...
Есть процедура, которая добавляет строку в таблицу при определенных условиях, соответственно ID для этого условия увеличивается на 1. Код процедуры примерно такой:
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

	BEGIN TRAN

	DECLARE @n INT

	SET @n = (SELECT max(Id)FROM MayTable) WHERE ...

	INSERT INTO MyTaBle (Id,Col_1)
	SELECT @n,'xyz'

	COMMIT
END


Хотелось бы быть уверенным, что если практически одновременно запускаются две процедуры, то что происходит в строке:
SET @n = (SELECT max(Id)FROM MayTable) WHERE ...

?
Могу ли я быть уверенным, что одна из процедур блокирует чтение из таблицы до тех пор, пока не закончится транзакция, либо нужны какие-то дополнительные подсказки?

Спасибо!
9 фев 17, 18:06    [20197966]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
Владислав Колосов
Member

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

SELECT max(Id)FROM MayTable


так уже давно никто не делает, используйте Idenitity.
9 фев 17, 18:12    [20198002]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
abrashka
Member

Откуда:
Сообщений: 524
Владислав Колосов,
Я сильно упростил процеду, для наглядности, в оригинале Identity- это другое поле и Id не уникальное. В моем примере есть условие, грубо говоря, если "Европа" то возьми последнее значение Id с Европой, и добавь новую строку с Id+1, если Америка, то...
Вопрос в том, что если будут запущены две процедуры с Европой, и если последнее значение Id было скажем 8 и "первая" процедура еще не успела внести новую строку в таблицу, то значит ли, что "вторая" процедура тоже вычислит 8 как и "первая", т.е. новая строка будет 9, т.е. обе процедуры внесут строки с ID=9? Либо первая процедура заблакирует считывание из таблицы пока не закончится транзакция?
9 фев 17, 18:29    [20198040]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Блокировки на чтение совместимы друг с другом, поэтому параллельную выборку вы не заблокируете таким образом.
9 фев 17, 18:35    [20198053]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
abrashka
Member

Откуда:
Сообщений: 524
Гавриленко Сергей Алексеевич,
Вот этого я и боюсь :(
Необходимо, чтоб "первая" процедура блокировала таблицу даже на чтение до того, как она внесет новую строку, т.е. закончит транзакцию.
Как можно этого добиться?
9 фев 17, 18:51    [20198105]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
abrashka
Необходимо, чтоб "первая" процедура блокировала таблицу даже на чтение до того, как она внесет новую строку, т.е. закончит транзакцию.
На чтение-то зачем целиком таблицу блокировать?

Для обеспечения непересекаемости по @n нужно:
1. Индекс по Id, если его еще нет.
2.
SET @n = (SELECT max(Id)FROM MayTable with (serializable) WHERE ...)
9 фев 17, 19:16    [20198182]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
abrashka
Member

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

Спасибо!
Т.е. если в начале транзакции стоит:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

этого не достаточно?
9 фев 17, 19:50    [20198253]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
abrashka
Member

Откуда:
Сообщений: 524
Точнее в начале процедуры:
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

	BEGIN TRAN

	DECLARE @n INT

	SET @n = (SELECT max(Id)FROM MayTable) WHERE ...
9 фев 17, 19:51    [20198255]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
invm
Member

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

Во-первых, я ошибся. Нужно так:
SET @n = (SELECT max(Id)FROM MayTable with (updlock, serializable) WHERE ...)

Во-вторых, зачем делать всю транзакцию serializable, если в этом нет необходимости?
9 фев 17, 20:00    [20198270]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
в-третьих, можно сериализовать вызовы процедуры не повышая уровень изоляции - с помощью sp_getapplock.
9 фев 17, 20:03    [20198276]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
abrashka
Member

Откуда:
Сообщений: 524
invm,
Ну раз можно (updlock, serializable) то видимо незачем :)


T.e. updlock означает, что select блокирует изменение считываемой селектом данных другой транзакцией?
А serializable означает, что другая транзакция не может добавить или изменить строки, которые считываются нынешней транзакцией?


В любом случае, если до сих пор вся процедура была serializable, то это означает что проблема с двойниками была решена?

Спасибо!
9 фев 17, 20:13    [20198312]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
invm
Member

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

Для борьбы с "двойниками" используется updlock + serializable
updlock - чтобы одновременно нельзя было прочитать одну и ту же строку разными транзакциями.
serializable - для случая, когда таблица еще пустая.

Просто serializable проблему не решает.
9 фев 17, 20:52    [20198432]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8839
Вопрос в том, что вставки будут вызывать простои загрузки в такой ситуации. Во избежание этого и сохранении уникальности нумерации данные надо сваливать в промежуточную кучу, а затем загрузчиком, одним процессом, помещать в рабочую таблицу с нумерацией.
10 фев 17, 11:56    [20199856]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
abrashka
Member

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

Для борьбы с "двойниками" используется updlock + serializable
updlock - чтобы одновременно нельзя было прочитать одну и ту же строку разными транзакциями.
serializable - для случая, когда таблица еще пустая.

Просто serializable проблему не решает.

Еще раз прошу прощения, судя по всему я туплю, но хотелось бы понять как это работает...
Еще раз, есть процедура, которая в первом шаге находит последнее значение ID для определенного условия и во втором действии добавляет строку с ID=ID+1
Если в первом шаге я добавляю with (updlock, serializable), то что произойдет в случае если две процедуры были запущены практически одновременно?
Скажем первая в первом шаге нашла что @n=7, но еще не успела выполнить врои шаг, то что произойдет со второй процедурой?
Она тоже запускает первое действие и в таком случае она тоже получит @n=7?
Т.е. (updlock, serializable) действует только на протяжении селекта или всей транзакции?
Мне нужно, чтоб вторая процедура "ждала" пока первая выполнит второе действие, т.е. внесет новую строку, тогда во второй процедуре @n будет равен 8.
Или я чего-то не понимаю?
12 фев 17, 17:11    [20204933]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
aleks2
Guest
Проще надо быть.

create table MyTable(id int primary key with(ignore_DUP_key = ON) ... );

...

INSERT MyTable(Id ... ) SELECT max(Id) + 1 FROM MyTable;
while @@rowcount = 0 INSERT MyTable(Id ... ) SELECT max(Id) + 1 FROM MyTable;
12 фев 17, 18:48    [20205079]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
лолл
Member

Откуда:
Сообщений: 450
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN

	DECLARE @n INT

	SET @n = (SELECT max(Id) FROM MayTable WITH (XLOCK, HOLDCLOCK)) WHERE ...

	INSERT INTO MyTaBle (Id,Col_1)
	SELECT @n,'xyz'

	COMMIT
END


Почему я пишу XLOCK вместо UPDLOCK? Потому что нужно запретить возможным параллельным транзакциям считать эти данные до конца текущей транзакции, тк параллельные при этом могут получить неверное значение max(Id) - в результате выполнения текущей транзакции значение max(Id) увеличиться.
13 фев 17, 09:44    [20206110]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
лолл
Member

Откуда:
Сообщений: 450
XLOCK, HOLDLOCK

Опечатка))
13 фев 17, 09:44    [20206115]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
welter
Member

Откуда:
Сообщений: 38
abrashka
День добрый!
Интересует такой вопрос...
Есть процедура, которая добавляет строку в таблицу при определенных условиях, соответственно ID для этого условия увеличивается на 1. Код процедуры примерно такой:
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

	BEGIN TRAN

	DECLARE @n INT

	SET @n = (SELECT max(Id)FROM MayTable) WHERE ...

	INSERT INTO MyTaBle (Id,Col_1)
	SELECT @n,'xyz'

	COMMIT
END


Хотелось бы быть уверенным, что если практически одновременно запускаются две процедуры, то что происходит в строке:
SET @n = (SELECT max(Id)FROM MayTable) WHERE ...

?
Могу ли я быть уверенным, что одна из процедур блокирует чтение из таблицы до тех пор, пока не закончится транзакция, либо нужны какие-то дополнительные подсказки?

Спасибо!



достаточно так:

declare @n int
begin tran
select @n =max(Id) from dbo.MayTable....(updlock)
insert into dbo.MayTable(id,....)
values(@n+1,...)
commit

P.S.
уровень изоляции по-умолчанию: READ COMMITED)
14 фев 17, 11:22    [20210089]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
welter
достаточно так:

declare @n int
begin tran
select @n =max(Id) from dbo.MayTable....(updlock)
insert into dbo.MayTable(id,....)
values(@n+1,...)
commit
А теперь подумайте, что произойдет, когда dbo.MayTable еще пустая.
14 фев 17, 11:34    [20210132]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
welter
Member

Откуда:
Сообщений: 38
invm
welter
достаточно так:

declare @n int
begin tran
select @n =max(Id) from dbo.MayTable....(updlock)
insert into dbo.MayTable(id,....)
values(@n+1,...)
commit
А теперь подумайте, что произойдет, когда dbo.MayTable еще пустая.


isnull(@n,0)
14 фев 17, 11:46    [20210181]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
TaPaK
Member

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

и все вставят 1?
14 фев 17, 11:48    [20210190]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
welter
Member

Откуда:
Сообщений: 38
TaPaK
welter,

и все вставят 1?


.....
TaPaK, не понял вопроса...
в первой процедуре updlock в данном случае накладывает блокировку KEY X/IX.
вторая процедура определит максимальный id, послк завершения транзакции в первой проце
14 фев 17, 11:56    [20210213]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
welter
TaPaK
welter,

и все вставят 1?


.....
TaPaK, не понял вопроса...
в первой процедуре updlock в данном случае накладывает блокировку KEY X/IX.
вторая процедура определит максимальный id, послк завершения транзакции в первой проце

пустая таблица
14 фев 17, 11:58    [20210223]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
welter
в первой процедуре updlock в данном случае накладывает блокировку KEY X/IX.
Таблица пустая - KEY X не будет.
Именно поэтому нужен serializable.
14 фев 17, 12:01    [20210231]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками при одновременном запуске нескольких процедур  [new]
welter
Member

Откуда:
Сообщений: 38
invm
welter
в первой процедуре updlock в данном случае накладывает блокировку KEY X/IX.
Таблица пустая - KEY X не будет.
Именно поэтому нужен serializable.


при select update конечно не будет, будет блокировка IU на страницу
после вставки появится блокировка ключа (в транзакции)
index_id resource mode
NULL OBJECT IX
1 PAGE IX
1 KEY X -- после insert
14 фев 17, 12:38    [20210365]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить