Рекомендуемые записи в блоге:

• Индекс высокой производительности mysql

Блокировки и транзакции MySQL

В обычном деле самое большее, это писать простенький sql.Даже транзакций меньше используется, а понимания блокировок еще больше не хватает.Раньше великий бог делил уровень изоляции транзакций mysql,что очень интересно.Я только что нашел отличный пост в блоге, а затем собрал некоторые важные знания. Я просто пришел, чтобы узнать тайну блокировок и транзакций в mysql. Основное содержание включает

1. Разница между разделяемыми блокировками и монопольными блокировками и их подходящая область действия
2. Разница между блокировкой таблицы mysql и блокировкой строки
3. Как определить, выполняет ли sql блокировку, выполняет ли она блокировку таблицы или блокировку строки
4. Что такое транзакция и как ее использовать
5. Характеристики транзакции ACID
6. Уровень изоляции транзакции (RU, RC, RR, SER)
7. Как проверить уровень изоляции, используемый mysql

I. Замок

При изучении многопоточности мы часто сталкиваемся с блокировками.В то время мы говорили об оптимистичных блокировках и пессимистичных блокировках.В чем разница между этими двумя блокировками и разделяемыми блокировками и эксклюзивными блокировками, часто упоминаемыми в БД? Сначала дайте наши известные определения оптимистической блокировки и пессимистической блокировки.

• Оптимистическая блокировка: CAS в многопоточности — это оптимистическая блокировка, на самом деле она не блокируется, сначала попробуйте выполнить ее, а в случае неудачи повторите попытку (или обработайте ее в соответствии со стратегией отказа).
• Пессимистическая блокировка: заблокирована, одновременно может получить доступ только один поток, а остальные могут только ждать

1. Общие блокировки и эксклюзивные блокировки

А. Общий замок

Выделение на ключевом слове совместного использования, как следует из названия, означает, что этой блокировкой могут пользоваться многие люди, и в целом ее можно назвать блокировкой чтения (S-блокировкой).

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

б. Эксклюзивный замок

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

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

C. gapLock и блокировка следующей клавиши

• следующая блокировка клавиш в основном в случае совпадения диапазона, она заблокирует определенный интервал диапазона
• gapLock в основном используется для блокировки границы

Например, в следующем случае (объясните, columnA — неуникальный индекс, уровень изоляции RR)

• where columnA between 10 and 30, следующая блокировка клавиш гарантирует, что новые строки данных не будут вставлены в течение 10, 30
• where columnA = 10, блокировка пробела гарантирует, что строка со столбцом A=10 не будет вставлена ​​снова

2. Блокировки таблиц и строк

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

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

Тогда возникает вопрос, какой sql вызовет блокировку строки и что вызовет блокировку записи? Даже как мы можем судить, будет ли sql запрашивать блокировку, будь то блокировка чтения или блокировка записи?

3. Как пользоваться замком

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

• select
• update
• delete
• insert

Нетрудно сделать вывод, чтоupdate, delete, insertТри из них связаны с блокировкой записи, и большинство сценариев для этой операции должны работать с определенными строками (подумайте, почему?), поэтому блокировки строк более распространены.

Операция чтения select немного особенная

а. выберите анализ

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

Снимок прочитан, без блокировки

select * from table ...

Текущий оператор read, select может указать блокировку чтения и блокировку записи следующим образом.

-- 读锁
select * from table lock in share mode;

-- 写锁
select * from table for update;

Обратите внимание, вставка, обновление, удаление также являются текущими чтениями, причины следующие:

1. Обновить и удалить декомпозицию процесса операции:

• Сначала запросите первую удовлетворенную запись через условие where и заблокируйте ее.
• Обновите эту запись и прочитайте следующую запись
• Обновите запись, продолжайте читать следующую, пока не закончите

2. Вставьте декомпозицию рабочего процесса:

• Уникальное обнаружение конфликтов ключей, будет текущее чтение
• Если нет конфликта, вставьте

Б. Анализ экземпляра SQL

--- SQL1：
select * from t1 where id = 10;

--- SQL2：
delete from t1 where id = 10;

Прежде чем анализировать приведенный выше sql, необходимо уточнить несколько предварительных условий:

• Является ли id первичным ключом (есть ли у id индекс)
• Уровень изоляции системы (что такое уровень изоляции, см. следующее введение)

Объясните отдельно:

case1:Первичный ключ + уровень RC

• sql1 не заблокирован, MySQL использует управление параллелизмом с несколькими версиями, а чтение не заблокировано.
• sql2 добавляет блокировку записи (т.е. блокировку X), блокирует только строку с id=10

case2:Уникальный индекс + уровень rc

• sql2 добавляет блокировку записи, как показано в случае ниже, есть две блокировки, одна соответствует записи с id = 10 в уникальном индексе id, а другая блокировка соответствует [name='d',id=10 в кластеризованный индекс ]запись

case3:id неуникальный индекс + RC

• SQL2 добавляет блокировки записи, как в случае, показанном ниже, будет четыре блокировки записи.

case4:без индекса + RC

• Анализ SQL2: если в столбце id нет индекса, SQL будет фильтровать путем полного сканирования кластеризованного индекса, поскольку фильтрация выполняется на уровне сервера MySQL. Поэтому к каждой записи, независимо от того, соответствует ли она условиям, будет добавлена ​​блокировка записи (блокировка X).
• Однако ради эффективности MySQL был оптимизирован. Для записей, которые не соответствуют условиям, блокировка будет снята после вынесения решения. Окончательное удержание — это блокировка записи, которая соответствует условию, но блокировка записи что не соответствует условию/ действие блокировки не будет сохранено

case5:Первичный ключ + RR

Блокировка такая же, как case1

case6:Уникальный индекс + RR

Блокировка такая же, как case2

case7:Неуникальный индекс + RR

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

То есть на рисунке ниже в дополнение к аналогичной блокировке x на рисунке 3 будет добавлена ​​блокировка пробела, которая главным образом гарантирует, что новые записи не могут быть вставлены в эти позиции.

case8:без индекса + RR

• При уровне изоляции Repeatable Read, если выполняется текущее чтение полного сканирования таблицы, все записи в таблице будут заблокированы, и все GAP в кластеризованном индексе будут заблокированы одновременно, предотвращая все одновременные обновления/удаления/удаления. операции вставки

case9:Сериализуемый уровень

• sql2: Сериализуемый уровень изоляции. Для SQL2: удалите из t1, где id = 10; уровень изоляции Serializable точно такой же, как уровень изоляции Repeatable Read
• SQL1: на уровнях изоляции RC и RR все операции чтения моментальных снимков выполняются без блокировки. Однако на уровне изоляции Serializable SQL1 добавит блокировку чтения, что означает, что чтение моментального снимка больше не существует, а уровень управления параллелизмом MVCC понижен до CC на основе блокировки.

---

II. Дела

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

Примечание. Анализ в этой статье в основном основан на подсистеме хранения innordb MySQL в качестве стандарта.

1. Определения

Транзакция — это набор атомарных SQL или независимая единица работы.

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

2. КИСЛОТНЫЕ характеристики

А. А: атомарность

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

Б. С: консистенция

Данные должны быть гарантированно переведены из одного непротиворечивого состояния в другое.

в. I: изоляция

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

г. Д: долговечность

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

3. Уровень изоляции

При анализе sql блокировки ранее упоминался уровень изоляции, обычно бывает четырех видов: RU, RC, RR, Serializable

Прежде чем объяснять это, сначала поймите несколько концепций

А. Основные понятия

• Грязное чтение: Чтение незафиксированных данных транзакции, поскольку успешное выполнение транзакции не может быть гарантировано, поэтому точность считанных данных не может быть гарантирована.
• Неповторяющееся чтение: Проще говоря, данные, считанные в транзакции, могут измениться.Один и тот же SQL может выполняться несколько раз в транзакции, и могут быть получены разные результаты.
• Фантомное чтение: запрос выполняется в транзакции сеанса T1, а затем в сеанс T2 вставляется новая строка записей, которая как раз соответствует условиям запроса, используемого T1. Затем T1 снова использует тот же запрос для получения таблицы, но в этот раз он видит новую строку, которую только что вставила транзакция T2.
• Заблокировано чтение:select * from table ...Добавлено ли выполнение блокировки чтения (это может относиться к приведенному выше анализу блокировки sql)

б. RU: Читать Uncommited

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

c. RC: Чтение зафиксировано

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

Однако могут быть случаи неповторяемости, например:

• Сеанс T1, выполнить запросselect * from where id=1, первый раз возвращает результат
• Сеанс T2, выполнить модификациюupdate table set updated=xxx where id=1и отправить
• Сеанс T1, повторите запросselect * from where id=1, поле обновления в возвращаемом результате отличается от предыдущего

В реальной производственной среде этот уровень используется очень часто, я специально проверил уровень изоляции БД компании.

Процесс демонстрации уровня RC:

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

Соответствующий код sql выглядит следующим образом:

-- 设置会话隔离级别
set session transaction ioslation read commited;

-- 查看当前会话隔离级别
select @@tx_isolation;

-- 会话1的操作
start transaction;
select * from newuser where userId=1;


-- 会话2开始操作
start transaction;
select * from newuser where userId=1;
update newuser set updated=1521786092 where userId=1;
select * from newuser where userId=1;
commit;


-- 再次进入会话1，同样执行上次的sql，对比两次输出结果
select * from newuser where userId=1;

-- 注意观察，会话1，前后两次这个sql的输出结果，特别是updated字段
-- 正常情况会如上面的demo图，会发生改变


-- 关闭会话
commit;

-- 再次查询
select * from newuser where userId=1;

г. RR: повторяемость повторного чтения.

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

Пример демонстрирует процесс решения грязных чтений (тот же процесс, что и выше)

• Обнаружено, что независимо от одного и того же sql в сеансе 1 возвращаемые результаты одинаковы.

е. Serializable может быть сериализован

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

е. Общие команды

• Просмотр текущего уровня изоляции сеанса:select @@tx_isolation
• Посмотреть текущий уровень изоляции системы:select @@global.tx_isolation
• Установите текущий уровень изоляции сеанса:set session transaction isolation level read committed;
• Установите текущий уровень изоляции системы:set global transaction isolation level read committed;
• Командная строка,
  • Начать транзакцию:start transactioin;
  • Отправить:commit;

4. Используйте осанку

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

• начать транзакциюstart transaction;
• Выполните свой бизнес sql
• совершить транзакциюcommit;

Теперь мы демонстрируем влияние блокировок чтения и блокировок записи на другую транзакцию в следующей транзакции.

а. Влияние блокировок чтения

Мы используем уровень RR mysql по умолчанию для тестирования, а userId является первичным ключом.

-- 会话1
start transaction;
select * from newuser where userId=1 lock in share mode;

-- 转入会话2
start transaction;
select * from newuser where userId=1; -- 会输出
select * from newuser where userId=1 lock in share mode; -- 会输出
update newuser set updated=1521787137 where userId=1; -- 会挂起


-- 转入会话1
-- 提交, 此时观察会话2的写是否完成
commit;

-- 转入会话2
commit;

Фактическая демонстрация выполнения:

б. Влияние блокировок записи

-- 会话1
start transaction;
select * from newuser where userId=1 for update;

-- 转入会话2
start transaction;
select * from newuser where userId=1; -- 会输出
select * from newuser where userId=1 lock in share mode; -- 会挂住

-- update newuser set updated=1521787137 where userId=1; -- 会挂住

-- 转入会话1
-- 提交, 此时观察会话2的写是否完成
commit;

-- 转入会话2
commit;

Фактическая демонстрация выполнения:

с. Резюме

• Блокировка чтения, которая блокирует другие выполнения SQL, запрашивающие блокировки записи.
• Блокировка записи, которая заблокирует выполнение sql других блокировок чтения и блокировок записи.
• Транзакция снимает блокировку только после того, как транзакция будет зафиксирована.
• Обратите внимание, что приведенная выше транзакция не снимет блокировку до тех пор, пока транзакция не будет зафиксирована, поэтому, если две транзакции циклически зависят от блокировки, может возникнуть взаимоблокировка.

---

III. Резюме

Блокировки и транзакции можно описать как очень важные точки знаний в db.В нашем фактическом процессе кодирования (обычно для mysql, механизма хранения innordb, уровня изоляции rr) мы делаем следующие резюме

1. SQL-анализ

• select * from table where xxx;(читать снапшоты, вообще не залоченные)
• select * from table where xxx lock in share mode;(Блокировки чтения блокируют другие запросы блокировки записи, но другие запросы блокировки чтения не действуют)
• select * from table where xxx for update;(Блокировка записи заблокирует другие запросы на чтение и запись)
• update tableName set xxx(запись блокировки)
• insert(запись блокировки)
• delete(запись блокировки)

2. Транзакции

Проще говоря, транзакция — это набор SQL-запросов, которые либо все выполняются успешно, либо все терпят неудачу.

Четыре особенности:A (атомарность) C (постоянство) I (изоляция) D (постоянство)

Четыре уровня изоляции:(mysql по умолчанию использует уровень RR)

уровень изоляции	грязное чтение	неповторяемое чтение	галлюцинации	блокировка чтения
read uncommited	возможный	возможный	возможный	никто
read commited	невозможно	возможный	возможный	никто
repeatable read	невозможно	невозможно	невозможно	никто
serializable	невозможно	невозможно	невозможно	имеют

Используйте позу:

start transaction;

-- xxx 具体的sql

commit;

IV. Другое

Ссылаться на

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

личный блог:Серый блог

Личный блог, основанный на страницах hexo+github, записывающий все посты блога в учебе и работе, добро пожаловать в гости

утверждение

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

• Адрес вейбо:Маленький серый блог
• QQ: серо-серый / 3302797840

Сканировать внимание