13 июля 2016 в 15:01

Короткая шпаргалка по блокировкам при чтении и изменении данных в зависимости от уровня изоляции транзакции в 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.
Потураев Андрей @Dronopotamus
карма
12,0
рейтинг 0,0
Разработчик систем автоматизации на стеке MS
Самое читаемое Разработка

Комментарии (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.
      И для того чтобы как раз «догнать и перегнать». Теперь и старым мсскуэльщикам таким как мне, все эти новороты с трудом запоминаются.

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