Блокировка таблицы MySQL и механизм блокировки строк
Блокировка строки становится блокировкой таблицы, это благословение или яма? Если вы не знаете принцип блокировки MySQL, вы будете несчастны! Не знаешь, где дырка? Ничего, я отмечу для вас несколько ям. Если вы встретите его, не наступайте на него. В этой главе я познакомлю вас с блокировками строк и таблиц в MySQL, преимуществами и недостатками двух типов блокировок, причинами, по которым блокировки строк превращаются в блокировки таблиц, и вопросами, требующими внимания при разработке. Чего же ты ждешь? Опыт ждет вас!
Механизм хранения MySQL — от MyISAM до InnoDB, а блокировки — от блокировок таблиц до блокировок строк. Появление последних в какой-то мере компенсирует недостатки первых. Например: MyISAM не поддерживает транзакции, InnoDB поддерживает транзакции. Хотя блокировки таблиц имеют низкие накладные расходы и быстрые блокировки таблиц, их производительность низка при высокой степени параллелизма. Хотя блокировки строк являются дорогостоящими и медленными для блокировки таблиц, они имеют более высокую производительность при высокой степени параллелизма. Как транзакции, так и блокировки строк улучшают возможности параллельной обработки за счет обеспечения точности данных. Эта глава посвящена блокировкам строк InnoDB.
анализ случая
В настоящее время обычно используемым механизмом хранения для MySQL является InnoDB, по сравнению с MyISAM. InnoDB больше подходит для сценариев с высокой степенью параллелизма, а также поддерживает обработку транзакций. Мы используем следующий случай (яма), чтобы понять блокировки строк и таблиц.
Бизнес: поскольку заказ импортируется повторно, вам необходимо использовать сценарий для пакетного изменения данных, статус заказа которых «подлежит подтверждению службой поддержки клиентов», а платформа — «xxx» на «закрыта».
Описание: Избегайте прямого изменения таблицы заказов, чтобы вызвать исключения данных. Здесь мы используем таблицу innodb_lock для демонстрации блокировок строк InnoDB. В таблице есть три поля: id, k (значение ключа), v (значение значения).
шаг:
Первый шаг: подключитесь к базе данных, здесь она называется Transaction-A для удобства различения, и установите autocommit на ноль, указывая на то, что транзакцию необходимо зафиксировать вручную.
Шаг 2: Транзакция-A, выполните команду обновления, чтобы изменить идентификатор на 1.
Шаг 3: Добавьте новое соединение с именем Transaction-B, которое может изменять данные с идентификатором 2 в обычном режиме. При выполнении команды для изменения данных с идентификатором 1 обнаруживается, что команда заблокирована и ожидает.
Шаг 4: Транзакция-A, выполните команду фиксации. Транзакция-B, команда изменить идентификатор на 1 выполняется автоматически, ожидая 37,51 секунды.
Суммировать:Когда несколько транзакций работают с одной и той же строкой данных, последующие транзакции находятся в состоянии ожидания блокировки. Это позволяет избежать проблем с согласованностью данных, таких как грязное чтение. Последующие транзакции могут оперировать данными других строк, решая проблему высокой параллелизма и низкой производительности блокировок таблиц..
# Transaction-A
mysql> set autocommit = 0;
mysql> update innodb_lock set v='1001' where id=1;
mysql> commit;
# Transaction-B
mysql> update innodb_lock set v='2001' where id=2;
Query OK, 1 row affected (0.37 sec)
mysql> update innodb_lock set v='1002' where id=1;
Query OK, 1 row affected (37.51 sec)При вышеописанных операциях моделирования результаты на удивление согласуются с теорией, и кажется, что можно смело и смело воевать. . . . . . Но реальность действительно жестока.
Реальность: блокировки строк обновляются до блокировок таблиц при выполнении сценариев пакетного изменения данных. Ожидаются другие операции по заказу,
Причина: InnoDB использует блокировки на уровне строк только при извлечении данных через условия индекса, в противном случае используются блокировки таблиц! Операция моделирования использует идентификатор в качестве условия извлечения, и идентификатор является единственным индексом, автоматически созданным MySQL, поэтому ситуация, когда блокировка строки становится блокировкой таблицы, игнорируется.
шаг:
Шаг 1: Восстановите проблему, транзакция-A, обновите v на k=1. Транзакция-B, обновить v с k=2, команда находится в состоянии ожидания блокировки.
Шаг 2: Решите проблему и добавьте индексы к полям, которые необходимо использовать в качестве условий запроса. Его можно удалить после использования.
Суммировать:Блокировки строк InnoDB — это блокировки для индексов, а не для записей. И индекс не может быть признан недействительным, иначе он будет обновлен с блокировки строки до блокировки таблицы.. Причины сбоя индекса были представлены в предыдущей главе:www.cnblogs.com/itdrag...
Transaction-A
mysql> update innodb_lock set v='1002' where k=1;
mysql> commit;
mysql> create index idx_k on innodb_lock(k);
Transaction-B
mysql> update innodb_lock set v='2002' where k=2;
Query OK, 1 row affected (19.82 sec)Из приведенного выше случая видно, что похоже на яму для блокировки строк, чтобы стать блокировками таблицы, но MySQL не настолько скучен, чтобы копать яму для вас. Это связано с тем, что у MySQL есть собственный план выполнения.
Когда вам нужно обновить большую часть или даже все данные большой таблицы. И вы по глупости используете индекс как условие поиска. Блокировка строки была случайно открыта (нет проблем! Обеспечьте согласованность данных!). Тем не менее, MySQL считает, что использование большого количества блокировок строк в таблице приведет к низкой эффективности выполнения транзакций, что может привести к длительному ожиданию блокировки других транзакций и большему количеству конфликтов блокировок, что приведет к серьезному снижению производительности. Таким образом, MySQL увеличивает блокировки строк до блокировки таблиц, т.е. индексы фактически не используются.
Если хорошенько подумать, то можно понять, что, поскольку большая часть данных во всей таблице нуждается в обновлении, менее эффективно блокировать построчно. Фактически, мы можем просмотреть план выполнения MySQL с помощью команды объяснения, и вы обнаружите, что ключ равен нулю. Это показывает, что MySQL на самом деле не использует индексы, и обновление блокировок строк до блокировок таблиц также согласуется с приведенным выше выводом.
В этой главе основное внимание уделяется блокировкам строк InnoDB и связанным с ними сведениям о транзакциях. Если вы хотите узнать план выполнения MySQL, см.предыдущая глава.
блокировка строки
Недостатки блокировки строк: большие накладные расходы, медленная блокировка, тупиковые ситуации.
Преимущества блокировок строк: малая степень детализации блокировок, низкая вероятность конфликтов блокировок, хорошая возможность обработки параллелизма.
Метод блокировки: автоматическая блокировка. Для операторов UPDATE, DELETE и INSERT InnoDB автоматически добавит эксклюзивные блокировки к задействованным наборам данных; для обычных операторов SELECT InnoDB не добавит никаких блокировок; конечно, мы также можем отобразить блокировки:
Общая блокировка: выберите * из tableName, где ... + заблокируйте общий доступ больше
Эксклюзивная блокировка: выберите * из tableName, где
... + за обновление
Между InnoDB и MyISAM есть два самых больших различия: во-первых, InnoDB поддерживает транзакции, во-вторых, по умолчанию используются блокировки на уровне строк. Блокировка может обеспечить согласованность транзакций.Можно сказать, что там, где есть люди (блокировки), есть реки и озера (транзакции), давайте вкратце разберемся в знании транзакций.
Свойства транзакции MySQL
Транзакция — это логическая единица обработки, состоящая из набора операторов SQL, и транзакция имеет свойства ACID.
атомарность(Атомарность): транзакция — это атомарная единица операции. В то время атом был наименьшим неделимым элементом, и его модификации данных либо все были успешными, либо все неудачными.
последовательность(Непротиворечивый): в течение периода времени от начала до конца транзакции данные должны оставаться в согласованном состоянии.
изоляция(Изоляция): система базы данных предоставляет определенный механизм изоляции, чтобы гарантировать выполнение транзакций в «независимой» среде, на которую не влияют внешние параллельные операции.
Упорство(Продолжительный): после завершения транзакции ее изменения в данных являются постоянными и могут сохраняться даже в случае сбоя системы.
Часто задаваемые вопросы о транзакциях
обновление потеряно(Потерянное обновление)
Причина: когда несколько транзакций выбирают одну и ту же операцию строки, и все они основаны на первоначально выбранном значении, поскольку каждая транзакция не знает о существовании других транзакций, возникает проблема охвата обновления. Аналогия с конфликтами коммитов на github.
грязное чтение(Грязные чтения)
Причина: Транзакция A прочитала данные, которые транзакция B изменила, но еще не зафиксировала. Если транзакция B откатывает данные, возникает проблема несогласованности данных транзакции A.
неповторяемое чтение(Неповторяемое чтение)
Причина: транзакция A считывает исходные данные в первый раз и считывает измененные или удаленные данные, зафиксированные транзакцией B, во второй раз. Это приводит к несогласованности между двумя чтениями. Не соответствует изоляции транзакций.
галлюцинации(Фантомные чтения)
Причина: транзакция A запрашивает вновь добавленные данные, отправленные транзакцией B, во второй раз в соответствии с теми же условиями, и наборы результатов двух данных несовместимы. Не соответствует изоляции транзакций.
Фантомные чтения похожи на грязные чтения
Грязное чтение означает, что данные изменены в транзакции B.
Фантомные чтения — это новые данные, добавленные в транзакцию B.
уровень изоляции транзакций
Чем строже изоляция транзакций в базе данных, тем меньше параллельных побочных эффектов, но выше стоимость. Это связано с тем, что изоляция транзакций в определенной степени является «последовательной», что явно противоречит «параллелизму». В соответствии с собственной бизнес-логикой взвесьте максимально допустимый побочный эффект. Таким образом, уравновешивая проблемы «изоляции» и «параллелизма». Уровень изоляции MySQL по умолчанию — повторяемое чтение. Грязные чтения, неповторяемые чтения и фантомные чтения на самом деле являются проблемами согласованности чтения базы данных, которые должны решаться базой данных, предоставляющей определенный механизм изоляции транзакций.
+------------------------------+---------------------+--------------+--------------+--------------+
| 隔离级别 | 读数据一致性 | 脏读 | 不可重复 读 | 幻读 |
+------------------------------+---------------------+--------------+--------------+--------------+
| 未提交读(Read uncommitted) | 最低级别 | 是 | 是 | 是 |
+------------------------------+---------------------+--------------+--------------+--------------+
| 已提交读(Read committed) | 语句级 | 否 | 是 | 是 |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可重复读(Repeatable read) | 事务级 | 否 | 否 | 是 |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可序列化(Serializable) | 最高级别，事务级 | 否 | 否 | 否 |
+------------------------------+---------------------+--------------+--------------+--------------+Просмотрите уровень изоляции транзакций текущей базы данных: покажите переменные, такие как «tx_isolation»;
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+гэп замок
Когда мы извлекаем данные с условием диапазона и запрашиваем общую или монопольную блокировку, InnoDB блокирует запись индекса существующей записи данных, которая соответствует условию; для записи, значение ключа которой находится в пределах диапазона условия, но не существует, она блокируется. называется «зазор (GAP)». InnoDB также заблокирует этот «пробел», и этот механизм блокировки представляет собой так называемую блокировку промежутка (блокировка Next-Key).
Transaction-A
mysql> update innodb_lock set k=66 where id >=6;
Query OK, 1 row affected (0.63 sec)
mysql> commit;
Transaction-B
mysql> insert into innodb_lock (id,k,v) values(7,'7','7000');
Query OK, 1 row affected (18.99 sec)Опасность (яма):Если условие выполнения состоит в том, что диапазон слишком велик, InnoDB заблокирует все значения ключа индекса во всем диапазоне, что легко повлияет на производительность..
эксклюзивный замок
Эксклюзивные блокировки, также известные как блокировки записи, монопольные блокировки, блокируют другие блокировки записи и блокировки чтения до завершения текущей операции записи.
# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k | v |
+----+------+------+
| 4 | 4 | 4000 |
+----+------+------+
1 row in set (0.00 sec)
mysql> update innodb_lock set v='4001' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.04 sec)# Transaction_B
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k | v |
+----+------+------+
| 4 | 4 | 4001 |
+----+------+------+
1 row in set (9.53 sec)общий замок
Общие блокировки, также известные как блокировки чтения, в основном используются для определения того, существуют ли данные, и несколько операций чтения могут выполняться одновременно, не влияя друг на друга. Когда транзакция изменяет блокировку чтения, это может привести к взаимоблокировке. Как показано ниже.
# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k | v |
+----+------+------+
| 4 | 4 | 4001 |
+----+------+------+
1 row in set (0.00 sec)
mysql> update innodb_lock set v='4002' where id=4;
Query OK, 1 row affected (31.29 sec)
Rows matched: 1 Changed: 1 Warnings: 0# Transaction_B
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k | v |
+----+------+------+
| 4 | 4 | 4001 |
+----+------+------+
1 row in set (0.00 sec)
mysql> update innodb_lock set v='4002' where id=4;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionАнализ блокировки строк
Проанализируйте конкуренцию за блокировку строк в вашей системе, проверив состояние отображения переменной состояния InnoDB_row_lock, например «innodb_row_lock%».
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+innodb_row_lock_current_waits: количество ожидающих блокировок.
innodb_row_lock_time: общее время, прошедшее с момента начала блокировки системы; очень важный параметр,
innodb_row_lock_time_avg: среднее время ожидания; очень важный параметр,
innodb_row_lock_time_max: время ожидания наиболее частого времени от запуска системы до настоящего времени;
innodb_row_lock_waits: общее количество ожиданий системы с момента ее запуска; очень важный параметр. Непосредственно определить направление и стратегию оптимизации.
оптимизация блокировки строк
1 Насколько это возможно, весь поиск данных должен выполняться через индексы, чтобы избежать обновления блокировок строк до блокировок таблиц из-за неиндексированных строк или сбоев индекса.
2 Насколько это возможно, избегайте снижения производительности, вызванного блокировками промежутков, и уменьшайте или используйте разумный диапазон извлечения.
3. Максимально уменьшите детализацию транзакции, например, контролируйте размер транзакции, уменьшите количество ресурсов блокировки и продолжительность времени, тем самым уменьшив конкуренцию блокировок и т. д., чтобы обеспечить производительность.
4. Низкоуровневая изоляция транзакций: чем выше уровень изоляции, тем ниже возможности параллельной обработки.
блокировка стола
Преимущества табличных блокировок: низкие накладные расходы, быстрая блокировка, отсутствие взаимоблокировок.
Недостатки табличных блокировок: большая степень детализации блокировок, высокая вероятность конфликта блокировок, низкие возможности параллельной обработки.
Метод блокировки: автоматическая блокировка. Операции запроса (SELECT) автоматически добавляют блокировки чтения ко всем задействованным таблицам, а операции обновления (UPDATE, DELETE, INSERT) автоматически добавляют блокировки записи к задействованным таблицам. Замки также могут отображаться:
Общая блокировка чтения: блокировка таблицы tableName read;
Эксклюзивная блокировка записи: блокировка таблицы tableName write;
Пакетная разблокировка: разблокировка столов;
общая блокировка чтения
Операция чтения (добавление блокировки чтения) в таблицу MyISAM не блокирует операцию чтения той же таблицы другими процессами, но блокирует операцию записи той же таблицы. Только после снятия блокировки чтения можно выполнять операции записи других процессов. Никакие другие таблицы не могут быть получены, пока блокировка не будет снята.
Transaction-A
mysql> lock table myisam_lock read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)
mysql> select * from innodb_lock;
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES
mysql> update myisam_lock set v='1001' where k='1';
ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)Transaction-B
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)
mysql> select * from innodb_lock;
8 rows in set (0.01 sec)
mysql> update myisam_lock set v='1001' where k='1';
Query OK, 1 row affected (18.67 sec)Эксклюзивная блокировка записи
Операция записи (добавление блокировки записи) в таблицу MyISAM заблокирует операции чтения и записи других процессов в ту же таблицу, и только когда блокировка записи будет снята, операции чтения и записи других процессов будут выполняться. Никакие другие таблицы не могут быть записаны, пока блокировка не будет снята.
Transaction-A
mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)
mysql> lock table myisam_lock write;
Query OK, 0 rows affected (0.03 sec)
mysql> update myisam_lock set v='2001' where k='2';
Query OK, 1 row affected (0.00 sec)
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)
mysql> update innodb_lock set v='1001' where k='1';
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)Transaction-B
mysql> select * from myisam_lock;
9 rows in set (42.83 sec)Суммировать:Блокировки таблицы, блокировки чтения блокируют запись, но не блокируют чтение. Блокировка записи блокирует как чтение, так и запись.
Проверить состояние блокировки
показывать открытые столы; 1 означает заблокировано, 0 означает разблокировано.
mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock | myisam_lock | 1 | 0 |
+----------+-------------+--------+-------------+Блокировка таблицы анализа
Блокировку таблиц в системе можно проанализировать, изучив переменные состояния table_locks_waited и table_locks_immediate: показать статус, например «table_locks%»
mysql> show status like 'table_locks%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 104 |
| Table_locks_waited | 0 |
+----------------------------+-------+table_locks_immediate: указывает количество блокировок таблицы, которые необходимо снять немедленно. table_locks_waited: указывает количество ожидаемых блокировок таблицы. Более высокие значения указывают на более серьезную конкуренцию за блокировку на уровне таблицы.
Кроме того, планирование блокировки чтения-записи MyISAM является приоритетом записи, что также не подходит в качестве механизма хранения для записи в основную таблицу. Потому что после записи блокировки другие потоки не могут выполнять никаких операций, а большое количество обновлений затруднит запросу получение блокировки, что приведет к постоянной блокировке.
В каких сценариях используются блокировки таблиц?
InnoDB по умолчанию использует блокировки строк, которые обновляются до блокировок таблиц при выполнении запросов без полей индекса. MySQL создан не для того, чтобы копать для вас яму. У него есть свое дизайнерское предназначение. Даже если вы используете поле индекса в условии, MySQL рассмотрит, использовать ли индекс в соответствии со своим собственным планом выполнения (поэтому в команде объяснения будут возможные_ключ и ключ). Если MySQL считает, что полное сканирование таблицы более эффективно, она не будет использовать индекс, и в этом случае InnoDB будет использовать блокировки таблицы вместо блокировки строк. Поэтому при анализе конфликтов блокировок не забудьте проверить план выполнения SQL, чтобы убедиться, что индекс действительно используется.
Первый случай:Полное обновление таблицы. Транзакция должна обновить большую часть или все данные, а таблица относительно велика. Если используются блокировки строк, эффективность выполнения транзакций будет низкой, что может привести к длительному ожиданию блокировки и большему количеству конфликтов блокировок для других транзакций.
Второй случай:Многотабличный запрос. Транзакции включают несколько таблиц, а сложные связанные запросы могут привести к взаимоблокировкам и большому количеству откатов транзакций. В этом случае, если вы сможете одновременно заблокировать таблицы, участвующие в транзакции, вы сможете избежать взаимной блокировки и снизить нагрузку на базу данных из-за отката транзакции.
блокировка страницы
Накладные расходы и время блокировки находятся между блокировками таблицы и блокировками строк; будут возникать взаимоблокировки; гранулярность блокировок находится между блокировками таблиц и блокировками строк, а возможности параллельной обработки являются средними. Просто взгляните.
Суммировать
1 InnoDB поддерживает блокировки таблиц и строк.Блокировки строк используются, когда данные изменяются с использованием индексов в качестве условий извлечения, а блокировки таблиц используются в противном случае.
2 InnoDB автоматически блокирует операции модификации, но не блокирует автоматически операции запроса
3 Блокировка строки может быть обновлена до блокировки таблицы, поскольку индекс не используется, поэтому в дополнение к проверке того, создан ли индекс, также необходимо запросить, действительно ли индекс используется с помощью плана выполнения объяснения.
Преимущество четырехстрочных блокировок по сравнению с блокировками таблиц состоит в том, что они более заметны в сценариях с высокой степенью параллелизма, поскольку степень детализации блокировок невелика.
5 Когда необходимо изменить большую часть данных в таблице или когда выполняется сложный запрос ассоциации с несколькими таблицами, рекомендуется использовать блокировки таблиц вместо блокировок строк.
6 Для обеспечения согласованной целостности данных любая база данных имеет механизм блокировки. Качество механизма блокировки напрямую влияет на возможности параллельной обработки и производительность базы данных.
На данный момент в Mysql введены блокировка таблицы и механизм блокировки строки.Если вы не знаете, что блокировка строки InnoDB будет обновлена до блокировки таблицы, вы сильно пострадаете в будущем. Пожалуйста, поправьте меня, если что-то не так. Если вы считаете, что статья хорошая, ставьте лайк! Иди сюда, оставь свои следы!