Помогите понять, почему не удается получить SQLSTATE 40001 в следующей ситуации.
Я написал такой тест. Есть один поток пишущий в базу (вставляет записи в простую таблицу id, name).
Есть несколько потоков (4) запрашивающих COUNT(id) этой таблицы. У каждого потока свое соединение.
Для соединения включен режим ручного коммита и уровень изоляции SERIALIZABLE.
Насколько я понимаю сериализацию, пишущая транзакция ставит эксклюзивную блокировку на объект (таблицу?).
Пока такая транзакция открыта, читающая транзакция не может поставить shared блокировку.
Тем не менее deadlock не происходит и 40001 не выбрасывает ни читающие транзакции, ни пишущая.
Подмешивание в пишущую транзакцию селекта ситуацию не меняет.
Установка паузы внутри транзакции (чтобы потоки подольше сидели в открытых транзакциях) также ничего не меняет.
Все это наблюдается одинаково на MS SQL 2008 R2 и Postgres 9.4.
Для меня очевидно, что транзакции, в которых как-то намешано чтение и запись, могут выкидывать 40001.
Поэтому для таких транзакций необходимо предусматривать возможность повторного выполнения.
Тест с несколькими потоками и транзакцией "поискать по name и если нет, то добавить" выбрасывает 40001 в изобилии.
Могут ли транзакции, в которых одни селекты, выкидывать 40001?
Нужно ли обеспечивать для читающих транзакций возможность перезапуска?
Буду признателен, если посоветуете книжку или сайт по теме.
В MSSQL начиная с 2005 версии, где появился версионный режим, читатели не блокируют писателей. Deadlock возможен при наличии двух пишущих транзакций. Но т.к. сейчас все стараются делать короткие транзакции, то это на практике не вызывает проблем.
Здравствуйте, BlackEric, Вы писали:
BE>Deadlock возможен при наличии двух пишущих транзакций.
Допустим есть много параллельных пишущих транзакций и одна читающая.
Пишущие транзакции иногда конфликтуют, возвращают 40001 и перезапускаются.
Есть ли гарантия, что читающая транзакция никогда не получит 40001 в таком окружении?
Здравствуйте, qaz77, Вы писали:
Q>Здравствуйте, BlackEric, Вы писали:
BE>>Deadlock возможен при наличии двух пишущих транзакций.
Q>Допустим есть много параллельных пишущих транзакций и одна читающая. Q>Пишущие транзакции иногда конфликтуют, возвращают 40001 и перезапускаются. Q>Есть ли гарантия, что читающая транзакция никогда не получит 40001 в таком окружении?
Насколько я понимаю, в MS SQL в режиме с ALLOW_SNAPSHOT_ISOLATION ON при уровне SERIALIZABLE проблем не будет.
Q>Насколько я понимаю сериализацию, пишущая транзакция ставит эксклюзивную блокировку на объект (таблицу?).
нет. Это популярное заблуждение, но это не так. На самом деле используются рэйндж-локи, по крайней мере в MSSQL https://technet.microsoft.com/en-us/library/ms191272(v=sql.105).aspx
Q>Поэтому для таких транзакций необходимо предусматривать возможность повторного выполнения.
Нет, в общем случае не стоит так делать
Транзакция может быть откачена по массе причин
— недостаток памяти, как обычной так и дисковой
— разрыв соединения
— таймауты
— дедлоки
— фэйловеры
— уход базы в офлайн
— и т.д. и т.п.
Ты следуешь 2м порочным практикам
— выделил свой конфликт в особенный случай, при том что он ничем не лучше других причин
— вместо того чтобы исправить проблему хочешь поставить затычку
Здравствуйте, BlackEric, Вы писали:
BE>Насколько я понимаю, в MS SQL в режиме с ALLOW_SNAPSHOT_ISOLATION ON при уровне SERIALIZABLE проблем не будет.
Уровень изоляции SNAPSHOT ниже SERIALIZABLE.
В SHAPSHOT нет блокировок при чтении, а в SERIALIZABLE — есть.
Меня интересует именно уровень изоляции SERIALIZABLE.
Здравствуйте, rm822, Вы писали:
Q>>Насколько я понимаю сериализацию, пишущая транзакция ставит эксклюзивную блокировку на объект (таблицу?). R>нет. Это популярное заблуждение, но это не так. На самом деле используются рэйндж-локи, по крайней мере в MSSQL
Как бы это ни было реализовано, в моем примере зависимость по данным есть:
1. пишущая транзакция добавляет запись
2. читающая транзакция запрашивает количество записей.
Если выполнить транзакции в порядке 1-2, а затем 2-1, то читающая транзакция получит разное количество записей.
Я полагал, что это будет проблемой при сериализации. Я не говорю о деталях реализации в СУБД, я хочу понять, в чем мое заблуждение о сериализации транзакций.
Q>>Поэтому для таких транзакций необходимо предусматривать возможность повторного выполнения. R>Нет, в общем случае не стоит так делать R>Транзакция может быть откачена по массе причин R> — недостаток памяти, как обычной так и дисковой R> — разрыв соединения R> — таймауты R> — дедлоки R> — фэйловеры R> — уход базы в офлайн R> — и т.д. и т.п. R>Ты следуешь 2м порочным практикам R> — выделил свой конфликт в особенный случай, при том что он ничем не лучше других причин R> — вместо того чтобы исправить проблему хочешь поставить затычку
Тут я не согласен.
Код 40001 — это вполне себе особый случай. Даже текстовое сообщение ошибки намекает: "Bla-Bla deadlock/serialization failure. Rerun transaction".
Некоторые перечисленные ошибки можно классифицировать как транзиентные.
Т.е. причина, по которой они возникают, может рассосаться сама по себе.
Отказ в сериализации транзакций связан исключительно с параллельным выполнение двух или более транзакций.
Соответственно его можно рассматривать как транзиентную ошибку, с возможностью восстановления.
"Исправить" проблему в общем случае невозможно.
Простой пример. Таблица id, name. Нужно возвращать id, если заданный name уже есть, или генерировать новый id и добавлять запись.
Если в нескольких тредах выполнять такие транзакции, то будем часто получать 40001.
Т.е. в нагруженной системе ошибка сериализации — норма жизни.
в чем мое заблуждение о сериализации транзакций.
Есть классы проблем lost update/unrepeatable read/double read/phantom read/etc..
И есть уровни изоляции которые решают часть из них. Каким именно образом — зависит от реализации.
Всё.
Q>Т.е. в нагруженной системе ошибка сериализации — норма жизни.
В нагруженной системе их вообще быть не должно, это исключение.
Дедлок устранятся дедлок монитором, который просыпается раз в 30 секунд, и все это время система _висит_ и ничего не делает.
Ты хочешь сделать говнод с дедлоками, а потом заткнуть его другим говнокодом с ретраями
Здравствуйте, rm822, Вы писали:
Q>>Насколько я понимаю сериализацию, пишущая транзакция ставит эксклюзивную блокировку на объект (таблицу?). R>нет. Это популярное заблуждение, но это не так. На самом деле используются рэйндж-локи, по крайней мере в MSSQL R>https://technet.microsoft.com/en-us/library/ms191272(v=sql.105).aspx
Не всегда. Key-Range Lock ставится только при наличии индекса. В противном случае там не начто блокировку ставить и блокируется таблица целиком.
Собственно по этому у топик-стартера дедлока и нет, ресурс один и его все поочереди блокируют.
дедлок возникает когда ресурсов несколько и они блокируются в разном порядке.
Здравствуйте, qaz77, Вы писали: Q>Помогите понять, почему не удается получить SQLSTATE 40001 в следующей ситуации. Q>Я написал такой тест. Есть один поток пишущий в базу (вставляет записи в простую таблицу id, name). Q>Есть несколько потоков (4) запрашивающих COUNT(id) этой таблицы. У каждого потока свое соединение. Q>Для соединения включен режим ручного коммита и уровень изоляции SERIALIZABLE. Q>Насколько я понимаю сериализацию, пишущая транзакция ставит эксклюзивную блокировку на объект (таблицу?).
Вообще применяются блокировки в диапазоне, но если нет индекса или объем записей достаточно велик, СУБД может эскалировать блокировки до уровня таблицы. Q>Пока такая транзакция открыта, читающая транзакция не может поставить shared блокировку. Q>Тем не менее deadlock не происходит и 40001 не выбрасывает ни читающие транзакции, ни пишущая. Q>Подмешивание в пишущую транзакцию селекта ситуацию не меняет. Q>Установка паузы внутри транзакции (чтобы потоки подольше сидели в открытых транзакциях) также ничего не меняет. Q>Все это наблюдается одинаково на MS SQL 2008 R2 и Postgres 9.4.
Блокировки чтения и эксклюзивные блокировки несовместимы, но это не означает, что при одновременном их наложении обязательно возникнет взаимоблокировка. Просто при наличии на объекте одного из видов блокировок, второй процесс при попытке наложить блокировку переходит в режим ожидания, и деадлока не происходит, что собственно и показывает ваш эксперимент. Дедалок — ситуация в многозадачной среде или СУБД, при которой несколько процессов находятся в состоянии бесконечного ожидания ресурсов, захваченных самими этими процессами. Q>Для меня очевидно, что транзакции, в которых как-то намешано чтение и запись, могут выкидывать 40001. Q>Поэтому для таких транзакций необходимо предусматривать возможность повторного выполнения. Q>Тест с несколькими потоками и транзакцией "поискать по name и если нет, то добавить" выбрасывает 40001 в изобилии. Q>Могут ли транзакции, в которых одни селекты, выкидывать 40001?
Пример, в котором возникает деадлок при двукратном чтении данных.
Схема и данные
drop table ##Test
create table ##Test
(
Id int,
Name varchar(100),
constraint PK_##Test primary key clustered (Id)
)
;with cte as
(
select *
from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) a(n)
)
-- Вставляем 1 000 000 записейinsert into ##Test(Id, Name)
select a.n, 'Test'
from
(
select top 1000000 t1.n + t2.n * 10 + t3.n * 100 + t4.n * 1000 + t5.n * 10000 + t6.n * 100000 as n
from cte t1, cte t2, cte t3, cte t4, cte t5, cte t6
order by n
) a
delete from ##Test where Id = 1000
1. Выполняем запрос 1 в первом окне
set transaction isolation level serializable
begin tran
select count(*) from ##Test where Id < 1000
2. Выполняем запрос 2 во втором окне
set transaction isolation level serializable
begin tran
insert into ##Test values(1000, 'Test')
3. Выполняем запрос 3 в первом окне
select count(*) from ##Test where Id > 1000
Деадлок на запросе 3 гарантирован. Q>Нужно ли обеспечивать для читающих транзакций возможность перезапуска?
Зависит от ваших бизнес-сценариев. Если он звучит так – во что бы то ни стало и при любых обстоятельствах необходимо выполнить запрос или вернуть данные, иначе рухнет мир, то повторять стоит. Если никаких экзотических требований нет, то приложение должно корректно отреагировать на исключение (сообщить, запротоколировать, уведомить и т.п.), а ИТ специалисты отвечающие за систему должны разобраться в ситуации и исключить ее повторение.
Здравствуйте, qaz77, Вы писали:
Q>Помогите понять, почему не удается получить SQLSTATE 40001 в следующей ситуации.
Для начала вот: http://rsdn.org/article/db/deadlocks.xml
Там есть довольно подробное описание дедлоков, как они возникают и некоторые сценарии, чтобы поиграться.
Там же список литературы для ознакомления.
Q>Я написал такой тест. Есть один поток пишущий в базу (вставляет записи в простую таблицу id, name). Q>Есть несколько потоков (4) запрашивающих COUNT(id) этой таблицы. У каждого потока свое соединение. Q>Для соединения включен режим ручного коммита и уровень изоляции SERIALIZABLE.
Q>Насколько я понимаю сериализацию, пишущая транзакция ставит эксклюзивную блокировку на объект (таблицу?). Q>Пока такая транзакция открыта, читающая транзакция не может поставить shared блокировку. Q>Тем не менее deadlock не происходит и 40001 не выбрасывает ни читающие транзакции, ни пишущая.
В данном случае, скорее всего, блокируется таблица целиком (индексов нет, я полагаю).
Ресурс один, порядок доступа вполне определен, дедлоку просто не откуда взяться. дедлок возникает когда нарушается порадок доступа к нескольким ресурсам.
Q>Подмешивание в пишущую транзакцию селекта ситуацию не меняет.
И не должно.
Q>Для меня очевидно, что транзакции, в которых как-то намешано чтение и запись, могут выкидывать 40001.
для того чтобы получить дедлок, мешать надо не просто чтение и запись, а чтение и запись к нескольким ресурсам в разном порядке.
Q>Могут ли транзакции, в которых одни селекты, выкидывать 40001?
За исключением экзотики типа внутренних блокировок сервера — нет, не могут.
Q>Нужно ли обеспечивать для читающих транзакций возможность перезапуска?
Как правило нет, если они не пересекаются с пишущими. И даже в этом случае, правильнее переписать запросы таким образом, чтобы соблюдался порядок доступа к ресурсам и понизился уровень изоляции, чем обрабатывать такие конфликты на клиенте.
Q>Буду признателен, если посоветуете книжку или сайт по теме.
Для начала вот: http://rsdn.org/article/db/deadlocks.xml
Здравствуйте, rm822, Вы писали:
R>В нагруженной системе их вообще быть не должно, это исключение. R>Дедлок устранятся дедлок монитором, который просыпается раз в 30 секунд, и все это время система _висит_ и ничего не делает.
Мой тест с 5 потоками, которые делают в транзакции select & insert в одну таблицу (1000 записей), работает менее 10 секунд и
при этом может возникать несколько десятков serialization failure (40001).
R>Ты хочешь сделать говнод с дедлоками, а потом заткнуть его другим говнокодом с ретраями
Я пока никакой код не хочу делать, только изучаю проблему и пишу тесты, чтобы понять как работает СУБД.
Здравствуйте, Olaf, Вы писали: O>Зависит от ваших бизнес-сценариев. Если он звучит так – во что бы то ни стало и при любых обстоятельствах необходимо выполнить запрос или вернуть данные, иначе рухнет мир, то повторять стоит. Если никаких экзотических требований нет, то приложение должно корректно отреагировать на исключение (сообщить, запротоколировать, уведомить и т.п.), а ИТ специалисты отвечающие за систему должны разобраться в ситуации и исключить ее повторение.
Если клиенты СУБД работают параллельно, не согласовывая свои действия, есть ли возможность спроектировать систему так, чтобы дедлоки не возникали?
В приведенном примере транзакция во втором окне что-то вставляет, не зная о существовании первого окна.
Кто тут плохой парень? Первое окно?
Как ИТ специалист, отвечающий за систему, мог бы исключить ее повторение?
Здравствуйте, qaz77, Вы писали:
Q>Здравствуйте, rm822, Вы писали:
R>>В нагруженной системе их вообще быть не должно, это исключение. R>>Дедлок устранятся дедлок монитором, который просыпается раз в 30 секунд, и все это время система _висит_ и ничего не делает.
Q>Мой тест с 5 потоками, которые делают в транзакции select & insert в одну таблицу (1000 записей), работает менее 10 секунд и Q>при этом может возникать несколько десятков serialization failure (40001).
С архитектурой у вас проблемы. Сделайте вначале все вставки, а затем уже читайте. Используйте MERGE или что-то еще.
Здравствуйте, qaz77, Вы писали:
Q>Если клиенты СУБД работают параллельно, не согласовывая свои действия, есть ли возможность спроектировать систему так, чтобы дедлоки не возникали?
Как правило да.
Q>В приведенном примере транзакция во втором окне что-то вставляет, не зная о существовании первого окна. Q>Кто тут плохой парень? Первое окно?
Оба.
Q>Как ИТ специалист, отвечающий за систему, мог бы исключить ее повторение?
Проанализировав текущие запросы и переписав их так, чтобы они не пересекались по данным не в том порядке. Это может быть изменение запросов, изменение уровней изоляции, новые индексы или наоборот, удаление лишних индексов, в крайнем случае другая структура данных.
Мы уже победили, просто это еще не так заметно...
