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

Откуда:
Сообщений: 1090
Делаем адаптер под MS SQL сервер, и прогоняем тесты на многопоточность. До этого все базы были версионники, это первый блокировочник. Тесты достаточно примитивные, чтение данных на сервер приложений, изменение, запись назад.

В режиме блокировочника все нормально - если изоляция READ COMMITED материализованные агрегации (например остатки) плывут, целостность нарушается. Если повысить изоляцию до REPEATABLE READ начинают возникать dead-локи, транзакции перезапускаются и целостность сохраняется.

Если же перевести в режим версионника, с изоляцией READ_COMMITED все нормально (то есть также), а вот в REPEATABLE READ (как и SERIALIZABLE) почему-то продолжают возникать все те же dead-lock'и и ни одного update conflict'а. При этом другие базы наоборот кидают сплошные update-conflict'ы и ни одного dead-lock'а (там в этом тесте собственно их по идее и быть не должно, так как порядок изменения таблиц вроде как детерменирован, хотя на 100 процентов не уверен). Если посмотреть локи в процессе работы, то S локов действительно не возникает, но возникают IS локи (я так понял блокировка page'ей), но если учесть что агрегации не плывут, они получается возникают на чтении данных, но тогда смысл версионника теряется. Может я чего то не понимаю?

ЗЫ: Только что проверил, в режиме блокировочника тоже только IS блокировки. То есть как будто режим версионника не включился, включаю его так:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Версия SQL Server 2012 Express
25 май 14, 20:23    [16069148]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Nitro_Junkie
если изоляция READ COMMITED материализованные агрегации (например остатки) плывут, целостность нарушается
Если вы получили результат запроса не тот, который ожидали, то из этого не следует, что имеет место нарушения целостности.
Nitro_Junkie
а вот в REPEATABLE READ (как и SERIALIZABLE) почему-то продолжают возникать все те же dead-lock'и и ни одного update conflict'а.
С чего вдруг должен быть update conflict?
Нет в MSSQL режима версионника. Есть TIL SNAPSHOT - обеспечивается согласованность данных на уровне транзакции. И есть TIL READ COMMITTED SNAPSHOT - при READ COMMITTED обеспечивается согласованность на уровне инструкции.
25 май 14, 21:10    [16069302]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Nitro_Junkie
Member

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

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


В данном тесте речь идет о классическом примере конкурентного доступа, когда нужно обновить остаток - скажем текущий 3 и один пользователь хочет его увеличить на 2 второй на 4. И в сумме должно получится 9, а не 5 или 7 и обе транзакции коммитнуты.

автор
Нет в MSSQL режима версионника. Есть TIL SNAPSHOT - обеспечивается согласованность данных на уровне транзакции. И есть TIL READ COMMITTED SNAPSHOT - при READ COMMITTED обеспечивается согласованность на уровне инструкции.


Тогда что они имеют ввиду в документации под Update Conflict'ом, который является важной частью версионника? Я сколько не читал, но в чем его отличия от аналогичного скажем в PostgreSQL так и не понял...
25 май 14, 21:21    [16069372]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Nitro_Junkie
Member

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

Ну и как при вот этом

No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server

может обеспечиваться целостность (а я проверил что обеспечивается) без update conflict'ов. Ну и IS блокировок по идее быть не должно.
25 май 14, 21:23    [16069385]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Nitro_Junkie
В данном тесте речь идет о классическом примере конкурентного доступа, когда нужно обновить остаток - скажем текущий 3 и один пользователь хочет его увеличить на 2 второй на 4. И в сумме должно получится 9, а не 5 или 7 и обе транзакции коммитнуты.
Вы привели не менее классический пример "потерянного обновления". А происходит это, потому что код у вас написан без учета реалий работы блокировочника. Отсюда и дедлоки и потерянные изменения и т.п.
Nitro_Junkie
Я сколько не читал, но в чем его отличия от аналогичного скажем в PostgreSQL так и не понял...
Отличие в том, что update conflict возможен только на TIL snapshot.
25 май 14, 21:49    [16069488]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Nitro_Junkie
Member

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

Хотя разобрался... Я думал что в MS SQL есть отдельный переключатель блокировочник \ версионник. А по сути это определяется на уровне изоляции транзакций (который одновременно определяет и режим блокировок и видимость в рамках транзакции)
25 май 14, 21:52    [16069508]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Nitro_Junkie
Member

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

Кстати раз уж возник вопрос, а есть разница между MS SQL SNAPSHOT, PostgreSQL REPEATABLE READ / SERIALIZABLE и Oracle SERIALIZABLE изоляциями....
25 май 14, 21:59    [16069524]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Класс!!!
Офигенный пример показывающий, что нельзя незнание заменить режимом работы программы.
В данном случае механизмом версионности.
invm
Нет в MSSQL режима версионника.
Чега?
invm
Вы привели не менее классический пример "потерянного обновления". А происходит это, потому что код у вас написан без учета реалий работы блокировочника.
Чега?
И в режиме версионника может такое быть, и update conflict это частный случай.
Ту банально неправильное написание кода.
26 май 14, 12:09    [16072418]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
автор
когда нужно обновить остаток - скажем текущий 3 и один пользователь хочет его увеличить на 2 второй на 4. И в сумме должно получится 9, а не 5 или 7 и обе транзакции коммитнуты


Простейший тест показывает, что для READ COMMITED именно 9 и получается. Ищите ошибки в коде.
26 май 14, 12:19    [16072478]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Nitro_Junkie
Кстати раз уж возник вопрос, а есть разница между MS SQL SNAPSHOT, PostgreSQL REPEATABLE READ / SERIALIZABLE и Oracle SERIALIZABLE изоляциями....
Тоже жду ответа, после таких заявлений invm.

И это, локировок (или конфликтов изменений или нарушение логики) в этой задаче не избежать просто из физики явления. Как ни крути.
26 май 14, 12:25    [16072538]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
Владислав Колосов
автор
когда нужно обновить остаток - скажем текущий 3 и один пользователь хочет его увеличить на 2 второй на 4. И в сумме должно получится 9, а не 5 или 7 и обе транзакции коммитнуты


Простейший тест показывает, что для READ COMMITED именно 9 и получается. Ищите ошибки в коде.


Да, ну:

-- 1-й пользователь

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировка сразу сбрасывается так как READ COMMITED

-- 2-й пользователь

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировка сразу сбрасывается так как READ COMMITED

--1-й пользователь

-- записали 5
UPDATE balances SET balance = 3+2 WHERE article = 5
COMMIT TRANSACTION;

--2-й пользователь

-- записали 7
UPDATE balances SET balance = 3+4 WHERE article = 5
COMMIT TRANSACTION;

Где 9?
26 май 14, 12:51    [16072741]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Mnior
invm
Нет в MSSQL режима версионника.
Чега?
А что есть режим, включающий версионность по умолчанию в контексте сервера? Именно такой механизм и хотел ТС.
Mnior
И в режиме версионника может такое быть, и update conflict это частный случай.
Можете продемонстрировать "потерянное обновление" на TIL SNAPSHOT?
26 май 14, 12:56    [16072806]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
А что есть режим, включающий версионность по умолчанию в контексте сервера?
А что это за зверь такой? И вы вообще о чём?
invm, не ходите вокруг да около, ответите сразу на вопрос (16069524), без намёков.

invm
Mnior
И в режиме версионника может такое быть, и update conflict это частный случай.
Можете продемонстрировать "потерянное обновление" на TIL SNAPSHOT?
Что продемонстрировать? update conflict?

+ invm
invm, всё нормально? мне кажется или я какую-то агрессию чувствую
26 май 14, 13:16    [16073008]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Nitro_Junkie
Владислав Колосов
пропущено...


Простейший тест показывает, что для READ COMMITED именно 9 и получается. Ищите ошибки в коде.


Да, ну:

-- 1-й пользователь

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировка сразу сбрасывается так как READ COMMITED

-- 2-й пользователь

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировка сразу сбрасывается так как READ COMMITED

--1-й пользователь

-- записали 5
UPDATE balances SET balance = 3+2 WHERE article = 5
COMMIT TRANSACTION;

--2-й пользователь

-- записали 7
UPDATE balances SET balance = 3+4 WHERE article = 5
COMMIT TRANSACTION;

Где 9?


SELECT здесь причем и READ COMMITTED? Попробуйте смоделировать это же с UPDATE. Вы неправильно строите запрос, производя предварительное чтение значения обновляемого столбца. Эта логика не относится к работе транзакций, это логика уровня приложения и работает совершенно корректно по принципу: кто последний обновил - то и прав.

1. BEGIN TRAN
1. UPDATE tbl1 SET f1 = F1 + 2
1. незафиксированнй результат = 5

2. BEGIN TRAN
2. UPDATE tbl1 SET f1 = F1 + 4
2.Транзакция ждет завершения 1
2. незафиксированнй результат = 5

1. COMMIT
1. Зафиксированный результат = 5

2. COMMIT
2. Зафксированный результат = 9.

Если начался UPDATE и следом идет SELECT в другом подключении, то возникнет конфликт типов блокировок и SELECТ будет приостановлен. SELECT имеет совместимый тип блокировки и может одновременно читать данные.
26 май 14, 13:17    [16073019]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Nitro_Junkie
Владислав Колосов
Простейший тест показывает, что для READ COMMITED именно 9 и получается. Ищите ошибки в коде.
Да, ну:

<демонстрация ошибки в коде>

Где 9?
Не вижу противоречия.
26 май 14, 13:23    [16073093]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Mnior
А что это за зверь такой? И вы вообще о чём?
Я вот об этом:
Nitro_Junkie
Я думал что в MS SQL есть отдельный переключатель блокировочник \ версионник.

Mnior
ответите сразу на вопрос (16069524)
Не могу, ибо этом контексте не имел дел ни с Postger, ни с Oracle.
Mnior
Что продемонстрировать? update conflict?
Нет. "Потерянное изменение" на TIL SNAPSHOT.

ЗЫ: Все ок, никакой агрессии :)
26 май 14, 13:24    [16073103]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
Владислав Колосов
Nitro_Junkie
пропущено...


Да, ну:

-- 1-й пользователь

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировка сразу сбрасывается так как READ COMMITED

-- 2-й пользователь

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировка сразу сбрасывается так как READ COMMITED

--1-й пользователь

-- записали 5
UPDATE balances SET balance = 3+2 WHERE article = 5
COMMIT TRANSACTION;

--2-й пользователь

-- записали 7
UPDATE balances SET balance = 3+4 WHERE article = 5
COMMIT TRANSACTION;

Где 9?


SELECT здесь причем и READ COMMITTED? Попробуйте смоделировать это же с UPDATE. Вы неправильно строите запрос, производя предварительное чтение значения обновляемого столбца. Эта логика не относится к работе транзакций, это логика уровня приложения и работает совершенно корректно по принципу: кто последний обновил - то и прав.

1. BEGIN TRAN
1. UPDATE tbl1 SET f1 = F1 + 2
1. незафиксированнй результат = 5

2. BEGIN TRAN
2. UPDATE tbl1 SET f1 = F1 + 4
2.Транзакция ждет завершения 1
2. незафиксированнй результат = 5

1. COMMIT
1. Зафиксированный результат = 5

2. COMMIT
2. Зафксированный результат = 9.

Если начался UPDATE и следом идет SELECT в другом подключении, то возникнет конфликт типов блокировок и SELECТ будет приостановлен. SELECT имеет совместимый тип блокировки и может одновременно читать данные.


В вашем примере как раз не важен уровень изоляции (хотя не уверен что на более низком уровне запрос не выполняется как в моем примере). А вот мой пример (собственно он же в документации по уровням изоляции) и демонстрирует отличие READ COMMITED и REPEATABLE READ (или SNAPSHOT).

REPEATABLE READ :

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировка S

-- 2-й пользователь

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировка S

--1-й пользователь

-- записываем 5
UPDATE balances SET balance = 3+2 WHERE article = 5
-- попытка уйти в X, ждем

--2-й пользователь

-- записываем 7
UPDATE balances SET balance = 3+4 WHERE article = 5
-- попытка уйти в X, ждем
-- dead lock, откатывается транзакция

--1-й пользователь
-- записали 5
COMMIT TRANSACTION;

-- 2-й пользователь повторяет транзакцию и записывает 9

SNAPSHOT

-- 1-й пользователь

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировки не было

-- 2-й пользователь

-- получаем 3
START TRANSACTION;
SELECT balance FROM balances WHERE article = 5
-- блокировки не было

--1-й пользователь

-- записали 5
UPDATE balances SET balance = 3+2 WHERE article = 5
COMMIT TRANSACTION;

--2-й пользователь

-- записываем 7
UPDATE balances SET balance = 3+4 WHERE article = 5
-- UPDATE CONFLICT, откатываем

-- 2-й пользователь повторяет транзакцию и записывает 9
26 май 14, 13:28    [16073145]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
Mnior, Ищите ошибки в коде

Не понимаю почему вы это считаете ошибкой в коде. Далеко не все транзакции можно записать одной строкой (да и я не уверен что в этом случае не может быть "потерянных изменений")... И в этом случае без повышения уровня изоляции не обойтись. Если вы конечно как в 1Се не хотите всем управлять "вручную" с соответствующей производительностью и вероятностью ошибки.
26 май 14, 13:32    [16073186]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Nitro_Junkie
Не понимаю почему вы это считаете ошибкой в коде.
Потому что вы пишите как для версионника и считаете, что на блокировочнике должно работать аналогично.

Nitro_Junkie
И в этом случае без повышения уровня изоляции не обойтись.
Всего лишь нужно написать
SELECT balance FROM balances with (xlock) WHERE article = 5
И можно оставаться на RC.
26 май 14, 13:46    [16073315]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
пессимистическая и оптимистическ
Guest
Nitro_Junkie
Mnior, Ищите ошибки в коде

Не понимаю почему вы это считаете ошибкой в коде.

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


с первым понятно, а теперь о втором


ошибка №1 - вы запрашиваете значение из таблицы, никак не сообщая другим пользователям, что вы собираетесь это значение изменить
ошибка №2 - а через какое-то время, на основе ранее полученного значения (без проверки изменилось оно или нет) обновляете данные в таблице


тут есть две стратегии обновления
1. пессимистическая
в момент получения данных накладывать блокировку на сроку
SELECT balance FROM balances (xlock, repeatableread) WHERE article = 5

он будет правилен в случае когда каждое (или почти каждое) подобное чтение приводит к обновлению

2. оптимистическая
в момент обновления данных проверять не поменялось ли исходное значение
UPDATE balances SET balance = 3+4 WHERE article = 5 and balance  = 3
if @@rowcount = 0 
begin
// обработка конфликта обнавления
end 

он будет правилен в случае когда далеко не каждое подобное чтение приводит к обновлению
26 май 14, 13:53    [16073369]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
[quot invm]
Nitro_Junkie
Не понимаю почему вы это считаете ошибкой в коде.
Потому что вы пишите как для версионника и считаете, что на блокировочнике должно работать аналогично.

А в чем разница? И в версионнике и в блокировочнике при READ COMMITED будут потерянные изменения, а при REPEATABLE READ (SERIALIZABLE - Oracle) не будут... Только в одном случае будут UPDATE CONFLICT'ы во втором DEAD LOCK'и (пусть реже но зато с оверхедом на блокировку)...
26 май 14, 13:56    [16073403]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
пессимистическая и оптимистическ,

А просто повысить уровень изоляции религия не позволяет. Или вы с оверхедом от избыточных блокировок боретесь? Так я вам открою секрет, что время программиста и риски для бизнеса из-за его ошибок в очень большом количестве случаев стоят дороже оверхеда даже в 10%.
26 май 14, 13:59    [16073426]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
пессимистическая и оптимистическ
Guest
quot Nitro_Junkie
проблемы не у меня, а у вас
если вам достаточно повысить уровень изоляции до RR, удачи (за одно расскажете о дедлока на конвертациях s->x)
26 май 14, 14:03    [16073463]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Nitro_Junkie
И в версионнике и в блокировочнике при READ COMMITED будут потерянные изменения
Тогда не понятно чего вы хотите достичь. Как остаться на RC и избежать потерянного изменения я вам уже показал.
26 май 14, 14:14    [16073563]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT ISOLATION и UPDATE CONFLICT'ы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
Я вот об этом:
Nitro_Junkie
Я думал что в MS SQL есть отдельный переключатель блокировочник \ версионник.
А что нет "переключателя" на базе ALTER DATABASE или SET TIL? До соответствующего типа версионника.
Какая разница как называть "переключатель" и каков у него синтакс. У оракакла (как я знаю) вообще нет переключателя в "не версионник" (в блокировщик).

И это было написано после вашего высказывания. И человек просто не придал значение написаному. Не придрался, так сказать, как я.
invm
Mnior
Что продемонстрировать? update conflict?
Нет. "Потерянное изменение" на TIL SNAPSHOT.
1. А я что утверждал что на версионнике типа "TIL SNAPSHOT" будут локировки или будут незаметно пропадать изменения?
2. Во вторых проблема "Потерянное изменение" не в том что что "update conflict" не дают потерять изменение, а в том что это проблема. С натягом можно сказать что "update conflict" это тоже потеря, но через ошибку.
А локировка это нормальное явление и только так оно решается (независимо от уровня изоляции).

Nitro_Junkie
Mnior, Ищите ошибки в коде

Не понимаю почему вы это считаете ошибкой в коде.
...
А в чем разница?

Вот я и говорю. Ораклойды часто не понимают всю проблематику явления и попадают в просак.
Нельзя без понимания что-то делать, это как играть в рулетку.
И фся эта канитель с версионностью сакс. Хотя и курсоры и т.п. тоже, но это другая история.
Nitro_Junkie
А просто повысить уровень изоляции религия не позволяет.

Пока вы не поймёте природу транзакционности вы не поймёте где у вас ошибка в коде. И что изменение уровеня изоляции не решает проблемы. И вообще говорить "повысить уровень изоляции" это не понимать суть.
Вся вот эта *уйня с 80ых, что можно научить кухарку писать на SQL провалилась, и вот эти отголоски "повысить уровень изоляции" так и осталось эвфеми́змом.
26 май 14, 18:35    [16075534]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить