Подошел человек с большим количеством пыли и пыли и сказал мне: молодой человек, я вижу, что вы хорошо владеете MySQL в своем резюме, тогда я спрошу вас о знаниях MySQL!
Я: Хорошо (не спрашивай слишком много, не спрашивай слишком много~~)
Интервьюер: Много ли вы знаете о блокировках MySQL?
Я: Все в порядке (на самом деле, это хорошо).
Интервьюер: Ну, тогда я в основном спрашиваю о знаниях, связанных с замками.
Я: ок ок (пусть лошадь подойдет, я просто из вежливости.)
Интервьюер: Какие уровни блокировок поддерживает MySQL?
Я: Поддерживать блокировки библиотек, таблиц и строк.
Интервьюер: Давайте сначала поговорим о блокировке библиотеки.Сколько существует способов заблокировать библиотеку?
Я: два вида, а именно FTWRL (Flush table with read lock) и set global readonly=true
Интервьюер: В чем разница между ними?
Я: Прежде всего, пока библиотека заблокирована, вся библиотека будет доступна только для чтения, и все операции обновления будут заблокированы. Режим FTWRL немного менее опасен. Если клиент аварийно отключен, Блокировка FTWRL будет снята автоматически. , но global readonly=true не снимает блокировку автоматически.
Интервьюер: В чем разница между блокировками myisam и innodb.
Я: myisam не поддерживает блокировки строк, только блокировки на уровне таблицы, innodb поддерживает более мелкие блокировки строк.
Интервьюер: Использовали ли вы блокировки на уровне таблицы?
Я: Я не использовал его, производительность блокировки таблицы плохая.
Интервьюер: Тогда вы знаете о блокировках MDL?
Я: Я понимаю, блокировка MDL (блокировка метаданных) — это блокировка на уровне сервера, блокировка на уровне таблицы, она неявная и не требует явного использования.Каждый раз, когда mysql читает и записывает данные (вставка, обновление, выбор , удалить) Чтобы сначала получить блокировку чтения MDL, следующую операцию можно выполнить только после получения блокировки чтения MDL, в противном случае она будет заблокирована. Блокировки чтения MDL распределяются между ними. При изменении структуры таблицы базы данных , будет получена блокировка записи MDL. , блокировка записи MDL и любая блокировка MDL являются взаимоисключающими, будь то блокировка чтения MDL или блокировка записи MDL.
Интервьюер: Какова функция блокировки MDL?
я: Блокировки MDL предназначены для разрешения конфликтов между DDL и DML.
- Предположим, транзакция A сначала запрашивает данные, а затем транзакция B выполняет модификацию поля, затем, когда транзакция A снова проверяет, она обнаруживает, что данные не совпадают.
- Транзакция A сначала обновляет данные, но не отправляется, транзакция B изменяет поле и отправляет его, ведомое устройство сначала изменяет поле, а затем обновляет данные, тогда будут проблемы
Когда используется блокировка MDL, операция DDL должна сначала получить блокировку записи MDL.Мы знаем, что блокировка записи и блокировка записи, а также блокировка записи и блокировка чтения конфликтуют, поэтому, если есть какой-либо запрос или обновление до DDL, он должен быть заблокирован и ждать, не позволит DDL выполниться, тем самым решив проблему конфликта.
Интервьюер: Безопасен ли сетевой DDL с блокировкой MDL?
Я: Не обязательно.
Предположим, что session1 сначала выполняет запрос, но не отправляет его. Затем Session2 выполняет DDL добавленного поля, и, наконец, Session3 выполняет запрос. В этот момент будет обнаружено, что заблокированы как сеанс 2, так и сеанс 3. Если позже будет запрос сеанса N, оба будут заблокированы, а в серьезных случаях будет заблокировано большое количество потоков.
Интервьюер: Тогда вы можете объяснить, почему в данном случае он блокируется?
Я: Прежде всего, блокировка MDL должна быть снята после фиксации транзакции. После того, как сеанс 1 выполнит запрос, фиксации нет, поэтому блокировка чтения MDL не будет снята. Сеанс 2 немедленно выполняет DDL. Выполнение DDL должно получить блокировка записи MDL. Поскольку блокировка записи и блокировка чтения являются взаимоисключающими, сеанс 2 зависает, он ожидает, пока сеанс 1 освободит блокировку чтения, а сеанс 3 выполняется после сеанса 2. В это время сеансу 3 требуется блокировка чтения, но потому что блокировка приобретается последовательно. Последовательно, они ставятся в очередь, а блокировки записи имеют более высокий приоритет, чем блокировки чтения, поэтому session3 зависает.
Интервьюер: Значит, после того, как session1 выполняет commit, затем сначала выполняется session2, и, наконец, сначала выполняется session3? Я только что попробовал ваш пример, кажется, что session2 и session3 работают почти одновременно, можете ли вы сказать мне, почему? (Посмотрите, сможете ли вы войти в яму)
Я: (Я хочу вырыть себе дыру, никак), нет, дело не в том, что сессия2 выполняется первой, на самом деле первой выполняется сессия3, но сессия2 сначала получит блокировку записи MDL, так как сессия3 явно не открывается транзакция, затем session3 По умолчанию она автоматически фиксируется после завершения выполнения, поэтому после фиксации session1 похоже, что session2 и session3 выполняются почти одновременно. Если вы позволите session3 явно открыть транзакцию, вы сможете узнать подробности операции.
Таким образом, когда сеанс 1 фиксируется, можно обнаружить, что сеанс 3 выполняется первым, а сеанс 2 все еще зависает, и только после фиксации сеанса 3 обнаруживается, что сеанс 2 может выполняться. Таким образом, реальная ситуация должна заключаться в том, что сначала выполняется сеанс 3, а затем сеанс 2.
Интервьюер: Противоречит ли это проблеме очередей для получения блокировок MDL, о которой вы упоминали выше?
Я: Нет противоречия, на самом деле это требует знания онлайн-DDL. Мы знаем, что mysql поддерживает онлайн-DDL и не блокирует пользовательские операции. При выполнении DDL его процесс, вероятно, выглядит следующим образом:
- Возьмите блокировку записи MDL
- Переход на блокировку чтения MDL
- действительно делай ДДЛ
- Обновление до блокировки записи MDL
- Снять блокировку MDL
После того, как session2 получит блокировку записи MDL, она будет понижена до блокировки чтения MDL.После понижения версии session3 получает блокировку чтения MDL, а затем выполняет выбор, но фиксации нет, поэтому блокировка чтения MDL не снимается, и затем сеанс 2 обновляется до записи MDL. При блокировке сеанс 2 блокируется, поскольку сеанс 3 не освобождает блокировку чтения.
Интервьюер: (Этот пацан неплох), тогда вы знаете, почему 1-2 понижаются в процессе DDL, а 3-4 снова обновляются?
Я: (Мне пришлось спросить, знала ли я раньше, но я готова),
Во-первых, во время блокировки записи MDL задача заключается в создании временных файлов frm и idb.Этот процесс должен быть безопасным и эксклюзивным.В то же время этот процесс также быстрый.После создания временного файла нет потребность в эксклюзивности.Он понижен до блокировки чтения и поддерживает обычное добавление, удаление, изменение и проверку, что является одной из причин, по которой DDL поддерживает онлайн. После записи нового файла данных старый файл данных необходимо заменить.Этот процесс безопасен, поэтому после выполнения 3 он попытается обновиться до блокировки записи MDL.Этот процесс также быстрый, и это также вторая причина для поддержки онлайн DDL.
Интервьюер: Мы знаем, что InnoDB поддерживает блокировки на уровне строк, а блокировки строк делятся на две категории, знаете ли вы?
Я: Я знаю, S (общая блокировка) и X (эксклюзивная блокировка), S-блокировка и S-блокировка являются общими, а X-блокировка и произвольная блокировка являются взаимоисключающими.
Интервьюер: Поскольку блокировка X и произвольная блокировка являются взаимоисключающими, если есть две транзакции, и транзакция A обновляет данные и не отправляет их, то блокируется ли транзакция B при запросе этих данных?
Я: Нет, поскольку InnoDB поддерживает MVCC (управление несколькими версиями), когда транзакция выполняет запрос, она может запросить моментальный снимок через журнал отмены, поэтому в блокировках нет необходимости.
Интервьюер: Тогда вы знаете IS (общая блокировка намерения) и IX (блокировка намерения эксклюзивная)?
Я: Во-первых, они обе являются блокировками на уровне таблицы, потому что InnoDB поддерживает блокировки строк.После того, как некоторые строки были заблокированы X, если вы хотите заблокировать таблицу, вы должны подтвердить, что в текущей таблице нет X-блокировок. . , в случае блокировки без намерения вам придется оценивать построчно, что будет очень неэффективно. После того, как у вас есть блокировка по намерению, вам не нужно судить построчно. Например:
select * from user where id=1 for update;
Когда транзакция имеет блокировку X для строки данных с идентификатором = 1, она также добавит блокировку IX в пользовательскую таблицу.
LOCK TABLES user READ;
На данный момент я хочу добавить к таблице блокировку чтения, но обнаружил, что на таблице есть блокировка IX, поэтому она будет заблокирована и не может быть выполнена. Точно так же, если транзакция добавляет общую блокировку к строке данных.
select * from user where id=1 lock in share mode;
Блокировка IS будет добавлена в соответствующую таблицу. В это время, если вы выполните
LOCK TABLES user WRITE;
Он также будет заблокирован, потому что на столе есть блокировка IS.
Интервьюер: Давайте поговорим о блокировках строк Какие блокировки строк поддерживает InnoDB?
Я: Блокировка записи, Блокировка промежутка, Блокировка следующей клавиши
Интервьюер: Допустим, есть таблица с 10 записями и полем user_id, а user_id — это общий индекс.
+----+---------+
| id | user_id |
+----+---------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
| 7 | 70 |
| 8 | 80 |
| 9 | 90 |
| 10 | 100 |
+----+---------+
Если транзакция A выполняется:
SELECT * FROM user WHERE user_id=50 FOR UPDATE;
Что происходит сразу после того, как транзакция B выполнит следующий sql? :
INSERT INTO user set user_id=45;
я: блокирую.
Интервьюер: Можете ли вы сказать мне, почему?
Я: Из-за алгоритма InnoDB Next-Key Lock он не только заблокирует запись user_id = 50, но и заблокирует промежуток около 50. Диапазон, заблокированный Next-Key Lock, остается открытым и закрытым справа, поэтому теоретически данные последнего интервала (40,50], (50,60] будут заблокированы.Поскольку вставляемое число 45 находится между 40 и 50, происходит блокировка.
Интервьюер: В соответствии с методом интервальной блокировки, о котором вы сказали, он содержит 60 фрагментов данных, верно, поэтому, если вы вставите фрагмент из 60 фрагментов данных, он заблокируется?
INSERT INTO user set user_id=60;
Я: На самом деле нет, это связано с оптимизацией блокировки следующей клавиши.В эквивалентном запросе, при перемещении вправо и последнем значении, не удовлетворяющем эквивалентному условию, блокировка следующей клавиши выродится в блокировку пробела, поэтому для ( 50, 60] Этот интервал в конечном итоге будет понижен до (50,60), тогда данные 60 не будут заблокированы и могут быть успешно вставлены.
Интервьюер: 40 фрагментов данных не входят в этот диапазон блокировки, поэтому можно вставить 40 фрагментов данных?
INSERT INTO user set user_id=40;
Я: На самом деле нет, также будет заблокирована вставка данных 40. Во-первых, для некластеризованного индекса user_id его листовые узлы должны быть отсортированы, вероятно, как (40,4), (50,5), а во-вторых поскольку идентификатор первичного ключа является самоувеличивающимся, то для вставки еще одной части данных из 40 его идентификатор первичного ключа должен быть больше 4. Для текущих 10 частей данных идентификатор следующей вставки должен быть 11, то (40,11) этот кусок данных должен быть после (40,4), в этом случае он попадет в замок пробела, поэтому будет заблокирован.На самом деле, выше 60 кусков данных можно вставить по той же причине.
Интервьюер: Тогда, если я не использую его сначалаselect for updateи использоватьselect lock in share mode, тогда все вставки будут меняться?
я: никаких изменений, все то же самое, потому что для вставки требуется X-блокировка, а X-блокировка и любая блокировка исключают друг друга.
Интервьюер: Если user_id — это не обычный индекс, а уникальный индекс, что изменится?
Я: Когда индекс является единственным индексом, произойдет понижение рейтинга, блокировка следующей клавиши будет понижена до блокировки записи, и в конце будет заблокировано только 50 записей.
Интервьюер: Что, если user_id не проиндексирован?
Я: Тогда все записи будут заблокированы, и любые вставки будут заблокированы.
Интервьюер: Тогда вы знаете, почему существует блокировка пробела?
Я: Разгадывать фантомные чтения. Например, когда транзакция A выполняет следующий запрос:
SELECT * FROM user WHERE id>=9 for update
Должен возвращать две записи (id=9 и id=10), в это время, если выполняется другая транзакция B
INSERT INTO user set user_id=110;
Если гэп-блокировки нет, то транзакция А при повторном запросе найдет еще одну запись и произойдет фантомное чтение, если гэп-блокировка есть, то интервал [9, +∞) будет заблокирован, и вставка транзакции Б произойдет блокирует. Однако гэп-блокировки поддерживаются только в том случае, если уровень изоляции транзакции установлен на повторяемое чтение.
Интервьюер: Если заблокированная транзакция не была зафиксирована, то транзакция, которая должна получить соответствующую блокировку, будет заблокирована В чем проблема?
Я: Если будет все больше и больше заблокированных транзакций, будет все больше и больше заблокированных потоков.В серьезных случаях пул соединений будет заполнен, и mysql не сможет предоставлять услуги. Однако после того, как InnoDB поддержит тайм-аут блокировки, он автоматически откажется от команды sql, ожидающей блокировки.Значение по умолчанию — 50 с.
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
Интервьюер посмотрел на меня: Тогда вы знаете AUTO-INC Locking?
Я: Я знаю, блокировка собственного роста. В движке InnoDB каждая таблица поддерживает счетчик собственного роста на уровне таблицы. При вставке в таблицу текущее значение собственного роста получается с помощью следующей команды.
SELECT MAX(auto_inc_col) FROM user FOR UPDATE;
Операция вставки добавит 1 к этой базе, чтобы получить самоувеличивающийся идентификатор для вставки.
Интервьюер: Мы знаем, что блокировки в транзакции снимаются после фиксации транзакции, поэтому после обновления самовозрастающего идентификатора, когда транзакция не отправляется вовремя, должны ли другие транзакции ждать, чтобы получить самовозрастающий идентификатор? Разве это не менее эффективно?
Я: Не нужно ждать. Чтобы повысить производительность вставки, самовозрастающая блокировка не будет снята до тех пор, пока транзакция не будет зафиксирована, но будет снята сразу после завершения соответствующего SQL-оператора вставки. Последовательные причины:
select * from user;
+----+---------+
| id | user_id |
+----+---------+
| .. | .. |
| 9 | 90 |
| 10 | 100 |
| 12 | 120 |
+----+---------+
# 11那条数据回滚了，但是id也被消耗了，id不会回滚。
Интервьюер: Хотя блокировку AUTO-INC можно снять, не дожидаясь отправки транзакции, когда она параллельна, поскольку блокировка AUTO-INC сама заблокирует самоинкрементный идентификатор, это все равно повлияет на эффективность Как решить эту проблему?
Я: Теперь InnoDB поддерживает мьютекс для достижения собственного роста, а счетчик в памяти можно накапливать через мьютекс, что быстрее, чем AUTO-INC Locking.
Интервьюер: Тогда вы знаете о тупиковой ситуации?
Я знаю.
Интервьюер: При каких обстоятельствах возникает взаимоблокировка?
Я: Условием взаимоблокировки является запрос и удержание, то есть каждая сторона сохраняет ресурсы, необходимые другой стороне, и запрашивает ресурсы, занятые другой стороной. Например:
Транзакция 1 сначала блокирует данные с id=1, затем транзакция 2 блокирует данные с id=2, а затем транзакция 1 снова пытается заблокировать данные с id=2, но обнаруживает, что она занята транзакцией 2, поэтому в этот момент time Транзакция 1 будет заблокирована, и, наконец, транзакция 2 попытается получить блокировку с id=1, но обнаружит, что она занята транзакцией 1, поэтому она также заблокируется, затем в это время она будет заблокирована, что является тупиком.
Интервьюер: Так как же решить проблему взаимоблокировки?
Я:
- InnoDB предоставляет функцию тайм-аута блокировки.Когда транзакция получает тайм-аут блокировки, она автоматически сдается и может быть выполнена другая транзакция.
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
- Каждое обновление может обновляться в согласованном порядке, что также позволяет избежать тупиковой ситуации.
- Чтобы заранее судить об обнаружении взаимоблокировок, InnoDB по умолчанию включает обнаружение взаимоблокировок.
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON |
+------------------------+-------+
InnoDB выполняет обнаружение взаимоблокировок, ожидая графа, который требует сохранения связанного списка информации о блокировке и списка ожидания транзакций, а затем строит граф ожидания с помощью связанного списка.
Интервьюер посмотрел на свой «Ролекс»: (Кажется, этого парня сегодня нельзя запирать, а время почти вышло) Тогда ждите здесь, я позвоню hr.
Я: ок ок (наконец-то закончилось).
Прошлые основные моменты:
Wechat ищет [делает вид, что разбирается в программировании], получает электронные книги и делится опытом интервью с крупными фабриками.