Глубокое понимание Mysql - блокировки, транзакции и контроль параллелизма

задняя часть MySQL

Статьи по Теме:
Глубокое понимание Mysql - индекс высокой производительности и высокопроизводительный SQL
Глубокое понимание Mysql — блокировки, транзакции и контроль параллелизма (слухи)
Глубокое понимание дизайна схемы Mysql и операции изменения больших таблиц.
перевод вывода объяснения mysql

В этой статье дается краткий обзор блокировок, транзакций и контроля параллелизма.После прочтения многих статей в Интернете, описания очень неточны. Если есть какое-то несоответствие с вашей точкой зрения, пожалуйста, погладьте кирпичи аргументированно!

Логическая архитектура сервера mysql

这里写图片描述

Каждое соединение будет генерировать поток на сервере mysql (поток управляется внутренне через пул потоков). Например, когда вводится оператор select, mysql сначала проверяет, кэшируется ли результирующий набор выбора в кэше запросов. нет, продолжайте выполнять синтаксический анализ, процесс оптимизации и выполнения, иначе результирующий набор будет извлечен из кеша.

Параллельный контроль MySQL — общие блокировки, эксклюзивные блокировки

общий замок

Общие блокировки также называются блокировками чтения.Блокировки чтения позволяют нескольким соединениям одновременно читать один и тот же ресурс в одно и то же время, не мешая друг другу;

эксклюзивный замок

Эксклюзивная блокировка также называется блокировкой записи.Блокировка записи блокирует другие блокировки записи или чтения, гарантируя, что только одно соединение может одновременно записывать данные, не позволяя другим пользователям читать и записывать эти данные.

стратегия блокировки

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

  • Стратегия блокировки MySQL: блокировка talbe (блокировка таблицы)

Блокировка таблицы — это самая основная стратегия блокировки MySQL, а также блокировка с наименьшими издержками: блокируется вся таблица;

Конкретная ситуация такова: если пользователь выполняет операцию записи, будет получена исключительная «блокировка записи», которая может заблокировать всю таблицу и заблокировать операции чтения и записи других пользователей;

Если пользователь выполняет операцию чтения, сначала будет получена разделяемая блокировка «блокировка чтения», которая запускает другие блокировки чтения для одновременного чтения таблицы, не мешая друг другу. Блокировки чтения могут быть одновременными чтениями универсального ресурса, пока не установлены блокировки записи.

Обычно это происходит в операторах DDL\операторах DML, которые не используют индексы, например, в этой таблице обновления DML столбец A="A" где columnB="B". Если в столбце B нет индекса (или нет префикса составного индекса). поле, оно заблокирует все. Запись представляет собой таблицу блокировки. Если выполнение оператора может выполнить индекс в поле columnB, строка, которая удовлетворяет условиям, будет заблокирована (блокировка строки).

  • Стратегия блокировки MySQL: блокировка строки (блокировка строки)

Блокировки строк могут в наибольшей степени поддерживать параллельную обработку, но, конечно же, также несут наибольшие накладные расходы.Как следует из названия, степень детализации блокировок строк находится в каждой строке данных.

дела

Транзакция — это набор атомарных SQL или независимая единица работы.
Транзакция означает, что либо движок mysql выполнит весь набор операторов sql, либо ни один из них не будет выполнен (например, если один из операторов завершится ошибкой).

Например, Тим хочет перевести 100 юаней на счет:
1. Проверьте, превышает ли баланс счета Тима 100 юаней;
2. Счет Тима уменьшен на 100 юаней;
3. Счет Билла увеличен на 100 юаней;
Эти три операции представляют собой транзакцию, которая должна быть упакована и выполнена, либо все они завершатся успешно, либо ни одна из них не будет выполнена, а сбой любой из них приведет к тому, что все три операции будут «не выполнены» — откат.

CREATE DATABASE IF NOT EXISTS employees;
USE employees;

CREATE TABLE `employees`.`account` (
  `id` BIGINT (11) NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR (4),
  `p_money` DECIMAL (10, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ;
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('1', 'tim', '200'); 
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('2', 'bill', '200'); 

START TRANSACTION;
SELECT p_money FROM account WHERE p_name="tim";-- step1
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";-- step2
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";-- step3
COMMIT;

Хорошая система транзакций должна соответствовать характеристикам ACID:

ACID транзакции

  • A: атомарность атомарность
    Транзакция должна обеспечивать выполнение всех операций в ней или откат всех, и невозможно, чтобы выполнялась только часть операций.

  • С: последовательность
    Данные должны быть гарантированно переведены из одного согласованного состояния в другое согласованное состояние..
    Например, при выполнении второго шага в предыдущей транзакции система дала сбой, и данные не показали бы, что счет Билла на 100 меньше, а счет Тима остался без изменений. Либо поддерживать оригинал (все откатилось), либо счет на 100 меньше и тим на 100 больше, только эти два состояния консистенции

  • I: изоляция изоляция
    Когда транзакция не завершена, обычно гарантируется, что другие сеансы не смогут увидеть результат выполнения транзакции.

  • Д: долговечность
    Как только транзакция будет зафиксирована, данные будут сохранены, и даже если система выйдет из строя после фиксации, данные не будут потеряны.

уровень изоляции

这里写图片描述

查看系统隔离级别：
select @@global.tx_isolation;
查看当前会话隔离级别
select @@tx_isolation;
设置当前会话隔离级别
SET session TRANSACTION ISOLATION LEVEL serializable;
设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ UNCOMMITTED (незафиксированное чтение, грязное чтение)

Изменения внутри транзакции, даже если они не зафиксированы, видны другим сеансам.
Незафиксированные данные могут быть прочитаны——грязное чтение. Грязные чтения могут вызвать множество проблем, и этот уровень изоляции, как правило, неприменим.
Пример:

-- ------------------------- read-uncommitted实例 ------------------------------
-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ UNCOMMITTED";
-- commit;

-- Session B
SELECT * FROM USER;

//SessionB Console 可以看到Session A未提交的事物处理，在另一个Session 中也看到了，这就是所谓的脏读
id  name
2   READ UNCOMMITTED
34  READ UNCOMMITTED

READ COMMITTED (фиксированное чтение или неповторяемое чтение, фантомное чтение)

Базы данных общего назначения используют этот уровень изоляции по умолчанию (mysql — нет).Этот уровень изоляции гарантирует, что если транзакция не будет полностью успешной (фиксация завершена), операции в транзакции будут невидимы для других сеансов..

-- ------------------------- read-cmmitted实例 ------------------------------
-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ  COMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ COMMITTED";
-- COMMIT;

-- Session B
SELECT * FROM USER;

//Console OUTPUT:
id  name
2   READ UNCOMMITTED
34  READ UNCOMMITTED


---------------------------------------------------
-- 当 Session  A执行了commit，Session B得到如下结果：
id  name
2   READ COMMITTED
34  READ COMMITTED

также провереноread committedДанные, измененные уровнем перед операцией фиксации вещи, не видны другим сеансам и будут видны другим сеансам только после выполнения фиксации.
Мы видим, что сеанс B дважды запрашивает и получает разные данные.

Уровень изоляции зафиксированного чтения решает проблему грязных чтений, но приводит к двум несогласованным результатам чтения для других сеансов (поскольку другой сеанс выполняет транзакции, согласованные изменения).

ПОВТОРЯЕМОЕ ЧТЕНИЕ

Унифицированный SQL для чтения выполняется несколько раз в транзакции, и возвращаемые результаты одинаковы.
Этот уровень изоляции решает проблему грязных и фантомных чтений. Это относится к уровню rr innodb.Блокировки следующего ключа используются в innodb для блокировки «текущего чтения», блокировки строки и позиции вставки, которые могут генерировать фантомные чтения, и предотвращения вставки новых данных от создания фантомных строк.
Подробный анализ ниже.

Для получения подробной информации обратитесь к руководству по mysql.

Dev.MySQL.com/doc/Furious/…

SERIALIZABLE (сериализуемый)

Самый сильный уровень изоляции, добавляя блокировки и блокировки записи к каждой строке чтения в транзакции, гарантирует, что не возникнет проблем с фантомным чтением, но это приведет к большому количеству тайм-аутов и проблемам блокировки блокировок.

Управление многоверсионным параллелизмом — MVCC

MVCC (multiple-version-concurrency-control) — этоблокировка уровня строкивариант , это вВ случае нормального чтения операции блокировки избегают, поэтому накладные расходы ниже..
Хотя реализации различаются, обычно реализациянеблокирующее чтение,заОперации записи блокируют только необходимые строки.

  • Согласованное чтение (то есть чтение моментальных снимков)
    выбрать * из таблицы ....;
  • Текущее чтение (то есть чтение фактических постоянных данных)
    Специальные операции чтения, операции вставки/обновления/удаления относятся к текущему чтению, которое обрабатывает текущие данные и должно быть заблокировано.
    select * from table where ? lock in share mode;
    select * from table where ? for update;
    insert;
    update ;
    delete;

Примечание: select... from where... (без дополнительного суффикса блокировки) использует MVCC для обеспечения моментальных снимков чтения (mysql называется непротиворечивым чтением). Обновления после старта этой транзакции читаться не будут. Подробности описаны в разделе выбора ниже.

Для заблокированного чтения SELECT с операторами FOR UPDATE (монопольная блокировка) или LOCK IN SHARE MODE (общая блокировка), update, delete необходимо учитывать, является ли это эквивалентным запросом уникального индекса.

Блокировка записи - RecordLock, gapLock, блокировка следующей клавиши

Для эквивалентных запросов, использующих уникальные индексы: например, где columnA=”…”, если используется индекс столбцаA,
Затем к записи будет добавлена ​​блокировка строки, которая удовлетворяет условию where (для обновления — это эксклюзивная блокировка, блокировка в общем — это общая блокировка, а другие операции записи добавляют эксклюзивную блокировку). Вот блокировка на уровне строки, блокировка записи.

Для запросов диапазона (с неуникальными индексами):
Например (выполнить запрос диапазона): где столбец A между 10 и 30, это приведет к невозможности вставки данных после 10 в других сеансах (блокировка следующей клавиши), что решит проблему фантомного чтения.

Здесь следующая ключевая блокировка будет включать в себя все задействованные линии.
next key lock=recordLock+gapLock, не только блокирует соответствующие данные, но и блокирует границу, полностью избегая фантомного чтения.Нажмите, чтобы просмотреть эту рекомендуемую статью

без индекса
стол блокировки
Обычно встречается в операторах DDL\операторах DML без индексации, например, в этом наборе таблиц обновления DML columnA="A", где columnB="B".
Если поле columnB не имеет индекса (или не является префиксом составного индекса), будут заблокированы все записи, то есть таблица блокировки. Если выполнение оператора может выполнить индекс в поле columnB, строка, которая удовлетворяет условиям, будет заблокирована (блокировка строки).

MVCC INNODB обычно реализуется путем сохранения двух скрытых столбцов за каждой строкой данных (на самом деле три столбца, третий столбец используется для отката транзакции, который здесь опущен),
Один содержит созданный номер версии строки, другой содержит обновленный номер версии строки (номер версии последних обновленных данных).
Этот номер версии представляет собой увеличивающийся номер версии каждой транзакции.

Это гарантирует, что innodb сможет правильно читать данные без блокировки операций чтения.

MVCC выбирает номер версии без блокировки и обслуживания

Ниже, при уровне изоляции Repeatable Read по умолчанию для mysql, давайте взглянем на операцию MVCC:

  • Выберите (снапшот чтения, так называемый моментальный снимок чтения для чтения данных перед текущей транзакцией):
    a.InnoDB выбирает только строки с номером версии, предшествующим текущему номеру версии., что гарантирует, что прочитанные данные либо фиксируются до начала транзакции (раньше текущего номера версии), либо данные, созданные в самой транзакции (равные текущему номеру версии).

    б) Обновленный номер версии строки поиска либо не определен, либо больше, чем текущий номер версии (чтобы убедиться, что транзакция может прочитать старые данные), что гарантирует, что транзакция читает данные, которые не были обновлены после начинается текущая транзакция.
    Примечание. Выбор здесь не может содержать операторы для обновления и блокировки в совместном использовании.
    Короче говоря, для достижения эффекта чтения снимка следует возвращать только те данные строки, которые удовлетворяют следующим условиям:

(行创建版本号< =当前版本号 && (行更新版本号==null or 行更新版本号>当前版本号 ) )

  • Insert

    InnoDB создает номера версий для вновь вставленных строк в этой транзакции, сохраняя строку с текущим номером версии транзакции в качестве строки строки.

  • Delete
    InnoDB сохраняет текущий номер версии транзакции для каждой удаленной строки в качестве маркера удаления строки.

  • Update

    Будет две части данных, сохраните номер текущей версии в качестве нового номера версии обновленных данных и сохраните номер текущей версии в качестве обновленного номера версии старой строки данных.

当前版本号—写—>新数据行创建版本号 && 当前版本号—写—>老数据更新版本号();

Грязные чтения, фантомные чтения и неповторяющиеся чтения

грязное чтение:Обновления незафиксированного промежуточного состояния транзакции считываются другими сеансами.Когда транзакция обращается к данным и изменяет данные, а изменение не было отправлено в базу данных (фиксация не была выполнена), в это время другой сеанс также обращается к данным, поскольку данные не были отправлены, затем другой сеанс Данные, считанные сеансом, являются грязными данными, и операции, основанные на грязных данных, также могут быть неверными.

неповторяемое чтение:Проще говоря, данные, считанные в транзакции, могут измениться.ReadCommitted также известен как неповторяемое чтение..

Многократное чтение одних и тех же данных в рамках одной и той же транзакции возвращает разные результаты. Другими словами, последующие чтения могут считывать обновленные данные, которые были зафиксированы другой сессионной транзакцией. Напротив, «повторяемое чтение» может гарантировать, что считываемые данные будут одинаковыми при многократном чтении данных в одной и той же транзакции, то есть последующие чтения не могут считывать обновленные данные, которые были зафиксированы другой сессионной транзакцией.

галлюцинации: выполнить запрос в транзакции сеанса T1, а затем вставить новую строку записей в сеанс T2,Эта строка записей как раз удовлетворяет условиям запроса, используемого T1.. Затем T1 снова использует тот же запрос для получения таблицы, но теперь видит новую строку, которую только что вставила транзакция T2. Эта новая строка называется «фантомом», потому что T1 кажется, будто она появилась из ниоткуда.
Уровень RR innoDB не может полностью избежать фантомных чтений, которые будут подробно проанализированы ниже.

----------------------------------前置准备----------------------------------------
prerequisite：
-- 创建表
mysql>
CREATE TABLE `t_bitfly` (
   `id` bigint(20) NOT NULL DEFAULT '0',
   `value` varchar(32) DEFAULT NULL,
   PRIMARY KEY (`id`)
 )

-- 确保当前隔离级别为默认的RR级别

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
---------------------------------------开始--------------------------------------------- 


session A                                           |   session B
                                                    |
                                                    |
mysql> START TRANSACTION;                           |   mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)                |   Query OK, 0 rows affected (0.00 sec)                                        
                                                    |   
                                                    |
mysql> SELECT * FROM test.t_bitfly;                 |   mysql> SELECT * FROM test.t_bitfly; 
Empty set (0.00 sec)                                |   Empty set (0.00 sec)
                                                    |
                                                    |   mysql> INSERT INTO t_bitfly VALUES (1, 'test');
                                                    |   Query OK, 1 row affected (0.00 sec)
                                                    |
                                                    |
mysql> SELECT * FROM test.t_bitfly;                 |
Empty set (0.00 sec)                                |
                                                    |
                                                    |   mysql> commit;
                                                    |   Query OK, 0 rows affected (0.01 sec)                                                
mysql> SELECT * FROM test.t_bitfly;                 |
Empty set (0.00 sec)                                |
-- 可以看到虽然两次执行结果返回的数据一致，         |
-- 但是不能说明没有幻读。接着看：                   |
                                                    |
mysql> INSERT INTO t_bitfly VALUES (1, 'test');     |
ERROR 1062 (23000):                                 |
Duplicate entry '1' for key 'PRIMARY'               |
                                                    |
-- 明明为空的表，为什么说主键重复？——幻读出现 ！！！       |

Как гарантировать, что уровень rr никогда не будет генерировать фантомное чтение?

Добавьте оператор for update (монопольная блокировка) или блокировка в режиме общего доступа (общая блокировка) в оператор select...where, используемый для достижения этой цели.Фактически, он блокирует данные, которые могут вызвать фантомное чтение, и предотвращает операции записи данных.

Фактически это связано с тем, что операция записи данных (вставка, обновление) должна сначала получить блокировку записи.Поскольку часть, которая может генерировать фантомное чтение, уже получила блокировку определенного типа, предпосылка получения блокировки записи в другом сеансе находится в текущем сеансе. Снимите все блокировки, созданные операторами блокировки.

проблема взаимоблокировки mysql

Тупик — это циклическая цепочка ожидания. Я жду ваших ресурсов, а вы ждете моих. Мы все ждем друг друга, и никто не освобождает ресурсы, которыми мы владеем, что приводит к ожиданию беспроводной связи. Например:

//Session A
START TRANSACTION;
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
COMMIT;
//Thread B
START TRANSACTION;
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
COMMIT;

Когда поток A выполняет первый оператор UPDATE account SET p_money=p_money-100 WHERE p_name="tim", блокирует данные строки p_name="tim" и пытается получить данные p_name="bill";

, в это время поток B также выполняет первый оператор: UPDATE account SET p_money=p_money+100 WHERE p_name="bill";

Заблокируйте данные p_name="bill" и попытайтесь одновременно получить данные p_name="tim"; в это время два потока входят в тупик, и никто не может получить ресурсы, которые они хотят получить, и войдите в беспроводную сеть, дождитесь тайм-аута!

innodb_lock_wait_timeout дождитесь тайм-аута блокировки, чтобы откатить транзакцию:
Интуитивно понятный метод заключается в том, что когда две транзакции ждут друг друга, когда время ожидания одной из них превышает определенный установленный порог, одна из транзакций откатывается, а другая транзакция может продолжать выполняться. Этот метод прост и эффективен.В innodb параметр innodb_lock_wait_timeout используется для установки периода ожидания.

Алгоритм ожидания графа для активного обнаружения взаимоблокировок:
Innodb также предоставляет алгоритм графа ожидания для активного обнаружения взаимоблокировок.Всякий раз, когда запрос на блокировку не может удовлетворить потребности немедленно и входит в ожидание, будет запущен алгоритм графа ожидания.

Как максимально избежать взаимоблокировок

1) Доступ к таблицам и строкам в фиксированном порядке. Например, для двух транзакций, которые обновляют данные, порядок обновления данных в транзакции A — 1, 2, порядок обновления данных в транзакции B — 2, 1. Это, скорее всего, вызовет взаимоблокировки.

2) Большие дела делятся на маленькие. Крупные транзакции более подвержены взаимоблокировкам.Если бизнес позволяет, разделите большие транзакции на мелкие.

3) В одной и той же транзакции попытайтесь заблокировать все необходимые ресурсы одновременно, чтобы уменьшить вероятность взаимоблокировки.

4) Понизить уровень изоляции. Если бизнес позволяет, также рекомендуется понизить уровень изоляции.Например, изменение уровня изоляции с RR на RC может избежать многих взаимоблокировок, вызванных гэп-блокировками.

5) Добавьте в таблицу разумный индекс. Видно, что если индекс не использовать, то к каждой строке таблицы будет добавлена ​​блокировка, и вероятность взаимоблокировки сильно возрастет.

Явные и неявные блокировки
неявная блокировка: Все блокировки, о которых мы упоминали выше, являются неявными блокировками, которые не требуют дополнительной блокировки операторов.
показать замок:

SELECT ... LOCK IN SHARE MODE(加共享锁);
SELECT ... FOR UPDATE(加排他锁);

Подробности были упомянуты выше.

Вы можете просмотреть статус ожидания блокировок через следующий sql

select * from information_schema.innodb_trx where trx_state="lock wait";
或
show engine innodb status;

транзакции в mysql

show variables like "autocommit";

set autocommit=0; //0表示AutoCommit关闭
set autocommit=1; //1表示AutoCommit开启
  • Автоматическая фиксация (AutoCommit, mysql по умолчанию)

По умолчанию mysql принимает режим AutoCommit, то есть каждый sql является транзакцией и не требует явного выполнения. Если autoCommit отключен, то каждый sql по умолчанию открывает транзакцию, и эта транзакция будет зафиксирована только после явного выполнения «commit».

рекомендовать:
Dev.MySQL.com/doc/Furious/…
hedengcheng.com/?p=577
hedengcheng.com/?p=771

Категории