Чтение Mysql между обычным чтением и чтением блокировки: SEMI-CONSISITENT READ

Ярлыки: Статьи официального аккаунта

Прежде чем читать эту статью, лучше всего прочитать три статьи, посвященные анализу блокировок операторов:

Сверхполноценный анализ блокировки операторов MySQL (Часть 1)

Сверхполноценный анализ блокировки операторов MySQL (Часть 2)

а

Для плавного развития истории мы сначала строим таблицу и вставляем в нее некоторые записи.Следующее является оператором SQL:

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

в настоящее времяheroЗаписи в таблице следующие:

mysql> SELECT * FROM hero;
+--------+------------+---------+
| number | name       | country |
+--------+------------+---------+
|      1 | l刘备      | 蜀      |
|      3 | z诸葛亮    | 蜀      |
|      8 | c曹操      | 魏      |
|     15 | x荀彧      | 魏      |
|     20 | s孙权      | 吴      |
+--------+------------+---------+
5 rows in set (0.01 sec)

Феномен

В группе вопросов и ответов буклета одноклассник задал вопрос:READ COMMITTEDПод уровнем изоляции произошло необъяснимое. Хорошо, сначала создайте среду и установите уровень изоляции текущего сеанса по умолчанию наREAD COMMITTED:

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

делаT1Первый забег:

# T1中，隔离级别为READ COMMITTED
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM hero WHERE country = '魏' FOR UPDATE;
+--------+---------+---------+
| number | name    | country |
+--------+---------+---------+
|      8 | c曹操   | 魏      |
|     15 | x荀彧   | 魏      |
+--------+---------+---------+
2 rows in set (0.01 sec)

countryСтолбец не индексирован столбец, поэтому это утверждение к реализации должно быть выполнено с использованием полной таблицы сканирования сканирования кластеризованного индекса,EXPLAINУтверждение также подтверждает нашу идею:

mysql> EXPLAIN SELECT * FROM hero WHERE country = '魏' FOR UPDATE;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | hero  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

Мы изучили анализ блокировок операторов MySQL раньше, зная, чтосуществуетREAD COMMITTEDНа уровне изоляции, если для выполнения оператора запроса используется полное сканирование таблицы, механизм хранения InnoDB добавит обычную блокировку записи к каждой записи по очереди и проверит, соответствует ли запись условию WHERE на уровне сервера. , он будет добавлен в запись. снимите блокировку на. используется в этом примереFOR UPDATEСледует добавить, что это серьезная блокировка записи X-типа. Совпадают только две записиWHEREУсловия, так что фактически добавлены только эти два итоговых квалификационных рекорда.X型正经记录锁(этоnumberзначение столбца8и15две записи). Конечно, мы можем использоватьSHOW ENGINE INNODB STATUSКоманда подтверждает наш анализ:

mysql> SHOW ENGINE INNODB STATUS\G
... 省略了很多内容

------------
TRANSACTIONS
------------
Trx id counter 39764
Purge done for trx's n:o < 39763 undo n:o < 0 state: running but idle
History list length 36
Total number of lock structs in row lock hash table 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479653009568, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 281479653012832, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 39763, ACTIVE 468 sec
2 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 19, OS thread handle 123145470611456, query id 586 localhost 127.0.0.1 root
TABLE LOCK table `xiaohaizi`.`hero` trx id 39763 lock mode IX
RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39763 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000009b4a; asc      J;;
 2: len 7; hex 80000001d3012a; asc       *;;
 3: len 7; hex 63e69bb9e6938d; asc c      ;;
 4: len 3; hex e9ad8f; asc    ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000f; asc     ;;
 1: len 6; hex 000000009b4a; asc      J;;
 2: len 7; hex 80000001d30137; asc       7;;
 3: len 7; hex 78e88d80e5bda7; asc x      ;;
 4: len 3; hex e9ad8f; asc    ;;

 ... 省略了很多内容

вidза39763деловые средстваT1, видно, что этоheap noзначение4и5Две записи добавленыX型正经记录锁(lock_mode X блокирует запись, но не пробел).

Затем включите другой уровень изоляцииREAD COMMITTEDделаT2, в котором выполнить:

# T2中，隔离级别为READ COMMITTED
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM hero WHERE country = '吴' FOR UPDATE;
(进入阻塞状态)

Очевидно, что этот оператор также будет выполняться при полном сканировании таблицы, при котором по очереди будет запрашиваться блокировка каждой записи кластеризованного индекса. Но потому чтоnumberзначение8записи былиT1добавлятьX型正经记录锁,T2Я хотел бы иметь, но не могу получить, могу позволить себе только быть заблокированным в это времяSHOW ENGINE INNODB STATUSЭто также может подтвердить нашу гипотезу (перехвачена только часть):

---TRANSACTION 39764, ACTIVE 34 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 20, OS thread handle 123145471168512, query id 590 localhost 127.0.0.1 root Sending data
SELECT * FROM hero WHERE country = '吴' FOR UPDATE
------- TRX HAS BEEN WAITING 34 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39764 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000009b4a; asc      J;;
 2: len 7; hex 80000001d3012a; asc       *;;
 3: len 7; hex 63e69bb9e6938d; asc c      ;;
 4: len 3; hex e9ad8f; asc    ;;

можно увидетьT2жду, чтобы получитьheap noза4в записиX型正经记录锁(lock_mode X блокирует запись, но не ожидает промежутка).

Выше приведена вполне нормальная логика блокировки, мы можем ее проанализировать, но если вT2выполнить следующееUPDATEЗаявление:

# T2中，隔离级别为READ COMMITTED
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE hero SET name = 'xxx' WHERE country = '吴';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

WTF?Блокировки нет,поэтому исполнение удалось так небрежно? такой жеWHEREУсловия, тот же план выполнения, какSELECT ... FOR UPDATEиUPDATEБлокировка операторов не одно и то же?

причина

Ха-ха, да, это действительно разное. На самом деле MySQL поддерживает 3 типа чтения:

Обычное чтение (также называемое последовательным чтением, английское название: Consistent Read).

Это относится к обычному оператору SELECT без добавления в концеFOR UPDATEилиLOCK IN SHARE MODEВЫБЕРИТЕ заявление. Обычные чтения выполняются путем генерацииReadViewНепосредственное использование механизма MVCC для чтения не блокирует запись.

Советы: 对于SERIALIZABLE隔离级别来说，如果autocommit系统变量被设置为OFF，那普通读的语句会转变为锁定读，和在普通的SELECT语句后边加LOCK IN SHARE MODE达成的效果一样。
Блокировка чтения (английское название: Locking Read).

Это связано с тем, что перед тем, как транзакция прочитает запись, она должна получить блокировку, соответствующую записи. Конечно, тип полученной блокировки зависит от уровня изоляции текущей транзакции, плана выполнения оператора, условий запроса и других факторов.Подробнее см.:
Semi-Consistent Read (английское название: Semi-Consistent Read).

Это чтение, которое находится между обычным чтением и заблокированным чтением. это только вREAD COMMITTEDИспользуйте на уровне изоляции (или когда системная переменная innodb_locks_unsafe_for_binlog включена)UPDATEиспользуется заявление. Конкретный смысл, когдаUPDATEКогда инструкция читает записи, которые были заблокированы другими транзакциями,InnoDBпрочитает последнюю зафиксированную версию записи, а затем определит, совпадает ли эта версия сUPDATEв предложенииWHEREЕсли условия совпадают, если не совпадают, то запись не будет заблокирована, то есть переход к следующей записи; если они совпадают, запись будет прочитана снова и заблокирована. Это делается только для того, чтобыUPDATEОператоры должны как можно меньше блокироваться другими операторами.

Советы: Полусогласованное чтение применимо только в случае блокировки записей кластеризованного индекса, но не в случае блокировки записей вторичного индекса.

Очевидно, в примере, о котором мы говорили выше, это связано с транзакциейT2воплощать в жизньUPDATEЗаявление использует полуобывающееся чтение, суждениеnumberзначение столбца8и15Последняя версия фиксации этих двух записейcountryНи одно из значений столбца неUPDATEв предложенииWHEREусловный'吴', поэтому просто пропустите их, не блокируя.

Эта точка знаний легко игнорироваться. Не забудьте рассмотреть это, когда вы проанализируете его в процессе работы.Semi-Consistent ReadО, это нелегко кодировать слова, это полезно, чтобы помочь вперед, о, о ~

буклет

Если вы хотите увидеть более продвинутые знания MySQL, вы можете проверить это в буклете:Ссылка на статью «Как работает MySQL: понимание MySQL у истоков». Содержание буклета в основном с точки зрения Xiaobai, с использованием относительно популярного языка для объяснения некоторых основных концепций расширенного MySQL, таких как записи, индексы, страницы, табличные пространства, оптимизация запросов, транзакции и блокировки и т. д. количество слов составляет около более 400 000 слов, с сотнями оригинальных иллюстраций. Основная цель состоит в том, чтобы облегчить обычным программистам изучение MySQL для продвинутых пользователей и сделать кривую обучения более плавной.

Не по теме

Писать довольно устал писать, иногда вы думаете, что это очень гладко, что на самом деле является результатом бесчисленных изменений позади. Если вы чувствуете себя хорошо, пожалуйста, помогите переслать его, большое спасибо ~ Вот мой публичный номер «Мы все маленькие лягушки», внутри есть больше технических галантереи, и иногда вы будете брать короткие, приветствую внимание: