Глава 4. Транзакции в схеме Производительности

Основными таблицами для расследования транзакций по схеме Производительности выступают таблицы событий транзакций. Имеются три таблицы для записи текущих или последний транзакций: events_transactions_current, events_transactions_history и events_transactions_history_long. Они обладают колонками, которые суммируются в Таблице 4-1.

Когда вы работаете с транзакциями XA, таблицы событий транзакций великолепны когда вам требуется восстановить некую транзакцию, поскольку идентификатор формата, значения gtrid и bqual напрямую доступны из таких таблиц, в отличии от соответствующего оператора XA RECOVER, для которого вам приходится выполнять синтаксический анализ получаемого вывода. В противном случае получаемые сведения совершенно аналогичны тем, которые доступны в соответствующем представлении information_schema.INNODB_TRX.

В качестве некого образца применения таблицы events_transactions_current вы можете запустить две транзакции, как это показано на Листинге 4-1.

-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1               53         163          6
--          2               54         164          6

-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city SET Population = Population + MOD(ID, 2) + SLEEP(0.01);

-- Connection 2
Connection 2> SET SESSION autocommit = ON;
Query OK, 0 rows affected (0.0004 sec)

Connection 2> SELECT COUNT(*) FROM world.city WHERE ID > SLEEP(0.01);
 	   

Первая транзакция это обычная транзакция, которая обновляет значение населения некоторых городов, а вторая транзакция это транзакция XA. Листинг 4-2.

-- Investigation #1
-- Connection 3
Connection 3> SELECT *
                FROM performance_schema.events_transactions_current
               WHERE state = 'ACTIVE'\G
*************************** 1. row ***************************
                      THREAD_ID: 140
                       EVENT_ID: 8
                   END_EVENT_ID: NULL
                     EVENT_NAME: transaction
                          STATE: ACTIVE
                         TRX_ID: NULL
                           GTID: AUTOMATIC
                  XID_FORMAT_ID: NULL
                      XID_GTRID: NULL
                      XID_BQUAL: NULL
                       XA_STATE: NULL
                         SOURCE: transaction.cc:209
                    TIMER_START: 72081362554600000
                      TIMER_END: 72161455792800000
                     TIMER_WAIT: 80093238200000
                    ACCESS_MODE: READ WRITE
                ISOLATION_LEVEL: REPEATABLE READ
                     AUTOCOMMIT: NO
           NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
    NUMBER_OF_RELEASE_SAVEPOINT: 0
          OBJECT_INSTANCE_BEGIN: NULL
               NESTING_EVENT_ID: 7
             NESTING_EVENT_TYPE: STATEMENT
*************************** 2. row ***************************
                      THREAD_ID: 141
                       EVENT_ID: 8
                   END_EVENT_ID: NULL
                     EVENT_NAME: transaction
                          STATE: ACTIVE
                         TRX_ID: NULL
                           GTID: AUTOMATIC
                  XID_FORMAT_ID: 1
                      XID_GTRID: abc
                      XID_BQUAL: def
                       XA_STATE: ACTIVE
                         SOURCE: transaction.cc:209
                    TIMER_START: 72081766957700000
                      TIMER_END: 72161455799300000
                     TIMER_WAIT: 79688841600000
                    ACCESS_MODE: READ WRITE
                ISOLATION_LEVEL: REPEATABLE READ
                     AUTOCOMMIT: NO
           NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
    NUMBER_OF_RELEASE_SAVEPOINT: 0
          OBJECT_INSTANCE_BEGIN: NULL
               NESTING_EVENT_ID: 7
             NESTING_EVENT_TYPE: STATEMENT
2 rows in set (0.0007 sec)
 	   

Транзакция в строке 1 это обычная транзакция, в то время как транзакция из строки 2 это транзакция XA. Обе транзакции были запущены оператором, который который можно обнаружить из значения типа встроенного типа события. Если вы желаете найти тот оператор, который включил данную транзакцию, вы можете воспользоваться тем чтобы выполнить запрос в таблице events_statements_history подобно

-- Investigation #2
Connection 3> SELECT sql_text
                FROM performance_schema.events_statements_history
               WHERE thread_id = 140
                     AND event_id = 7\G
*************************** 1. row ***************************
sql_text: start transaction
1 row in set (0.0434 sec)
 	   

Это показывает, что данная транзакция исполняется thread_id = 140, которая была запущена оператором START TRANSACTION. Поскольку таблица events_statements_history включает самые последние десять операторов для своего соединения, нет никакой гарантии что тот оператор, который запустил данную транзакцию всё ещё пребывает в данной исторической таблице. Когда вы ищите транзакцию единственного оператора или самый первый оператор (когда она всё ещё исполняется), в то время как отключён autocommit, вам потребуется вместо этого выполнить запрос к таблице events_statements_current.

Взаимоотношения между транзакциями и операторами также следуют иным способом.

Воспользовавшись идентификатором события транзакции и значением идентификатора потока вы можете запросить последние десять операторов, выполняемых для данной транзакции, воспользовавшись соответствующей историей событий операторов и текущими таблицами. В Листинге 4-3 показан образец для thread_id = 140 и EVENT_ID = 8 транзакции (из строки 1 Листинга 4-2), где запускаются оба эти оператора, включая саму транзакцию и последующие операторы.

-- Investigation #4
Connection 3> SET @thread_id = 140,
                  @event_id = 8,
                  @nesting_event_id = 7;
Query OK, 0 rows affected (0.0007 sec)

-- Investigation #6
Connection 3> SELECT event_id, sql_text,
                     FORMAT_PICO_TIME(timer_wait) AS latency,
                     IF(end_event_id IS NULL, 'YES', 'NO') AS current
                FROM ((SELECT event_id, end_event_id,
                              timer_wait,
                              sql_text, nesting_event_id,
                              nesting_event_type
                         FROM performance_schema.events_statements_current
                        WHERE thread_id = @thread_id
                      ) UNION (
                       SELECT event_id, end_event_id,
                              timer_wait,
                              sql_text, nesting_event_id,
                              nesting_event_type
                         FROM performance_schema.events_statements_history
                        WHERE thread_id = @thread_id
                      )
                     ) events
               WHERE (nesting_event_type = 'TRANSACTION'
                      AND nesting_event_id = @event_id)
                     OR event_id = @nesting_event_id
               ORDER BY event_id DESC\G
*************************** 1. row ***************************
event_id: 12
sql_text: UPDATE world.city SET Population = 2000000 WHERE ID = 133
 latency: 384.00 us
 current: NO
*************************** 2. row ***************************
event_id: 11
sql_text: UPDATE world.city SET Population = 2400000 WHERE ID = 132
 latency: 316.20 us
 current: NO
*************************** 3. row ***************************
event_id: 10
sql_text: UPDATE world.city SET Population = 4900000 WHERE ID = 131
 latency: 299.30 us
 current: NO
*************************** 4. row ***************************
event_id: 9
sql_text: UPDATE world.city SET Population = 5200000 WHERE ID = 130
 latency: 176.95 ms
 current: NO
*************************** 5. row ***************************
event_id: 7
sql_text: start transaction
 latency: 223.20 us
 current: NO
5 rows in set (0.0016 sec)
 	   

Этот подзапрос (порождаемая таблица) находит все события операторов для данного потока из таблиц events_statements_current и events_statements_history. Необходимо включать все текущие события, поскольку для данной транзакции может иметься всё ещё выполняющийся оператор. Эти операторы фильтруются либо как дочерние для данной транзакции, либо как соответствующее встроенное событие для определённой транзакции (event_id = 7). Они будут содержать все операторы, начинающиеся с того оператора, который запускает эти транзакции. Когда имеется выполняющийся оператор, будет иметься до 11 операторов, а в противном случае - до десяти.

Значение end_event_id применяется для определения того выполняется ли в данный момент этот оператор, а сами операторы упорядочиваются в обратном порядке при помощи значения event_id, а потому самый последний оператор находится в строке 1, а самый старый (оператор START TRANSACTION) в строке 5.

Такой тип запроса не только полезен для расследования транзакций, которые всё ещё исполняют запросы. Будет также очень полезно когда вы сталкиваетесь с некой простаивающей транзакцией и вы желаете узнать что эта транзакция сделала перед тем как она оказалась брошенной. Другой родственный способ поиска активных транзакций состоит в применении представления sys.session, которое пользуется таблицей events_transactions_current для включения сведений относительно значений состояний транзакций для каждого соединения. Листинг 4-4 отображает пример запроса активных транзакций за исключением самой строки для того соединения, которое выполняет этот запрос.

-- Investigation #7
Connection 3> SELECT *
                FROM sys.session
               WHERE trx_state = 'ACTIVE'
                     AND conn_id <> CONNECTION_ID()\G
*************************** 1. row ***************************
                thd_id: 140
               conn_id: 57
                  user: mysqlx/worker
                    db: NULL
               command: Sleep
                 state: NULL
                  time: 449
     current_statement: UPDATE world.city SET Population = 2000000 WHERE ID = 133
     statement_latency: NULL
              progress: NULL
          lock_latency: 111.00 us
         rows_examined: 1
             rows_sent: 0
         rows_affected: 1
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: UPDATE world.city SET Population = 2000000 WHERE ID = 133
last_statement_latency: 384.00 us
        current_memory: 228.31 KiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 7.48 min
             trx_state: ACTIVE
        trx_autocommit: NO
                   pid: 30936
          program_name: mysqlsh
*************************** 2. row ***************************
                thd_id: 141
               conn_id: 58
                  user: mysqlx/worker
                    db: NULL
               command: Sleep
                 state: NULL
                  time: 449
     current_statement: UPDATE world.city SET Population = 900000 WHERE ID = 3805
     statement_latency: NULL
              progress: NULL
          lock_latency: 387.00 us
         rows_examined: 1
             rows_sent: 0
         rows_affected: 1
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: UPDATE world.city SET Population = 900000 WHERE ID = 3805
last_statement_latency: 49.39 ms
        current_memory: 70.14 KiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 7.48 min
             trx_state: ACTIVE
        trx_autocommit: NO
                   pid: 30936
          program_name: mysqlsh
2 rows in set (0.0422 sec)
 	   

Здесь показано что транзакция из самой первой строки была активной более минут и это составило 449 секунд (7.5 минут) с момента выполнения самого последнего запроса (ваше значение может отличаться). Значение last_statement может применяться для определения самого последнего запроса, выполненного данным соединением. Это пример прерванной транзакции, которая воспрепятствовала InnoDB очистки его журнала отмены. Наиболее распространённые причины прерывания транзакций это то, что администратор базы данных запускает транзакцию в интерактивном режиме и отвлекается, либо отключён autocommit и не распознаёт что была запущена некая транзакция.

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

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

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

А для транзакции XA:

Connection 2> XA END 'abc', 'def', 1;
Query OK, 0 rows affected (0.0002 sec)

Connection 2> XA ROLLBACK 'abc', 'def', 1;
Query OK, 0 rows affected (0.0308 sec)
 	   

Ещё один способ прмиенения таблиц схемы Производительности для анализа транзакций это использование сводных таблиц для получения агрегированных данных.

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

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

Каждая таблица содержит такие столбцы, которые группируют статистические данные транзакций по трём группам столбцов: итоговым, для транзакций чтения- записи и для транзакций исключительного считывания. Для каждой из этих трёх групп столбцов имеются общее число транзакций, а также общая, минимальная, средняя и максимальная задержки. Листинг 4-5 отображает некий пример для данных из таблицы events_transactions_summary_global_by_event_name.

-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1               60         143          6

-- Connection 1
Connection 1> SELECT *
         FROM performance_schema.events_transactions_summary_global_by_event_name\G
*************************** 1. row ***************************
          EVENT_NAME: transaction
          COUNT_STAR: 40485
      SUM_TIMER_WAIT: 90259064465300000
      MIN_TIMER_WAIT: 4800000
      AVG_TIMER_WAIT: 2229444500000
      MAX_TIMER_WAIT: 62122342944500000
    COUNT_READ_WRITE: 40483
SUM_TIMER_READ_WRITE: 90230783742700000
MIN_TIMER_READ_WRITE: 4800000
AVG_TIMER_READ_WRITE: 2228856100000
MAX_TIMER_READ_WRITE: 62122342944500000
     COUNT_READ_ONLY: 2
 SUM_TIMER_READ_ONLY: 28280722600000
 MIN_TIMER_READ_ONLY: 9561820600000
 AVG_TIMER_READ_ONLY: 14140361300000
 MAX_TIMER_READ_ONLY: 18718902000000
1 row in set (0.0007 sec)
 	   

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

START TRANSACTION READ ONLY;
 	   

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

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

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