19 Jun 2014

InnoDB: транзакции и блокировки

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

InnoDB следует принципам ACID (Atomicity, Consistency, Isolation, Durability). В рамках обсуждаемой темы нас интересуют два принципа: атомарность и изоляция, которые обеспечиваются в основном за счет транзакций и блокировок.

Транзакции

Для обеспечения атомарности любая последовательность операций выполняется в рамках своей транзакции, т.е. все изменения выполняемые транзакцией либо применяются - COMMIT, либо откатываются - ROLLBACK. По-умолчанию каждая новая сессия работает в режиме автоматического коммита. В этом режиме любое SQL-выражение либо будет автоматически закоммичено (при отсутсвии ошибок), либо будет откатано назад (при возникновении ошибки поведение коммита и роллбека зависит от типа ошибки, см. Error Handling). Чтобы выполнять последовательность операций без автокоммита, можно либо воспользоваться выражениями START TRANSACTION / BEGIN, либо выключить автокоммит с помощью выражения SET autocommit = 0. При выключенном автокоммите транзакция будет открыта до тех пор пока явно не выполнится COMMIT или ROLLBACK, которые завершат текущую тразакцию и начнут новую. При выполнении COMMIT или ROLLBACK все блокировки установленные внутри транзакции снимаются.

Изоляция транзакций в InnoDB реализована в соответсвии со стандартом SQL-92 в виде четырех уровней: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. По-умолчанию используется REPEATABLE READ, т.к. он обычно является более подходящим под требования, чем остальные. Реже используется READ COMMITED, например в ситуациях когда нужно повысить concurrency и можно немного забить на изоляцию. READ UNCOMMITED и SERIALIZABLE используются редко, т.к. это крайности: либо никакой изоляции, либо полная изоляция. Пользователь может изменить уровень изоляции для текущей сессии или для всех новых сессий с помощью выражения SET TRANSACTION. Перед тем как рассмотреть что из себя представляют эти уровни изоляции, стоит кратко пояснить что чтение бывает двух типов: консистентное неблокирующее (Consistent Nonlocking Reads - обычные SELECT) и блокирущее (Locking Reads - SELECT ... FOR UPDATE и SELECT ... LOCK IN SHARE MODE). О типах чтения и блокировках поговорим подробнее чуть позже. Итак, рассмотрим что же из себя представляют уровни изоляции транзакций:

  1. READ UNCOMMITTED. Самый низкий уровень изоляции. При этом уровне изоляции для неблокирующих чтений возможны ситуации когда будут использованы ранние версии данных. Такие чтения будут неконсистентны, их еще называют “грязным чтением”. В остальном этот уровень работает так же как READ COMMITED.
  2. READ COMMITED. При этом уровне изоляции каждое неблокирующее чтение работает со своим собственным свежим снимком данных, в том числе и в рамках одной транзакции. При выполнении блокирующих чтений, UPDATE и DELETE будут блокироваться только индексные записи (record lock) и не будут блокираться интервалы (gaps). Поэтому данный уровень изоляции позволяет вставлять новые строки в интервалы перед индексными записями. Такие строки называют фантомными. Уровень REPEATABLE READ решает данную проблему.
  3. REPEATABLE READ. При этом уровне изоляции каждое неблокирующие чтение (так же как и при READ COMMITED) работает со своим снимком данных, но снимок этот не обновляется в рамках одной транзакции, а используется тот что был получен при первом чтении. Т.е. каждое неблокирующее чтение в рамках одной транзакции является консистентным по отношению к остальным. Поведение блокирующего чтения, UPDATE и DELETE зависит от уникальности индекса и уникальности условия поиска. Когда и индекс и условие поиска уникально - блокируется только найденная индексная запись и не блокируется интервал перед ней. В остальных случаях InnoDB будет блокировать некоторый индексный интервал, используя так называемую интервальную блокировку gap lock или блокировку следующего ключа next-key lock. Блокировки интервалов и следующего ключа позволяют избежать фантомных чтений (Phantom Problem).
  4. SERIALIZABLE. Этот уровень аналогичен REPEATABLE READ, но InnoDB будет неявно преобразовывать все неблокирующие чтения (SELECT), в блокирующие (SELECT ... LOCK IN SHARE MODE) если автокоммит выключен. В случае если автокоммит включен, неблокирующее чтение будет единственным оператором в транзакции, т.е. понятно что транзакция будет read only, соответственно не имеет смысла блокировать записи такой транзакцией.

Блокировки

В InnoDB блокировки происходят на уровне строк, что позволяет различным транзакциям работать с одними и теми же таблицами одновременно. В случае когда транзакция пытается изменить строку, в которую уже вносит изменение другая транзакция, происходит блокировка, т.е. ожидание пока первая транзакция не закончит работу с изменяемой строкой. Любой вид блокировки строк - это по сути блокировка индексов. В случае если в таблице не объявленны уникальные индексы, InnoDB создаст скрытый ключ в виде номера строки (подробнее см. Clustered and Secondary Indexes) и будет вешать блокировки на них. Посмотреть какие индексы есть в таблице можно с помощью команды SHOW INDEX FROM <table name>.

Существует три типа блокировок:

  1. блокировка индексной записи (record lock) - такая блокировка происходит, если условие запроса ограничивает только одну запись уникального индекса (unique index); например, если в таблице t поле c1 является уникальным индексом и существует запись для которой с1 = 10, то при выполнении блокирующего чтения SELECT * FROM t WHERE c1 = 10 FOR UPDATE InnoDB установит блокировку на этот индекс и не допустит чтобы другая транзакция вставила, обновила или удалила строку с полем с1 = 10; если выполнить тот же запрос, но записи с полем с1 = 10 (а соответственно и записи индекса) не будет существовать, то это уже будет блокировка интервала
  2. блокировка интервала (gap lock) - происходит когда блокируется интервал между индексными записями, интервал до первой индексной записи или интервал после последней индексной записи; допустим что в таблице есть две строки для которых с1 = 10 и c1 = 20, т.е. индекс содержит значения 10 и 20; интервалами будут являтся следующие отрезки: (минус бесконечность, 10), (10, 20), (20, плюс беконечность); если мы выполним запрос на блокирующее чтение несуществующей пока записи SELECT * FROM t WHERE c1 = 15 FOR UPDATE, то будет блокирован интервал от 10 до 20, но не включительно, т.е. обновить граничные записи можно, можно даже их удалить, а вот вставка новой строки в этот интервал будет блокирована; еще один интересный пример: если выполнить предыдущий запрос на блокирующее чтение строки, но таблица t будет пуста, то заблокируется интервал, размером во все индексное пространство, т.е. вся таблица
  3. блокировка следующего ключа (next-ket lock) - комбинация блокировок индексных записей и блокировок интервалов; возьмем предыдущий пример, но выполним другой запрос: SELECT * FROM t WHERE c1 > 15; в данном случае помимо индекса со значением 20 заблокируются также интервалы (10, 20) и (20, плюс бесконечность); при этом строку с индексом 10 можно изменять, т.к. она не блокируется; в общем случае блокируемых индексных интервалов и индексных записей может быть гораздо больше, все зависит от условий блокирующего запроса

Таким образом видно, что InnoDB использует блокироки интервалов и блокировки следующего ключа так, чтобы полностью покрыть условие поиска блокирующего запроса и при этом заблокировать наименьшее возможное количество индексного пространства. Рассмотрим как блокировки интервалов и блокировки следующих ключей решают проблему фантомных чтений (Phantom Problem). Фантомное чтение - это когда в рамках одной транзакции возвращается разные результаты. Возьмем последний пример и допустим что для текущей транзакции установлен уровень изоляции READ UNCOMMITTED или READ COMMITED. Выполним блокирующее чтение SELECT * FROM t WHERE c1 BETWEEN 12 AND 18 и получим пустое множество. Теперь в другой транзакции (с любым уровнем изоляции) выполним запрос на вставку строки: INSERT INTO t (c1) VALUES (15). В первой транзакции повторим то же самое блокирующее чтение и в результате получим фантомную строку с полем c1 = 15, вставленную второй транзакцией. Если попробовать провернуть все то же самое но установив для первой транзакции уровень изоляции REPEATABLE READ или SERIALIZABLE, то при попытке вставки строки второй транзакцией она будет блокирована, т.к. первая транзакция заблокирует индексный интервал (10, 20). Таким образом блокирующее чтение будет возвращать один и тот же результат (пустое множество), независимо от действий других транзакций. Блокировка следующего ключа избавляет от фантомных чтений аналогично, с той лишь разницей что в этих блокировках могут участвовать несколько индексных записей и интервалов.

Режимы работы блокировок

InnoDB реализует блокировки строк в двух режимах: чтения (shared (S) lock) и записи (exclusive (X) lock).

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

Если транзакция T1 обладает блокировкой чтения на строку r, то запрос от второй транзакции T2 на блокировку той же строки r обрабатывается следующим образом:

  1. запрос на блокировку чтения может быть выдан сразу же, т.е. в результате обе транзакции T1 и T2 будут обладать блокировками чтения на строку r
  2. запрос на блокировку записи не может быть выдан до тех пор, пока транзакция T1 не освободит блокировку чтения на строку r

Если транзакция T1 обладает блокировкой записи на строку r, то запрос от второй транзакции T2 на блокировку любого режима будет приостановлен до тех пор пока транзакция T1 не освободит блокировку записи на строку r.

Для поддежки различной гранулярности блокировки данных, в InnoDB существуют так называемые целевые блокировки (intention locks), которые работают не на уровне строк, а на уровне таблиц. Целевые блокировки предназначены для того чтобы обозначить намерение транзакции получить в будующем блокировки чтения или записи на некоторые строки в данной таблице. Если транзакция собирается получить в таблице только блокировки чтения, то это целевая блокировка чтения (intention shared (IS) lock). Если транзакция намерена получить блокировки записи, то это целевая блокировка записи (intention exclusive (IX) lock). Например, выражение SELECT ... LOCK IN SHARE MODE установит на таблицу целевую блокировку чтения (IS lock), а выражение SELECT ... FOR UPDATE установит целевую блокировку записи (IX lock).

Перед тем как установить блокировку чтения (S lock) на строку в таблице, транзакция должна сначала установить целевую блокировку чтения (IS lock) или блокировку уровнем строже на эту таблицу. Перед тем как получить блокировку записи (X lock) на строку в таблице, транзакция должна сначала установить целевую блокировку записи (IX lock) на эту таблицу. Совместимость блокировок представлена в таблице ниже:

X IX S IS
X Конфликт Конфликт Конфликт Конфликт
IX Конфликт Совместимо Конфликт Совместимо
S Конфликт Конфликт Совместимо Совместимо
IS Конфликт Совместимо Совместимо Совместимо

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

Целевые блокировки блокируют только запросы на всю таблицу, например LOCK TABLES ... WRITE. Основное предназначение целевых блокировок - это обозначить что транзакция уже блокирует или собирается заблокировать строки в таблице.

Read more
21 Dec 2012

Пример настройки Database Connection Pool

Собственно хочу поделиться и описать конфигурацию DB Pool’a который мы используем в JTalks:

<!--Создаем Ленивый DataSource, который по возможности не будет вытягивать настоящее--> 
    <!--соединение к БД из настоящего пула до тех пор пока это правда необходимо. Например, -->
    <!--если Hibernate достает данные из кеша и не делает запрос в БД, нам не нужно--> 
    <!--настоящее соединение. Однако если не использовать ленивый DataSource, то соединение--> 
    <!--будет вытянуто и транзакция будет начата если метод помечен @Transactional-->
  <bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
    <property name="targetDataSource">
    <!--Это собственно настоящий DB Pool. Многие говорят что пул, который Hibernate--> 
    <!--использует по умолчанию не является серьезным и использование его в production-->
    <!--коде не допустимо. Однако C3P0 как раз не является пулом по умочанию, у Hibernate -->
    <!--есть свой внутренний пул для "поиграться", однако это не C3P0 как многие думают! С другими-->
    <!--же пулами не сравнивал на самом деле, поэтому не могу сказать какой лучше,--> 
    <!--C3P0 используется исторически во многих приложениях с Hibernate. -->
      <bean class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="${jdbc.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${JCOMMUNE_DB_USER:root}"/>
        <property name="password" value="${JCOMMUNE_DB_PASS:root}"/>
        <!--Эта опция показывает сколько PreparedStatement'ов должно быть закешировано-->
        <!--на стороне Java, в самом Connection Pool. Имейте в виду что СУБД может -->
        <!--требовать дополнительной настройки для того чтоб эта опция показала себя эффективной-->
        <property name="maxStatements" value="1500"/>
        <!--А это сколько PreparedStatement'ов каждое соединение может кешировать для себя -->
        <property name="maxStatementsPerConnection" value="50"/>
        <!--Вот это самая сложная опция и для того чтоб ее правильно настроить,-->
        <!--нам нужны нагрузочные тесты а также рабочее в PROD приложение-->
        <!--Есть разные стратегии по работе с соединениями которые влияют на оптимальный-->
        <!--размер DB Pool'a, подробней читайте прекрасную книгу Release It!-->
        <property name="maxPoolSize" value="50"/>
        <!--MySQL прибивает соединение если оно не использовалось какое-то время.--> 
        <!--По умолчанию это 8 часов. Дабы избежать неприятных исключений нам -->
        <!--нужно бомбардировать MySQL запросами каждый N часов (минут)-->
        <property name="idleConnectionTestPeriod" value="3600"/>
        <!--Если мы испытываем большую нагрузку и/или запросы выполняются очень долго,-->
        <!--мы сможем обслуживать 50 пользователей одновременно (размер пула), однако -->
        <!--при увеличении нагрузки, клиенты начнут выстраиваться в очередь и просто ждать-->
        <!--заблокированные в synchronized методах внутри DB Pool'a. Дабы мы не оказались-->
        <!--в ситуации, когда мы заблокированы надолго и приложение совсем не отвечает, -->
        <!--через 10 секунд простаивания в очереди выбрасывается исключение и сервер разгружается-->
        <!--от лишних запросов. Да, это исключение и это неприятно, однако лучше отвалится -->
        <!--несколько клиентов (особенно если это DDoS'еры поганые) нежели сайт будет в полной отключке.-->
        <property name="checkoutTimeout" value="10000"/>
        <!--Также соединение может издохнуть. Пул может проверять его на работоспособность-->
        <!--в то время как приложение запрашивает соединение и наоборот - когда возвращает обратно.-->
        <!--Первое - более надежное, второе - более быстрое.-->
        <property name="testConnectionOnCheckin" value="true"/>
      </bean>
    </property>
  </bean>


  • Подробрей о кешировании PreparedStatement

  • Больше о разрыве и тестировании соединений можно прочесть тут, мини проект с примером настроенного пула тут.

  • Если вы получили подобное исключение, значит вам поможет iddleConnectionTestPeriod описанный выше:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 64,129,968 milliseconds ago.  The last packet sent successfully to the server was 64,129,968 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3851)
Read more
18 Dec 2012

Логирование в Hibenrate + MySQL. Конфигурация Log4j/logback

Как логировать генерируемые Hibernate’ом запросы? Как логировать параметры запросов? Может ли лог Hibernate’a лгать?

Если вы работаете с Hibernate, вам скорей всего понадобится видеть, то ли для оптимизации, то ли для дебага какие запросы и зачем он генерирует. Следующие свойства следует описать в hibernate.cfg.xml ну или передать во время конфигурирования SessionFactory.

Самое важное, это возможность увидеть SQL генерируемый Хибом:

<property name="show_sql">true</property>

Однако запросы появятся только в консоли. Дабы по-настоящему логировать SQL запросы, нужно указать следующий логер в log4j: org.hibernate.SQL=TRACE. Обратите внимание на заглавные буквы SQL.

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

<property name="use_sql_comments">true</property>

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

<property name="format_sql">true</property>

А такой логер нужно настроить чтоб Hibernate логировал значения передаваемые в PreparedStatement вместо ? и :name

log4j.logger.org.hibernate.type = TRACE

А вот так будет выглядеть файл конфигурации для logback (groovy-версия):
import ch.qos.logback.classic.encoder.PatternLayoutEncoder
import ch.qos.logback.core.ConsoleAppender

import static ch.qos.logback.classic.Level.*

appender("consoleAppender", ConsoleAppender) {
    encoder(PatternLayoutEncoder) {
        pattern = "[%d{HH:mm:ss.SSS}] [%thread] [%-5level] [%logger{36}] - %msg%n"
    }
}

/**
 * This would allow us to see parameters passed into prepared statements
 */
logger("org.hibernate.type.descriptor.sql.BasicBinder", TRACE)
/**
 * We don't want to include the whole org.hibernate.type package to get rid of noise, 
 * thus we need to include necessary classes explicitly.
 */
logger("org.hibernate.type.EnumType", TRACE)
/**
 * Shows executed SQL statements. This one is better than show_sql because the latter can log only to console. Note,
 * capitalized SQL letters, it's important.
 */
logger("org.hibernate.SQL", TRACE)
root(INFO, ["consoleAppender"])

DB/Driver Logging

Однако и это не все. Иногда Hibernate не в состоянии логировать запрос как есть, потому как он генерируется самим драйвером, так например происходит во время Batch операций. Для того, чтоб увидеть такие запросы по-настоящему (Hibernate их будет выводить как и другие запросы), нужно смотреть на логи БД или JDBC Driver’a. Рассмотрим пример MySQL. Увидеть запросы можно либо заглянув в /var/log/mysql/mysql.log (это может зависить от версии и настроек MySQL Server’a), либо указав в строке соединения параметры логирования:

jdbc.url=jdbc:mysql://localhost/hib_training?characterEncoding=UTF-8\
  &rewriteBatchedStatements=true\
  &logger=com.mysql.jdbc.log.StandardLogger\
  &profileSQL=true

Стандарный логер (он будет использоваться по умолчанию) может вас не удовлетворить потому как он будет выводить сообщения не синхронно с самим Hibernate. По этим или другим причинам вы можете настроить com.mysql.jdbc.log.Slf4JLogger либо com.mysql.jdbc.log.Jdk14Logger.

Read more