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

Откуда: Москва
Сообщений: 331
Ситуация следующая.
Есть необходимость выполнить блок кода в хранимой процедуре в условиях запрета изменения данных в конкретной таблице. Блокировать таблицу совсем на время выполнения блока не выход - остальные клиенты будут ждать, и могут ждать долго. В MSSQL 2005 появился уровень изоляции транзакций SNAPSHOT, который для моих целей подходит. Но есть один момент: для того, чтобы использовать уровень изоляции SNAPSHOT необходимо изменить атрибут базы данных:
из документации

Перед запуском транзакции, использующей уровень изоляции моментальных снимков, необходимо установить аргумент базы данных ALLOW_SNAPSHOT_ISOLATION в ON. Если транзакция с уровнем изоляции моментальных снимков обращается к данным из нескольких баз данных, аргумент ALLOW_SNAPSHOT_ISOLATION должен быть включен в каждой базе данных.

а вот пример использования
ALTER DATABASE [OS]
  SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
 -- блок кода


COMMIT TRANSACTION;
ALTER DATABASE [OS]
  SET ALLOW_SNAPSHOT_ISOLATION OFF;
Вроде все хорошо, до тех пор пока этой процедурой не начинает пользоваться одновременно 2 и более человек. В этом случае хорошо тому, кто успел первым запустить процедуру на выполнение, второй же получает сообщение об ошибке:
ALTER DATABASE failed because a lock could not be placed in database 'mydatabasename'. Try again later.
Таким образом получается, что одновременно 2 клиента вероятно не могут установить атрибут базы данных ALLOW_SNAPSHOT_ISOLATION. Хотя в документации написано следующее:

При изменении состояния параметра ALLOW_SNAPSHOT_ISOLATION (из ON в OFF или из OFF в ON) инструкция ALTER DATABASE не возвращает управления вызвавшей ее программе, пока все существующие транзакции в базе данных не будут зафиксированы. Если база данных уже находится в состоянии, указанном в инструкции ALTER DATABASE, управление вызвавшей программе будет возвращено немедленно.

Но в моем случае второй клиент, пытающийся установить атрибут получает сообщение об ошибке. Как действовать пока непонятно. Буду благодарен за совет или ценные знания.
15 июл 09, 14:16    [7418906]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36803
Эээ, а нафига базу каждый раз в снапшот переключать, а потом обратно???
15 июл 09, 14:19    [7418932]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А задлянафига базу при кажном вызове процедуры альтерить?
Установили разок и забыли - снапшот изоляция будет вставать только при явном её хинтовании.
15 июл 09, 14:20    [7418938]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
delphinchik
Member

Откуда: Москва
Сообщений: 331
Гавриленко Сергей Алексеевич
Эээ, а нафига базу каждый раз в снапшот переключать, а потом обратно???

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

Перед запуском транзакции, использующей уровень изоляции моментальных снимков, необходимо установить аргумент базы данных ALLOW_SNAPSHOT_ISOLATION в ON. Если транзакция с уровнем изоляции моментальных снимков обращается к данным из нескольких баз данных, аргумент ALLOW_SNAPSHOT_ISOLATION должен быть включен в каждой базе данных.

Ну а я как аккуратный пользователь - где взял туда и вернул ...
Вы хотите сказать что этот атрибут можно базе выставить "навечно"?
15 июл 09, 14:45    [7419140]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
delphinchik
Вы хотите сказать что этот атрибут можно базе выставить "навечно"?


Ага.
15 июл 09, 14:47    [7419150]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2delphinchik
а что, там написано, что для нормальной работы базы нужно вернуть все взад?

для спящего время бодрствования равносильно сну
15 июл 09, 14:49    [7419166]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
delphinchik
Member

Откуда: Москва
Сообщений: 331
tpg
А задлянафига базу при кажном вызове процедуры альтерить?
Установили разок и забыли - снапшот изоляция будет вставать только при явном её хинтовании.

Т.е. другими словами атрибут я могу выставить ив дальнейшем не обращать на него внимания. А в транзакциях уровень изоляции snapshot необходимо указывать явно, незавсисимо от установленного атрибута базы данных. Не имеет ли этот атрибут какого то "побочного" действия?
15 июл 09, 14:49    [7419169]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
delphinchik
Member

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

Что ж, хорошо коли так. Спасибо :-).
15 июл 09, 14:50    [7419176]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
delphinchik
Т.е. другими словами атрибут я могу выставить ив дальнейшем не обращать на него внимания. А в транзакциях уровень изоляции snapshot необходимо указывать явно, незавсисимо от установленного атрибута базы данных. Не имеет ли этот атрибут какого то "побочного" действия?


Да. Да. Практически нет.
15 июл 09, 14:50    [7419181]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
А в транзакциях уровень изоляции snapshot необходимо указывать явно, незавсисимо от установленного атрибута базы данных.


Мое второе "да" касается только необходимости явного указания TIL. Но вот если опция не включена, то у Вас не получится установить этот TIL и будет ошибка.
15 июл 09, 14:52    [7419206]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
delphinchik
Member

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

Да, это я знаю (про ошибку). Спасибо за информацию.
15 июл 09, 14:53    [7419211]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
кроме того, что данные будут получаться без хинтов нолок, как если бы была обычная транзакция и у селектов ставить nolock. но за счет этого будет меньше блокировок. все записывается 1 раз в момент комита..

для спящего время бодрствования равносильно сну
15 июл 09, 14:54    [7419226]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
delphinchik

Т.е. другими словами атрибут я могу выставить ив дальнейшем не обращать на него внимания. А в транзакциях уровень изоляции snapshot необходимо указывать явно, незавсисимо от установленного атрибута базы данных. Не имеет ли этот атрибут какого то "побочного" действия?
Более того, если вы выставете базе READ_COMMITTED_SNAPSHOT в ON, то все транзакции с уровнем изоляции READ_COMMITTED вместо блокировки будут использовать управление версиями строк - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ru/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm
15 июл 09, 14:54    [7419229]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
а ссылку в MSDN на сайте мелкомягких можно плиз?

для спящего время бодрствования равносильно сну
15 июл 09, 14:57    [7419247]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
delphinchik
Member

Откуда: Москва
Сообщений: 331
Алексей2003
кроме того, что данные будут получаться без хинтов нолок, как если бы была обычная транзакция и у селектов ставить nolock. но за счет этого будет меньше блокировок. все записывается 1 раз в момент комита..

для спящего время бодрствования равносильно сну

Вы имеете в виду постоянное использование атрибута = ON?

Т.е. при включенном атрибуте
select * from tablename 
=
select * from tablename (nolock)
?
15 июл 09, 14:58    [7419251]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Алексей2003
а ссылку в MSDN на сайте мелкомягких можно плиз?

для спящего время бодрствования равносильно сну
Это ссылка на статью ALTER DATABASE.
15 июл 09, 14:59    [7419256]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
delphinchik
Member

Откуда: Москва
Сообщений: 331
tpg
delphinchik

Т.е. другими словами атрибут я могу выставить ив дальнейшем не обращать на него внимания. А в транзакциях уровень изоляции snapshot необходимо указывать явно, незавсисимо от установленного атрибута базы данных. Не имеет ли этот атрибут какого то "побочного" действия?
Более того, если вы выставете базе READ_COMMITTED_SNAPSHOT в ON, то все транзакции с уровнем изоляции READ_COMMITTED вместо блокировки будут использовать управление версиями строк - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ru/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm

Да, об этом я читал в документации, но в моем случае нужен именно снимок.
15 июл 09, 15:01    [7419273]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
примерно да.
если уж быть совсем точным, и на пальцах то следующая:
1 строка у таблицы. значение в колонке 1.
открываем первую транзакцию селект, получаем 1, делаем апдейт на 2. ничего не комитим
открываем вторую транзакцию селект, получаем 2, делаем апдейт на 3. ничего не комитим
открываем третью транзакцию селект, получаем 3, делаем апдейт на 1. ничего не комитим.
ну и т.д.

для спящего время бодрствования равносильно сну
15 июл 09, 15:04    [7419289]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
delphinchik
Т.е. при включенном атрибуте
select * from tablename 
=
select * from tablename (nolock)
?


ой, совсем не так. При включенном режиме версионности и RС TIL Вы никогда не получите грязные данные и не будут накладываться разделяемые блокировки на читаемые Вами данные. Т.е. читатель не будет мешать писателю и наоборот, так как для записей, изменных, но не закоммиченных в другой сессии будет использоваться версии из версионного хранилища в tempdb.

Сообщение было отредактировано: 15 июл 09, 16:08
15 июл 09, 16:08    [7419758]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
pkarklin
delphinchik
Т.е. при включенном атрибуте
select * from tablename 
=
select * from tablename (nolock)
?


ой, совсем не так. При включенном режиме версионности и RС TIL Вы никогда не получите грязные данные и не будут накладываться разделяемые блокировки на читаемые Вами данные. Т.е. читатель не будет мешать писателю и наоборот, так как для записей, изменных, но не закоммиченных в другой сессии будет использоваться версии из версионного хранилища в tempdb.

ой ли?
было @val = 2
1ый пользователь меняет данные в строке на @val = 4 и еще час обработки (транзакция не комичена).
2ой пользователь считывает данные в этой строке и устанавливает значение для себя @val|2! транзакцию комитит.
но тут 1ый пользователь грит, не, все взад. что будет со 2ой транзакцией?
15 июл 09, 16:11    [7419784]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Алексей2003,

Во-первых, речь шла о сравнении обычного селекта с включенной версионностью и селекта с NOLOCK, что может дать совершенно разные данные.

Далее:

автор
1 строка у таблицы. значение в колонке 1.
открываем первую транзакцию селект, получаем 1, делаем апдейт на 2. ничего не комитим
открываем вторую транзакцию селект, получаем 2, делаем апдейт на 3. ничего не комитим
открываем третью транзакцию селект, получаем 3, делаем апдейт на 1. ничего не комитим.
ну и т.д.


При включенной версионности вторая и третья транзакции получат 1, а не 2 и 3.
15 июл 09, 16:17    [7419826]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2pkarkling
а как же тогда это:
MSDN
Операции считывания, выполняемые транзакцией моментальных снимков, извлекают последнюю версию каждой строки, зафиксированной в момент начала транзакции. Тем самым предоставляется совместимый на уровне транзакций моментальный снимок данных на момент начала транзакции.


для спящего время бодрствования равносильно сну
15 июл 09, 16:18    [7419835]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Алексей2003
2pkarkling
а как же тогда это:
MSDN
Операции считывания, выполняемые транзакцией моментальных снимков, извлекают последнюю версию каждой строки, зафиксированной в момент начала транзакции. Тем самым предоставляется совместимый на уровне транзакций моментальный снимок данных на момент начала транзакции.


Ну так выделенное в Вашем примере моменту снимка будет соответствовать выделенное:

автор
открываем первую транзакцию селект, получаем 1, делаем апдейт на 2. ничего не комитим
открываем вторую транзакцию селект, получаем 2, делаем апдейт на 3. ничего не комитим
открываем третью транзакцию селект, получаем 3, делаем апдейт на 1. ничего не комитим.
ну и т.д.


Так как вторая и третья транзакции произошли позже, то и при SNAPSHOT в рамках всей транзакции и при RC в режиме версионности вторая и треться транзакция получать версию с 1.

Сообщение было отредактировано: 15 июл 09, 16:24
15 июл 09, 16:23    [7419864]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
что касается принципа версионности:
MSDN
При каждом изменении строки конкретной транзакцией экземпляр компонента Database Engine сохраняет версию ранее зафиксированного образа строки в tempdb. Каждой версии присваивается порядковый номер транзакции, выполнившей изменение. Версии измененных строк сцепляются с помощью списка ссылок. Самое последнее значение строки всегда хранится в текущей базе данных с указанием на цепочку версий, хранящихся в базе данных tempdb.


что касается выборки при снапшот изоляции:
MSDN
Когда транзакция моментальных снимков считывает строку, имеющую цепочку версий, компонент Database Engine следует по цепочке и извлекает строку, если порядковый номер транзакции:

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


я читаю неправильно?

для спящего время бодрствования равносильно сну
15 июл 09, 16:26    [7419890]     Ответить | Цитировать Сообщить модератору
 Re: Использование транзакций с уровнем изоляции SNAPSHOT  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Алексей2003
что касается принципа версионности:
MSDN
При каждом изменении строки конкретной транзакцией экземпляр компонента Database Engine сохраняет версию ранее зафиксированного образа строки в tempdb. Каждой версии присваивается порядковый номер транзакции, выполнившей изменение. Версии измененных строк сцепляются с помощью списка ссылок. Самое последнее значение строки всегда хранится в текущей базе данных с указанием на цепочку версий, хранящихся в базе данных tempdb.


что касается выборки при снапшот изоляции:
MSDN
Когда транзакция моментальных снимков считывает строку, имеющую цепочку версий, компонент Database Engine следует по цепочке и извлекает строку, если порядковый номер транзакции:

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


я читаю неправильно?

для спящего время бодрствования равносильно сну


Походу да. Какой будет ближайший порядковый номер, который меньше номера транзакции моментальных снимков, считывающей строку в Вашем примере? правильно, той которая бала раньше Вашей первой, а не Ваша вторая, ибо у нее будет больший номер.
15 июл 09, 16:30    [7419940]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить