Короткая шпаргалка по блокировкам при чтении и изменении данных в зависимости от уровня изоляции транзакции в MSSQL

    Read Uncommitted

    • если в одной транзакции поменять данные — селект этих данных (в другой транзакции или без транзакции) не будут ждать окончания первой транзакции и вернут записанные данные незакомиченных транзакций
    • если в одной транзакции считать данные — апдейты этих данных в другой транзакции не будут ждать окончания первой транзакции
    • шаред локи не используются. Что аналогично установке NOLOCK хинта во все селекты в Read Commited
    • эксклюзивные локировки устанавливаются в процессе выполнения стейтмента и снимаются по окончанию транзакции


    Read Committed + read_committed_snapshot off

    (alter database xxx set read_committed_snapshot off)

    • если в одной транзакции поменять данные — селект этих данных (в другой транзакции или без транзакции) будут ждать окончания первой транзакции. Селект с NOLOCK хинтом вернёт изменённые, но не закомиченные данные.
    • если в одной транзакции считать данные — апдейты этих данных в другой транзакции не будут ждать окончания первой транзакции
    • шаред локировки устанавливаются в процессе работы стейтмента и снимаются по окончанию стейтмента
    • эксклюзивные локировки устанавливаются в процессе выполнения стейтмента и снимаются по окончанию транзакции




    Read Committed + read_committed_snapshot on

    (alter database xxx set read_committed_snapshot on)
    • если в одной транзакции поменять данные — селект этих данных (в другой транзакции или без транзакции) не будут ждать окончания первой транзакции и вернут значения на момент старта стейтмента. Селект с NOLOCK хинтом вернёт изменённые, но не закомиченные данные.
    • если в одной транзакции считать данные — апдейты этих данных в другой транзакции не будут ждать окончания первой транзакции
    • шаред локировки не используются, вместо этого используется механизм Row Versioning — данные обновлённых записей хранятся в tempdb
    • эксклюзивные локировки устанавливаются в процессе выполнения стейтмента и снимаются по окончанию транзакции


    Repeatable Read

    • если в одной транзакции поменять данные — селект этих данных (в другой транзакции или без транзакции) будет ждать окончания первой транзакции. Селект с NOLOCK хинтом вернёт изменённые, но не закомиченные данные.
    • если в одной транзакции считать данные — апдейты этих данных в другой транзакции будет ждать окончания первой транзакции
    • шаред локировки устанавливаются в процессе работы стейтмента и снимаются по окончанию транзакции в отличии от Read Commited
    • эксклюзивные локировки устанавливаются в процессе выполнения стейтмента и снимаются по окончанию транзакции


    Serializable

    • если в одной транзакции поменять данные — селект этих данных (в другой транзакции или без транзакции) будет ждать окончания первой транзакции. Селект с NOLOCK хинтом вернёт изменённые, но не закомиченные данные.
    • если в одной транзакции считать данные — апдейт этих данных в другой транзакции будет ждать окончания первой транзакции
    • шаред локировки устанавливаются в процессе работы стейтмента и снимаются по окончанию транзакции
    • эксклюзивные локировки устанавливаются в процессе выполнения стейтмента и снимаются по окончанию транзакции
    • устанавливаются эксклюзивные range локи на ключи, попадающие в диапазоны критериев запроса, что запрещает делать инсерты новых записей, попадающих в эти диапазоны, что аналогично установке HOLDLOCK хинта во все селекты в Read Commited


    Snapshot
    (alter database xxx set allow_snapshot_isolation on)

    • если в одной транзакции поменять данные — селект этих данных (в другой транзакции или без транзакции) не будут ждать окончания первой транзакции и вернут значения на момент старта транзакции. Селект с NOLOCK хинтом вернёт изменённые, но не закомиченные данные.
    • если в одной транзакции считать данные — апдейты этих данных в другой транзакции не будут ждать окончания первой транзакции
    • шаред локировки не используются, вместо этого используется механизм Row Versioning — данные обновлённых записей хранятся в tempdb
    • эксклюзивные локировки устанавливаются в процессе выполнения стейтмента и снимаются по окончанию транзакции


    Проверялось на MSSQL 2014.
    Метки:
    Поделиться публикацией
    Комментарии 26
    • 0
      Добавил в закладки!
      • +4
        Не забывайте сказать про:
        Примечание

        Поддержка использования подсказок READUNCOMMITTED и NOLOCK в предложении FROM, применяемом к целевой таблице инструкции UPDATE или DELETE, будет удалена в следующей версии SQL Server. Следует избегать использования этих указаний в таком контексте в новой разработке и запланировать изменение приложений, использующих их в настоящий момент.

        Источник: MSDN
        • 0
          Русский msdn — большое зло. В SQL2014 до сих пор работает.
          • 0
            Видимо это к предыдущему комментарию относится? Этот текст присутствует на MSDN и в английской версии, и присутствует уже очень давно, точно с SQL2012, а вроде бы и ещё раньше. Но в остальном согласен.
            А, в английской версии это находится в разделе «Features Not Supported in a Future Version of SQL Server», а не в Next Version.
          • +2
            в другой транзакции или без транзакции


            У меня есть замечание к этой фразе.

            В MS SQL, как и в других СУБД, не бывает выполнения запросов без транзакций. Просто, если явно не прописать начало транзакции, транзакция автоматически начинается со стартом запроса и автоматически завершается (коммитом при успешном выполнении) при его завершении. Это поведение еще и поменять можно через SET IMPLICIT_TRANSACTIONS.
            • 0
              спасибо за коментарий!
              я знаю про автоматическое открытие транзакции, НО у меня есть вопрос про SET IMPLICIT_TRANSACTIONS
              Я не теоретик БД, а практик, так вот — я обнаруживал создание транзакций при выполнении insert, update и даже select (с БД нотификациями) при выключенной опции IMPLICIT_TRANSACTIONS.
              Я так понял, при выполнении этих операций сиквел всегда создаёт транзакции. Я прав, или там какая-то хитрость?
              Скриншот:

              • +1
                Как я и писал, SQL Server ВСЕГДА создает транзакции, SET IMPLICIT_TRANSACTIONS влияет только на поведение при окончании запроса — будет транзакция завершена автоматически (при OFF) или не будет (при ON).

                Другими словами (как это написано в MSDN, кстати), SET IMPLICIT_TRANSACTIONS всегда, если количество текущих транзакций равно нулю, перед выполнением команд добавляет BEGIN TRANSACTION. Завершить при этом начатую сервером транзакцию вы должны самостоятельно — видимо, это поведение сделано для большей совместимости с Oracle, например, там это поведение по умолчанию — все транзакции должны быть завершены явно.
                • 0
                  спасибо, всё понятно!
                  извиняюсь спросить, а как это работает при использовании распределенных транзакций в приложении?

                  • +1
                    Это тема, с которой я подробно не знаком )

                    Чтобы не врать и не пересказывать своими словами, просто дам ссылку — там вкратце вроде понятно написано, в деталях нужно разбираться, конечно, на практике:

                    https://technet.microsoft.com/ru-ru/library/ms191440(v=sql.105).aspx
                    • 0

                      Распределенные транзакции — это которые через MSDTS? Я думаю, сервер транзакций явно начинает и завершает (или откатывает, в случае неудачи одной из транзакций) транзакции для каждого соединения.

              • +3
                Ну и в целом полезно, можно только дать внизу ссылку на полное описание для тех, кому нужно разобраться глубже.
                https://technet.microsoft.com/en-us/library/jj856598.aspx

                Так, часто бывает важно понимать, какие конкретно блокировки накладываются и как они друг с другом сочетаются, для этого нужна вот эта таблица:
                image
                • 0
                  Отличная статья! Буду давать коллегам почитать, если возникнут вопросы по транзакциям ).

                  Мне было всегда интересно, но руки не доходили потестировать на высокой нагрузке: если мы в БД включили Snapshot, но не включили Read Committed + read_committed_snapshot on по умолчанию и получается работаем с Read Committed + read_committed_snapshot off, но при этом в tempdb версионирование все равно хранится, так как БД не может знать в какой момент мы попросим изоляцию Snapshot, то будет ли хоть какое-то ухудшение производительности (за счет увеличения чтений с диска или других факторов) при включении по умолчанию Read Committed + read_committed_snapshot on? Да и при каких условиях лучше оставлять по умолчанию Read Committed + read_committed_snapshot off?
                  • +1
                    по поводу
                    при этом в tempdb версионирование все равно хранится, так как БД не может знать в какой момент мы попросим изоляцию Snapshot


                    я провел эксперимент:
                    1. включил allow_snapshot_isolation и выключил read_committed_snapshot



                    2. запустил Read Commited транзакцию и понаставил блокировок



                    3. убедился, что блокировки стоят



                    4. запустил транзакцию SNAPSHOT и попытался прочитать данные



                    5. и получил замечательный висяк на блокировке



                    поэтому, ИМХО, утверждение
                    при этом в tempdb версионирование все равно хранится
                    не верное.
                    Пожалуйста, поправьте меня, если я не прав.
                    • 0
                      Спасибо за ответ!

                      Только все равно немного не сходится. В статье (как и в других источниках) так написано про Snapshot:
                      если в одной транзакции поменять данные — селект этих данных (в другой транзакции или без транзакции) не будут ждать окончания первой транзакции и вернут значения на момент старта транзакции. Селект с NOLOCK хинтом вернёт изменённые, но не закомиченные данные.


                      В Вашем примере транзакция именно ждет, значит это не Snapshot (например, Вы ее вложили в Read Commited случайно) или lock мешает выполнению Snapshot. Это не означает, что в tempdb ничего не пишется, если я правильно понял.
                  • 0
                    было бы еще хорошо, если бы добавили про исключения, возникающие при изменении данных в разных транзакциях для разных типов блокировок.
                    • –3
                      Зарегился только чтобы написать этот коментрарий. Что за мода пошла коверкать английские слова в русские, что это за лексическая порнография такая! Стейтмент, локи и т.д. Вы уж или пишите по русски или уж по английски. Шаред локи, класс, чем вам не угодила совмещенная блокировка или S Lock? Новичок потом так и запомнит, а если не знает английского то просто не поймет. Без запоминания верной терминологии не будет и понимания темы.
                      • +1
                        Особенно бесит «локировки». Хоть одну буквы не напишу — но «по-англицки» будет
                        • 0
                          Поддержу коллег, меня «сиквел» всегда «радует»…
                          • +1
                            КО спешит на помощь! :)

                            Сиквел — это, кстати, не коверканье слова, но устоявшийся профессиональный жаргонизм: на сайте Microsoft Virtual Academy (в роликах по 70-461 и далее по списку) половина ведущих, в основном, пожилых :) так говорит. Всё дело в языке SEQUEL.

                            Это как сейчас в окончание разговора по телефону «повесить трубку» хотя никто никуда их не вешает и даже уже не «кладёт».
                      • +1
                        Зарегистрировался, чтобы написать этот комментарий!
                        Что за мода то пошла оправдываться незнанием английского языка?!
                        В мире три языка: китайский, испанский, английский.
                        Вы лезете в программированием и ИТ в целом и ноете что не знаете базиса. Тьфу!
                        Скорость развития бешеная, вам пока маны переведут на русский, уже следующий виток развития.
                        Вы еще требуйте бумажную версию.

                        Валите в 1С разработку все кому не нравятся маны, коммиты, бинды и прочее, там Паскаль Промтом перевели уже.
                        ЗЫ
                        Про игры даже ноют, что не перевели.
                        • +3
                          Я уловил в какую сторону направлен этот поток мысли, но откуда он возник — никак не возьму в толк. Вроде тут никто не говорил про незнание английского языка, про бумажные версии и т.д… С вами всё хорошо?
                          • +2
                            Вы неправы. Где то на хабре читал споры, что типа не фиг идти в IT если не знаешь английского, да и вообще перевод книг это неправильное и глупое занятие. Мне понравился ответ одного из пользователей, что человек может прийти в IT как раз после прочтения книги и именно это может стимулировать его выучить английский. К примеру, прочитал книгу по C#, круто, хочу еще, а еще только в оригинале, пошел учить английский. И обратная ситуация, решил учить с#, все по английски, открыл, ниче не понял, бросил. Но да, если хочешь работать в IT, английский необходим.
                            Но мой комментарий был не об этом, а о коверкании терминологии. Есть, скажем так, русская терминология, а есть английская. Статья на русском, значит я хочу читать статью написанную русским языком с устоявшейся русской терминологией.
                            Шаред локировка устанавливаются в процессе работы стейтмента…
                            Совмещенная блокировка устанавиливается во время выполнения запроса — лично мне так больше нравиться.
                            Это все ровно, что я напишу статью на английском вида: This statement will place a Shared Blokirovku on a Tablicu.
                            • 0
                              Зарегистрировался, чтобы оставить этот комментайрий
                              Перед дискуссией о русском языке (даже если она не о правописании) особенно важно сначала сходить на tsya.ru. Освежить, так сказать, школьный курс.
                              • +1
                                Возможно, даже скорее всего, я сделал немало ошибок в своем сообщении, так как грамматика и определенные правила через десяток лет после учебы забываются, в особенности когда ты редко что либо пишешь, а читаешь литературу или статьи в основном не на русском. Но вопрос не в правописании, да и не в русском языке в принципе. Вопрос в коверкании английских слов в русские, когда их переводят как имена, как читается так и на русском пускай будет. Одно дело когда термины в принципе не переводимые, типа AlwaysOn, Database Mail и т.д., другое дело когда в русском есть устоявшийся перевод, который и хочется увидеть на подобном ресурсе. Если так переводить английские термины то так далеко можно уйти: контейнт БД, булк операции и т.д.
                          • НЛО прилетело и опубликовало эту надпись здесь
                            • 0
                              ну так и есть. Все эти снапшот изоляции и рид комитед снапшот — добавлены сильно после устаканивания основ транзакционной модели MSSQL.
                              И для того чтобы как раз «догнать и перегнать». Теперь и старым мсскуэльщикам таким как мне, все эти новороты с трудом запоминаются.

                            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.