введение
Вы встречали интервьюера, который спрашивал вас о разнице между следующими шестью предложениями Sql на собеседовании?
select * from table where id = ?
select * from table where id < ?
select * from table where id = ? lock in share mode
select * from table where id < ? lock in share mode
select * from table where id = ? for update
select * from table where id < ? for update
Если вы можете четко сказать, блокируются ли эти шесть sql-предложений под разными уровнями изоляции транзакций, добавляются ли разделяемые блокировки или монопольные блокировки и есть ли гэп-блокировки, то эта статья не имеет смысла.
Причина, по которой я написал эту статью, заключается в том, что статьи в Интернете до сих пор слишком однобоки, они говорят только о половине, и в большинстве из них не указывается уровень изоляции, иwhereСледует ли за ним столбец условия индекса. Здесь я не буду перечислять эти неправильные статьи одну за другой, вы можете поискать их сами, но большинство из них непонятны.
Хорошо, чтобы ответить на этот вопрос, сначала задайте себе три вопроса
- Каков текущий уровень изоляции транзакций
- Есть ли индекс в столбце id?
- Если есть индекс, это кластеризованный индекс или некластеризованный индекс?
Хорошо, начните отвечать
текст
- В innodb должен быть кластерный индекс, по умолчанию в качестве кластерного индекса используется первичный ключ.
- Есть несколько индексов, и есть несколько деревьев B+ (без учета случая хеш-индексов)
- Листовые узлы кластеризованного индекса — это реальные данные на диске. Листовой узел некластеризованного индекса по-прежнему является индексом, указывающим на дерево B+ кластеризованного индекса.
Ниже приведены базовые знания
тип замка
общий замок(S-блокировка): если предположить, что транзакция T1 добавляет общую блокировку к данным A, то транзакция T2Можетпрочитать данные А,не можетИзменить данные А.
эксклюзивный замок(Блокировка X): если предположить, что транзакция T1 добавляет общую блокировку к данным A, то транзакция T2не можетпрочитать данные А,не можетИзменить данные А.
мы проходимupdate,deleteБлокировки, добавленные в инструкцию, являются блокировками на уровне строк. ТолькоLOCK TABLE … READиLOCK TABLE … WRITEДля применения блокировок на уровне таблицы.
Общая блокировка намерения(Блокировка IS): прежде чем транзакция получит (любую строку или всю таблицу) блокировку S, она должна сначала добавить блокировку IS к таблице, в которой она расположена.
эксклюзивная блокировка намерения(Блокировка IX): прежде чем транзакция получит (любую строку или всю таблицу) блокировку X, она должна сначала добавить блокировку IX для таблицы, в которой она расположена.
Какова цель блокировки намерения?
Хорошо, давайте поговорим здесь о цели преднамеренных блокировок. Если предположить, что транзакция T1 использует блокировку X для блокировки нескольких записей в таблице, то в это время в таблице имеется блокировка IX, то есть блокировка с исключительным намерением. Тогда в это время будет выполняться транзакция Т2LOCK TABLE … WRITEЗапрос на блокировку на уровне таблицы может напрямую определить, есть ли конфликт блокировок, основываясь на том, существует ли намеренная блокировка.
алгоритм блокировки
Мое утверждение взято из официальной документации:https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.htmlДобавьте свои претенциозные мнения.
хорошо, запомните следующие три, этой статьи достаточно
Record Locks: Просто переведите это как блокировку строки. Обратите внимание, что эта блокировка блокирует индексную запись! Блокировка находится на индексе, а не на строке. Обратите внимание, что в innodb должен быть кластеризованный индекс, поэтому блокировки строк в конечном итоге попадут на кластеризованный индекс!
Gap Locks: Проще говоря, блокировка промежутка, это блокировка промежутка индекса, и его цель только одна, чтобы предотвратить вставку данных другими вещами. существуетRead CommittedНа уровне изоляции гэп-блокировки не используются. Здесь я добавлю на официальном сайте, что уровень изоляции выше, чемRead CommittedВ случае низкого уровня блокировка зазора не будет использоваться, например, уровень изоляцииRead UncommitedКогда нет блокировки гэпа. Когда уровень изоляцииRepeatable ReadиSerializableКогда есть замок гэп.
Next-Key Locks: это понимается какRecord Lock+ перед указателемGap Lock. Помните, что заблокирован пробел перед индексом! Например, индекс содержит значения 10, 11, 13 и 20. Тогда область действия блокировки пробела выглядит следующим образом.
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
Чтение моментального снимка и текущее чтение
Последние базовые знания, которые все настаивают на чтении, являются основой для последующего анализа! Выбор в mysql делится на чтение моментального снимка и текущее чтение, выполните следующую инструкцию
select * from table where id = ?;
Выполняется чтение моментального снимка, и считывается версия моментального снимка записи базы данных, которая разблокирована. (Это утверждение находится на уровне изоляцииSerializableне установлено, добавлю позже. )
Затем выполните
select * from table where id = ? lock in share mode;
Добавьте блокировку S (общую блокировку) в запись чтения, выполните
select * from table where id = ? for update
Блокировка X (эксклюзивная блокировка) будет добавлена к записи чтения, затем
Вы добавляете блокировку таблицы или блокировку строки?
В ответ на это давайте сначала вспомним четыре уровня изоляции транзакций, которые расположены следующим образом от слабого к сильному:
-
Read Uncommited(RU): Чтение незафиксированных данных, транзакция может читать незафиксированные данные другой транзакции! -
Read Committed (RC): read commit, одна транзакция может прочитать данные, которые были зафиксированы другой транзакцией! -
Repeatable Read (RR): Повторяющееся чтение, добавление гэп-локов, в определенной степени позволяет избежать генерации фантомных чтений! Внимание, только до некоторой степени, не полностью избежать!Я объясню в следующей статье!Кроме того, не забудьте начать добавлять гэп-замки с этого уровня (запишите это предложение, оно пригодится позже)! -
Serializable: Сериализация, чтение и запись сериализации на этом уровне и всеselectавтоматически добавляются после оператораlock in share mode, даже если используется общая блокировка. Поэтому на этом уровне изоляции вместо чтения моментального снимка используется текущее чтение.
Итак, о том, является ли это блокировкой таблицы или блокировкой строки, вы можете увидеть одно из самых популярных высказываний в Интернете:
Блокировки строк InnoDB реализуются путем блокировки элементов индекса в индексе, что отличается от MySQL, который реализуется путем блокировки соответствующих строк данных в блоке данных. Функция реализации блокировки строк в InnoDB означает, что InnoDB использует блокировки на уровне строк только тогда, когда данные извлекаются с помощью условий индекса, в противном случае InnoDB будет использовать блокировки таблиц!
Вы можете поискать это предложение, вы скопировали меня, а я скопировал вас. Ну, в самом этом предложении две ошибки!ошибка номер один: не использует блокировки таблицы для реализации операции блокировки таблицы, а используетNext-Key Locks, также можно понимать как использование блокировки строки + блокировки промежутка для достижения работы таблицы блокировки!
Для иллюстрации позвольте мне привести пример, предполагая, что данные таблицы выглядят следующим образом: pId — это индекс первичного ключа.
| pId(int) | name(varchar) | num(int) |
|---|---|---|
| 1 | aaa | 100 |
| 2 | bbb | 200 |
| 7 | ccc | 200 |
Оператор выполнения (столбец имени не имеет индекса)
select * from table where name = `aaa` for update
Затем происходит блокировка строки (строка заблокирована) для трех записей pId=1, 2 и 7 в это время. Кроме того, на (-∞,1)(1,2)(2,7)(7,+∞) есть гэп-лок (гэп заблокирован). Таким образом создается иллюзия, что весь стол заблокирован!
ps:Если у вас есть сомнения по поводу заключения, вы можете оформить его самостоятельноshow engine innodb status;Анализ предложения.
ошибка вторая: Во всех статьях не упоминается уровень изоляции!
Обратите внимание на то, что я сказал выше, причина, по которой таблица может быть заблокирована, достигается блокировкой строки + блокировкой промежутка. Так,RUиRCБлокировки пробела нет, этот оператор находится вRUиRCМожно ли еще установить?
Поэтому аргумент толькоRRиSerializableбыл основан. Если уровень изоляцииRUиRC, независимо от того, есть ли индекс на условном столбце, таблица не будет заблокирована, будет заблокирована только строка!
анализировать
Давайте ответим на вопрос в начале, предполагая, что есть таблица следующим образом, pId - это индекс первичного ключа
| pId(int) | name(varchar) | num(int) |
|---|---|---|
| 1 | aaa | 100 |
| 2 | bbb | 200 |
| 3 | bbb | 300 |
| 7 | ccc | 200 |
RC/RU+ условный столбец без индекса
(1)select * from table where num = 200
Без какой-либо блокировки это чтение моментального снимка.
(2)select * from table where num > 200
Без какой-либо блокировки это чтение моментального снимка.
(3)select * from table where num = 200 lock in share mode
Когда num = 200, есть две записи. Эти две записи соответствуют pId=2, 7, поэтому блокировка S на уровне строки добавляется к индексу кластера с pId=2, 7, и используется текущее чтение.
(4)select * from table where num > 200 lock in share mode
Когда число > 200, имеется одна запись. Эта запись соответствует pId=3, поэтому блокировка S на уровне строки добавляется к кластеризованному индексу с pId=3, и используется текущее чтение.
(5)select * from table where num = 200 for update
Когда num = 200, есть две записи. Эти две записи соответствуют pId=2, 7, поэтому блокировка X на уровне строки добавляется к индексу кластера с pId=2, 7, и используется текущее чтение.
(6)select * from table where num > 200 for update
Когда число > 200, имеется одна запись. Эта запись соответствует pId=3, поэтому блокировка X на уровне строки добавляется к кластеризованному индексу с pId=3, и используется текущее чтение.
Условный столбец RC/RU+ является кластеризованным индексом
Что ж, всем следует знать, что pId — это столбец первичного ключа, поэтому pId использует кластеризованный индекс. На самом деле эта ситуация иRC/RU+ условный столбец без индексаСитуация похожая.
(1)select * from table where pId = 2
Без какой-либо блокировки это чтение моментального снимка.
(2)select * from table where pId > 2
Без какой-либо блокировки это чтение моментального снимка.
(3)select * from table where pId = 2 lock in share mode
В кластеризованном индексе с pId=2 добавьте блокировку S для текущего чтения.
(4)select * from table where pId > 2 lock in share mode
В кластеризованный индекс с pId=3, 7 добавьте блокировку S, которая является текущим чтением.
(5)select * from table where pId = 2 for update
В кластеризованном индексе с pId=2 добавьте блокировку X для текущего чтения.
(6)select * from table where pId > 2 for update
В кластеризованный индекс с pId=3, 7 добавьте блокировку X, которая является текущим чтением.
Здесь у вас могут возникнуть вопросы
Почему условный столбец добавлен или не проиндексирован, а ситуация с блокировкой такая же?
ладно, на самом деле все по-другому. На уровне изоляции RC/RU MySQL Server оптимизирован. В случае, если условный столбец не имеет индекса, хотя вся таблица просматривается через кластеризованный индекс, блокируется вся таблица. Однако уровень сервера MySQL будет фильтровать и немедленно освобождать блокировки, которые не соответствуют условиям, так что конечный результат будет таким же. ноRC/RU+ условный столбец без индексаСуществует еще один процесс снятия неквалифицированных блокировок, кроме этого примера!
Условный столбец RC/RU+ является некластеризованным индексом.
Мы строим неуникальный индекс для столбца num. В это время существует индексное дерево B+, сформированное кластеризованным индексом (первичный ключевой индекс, pId), и его конечные узлы представляют собой реальные данные на жестком диске. И еще одно индексное дерево B+, сформированное некластеризованным индексом (неуникальный индекс, num), его конечные узлы по-прежнему являются индексными узлами, которые хранят значение поля столбца num и соответствующий кластеризованный индекс.
Далее начинается анализ
(1)select * from table where num = 200
Без какой-либо блокировки это чтение моментального снимка.
(2)select * from table where num > 200
Без какой-либо блокировки это чтение моментального снимка.
(3)select * from table where num = 200 lock in share mode
Когда num = 200, поскольку в столбце num есть индекс, сначала в num = Блокировка S на уровне строки добавляется к двум индексным записям 200. Затем перейдите к дереву кластеризованного индекса для запроса, эти две записи соответствуют pId = 2, 7, поэтому добавьте S-блокировку на уровне строки в кластеризованный индекс с pId = 2, 7 и используйте текущее чтение.
(4)select * from table where num > 200 lock in share mode
Когда num > 200, поскольку в столбце num есть индекс, блокировка S на уровне строки сначала добавляется к записи индекса с num = 300, которая удовлетворяет условиям. Затем выполните запрос к дереву кластеризованного индекса, pId=3 соответствует этой записи, поэтому добавьте S-блокировку на уровне строки в кластеризованный индекс с pId=3 и используйте текущее чтение.
(5)select * from table where num = 200 for update
Когда num = 200, поскольку в столбце num есть индекс, сначала добавьте X-блокировки на уровне строки к двум индексным записям с num = 200. Затем перейдите к дереву кластеризованного индекса для запроса, эти две записи соответствуют pId = 2, 7, поэтому добавьте X-блокировку на уровне строки в кластеризованный индекс с pId = 2, 7 и используйте текущее чтение.
(6)select * from table where num > 200 for update
Когда num > 200, поскольку в столбце num есть индекс, блокировка X на уровне строки сначала добавляется к записи индекса с num = 300, которая удовлетворяет условиям. Затем выполните запрос к дереву кластеризованного индекса, pId=3 соответствует этой записи, поэтому добавьте X-блокировку на уровне строки в кластеризованный индекс с pId=3 и используйте текущее чтение.
RR/Serializable+ условный столбец без индекса
На уровне RR нужно больше учитывать блокировку промежутка, Его особенностью блокировки является то, что независимо от того, как вы ее проверяете, блокируется вся таблица. Следующее Далее начинается анализ
(1)select * from table where num = 200
На уровне RR блокировка не добавляется, и это чтение моментального снимка. На уровне Serializable добавьте блокировку S на кластеризованный индекс с pId = 1, 2, 3, 7 (все записи во всей таблице). И в Все промежутки кластеризованного индекса (-∞,1)(1,2)(2,3)(3,7)(7,+∞) плюс блокировка промежутка
(2)select * from table where num > 200
На уровне RR блокировка не добавляется, и это чтение моментального снимка. На уровне Serializable добавьте блокировку S на кластеризованный индекс с pId = 1, 2, 3, 7 (все записи во всей таблице). И в Все промежутки кластеризованного индекса (-∞,1)(1,2)(2,3)(3,7)(7,+∞) плюс блокировка промежутка
(3)select * from table where num = 200 lock in share mode
Добавьте блокировку S на кластеризованный индекс с pId = 1, 2, 3, 7 (все записи во всей таблице). И в Все промежутки кластеризованного индекса (-∞,1)(1,2)(2,3)(3,7)(7,+∞) плюс блокировка промежутка
(4)select * from table where num > 200 lock in share mode
Добавьте блокировку S на кластеризованный индекс с pId = 1, 2, 3, 7 (все записи во всей таблице). И в Все промежутки кластеризованного индекса (-∞,1)(1,2)(2,3)(3,7)(7,+∞) плюс блокировка промежутка
(5)select * from table where num = 200 for update
Добавьте блокировку X на кластеризованный индекс с pId = 1, 2, 3, 7 (все записи в полной таблице). И в Все промежутки кластеризованного индекса (-∞,1)(1,2)(2,3)(3,7)(7,+∞) плюс блокировка промежутка
(6)select * from table where num > 200 for update
Добавьте блокировку X на кластеризованный индекс с pId = 1, 2, 3, 7 (все записи в полной таблице). И в Все промежутки кластеризованного индекса (-∞,1)(1,2)(2,3)(3,7)(7,+∞) плюс блокировка промежутка
Условный столбец RR/Serializable+ является кластеризованным индексом.
Что ж, всем следует знать, что pId — это столбец первичного ключа, поэтому pId использует кластеризованный индекс. Запирающая особенность этой ситуации заключается в том, что еслиwhereПоследнее условие является точным запросом (=случае), то существует только блокировка записи. еслиwhereПоследнее условие представляет собой запрос диапазона (>или<случай), то есть блокировка записи + блокировка пробела.
(1)select * from table where pId = 2
На уровне RR блокировка не добавляется, и это чтение моментального снимка. На уровне Serializable это текущее чтение, а блокировка S добавляется к кластеризованному индексу с pId=2, а блокировка пробела отсутствует.
(2)select * from table where pId > 2
На уровне RR блокировка не добавляется, и это чтение моментального снимка. На уровне Serializable это текущее чтение, а блокировка S добавляется к кластеризованному индексу с pId=3,7. Добавить блокировку пробела в (2,3)(3,7)(7,+∞)
(3)select * from table where pId = 2 lock in share mode
Это текущее чтение, и к кластеризованному индексу добавляется S-блокировка с pId=2, а блокировка пробела отсутствует.
(4)select * from table where pId > 2 lock in share mode
является текущим чтением, и к кластеризованному индексу добавляется S-блокировка с pId=3,7. Добавить блокировку пробела в (2,3)(3,7)(7,+∞)
(5)select * from table where pId = 2 for update
является текущим чтением, а блокировка X добавляется к кластеризованному индексу с pId=2.
(6)select * from table where pId > 2 for update
Добавьте блокировку X на кластеризованный индекс с pId=3,7. Добавить блокировку пробела в (2,3)(3,7)(7,+∞)
(7)select * from table where pId = 6 [lock in share mode|for update]
Обратите внимание, что столбец pId=6 не существует, в этом случае к (3,7) будет добавлена блокировка пробела.
(8)select * from table where pId > 18 [lock in share mode|for update]
Обратите внимание, что pId>18, результат запроса пуст. В этом случае к (7,+∞) добавляется гэп-замок.
Условный столбец RR/Serializable+ — это некластеризованный индекс.
Здесь некластеризованный индекс должен различать, является ли он уникальным индексом или нет. Потому что, если это неуникальный индекс, метод блокировки промежутка будет другим.
Давайте сначала поговорим об уникальном индексе. Если это уникальный индекс, случай иУсловный столбец RR/Serializable+ является кластеризованным индексом.Аналогично, разница только в том, что на данный момент существует два индексных дерева, и блокировка добавляется к соответствующему некластеризованному индексному дереву и кластерному индексному дереву! Каждый может разобраться сам!
Поговорим о случае, когда некластеризованный индекс является неуникальным индексом, отличие его от уникального индекса в том, что после точного запроса по индексу происходит не только блокировка записи, но и блокировка пробела. Однако после выполнения точного запроса по уникальному индексу имеется только блокировка записи, а не блокировка пробела. Старое правило заключается в создании неуникального индекса для столбца num.
(1)select * from table where num = 200
На уровне RR блокировка не добавляется, и это чтение моментального снимка. На уровне Serializable это текущее чтение, добавьте блокировку S для кластеризованного индекса с pId = 2, 7, добавьте блокировку S для некластеризованного индекса с num = 200 и добавьте блокировку пробела для (100 200) (200 300) .
(2)select * from table where num > 200
На уровне RR блокировка не добавляется, и это чтение моментального снимка. На уровне Serializable это текущее чтение, добавляющее S-блокировку к кластеризованному индексу с pId=3 и добавляющее S-блокировку к некластеризованному индексу с num=300. Добавить блокировку гэпа к (200,300)(300,+∞)
(3)select * from table where num = 200 lock in share mode
Является текущим чтением, добавьте блокировку S для кластеризованного индекса с pId = 2, 7, добавьте блокировку S для некластеризованного индекса с num = 200 и добавьте блокировку пробела для (100 200) (200 300).
(4)select * from table where num > 200 lock in share mode
Является текущим чтением, добавьте блокировку S для кластеризованного индекса с pId = 3 и добавьте блокировку S для некластеризованного индекса с num = 300. Добавьте блокировку гэпа к (200,300)(300,+∞).
(5)select * from table where num = 200 for update
Это текущее чтение, добавьте блокировку S для кластеризованного индекса с pId = 2, 7, добавьте блокировку X для некластеризованного индекса с num = 200 и добавьте блокировку пробела для (100 200) (200 300).
(6)select * from table where num > 200 for update
Является текущим чтением, добавьте блокировку S для кластеризованного индекса с pId = 3 и добавьте блокировку X для некластеризованного индекса с num = 300. Добавить блокировку гэпа к (200,300)(300,+∞)
(7)select * from table where num = 250 [lock in share mode|for update]
Обратите внимание, что столбец num=250 не существует. В этом случае к (200 300) будет добавлена блокировка пробела.
(8)select * from table where num > 400 [lock in share mode|for update]
Обратите внимание, что если pId>400, результат запроса будет пустым. В этом случае к (400,+∞) добавляется гэп-замок.
Автор: Одинокий дым
Источник:rjzheng.cnblogs.com/
Авторские права на эту статью принадлежат автору и блог-саду.Вы можете перепечатать ее, но это заявление должно быть сохранено без согласия автора, а ссылка на исходный текст должна быть дана в видном месте на странице статьи. , в противном случае сохраняется право на юридическую ответственность.