Глава 6. Типы блокировок верхнего уровня

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

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

Имеется возможность получения множества блокировок при инициации GET_LOCK() множеством раз. Когда вы осуществляете это, убедитесь что вы блокировки получались всеми пользователями в том же самом порядке, ибо в противном случае может произойти взаимная блокировка. В случае возникновения взаимной блокировки будет возвращена ошибка ER_USER_LOCK_DEADLOCK (с кодом ошибки 3058). Некий образец этого показан в Листинге 6-1.

-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1              322         617          6
--          2              323         618          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.
 	   

Когда Соединение 2 пытается получить my_lock_1, этот оператор будет блокирован до тех пор пока Соединение 1 пытается овладеть блокировкой my_lock_2, включающей взаимное блокирование. Когда вы получаете множество блокировок, вы должны быть готовы к обработке взаимных блокировок. Обратите внимание, что для блокировок уровня пользователя некая взаимная блокировка не включает обратный откат её р=транзакции.

Такие предоставленные и отложенные блокировки уровня пользователя можно обнаружить в таблице performance_schema.metadata_locks в соответствующем столбце OBJECT_TYPE установленном в USER LEVEL LOCK, как это показано в Листинге 6-2. Эти перечисленные блокировки предполагают вашу систему оставленной в тот момент, когда включилось взаимное блокирование в Листинге 6-1. Обратите внимание, что некоторые значения, такие как OBJECT_INSTANCE_BEGIN, у вас будут отличаться и вам придётся изменить значения идентификаторов для owner_thread_id в выражении WHERE, чтобы соответствовать вашим из Листинга 6-1.

-- Investigation #1
-- Connection 3
Connection 3> SELECT *
                FROM performance_schema.metadata_locks
               WHERE object_type = 'USER LEVEL LOCK'
                     AND owner_thread_id IN (617, 618)\G
*************************** 1. row ***************************
          OBJECT_TYPE: USER LEVEL LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: my_lock_1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2124404669104
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: GRANTE
               SOURCE: item_func.cc:5067
      OWNER_THREAD_ID: 617
       OWNER_EVENT_ID: 8
*************************** 2. row ***************************
          OBJECT_TYPE: USER LEVEL LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: my_lock_2
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2124463901664
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: GRANTED
               SOURCE: item_func.cc:5067
      OWNER_THREAD_ID: 618
       OWNER_EVENT_ID: 8
*************************** 3. row ***************************
          OBJECT_TYPE: USER LEVEL LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: my_lock_1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2124463901088
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: PENDING
               SOURCE: item_func.cc:5067
      OWNER_THREAD_ID: 618
       OWNER_EVENT_ID: 9
3 rows in set (0.0015 sec)
 	   

Значением OBJECT_TYPE для блокировок уровня пользователя выступает USER LEVEL LOCK, а значением продолжительности этой блокировки является EXPLICIT, поскольку именно самому пользователю или приложению придётся высвободить эту блокировку снова. В строке 1 соединению с идентификатором 617 потока схемы Производительности была предоставлена блокировка my_lock_1, а в строке 3 идентификатор 618 потока ожидает (в отложенном состоянии) для предоставления блокировке себе. Идентификатор 618 потока также обладает предоставленной ему блокировкой , которая содержится в строке 2. Когда вы выполняете своё расследование, не забывайте высвобождать соответствующие блокировки, например, выполняйте вначале в Соединении SELECT RELEASE_ALL_LOCKS(), а затем и в Соединении 2 (это происходит автоматически при выполнении данного рабочего потока при помощи модуля concurrency_book Оболочки MySQL).

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

Блокировки сброса знакомы всем, кто был вовлечён в выполнение резервного копирования. Они происходят когда вы применяете оператор FLUSH TABLES и длятся на протяжении всего этого оператора, если только вы не добавили WITH READ LOCK и в такой ситуации удерживается совместная блокировка (на считывание) до тех пор, пока такая блокировка не быдет высвобождена в явном виде. Неявное блокирование сброса также включается в самом конце соответствующего оператора ANALYZE TABLE. Такая блокировка сброса является блокировкой табличного уровня. Блокировка на считывание при помощи FLUSH TABLES WITH READ LOCK обсуждается позднее в блокировках таблиц в явном виде.

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

Блокировки сброса выступают предметом настроек lock_wait_timeout. Когда для овладения такой блокировкой требуется более lock_wait_timeout секунд, MySQL прервёт эту блокировку. То же самое применимо и для ситуации с уничтожением (kill) самого оператора FLUSH TABLES. Тем не менее, по причине внутреннего устройства MySQL блокировки нижнего уровня с названием варианта блокировки TDC (table definition cache) не могут всегда высвобождаться до окончания запроса с длительным временем исполнения (см. баг 44884). Это означает, что единственный способ предоставления гарантии разрешения проблемы с блокированием состит в уничтожении запроса с длительным временем выполнения, однако имейте в виду, что если такой запрос изменил большое число строк, может потребоваться значительное время на его обратный откат.

Когда имеется некое заблокированное содержимое в пределах блокировки сброса, и оператор FLUSH TABLES, и сам запрос впоследствии получат значение состояния установленным в "Waiting for table flush". Листинг 6-3 Отображает некий пример этого, с вовлечёнными в него тремя запросами. Когда вы воспроизведёте этот сценарий в своём случае (в противовес применению модуля concurrency_book Оболочки MySQL), тогда вы можете изменить значение параметра SLEEP() в Соединении 1 чтобы получить для себя больше времени на исполнение этого примера.

-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1              375         691          6
--          2              376         692          6
--          3              377         693          6
--          4              378         694          6

-- Connection 1
Connection 1> SELECT city.*, SLEEP(3) FROM world.city WHERE ID = 130;

-- Connection 2
Connection 2> FLUSH TABLES world.city;

-- Connection 3
Connection 3> SELECT * FROM world.city WHERE ID = 201;

-- Connection 4
-- Query sys.session for the three threads involved in the lock situation
Connection 4> SELECT thd_id, conn_id, state,
                     current_statement
                FROM sys.session
               WHERE current_statement IS NOT NULL
                     AND thd_id IN (691, 692, 693)
               ORDER BY thd_id\G
*************************** 1. row ***************************
           thd_id: 691
          conn_id: 375
            state: User sleep
current_statement: SELECT city.*, SLEEP(3) FROM world.city WHERE ID = 130
*************************** 2. row ***************************
           thd_id: 692
          conn_id: 376
            state: Waiting for table flush
current_statement: FLUSH TABLES world.city
*************************** 3. row ***************************
           thd_id: 693
          conn_id: 377
            state: Waiting for table flush
current_statement: SELECT * FROM world.city WHERE ID = 201
3 rows in set (0.0586 sec)
 	   

Этот пример пользуется представлением sys.session view; аналогичные результаты можно получить при помощи performance_schema.threads и SHOW PROCESSLIST. Для снижения вывода с тем, чтобы он содержал только те запросы, которые относятся к обсуждаемому нами блокированию сброса, значение выражения WHERE установлено на включение лишь тех идентификаторов потоков, которые пребывают в самых первых трёх соединениях.

Подключение с conn_id = 375 исполняет медленный запрос, который пользуется таблицей world.city (для гарантии того, что он потребует достаточно времени для выполнения операторов прочих соединений был применён SLEEP(3)). Тем временем conn_id = 376 исполняет оператор FLUSH TABLES для таблицы world.city. По той причине, что самый первый запрос всё ещё обладает открытой таблицей (она высвобождается только по завершению данного запроса), соответствующий оператор FLUSH TABLES завершается ожиданием блокировки сброса своей таблицы. Наконец, conn_id = 377 пытается запросить эту же таблицу и тем самым должен ожидать соответствующий оператор FLUSH TABLES.

Другой блокировкой таблиц без данных выступает блокировка метаданных.

Блокировки метаданных являются одним из самых новых типов блокировок в MySQL. Они были введены в MySQL 5.5 и их цель состоит в защите самой схемы, а потому она не принимает изменения пока запросы и транзакции полагаются на неизменность такой схемы. Блокировки метаданных работают на уровне самой таблицы, тем не менее их следует рассматривать как независимый тип блокировок в блокировках таблиц, ибо они не защищают собственно данные в своих таблицах.

Оператор SELECT и запросы DML получают совместные блокировки метаданных, в то время как операторы DDL получают исключительные блокировки. Некое соединение получает блокировку метаданных таблицы при первом использовании такой таблицы и удерживает эту блокировку вплоть до самого концы своей транзакции. На протяжении удержания блокировки метаданных никаким прочим подключениям не допускается изменять имеющееся в данной таблице определение её схемы. Тем не менее, прочие соединения способны выполнять операторы SELECT и DML без ограничений. Обычно самой большой засадой с блокированием металданных бывают длительные транзакции, которые могут простаивать, не позволяя операторам DDL приступать к своей работе.

Если вы столкнулись с неким конфликтом, относящимся к блокировке метаданных, вы обнаружите в своём списке процессов значение состояния запроса установленным в "Waiting for table metadata lock". Некий образец этого содержит запросы в установке, отображаемой в Листинг 6-4.

-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1              428         768          6
--          2              429         769          6

-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

Connection 1> SELECT * FROM world.city WHERE ID = 130\G
*************************** 1. row ***************************
         ID: 130
       Name: Sydney
CountryCode: AUS
   District: New South Wales
 Population: 3276207
1 row in set (0.0006 sec)

-- Connection 2
Connection 2> OPTIMIZE TABLE world.city;
 	   

Соединение 2 заблокировано и пока вы пребываете в этой ситуации, вы можете выполнить запрос к sys.session или нечто подобное, как то показано в Листинге 6-5.

-- Investigation #1
-- Connection 3
Connection 3> SELECT thd_id, conn_id, state,
                     current_statement, statement_latency,
                     last_statement, trx_state
                FROM sys.session
               WHERE conn_id IN (428, 429)
               ORDER BY conn_id\G
*************************** 1. row ***************************
           thd_id: 768
          conn_id: 428
            state: NULL
current_statement: SELECT * FROM world.city WHERE ID = 130
statement_latency: NULL
   last_statement: SELECT * FROM world.city WHERE ID = 130
        trx_state: ACTIVE
*************************** 2. row ***************************
           thd_id: 769
          conn_id: 429
            state: Waiting for table metadata lock
current_statement: OPTIMIZE TABLE world.city
statement_latency: 26.62 s
   last_statement: NULL
        trx_state: COMMITTED
2 rows in set (0.0607 sec)
 	   

В этом примере соединение с conn_id = 428 обладает исполняемой транзакцией, а в его предыдущем операторе был выполнен запрос к таблице world.city (текущий оператор в этом случае тот же самый, ибо он не очистоится пока не будет выполнен следующий оператор). В то время как данная транзакция всё ещё активна, в неком операторе OPTIMIZE TABLE выполняется conn_id = 429, которое теперь ожидает необходимую блокировку метаданных. (Да, оператор OPTIMIZE TABLE не изменяет собственно определение схемы, но в качестве оператора DDL он всё ещё подвержен влиянию установленной блокировки метаданных.) Поскольку MySQL не обладает операторами DDL с транзакциями, значение состояния транзакции для conn_id = 429 будет отращаться как фиксированное.

Удобно когда имеется текущим или последним тот оператор, который вызвал данную блокировку метаданных. В наиболее распространённых случаях вы можете применять performance_schema.metadata_locks со столбцом OBJECT_TYPE установленным в значение TABLE для поиска предоставленных и отложенных блокировок метаданных. Листинг 6-6 отражает некий образец предоставленных и отложенных блокировок метаданных при помощи той же самой настройки, что и в нашем предыдущем примере. Глава 14 вдаётся в дополнительные подробности относительно расследования блокировок метаданных.

-- Investigation #2
Connection 3> SELECT object_type, object_schema, object_name,
                     lock_type, lock_duration, lock_status,
                     owner_thread_id
                FROM performance_schema.metadata_locks
               WHERE owner_thread_id IN (768, 769)
                     AND object_type = 'TABLE'\G
*************************** 1. row ***************************
    object_type: TABLE
  object_schema: world
    object_name: city
      lock_type: SHARED_READ
  lock_duration: TRANSACTION
    lock_status: GRANTED
owner_thread_id: 768
*************************** 2. row ***************************
    object_type: TABLE
  object_schema: world
    object_name: city
      lock_type: SHARED_NO_READ_WRITE
  lock_duration: TRANSACTION
    lock_status: PENDING
owner_thread_id: 769
2 rows in set (0.0010 sec)
 	   

В этом примере поток с идентификатором 768 (то же самое что и conn_id = 428 из вывода sys.session) владеет совместной блокировкой на чтение на таблицу world.city по причине проходящей транзакции, а поток с идентификатором 769 ожидает блокировку, поскольку он пытается исполнить некий оператор DDL с этой таблицей.

Когда вы это сделаете, убедитесь что вы откатили или зафиксировали данную транзакцию в Соединении 1, а потому OPTIMIZE TABLE может быть осуществлён:

-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0004 sec)
 	   

Неким особым случаем блокировок метаданных являются блокировки, берущиеся в явном виде при помщи оператора LOCK TABLES.

Явные блокировки таблиц берутся при помощи операторов LOCK TABLES и FLUSH TABLES WITH READ LOCK. Посредством оператора LOCK TABLES возможно получать совместные или исключительные блокировки; FLUSH TABLES WITH READ LOCK всегда получает совместную блокировку. Соответствующие таблицы блокированы до тех пор, пока они на высвобождаются в явном виде при помощи соответствующего оператора UNLOCK TABLES. Когда FLUSH TABLES WITH READ LOCK исполняется без перечисления каких бы то ни было таблиц, принимается глобальное блокирование на чтение (то есть воздействующее на все таблицы). В то время как эти блокировки также защищают сами данные, он рассматриваются в MySQL как блокировки метаданных.

Блокировки таблиц в явном виде, отличающиеся от FLUSH TABLES WITH READ LOCK в соединениях с резервным копированием, не очень часто используются с InnoDB, ибо функциональные возможности современных блокировок InnoDB в большинстве случаев более подходят для обработки блокировок вами самостоятельно. Тем не менее, когда вам на самом деле требуется блокировать соответствующую таблицу целиком, могут быть полезными блокировки в явном виде, поскольку они очень мало стоят для проверки со стороны MySQL.

Некий образец соединения, получающего блокировку на чтение в явном виде для таблиц world.country и world.countrylanguage и блокировку на запись для таблицы world.city отображён в Листинге 6-7.

-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1              432         772          6

-- Connection 1
Connection 1> LOCK TABLES world.country READ,
                          world.countrylanguage READ,
                          world.city WRITE;
Query OK, 0 rows affected (0.0029 sec)
 	   

Когда вы получаете блокировки в явном виде, вам разрешается использовать только те таблицы, которые вы заблокировали и в соответствии с имеющимися запрошенными блокировками. Это означает, что вы получите некую ошибку если вы возьмёте блокировку на считывание и попытаетесь выполнить запись в соответствующую таблицу (ER_TABLE_NOT_LOCKED_FOR_WRITE) или когда вы попытаететсь воспользоваться таблицей, для которой вы не получили блокировку (ER_TABLE_NOT_LOCKED), например (продолжение Листинга 6-7).

Connection 1> UPDATE world.country
                 SET Population = Population + 1
               WHERE Code = 'AUS';
ERROR: 1099: Table 'country' was locked with a READ lock and can't be updated

Connection 1> SELECT *
                FROM sakila.film
               WHERE film_id = 1;
ERROR: 1100: Table 'film' was not locked with LOCK TABLES
 	   

Поскольку блокировки в явном виде рассматриваются как блокировки метаданных, имеющиеся симптомы и сведения в таблице performance_schema.metadata_locks те же самые что и для блокировок метаданных в неявном виде и вы также выполняете разблокирование этих таблиц при помощи оператора UNLOCK TABLES:

Connection 1> UNLOCK TABLES;
Query OK, 0 rows affected (0.0006 sec)
 	   

Другая блокировка уровня таблицы, но обрабатываемая неявно, прямо именуется блокировкой таблицы.

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

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

Для получаемых транзакциями блокировок, прежде всего берутся намерения блокировок, а затем они по мере необходимости обновляются. Это отличает их от LOCK TABLES в явном виде, которые неизменны. Для получения некой совместной блокировки соответствующая транзакция сначала получает некое намерение совместного блокирования, а затем и соответствующее совместное блокирование. Аналогично, для некой исключительной блокировки, поначалу предпринимается намерение исключительного блокирования. Вот некоторые примеры намерений блокировок:

Блокировки уровня таблиц можно найти в таблице performance_schema.data_locks по столбцу LOCK_TYPE, установленному в TABLE. Листинг 6-8 отображает некий пример намерения совместной блокировки.

-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1              446         796          6
--          2              447         797          6
-- Connection 1

Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

Connection 1> SELECT *
                FROM world.city
               WHERE ID = 130
                 FOR SHARE\G
*************************** 1. row ***************************
         ID: 130
       Name: Sydney
CountryCode: AUS
   District: New South Wales
 Population: 3276207
1 row in set (0.0010 sec)

-- Connection 2
Connection 2> SELECT engine, thread_id, object_schema,
                     object_name, lock_type, lock_mode,
                     lock_status, lock_data
                FROM performance_schema.data_locks
               WHERE lock_type = 'TABLE'
                     AND thread_id = 796\G
*************************** 1. row ***************************
       engine: INNODB
    thread_id: 796
object_schema: world
  object_name: city
    lock_type: TABLE
    lock_mode: IS
  lock_status: GRANTED
    lock_data: NULL
1 row in set (0.0011 sec)

-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0004 sec)
 	   

Это показывает, что некое намерение совместной блокировки для таблицы world.city. Обратите внимание, что значение engine установлено в INNODB, а lock_data это NULL.