Глава 1. Введение

Мир может показаться исключительным, когда нет нужды в блокировках баз данных. Тем не менее стоимость этого будет столь значительна, что окажется что такую базу данных смогут применять лишь несколько вариантов использования, а для баз данных общего назначения, таких как MySQL невозможно избежать блокировок. Если у вас нет блокировок, вы не сможете обладать одновременностью. Представьте себе, что к вашей базе данных разрешено лишь одно подключение (вы можете согласиться, что это само по себе является блокировкой, тем самым такая система в любом случае не свободна от блокирований)- это не очень полезно.

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

Когда у вас имеется несколько подключений, одновременно выполняющих запросы, вам потребуется некий способ, которым эти соединения не наступают друг другу на ноги. Именно здесь в картину вступают блокировки. Вы можете представлять себе блокировки аналогично дорожным сигналам при движении по трассам (Рисунок 1-1), которые регулируют доступ к имеющимся ресурсам во избежание происшествий. При пересечении дорог необходимо гарантировать что две машины не пересекутся друг с другом и не столкнутся.

 

Рисунок 1-1

Блокировки базы данных схожи со светофорами

В базе данных двум запросам на доступ требуется обеспечить что их данные не конфликтуют. Так же как и на перекрёстке существуют различные уровни управления доступом - сигналы уступи дорогу, остановись, а также светофоры - также имеются и различные типы блокировок в базе данных.

Блокировки в MySQL бывают нескольких разновидностей, причём действующих на различных уровнях MySQL в диапазоне от блокировок уровня пользователя до блокировок записей. На самых верхних уровнях находятся блокировки уровня пользователя, которые способны защищать пути кода в его приложении целиком и любые объекты внутри самой базы данных. В середине имеются блокировки, которые работают с объектами самой базы данных. Они включают блокировки метаданных, которые защищают собственно метаданные имеющихся таблиц, а также в качестве блокировок таблиц, которые защищают все данные в этой таблице. Общими для блокировок уровня пользователя и уровня таблиц выступает то, что они реализуются на уровне SQL самой базы данных. Блокировки верхнего уровня обсуждаются в Главе 6.

На самом нижнем уровне находятся те блокировки, которые реализуются самими механизмами хранения. По своей природе эти блокировки зависят от того механизма хранения, который вы применяете. Поскольку InnoDB на сегодняшний день наиболее применяемый механизм в MySQL (и установлен по умолчанию), данная книга охватывает блокировки, специфичные для InnoDB. InnoDB включает в свой состав блокировки на своих записях, которые наиболее просты для понимания, а также более сложные понятия, такие как блокировки промежутков, блокировки следующего ключа, блокировки предикатов и блокировки намерения вставки. Кроме того, имеются взаимные исключения (мьютексы) и семафоры (они также случаются и на самом верхнем уровне SQL). Специфичные для InnoDB блокировки и взаимные исключения, семафоры обсуждаются в Главе 7.

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

Само понятие уровня изоляции транзакции также играет важную роль при работе с блокировками. Значение уровня изоляции влияет как на то какие блокировки предпринимаются и насколько долго они удерживаются.

Глава 3 и Глава 4 рассматривают как отслеживать транзакции, а Глава 11 и Главв 12 покрывают то как работают транзакции, их воздействие и собственно уровни изоляции транзакции.

На протяжении всей книги имеются примеры, которые способствуют иллюстрации обсуждаемой темы или настройки ситуации, которую вы можете расследовать. За исключением Глав17 и Глава 18 все необходимые операторы для воспроизведения всех перечисляемых тестов. В целом вам потребуется для этих примеров более одного подключения, а потому приглашения для всех запросов были настроены на то чтобы указывать какое подключение применять для какого из запросов когда это важно. Например, Connection 1> означает что этот запрос должен исполняться самым первым вашим подключением.

Все примеры из этой книги исполнялись в Оболочке MySQL. Для краткости значением приглашения в наших примерах это mysql> за исключением когда важно значение подключения или когда режим языка отличен от SQL. Эти примеры, тем не менее, также работают и со старым клиентом командной строки mysql.

Совет

Если вы не знакомы с Оболочкой MySQL, тогда это клиент командной строки MySQL второго поколения с поддержкой обоих SQL, Python и JavaScript. Он также поставляется с несколькими встроенными утилитами, содержащими инструменты для управления Кластером InnoDB MySQL и традиционных топологий репликации. Для знакомства с Оболочкой MySQL обратитесь к руководству пользователя или к книге Чарли Белла Introducing MySQL Shell (Apress).

К тому же, данная книга поставляется с модулем Python - concurrency_book.generate - который может быть импортирован в Оболочку MySQL и применяться для воспроизводства всех не самых простых примеров. Остаток данного раздела описывает как применять этот модуль Оболочки MySQL. Приводимое здесь содержимое представляет собой отрывок Дополнения B, которое содержит более пространное справочное руководство по этому модулю, включая то как реализовать ваши собственные примеры.

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

Наиболее важное требование для использования предоставляемого этой книгой модуля Оболочки MySQL состоит в том, что вы применяете MySQL Shell 8.0.20 более позднюю версию. Это строгое требование, ибо данный модуль изначально для создания необходимых под приводимые варианты использования пользуется методом shell.open_session(). Этот метод был введён лишь в выпуске 8.0.20. Преимущество shell.open_session() над mysql.get_classic_session() и mysqlx.get_session() состоит в том, что open_session() работает прозрачно с обоими протоколами, как классическим протоколом MySQL, так и с новым протоколом X.

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

Также требуется чтобы некое подключение от Оболочки MySQL к Серверу MySQL уже имелось, так как модуль применяет значение URI этого подключения при создании всех дополнительных подключений, требуемых для рассматриваемого примера.

Все примеры были проверены на MySQL Server 8.0.21; тем не менее, большинство примеров будут работать и с более старыми выпусками, а некоторые даже с MySQL 5.7. Тем не менее, рекомендуется применять MySQL Server 8.0.21 или более позднюю версию.

Для применения этого модуля вам необходимо выгрузить все файлы в каталог concurrency_book из репозитория GitHub этой книги (ссылку можно обнаружить на домашней странице этой книги). Самым простым является клонирование этого репозитория или выгрузка соответствующего файла ZIP со всеми файлами при помощи показанного на Рисунке 1-2 меню.

 

Рисунок 1-2

Меню GitHub для клонирования или выгрузки необходимого репозитория

Кликните по иконке планшета для копирования указанного URL, применяемого под клонирование соответствующего репозитория при помощи программного обеспечения Git в вашей системе или воспользуйтесь ссылкой Download ZIP для выгрузки файла ZIP из соответствующего репозитория. Вы вольны выбрать любой путь в качестве его местоположения для своих файлов,поскольку сама структура вниз такого каталога concurrency_book остаётся неизменной. Для данного обсуждения предполагается что вы клонировали или распаковали все файлы этого репозитория в C:\Book\mysql\concurrency, а потому файл generate.py находится в каталоге C:\Book\mysql-concurrency\concurrency_book\.

Чтобы иметь возможность импорта этого модуля в Оболочку MySQL, откройте или создайте файл mysqlshrc.py. Оболочка MySQL выполняет поиск этого файла в четырёх местах. В путях Microsoft Windows поиск выполняется в следующем порядке:

В Linux и Unix это

Всегда выполняется поиск во всех четырёх каталогах, а если этот файл обнаружен во множестве мест, будет исполнен каждый из файлов. Это означает, что предшествует самый последний файл если эти файлы оказывают воздействие на те же самые переменные. Когда вы вносите изменения предназначенные для вас персонально, наилучшим местом для внесения таких изменений выступает четвёртое местоположение. Значение пути на шаге 5 может быть перекрыто переменной среды MYSQLSH_USER_CONFIG_HOME.

Вам требуется обеспечить добавление самого файла mysqlshrc.py в каталог с самим модулем в путь поиска Python и в качестве необязательного действия вы можете добавить оператор import чтобы сделать этот модуль доступным при запуске Оболочки MyShell. Неким примером такого файла mysqlshrc.py является

import sys
sys.path.append('C:\\Book\\mysql-concurrency')
import concurrency_book.generate
 	   

Двойные обратные слеши предназначены для Windows; в Linux и Unix вам нет необходимости экранировать слеши, которые отделяют элементы значения пути. Когда вы не включаете оператор import в файл mysqlshrc.py, вам потребуется исполнить его в Оболочке MySQL прежде чем вы воспользуетесь этим модулем.

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

mysql-py> concurrency_book.generate.help()
		

Также существует метод show(), который перечисляет те рабочие нагрузки, которые способен исполнять метод run(), а также схемы, которые способен загружать метод load():

mysql-py> concurrency_book.generate.show()
		

Соответствующие рабочие нагрузки поименованы после перечисленного в этой книге кода, например, рабочая нагрузка с названием "Листинге 6-1" реализованную своим примером в Листинге 6-1.

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

Описываемый модуль concurrency_book.generate поддерживает загрузку примеров баз данных employees, sakila и world в ваш экземпляр MySQL. Для базы данных employees вы по своему желанию можете выбрать некую версию с разделами. База данных world наиболее важна для этой книги вслед за базой данных sakila. База данных employees применяется только для варианта применения в Главе 18. Каждая из этих трёх схем более подробно описывается позднее в этой главе.

	Замечание
	Если соответствующая схема уже существует, она будет сброшена как часть задания её загрузки. На практике это означает, что load() сбрасывает эту схему.

Вы загрузите схему при помощи метода load(), которая опционально возьмёт название той схемы, которую вы загружаете. Если вы не предоставляете названия схемы, тогда вы получите приглашение на её ввод. Листинг 1-1 показывает пример загрузки схемы world.

mysql-py> concurrency_book.generate.load()
Available Schema load jobs:
===========================
 # Name                   Description
---------------------------------------------------------------------------
 1 employees              The employee database
 2 employees partitioned  The employee database with partitions
 3 sakila                 The sakila database
 4 world                  The world database
Choose Schema load job (# or name - empty to exit): 4
2020-07-20 21:27:15.221340  0 [INFO] Downloading https://downloads.mysql.com/docs/world.sql.zip to C:\Users\myuser\AppData\Roaming\mysql_concurrency_book\sample_data\world.sql.zip
2020-07-20 21:27:18.159554  0 [INFO] Processing statements in world.sql
2020-07-20 21:27:27.045219  0 [INFO] Load of the world schema completed
Available Schema load jobs:
===========================
 # Name                   Description
---------------------------------------------------------------------------
 1 employees              The employee database
 2 employees partitioned  The employee database with partitions
 3 sakila                 The sakila database
 4 world                  The world database
Choose Schema load job (# or name - empty to exit):
 	   

Метод load() выгружает необходимый файл с определением соответствующей схемы, если только он уже не имеется. Такой выгружаемый файл сохраняется в %APPDATA\mysql_concurrency_book\sample_data\ для Microsoft Windows и в ${HOME}/.mysql_concurrency_book/sample_data/ для прочих платформ. Если вы желаете выгрузить этот файл повторно, удалите его из этого каталога.

Совет

Поскольку в Python Оболочки MySQL доступны лишь процедуры относительно нижнего сетевого уровня, когда у вас имеется медленное или нестабильное соединение, выгрузка базы данных employees может завершиться неудачей. Ещё один вариант - отличающийся от установки этой схемы вручную - состоит в выгрузке https://github.com/datacharmer/test_db/archive/master.zip и его сохранении в каталоге sample_data. После этого метод load() подхватит его и не будет предпринимать попыток его повторной выгрузки.

Если вы желаете выгрузить единственную схему, вы можете определить её название в качестве аргумента для load(). В частности, это может быть полезно при первоначальной загрузки некой схемы в виде команды, представляемой непосредственно в вашей командной строке при вызове оболочки MySQL, например:

shell> mysqlsh --user=myuser --py -e "concurrency_book.generate.load('world')"
		

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

	Замечание
	Если в процессе загрузки происходит сбой, например по причине того что этот файл не является файлом ZIP, это означает что такой файл повреждён или неполный. В таком случае удалите этот файл и повторите выгрузку или попробуйте выгрузить этот файл вручную при помощи своего браузера.

Вы исполняете рабочую нагрузку при помощи метода run(). Если вы определите название известной рабочей нагрузки, тогда такая рабочая нагрузка исполнится немедленно. В противном случае будут перечислены все доступные рабочие нагрузки и вы получите приглашение на ввод необходимой рабочей нагрузки. В этом случае вы можете определить такую рабочую нагрузку по её номеру (например, 15 для Листинга 6-1) или по её названию. При использовании названия значение числа пробелов между Listing и значение номера не принимается во внимание если имеется по крайней мере один пробел. Когда вы выбираете соответствующую нагрузку при помощи приглашения на ввод, после того ка завершится предыдущая, вы можете выбрать другую рабочую нагрузку.

После завершения выбранной рабочей нагрузки, для некоторых из таких рабочих потоков вам будет представлен перечень предложений для расследований, которые вы можете осуществлять. Например, это может быть запрос на блокировки, удерживаемые теми подключениями, которые применяются в этом примере. Эти расследования служат в качестве стимула и вам предлагается изучить эту рабочую нагрузку для применения своих собственных запросов. Некоторые из таких расследований также используются при обсуждении выбранного примера. Листинг 1-2 отображает некий пример выполнения рабочей нагрузки при помощи приглашения на ввод.

mysql-py> concurrency_book.generate.run()
Available workloads:
====================
 # Name                   Description
---------------------------------------------------------------------------
 1 Listing  2-1           Example use of the metadata_locks table
 2 Listing  2-2           Example of using the table_handles table
 3 Listing  2-3           Using the data_locks table
...
14 Listing  5-2           Example of obtaining exclusive locks
15 Listing  6-1           A deadlock for user-level locks
...
Choose workload (# or name - empty to exit): 15
Password for connections: ********
2020-07-20 20:50:41.666488  0 [INFO] Starting the workload Listing 6-1
****************************************************
*                                                  *
*   Listing 6-1. A deadlock for user-level locks   *
*                                                  *
****************************************************
-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1              105         249          6
--          2              106         250          6
-- Connection 1
Connection 1> SELECT GET_LOCK('my_lock_1', -1);
+---------------------------+
| GET_LOCK('my_lock_1', -1) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.0003 sec)
-- Connection 2
Connection 2> SELECT GET_LOCK('my_lock_2', -1);
+---------------------------+
| GET_LOCK('my_lock_2', -1) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.0003 sec)
Connection 2> SELECT GET_LOCK('my_lock_1', -1);
-- Connection 1
Connection 1> SELECT GET_LOCK('my_lock_2', -1);
ERROR: 3058: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
Available investigations:
=========================
 # Query
--------------------------------------------------
 1 SELECT *
     FROM performance_schema.metadata_locks
    WHERE object_type = 'USER LEVEL LOCK'
          AND owner_thread_id IN (249, 250)
 2 SELECT thread_id, event_id, sql_text,
          mysql_errno, returned_sqlstate, message_text,
          errors, warnings
     FROM performance_schema.events_statements_history
    WHERE thread_id = 249 AND event_id > 6
    ORDER BY event_id
...
Choose investigation (# - empty to exit): 2
-- Investigation #2
-- Connection 3
Connection 3> SELECT thread_id, event_id, sql_text,
                     mysql_errno, returned_sqlstate, message_text,
                     errors, warnings
                FROM performance_schema.events_statements_history
               WHERE thread_id = 249 AND event_id > 6
               ORDER BY event_id\G
*************************** 1. row ***************************
        thread_id: 249
         event_id: 7
         sql_text: SELECT GET_LOCK('my_lock_1', -1)
      mysql_errno: 0
returned_sqlstate: NULL
     message_text: NULL
           errors: 0
         warnings: 0
*************************** 2. row ***************************
        thread_id: 249
         event_id: 8
         sql_text: SELECT GET_LOCK('my_lock_2', -1)
      mysql_errno: 3058
returned_sqlstate: HY000
     message_text: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
           errors: 1
         warnings: 0
*************************** 3. row ***************************
        thread_id: 249
         event_id: 9
         sql_text: SHOW WARNINGS
      mysql_errno: 0
returned_sqlstate: NULL
     message_text: NULL
           errors: 0
         warnings: 0
3 rows in set (0.0009 sec)
Available investigations:
=========================
 # Query
--------------------------------------------------
...
Choose investigation (# - empty to exit):
2020-07-20 20:50:46.749971  0 [INFO] Completing the workload Listing 6-1
-- Connection 1
Connection 1> SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.0004 sec)
-- Connection 2
Connection 2> SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.0002 sec)
2020-07-20 20:50:46.749971  0 [INFO] Disconnecting for the workload Listing 6-1
2020-07-20 20:50:46.749971  0 [INFO] Completed the workload Listing 6-1
Available workloads:
====================
 # Name                   Description
---------------------------------------------------------------------------
 1 Listing  2-1           Example use of the metadata_locks table
 2 Listing  2-2           Example of using the table_handles table
 3 Listing  2-3           Using the data_locks table
...
Choose workload (# or name - empty to exit):
mysql-py>
 	   

-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1              105         249          6
--          2              106         250          6
 	   

-- Investigation #2
 	   

shell> mysqlsh --user=myuser --py -e "concurrency_book.generate.run('Listing 6-1')"