Как оказалось, знают все, а понимают не все. Транзакции в mysql и SELECT FOR UPDATE / Habr

По долгу службы мне приходится иногда проводить собеседования на позицию «[старший|младший] разработчик python/django», «тимлид». К моему великому удивлению я обнаружил, что 9 из 10 соискателей, в резюме которых значатся слова » Mysql/Innodb/transactions/triggers/stored proc etc.», абсолютно ничего не могут рассказать о своем прошлом опыте работы с ними. Ни одного описания варианта использования, к сожалению, я так и не получил.

Далее по собеседованию я предлагал попробовать предложить вариант решения для следующей ситуации:

Допустим, мы являемся онлайн сервисом, который в свою очередь пользуется каким-то внешним платным API (активация услуги, платный контент, или что вашей душе угодно), то есть наш сервис сам платит деньги за пользование API. Пользователь в нашей системе создает запрос на активацию услуги, заполняет все поля и на последней странице жмет кнопку «Активировать услугу». То есть на момент отправки HTTP запроса мы имеем в нашей БД запись (запрос на активацию услуги). Каков наш алгоритм?- спрашиваю я и сам продолжаю:

— достаем из базы баланс пользователя;
— если баланса достаточно, то дергаем API;
— если всё хорошо, то списываем с баланса сумму за услугу, делаем UPDATE, коммитим, иначе откатываемся;
— отвечаем пользователю.

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

Как известно (а это знали все кандидаты!), innodb в mysql предоставляет транзакционный механизм и возможность построчной блокировки. Для того, чтобы применить этот самый построчный лок, достаточно добавить в конце SELECT-а выражение FOR UPDATE, например так:

SELECT * FROM requests WHERE id=5 FOR UPDATE

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

Также стоит заметить, что использование FOR UPDATE лучше делать с выключенным autocommit-ом, так как вне зависимости от того, что вы залочили, после первого апдейта лок снимется.

Вроде мелочь, вроде очевидно, но 9 из 10…

upd
прежнее название «Транзакции в mysql», не раскрытое в статье было заменено на » Транзакции в mysql и SELECT FOR UPDATE»

ЗЫ
в статье не говорится о том, что АПИ нужно дергать в рамках транзакции и что делать в случае сбоя и как обрабатывать исключительные ситуации.

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

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

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

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

Свойства транзакций

Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID.

Атомарность – обеспечивает, чтобы все операции входящие в единицу работы были завершены успешно. В противном случае транзакция прерывается в момент сбоя, и все предыдущие операции возвращаются в прежнее состояние.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

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

Изолированность — позволяет транзакциям работать независимо друг от друга и прозрачно.

Долговечность — гарантирует, что результат совершенной транзакции сохранится в случае сбоя системы.

Управление транзакциями

Для управления транзакциями используются следующие команды.

COMMIT — сохранить изменения.

ROLLBACK — отменить изменения.

SAVEPOINT — создает точки сохранения в группах транзакций.

SET TRANSACTION — помещает имя в транзакцию.

Команды управления транзакциями

Команды управления транзакциями используются только с командами DML, такими как — INSERT, UPDATE и DELETE. Они не могут использоваться при создании таблиц или их удалении, поскольку эти операции автоматически фиксируются в базе данных.

Команда COMMIT

Команда COMMIT — это транзакционная команда, используемая для сохранения изменений внесенных транзакцией в базу данных. Команда COMMIT сохраняет все транзакции в базе данных с момента выполнения последней команды COMMIT или ROLLBACK.

Синтаксис команды COMMIT следующий.

Пример

Рассмотрим таблицу CUSTOMERS, содержащую следующие записи:

Ниже приведен пример, в котором из таблицы будут удалены клиенты с возрастом = 25, а затем эти изменения будут сохранены в базе данных.

DELETE FROM CUSTOMERS WHERE AGE = 25; COMMIT;

DELETE FROM CUSTOMERS   WHERE AGE = 25; COMMIT;

Таким образом, из таблицы будут удалены две строки, и результат выполнения инструкции SELECT будет выглядеть следующим образом.

Команда ROLLBACK

Команда ROLLBACK — это транзакционная команда, используемая для отмены транзакций, которые еще не были сохранены в базе данных. Эта команда может использоваться только для отмены транзакций с момента выполнения последней команды COMMIT или ROLLBACK.

Синтаксис команды ROLLBACK следующий:

Пример

Рассмотрим таблицу CUSTOMERS, содержащую следующие записи:

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Ниже приведен пример, в котором из базы данных будут удалены все записи для которых возраст = 25, а затем эти изменения будут отменены.

DELETE FROM CUSTOMERS WHERE AGE = 25; ROLLBACK;

DELETE FROM CUSTOMERS   WHERE AGE = 25; ROLLBACK;

Таким образом, операция удаления не приведет к изменениям в таблице, и результат выполнения инструкции SELECT будет выглядеть следующим образом.

Команда SAVEPOINT

SAVEPOINT – это точка транзакции, к которой вы можете вернуть транзакцию, не откатывая ее полностью. Синтаксис команды SAVEPOINT приведен ниже.

SAVEPOINT SAVEPOINT_NAME;

SAVEPOINT SAVEPOINT_NAME;

Эта команда предназначена только для создания SAVEPOINT в других транзакционных операторах. Команда ROLLBACK используется для отмены группы транзакций до точки SAVEPOINT.

Синтаксис, который используется для возврата к SAVEPOINT, показан ниже.

ROLLBACK TO SAVEPOINT_NAME;

ROLLBACK TO SAVEPOINT_NAME;

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

Пример

Рассмотрим таблицу CUSTOMERS, содержащую следующие записи.

Следующий блок кода содержит ряд операций.

SAVEPOINT SP1; Savepoint created. DELETE FROM CUSTOMERS WHERE ID=1; 1 row deleted. SAVEPOINT SP2; Savepoint created. DELETE FROM CUSTOMERS WHERE ID=2; 1 row deleted. SAVEPOINT SP3; Savepoint created. DELETE FROM CUSTOMERS WHERE ID=3; 1 row deleted.

SAVEPOINT SP1; Savepoint created. DELETE FROM CUSTOMERS WHERE ID=1; 1 row deleted. SAVEPOINT SP2; Savepoint created. DELETE FROM CUSTOMERS WHERE ID=2; 1 row deleted. SAVEPOINT SP3; Savepoint created. DELETE FROM CUSTOMERS WHERE ID=3; 1 row deleted.

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

ROLLBACK TO SP2; Rollback complete.

ROLLBACK TO SP2; Rollback complete.

Обратите внимание, что перед тем как мы вернулись к SP2, было произведено первое удаление

Команда RELEASE SAVEPOINT

Команда RELEASE SAVEPOINT используется для удаления созданной точки SAVEPOINT. Синтаксис команды RELEASE SAVEPOINT следующий.

RELEASE SAVEPOINT SAVEPOINT_NAME;

RELEASE SAVEPOINT SAVEPOINT_NAME;

После того как SAVEPOINT будет удалена, вы больше не сможете использовать команду ROLLBACK для отмены транзакций, выполненных после последней SAVEPOINT.

Команда SET TRANSACTION

Команда SET TRANSACTION может использоваться для инициирования транзакции базы данных. Эта команда используется для указания характеристик транзакций, которая задается после команды. Например, вы можете задать для транзакции режим только чтения или чтение и запись. Синтаксис команды SET TRANSACTION следующий.

SET TRANSACTION [ READ WRITE | READ ONLY ];

SET TRANSACTION [ READ WRITE | READ ONLY ];

Источник: https://www.tutorialspoint.com/

Редакция: Команда webformyself.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Хотите изучить MySQL?

Посмотрите курс по базе данных MySQL!

Смотреть

продвижение сайтов, способы заработать в интернете

Скажите мне, уважаемые читатели, вам что-нибудь говорит слово продающие запросы? Ну, наверняка, в этом слове так много смысла, что просто ппц.

Ведь, по сути, продающие запросы – это одна из основ будущего сайта. И если при создании сайта ошибешься при выборе запросов, которые будут продавать, то в будущем только впустую сольешь бабло, а выхлопа так и не будет.

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

Кстати, идея этого поста возникла после того, как меня просто задолбали терроризировать некоторые читатели, после того как прочитали статью про платные архивы — Как заработать на платных архивах. Мега мануал для будущих адвертов. Создание сайта под платные архивы. То есть, понимаете, в чем соль? В том, что по сути нефиг даже создавать сайт под платные архивы, если не знаешь запросы, под которые будешь двигать. Вернее, если не знаешь запросы, которые будут конвертиться.

Или, например, если собираешься создавать сайт под Миксмаркет, или под любую другую партнерку (вот вам куча партнерок с описаниями), то один же фиг, считай, самое важное – это продающие запросы.

То есть, важность выявления продающих запросов первична. Создание и продвижение сайта – вторично. Можете возразить?

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

Их есть три вида:

1. Навигационные. Цель пользователя – найти какой-то сайт

2. Информационные. Цель пользователя – найти нужную информацию

3. Транзакционные. От слова «транзакция». Здесь само слово подразумевает, что по таким запросам пользователи совершают какие-либо действия

Отсюда делаем вывод, из которого будем исходить далее при движении по логической цепочке. А именно: нам нужные транзакционные запросы – это раз.

Примеры транзакционных запросов:

— купить домик в подмосковье
— заказать футболку
— ремонт авто
— скачать скайп
— подписаться на журнал мурзилка

То есть, я логически мыслю так. Если я создаю сайт под какую-то партнерку, на которую планирую в будущем сливать траф, то мне нужен список запросов, по которым пользователи хотят совершить какое-то действие. Если я делаю сайт под платные архивы, то, разумеется, в первую очередь меня будут интересовать ключевые слова со словом «скачать». Понятно, что если покупать траф через тот же Google Adwords, то такие слова не пропустят модераторы. Здесь, правда, не об этом речь, поэтому что касается AdWords трафа, лучше почитайте статью Мастер-класс по Google Adwords или как правильно покупать траф для слива на партнерки. Я там все популярным языком объяснил.

Второе – мне нужны коммерческие запросы.

Третье – мне нужно тщательно изучить тематику, в которой я планирую продвигаться, посмотреть, какая там конкуренция, и можно ли потеснить ныне действующих лидеров рынка. А лучше всего – выявить ключевые слова, по которым конкуренты рекламируются в AdWords. Ну или откуда вы там планируете сливать трафик. Для того чтобы подсмотреть ключевики конкурентов в AdWords, можно использовать SemRush (см. Полноценный мануал по SemRush. Дайте пинка вашему бизнесу!) Если планируете лить траф из ВКонтакте, то можно использовать AdsDock (см. Занимаетесь арбитражем ВКонтакте? Упростите процесс с помощью AdsDock.com) То есть, подсмотреть, как рекламируются конкуренты – это ступодово правильный результат.

Четверное – логика

Что тут ни говори, а логика в деле подбора продающих запросов – дело весьма и весьма ответственное. Я раньше ложил болт на логику, и особо не задумывался над всеми этими психологическими заморочками. То есть, шел как баран: искал более-менее подходящий запрос, и двигал его. На заре своего обучения seo я даже когда-то для одного своего старого блога покупал постовые с анкором «анализатор сайтов». Покупал их на страницу с описанием какого-то сервиса. И все бабки, помню, ложил на этот запрос. Хорошо, что потом посредством своей головы и кошелька тоже понял, что смысл в таком продвижении практически отсутствует.

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

Пример. Возьмем самый популярный запрос – «пластиковые окна». Продвигать этот запрос сразу, сходу, будет не совсем правильно. Нужно подумать, как мыслит покупатель, когда хочет купить пластиковые окна. Здесь я бы сказал, что хорошим продающим запросом будет «пластиковые окна отзывы». Почему? Потому что здесь человек уже достаточно находился по сайтам, и теперь хочет отсеять говнокомпании, которые предлагают говноокна. Им нужны хорошие характеристики.

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

В конечном счете, все это я подвожу к тому, что лучше всего рулят именно низкочастотники. Именно они дают возможность получить максимальную отдачу. Вот по этой причине запрос «пластиковые окна Krauss» будет более продающим, чем запрос «пластиковые окна».

Заключение

Лично я в своей практике сильно далеко не лезу в дебри при определении продающих запросов. Наиболее часто использую три метода:

1. Подсматриваю, по каким ключикам рекламируются конкуренты в AdWords

2. Отслеживаю цепочку переходов по сайту, и последующие переходы на партнерку. Для этого в партнерках (там где это можно) смотрю IP, с которого приходят покупатели на сайт, а потом с помощью LI (если есть возможность и примерно знаю где копать) нахожу эти IP, и вижу по каким запросам приходили покупатели

3. Часто бывает так, что приходится общаться с покупателями напрямую. В этом случае просто спрашиваю у них, как они нашли меня и мой сайт.

Вот такой получился обзор-исследование. Может, немного сумбурно, но я просто сам еще не до конца свел все свои предположения доводы в одну логическую цепочку. Поэтому данный пост – это только начало. Далее буду стараться развивать тему. Если у вас, уважаемые читатели, есть что-то интересное на данную тему, и если не жалко поделиться наработками, то велкам в комменты. Если наберется больше 15 комментов с хорошими доводами, то автору лучшей идеи по поводу того как можно определять продающие запросы, я презентую небольшой подарок — $15. До связи!

Сегодня на десерт: Филигранная точность футболистов

Оцените статью по 5-бальной шкале:

Загрузка…

Вконтакте

Facebook

Twitter

Google+

Понравилась запись? Подпишитесь на обновления по почте:

TwitterFacebook

Транзакции и механизмы их контроля / Habr

Транзакции

Транзакцией называется последовательность операций над данными имеющая начало и конец

Транзакция это последовательное выполнение операций чтения и записи. Окончанием транзакции может быть либо сохранение изменений (фиксация, commit) либо отмена изменений (откат, rollback). Применительно к БД транзакция это нескольких запросов, которые трактуются как единый запрос.

Транзакции должны удовлетворять свойствам ACID

Атомарность. Транзакция либо выполняется полностью либо не выполняется вовсе.

Согласованность. При завершении транзакции не должны быть нарушены ограничения накладываемые на данные (например constraints в БД). Согласованность подразумевает, что система будет переведена из одного корректного состояния в другое корректное.

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

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

Журнал транзакций

Журнал хранит изменения выполненные транзакциями, обеспечивает атомарность и устойчивость данных в случае сбоя системы

Журнал содержит значения, которые данные имели до и после их изменения транзакцией. Write-ahead log strategy обязывает добавлять в журнал запись о предыдущих значениях до начала, а о конечных после завершения транзакции. В случае внезапной остановки системы БД читает лог в обратном порядке и отменяет изменения сделанные транзакциями. Встретив прерванную транзакцию БД выполняет ее и вносит изменения о ней в журнал. Находясь в состоянии на момент сбоя, БД читает лог в прямом порядке и возвращает изменения сделанные транзакциями. Таким образом сохраняется устойчивость транзакций которые уже были зафиксированы и атомарность прерванной транзакции.

Простое повторное выполнение ошибочных транзакций недостаточно для восстановления.

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

Уровни изоляции

Чтение фиксированных данных (Read Committed)

Проблема грязного чтения (Dirty Read) заключается в том, что транзакция может прочесть промежуточный результат работы другой транзакции.

Пример. Начальное значение баланса 0$. Т1 добавляет к балансу 50$. Т2 считывает значение баланса (50$). Т1 отменяет изменения и завершается. T2 продолжает выполнение располагая неверными данными о балансе.

Решением является чтение фиксированных данных (Read Committed) запрещающее читать данные, измененные транзакцией. Если транзакция A изменила некоторый набор данных, то транзакция B при обращении за этими данными вынуждена ожидать завершения транзакции A.

Повторяемое чтение (Repeatable Read)

Проблема потерянных изменений (Lost Updates). Т1 сохраняет изменения поверх изменений Т2.

Пример. Начальное значение баланса 0$ и две транзакции одновременно пополняют баланс. T1 и T2 читают баланс равный 0$. Затем T2 прибавляет 200$ к 0$ и сохраняет результат. T1 прибавляет 100$ к 0$ и сохраняет результат. Итоговый результат 100$ вместо 300$.

Проблема неповторяемого чтения (Unrepeatable read). Повторное чтение одних и тех же данных возвращает разные значения.

Пример. Т1 читает значение баланса равное 0$. Затем Т2 добавляет к балансу 50$ и завершается. Т1 повторно читает данные и обнаруживает несоответствие с предыдущим результатом.

Повторяемое чтение (Repeatable Read) гарантирует что повторное чтение вернет тот же результат. Данные прочитанные одной транзакцией запрещено менять в других до завершения транзакции. Если транзакция A прочла некоторый набор данных, то транзакция B при обращении за этими данными вынуждена ожидать завершения транзакции A.

Упорядоченное чтение (Serializable)

Проблема фантомного чтения (Phantom Reads). Два запроса выбирающие данные по некоему условию возвращают разные значения.

Пример. T1 запрашивает количество всех пользователей баланс которых больше 0$ но меньше 100$. T2 вычитает 1$ у пользователя с балансом 101$. T1 повторно выполняет запрос.

Упорядоченное чтение (Serializable). Транзакции выполняются как полностью последовательные. Запрещается обновлять и добавлять записи, подпадающие под условия запроса. Если транзакция A запросила данные всей таблицы, то таблица целиком замораживается для остальных транзакций до завершения транзакции A.

Планировщик (Scheduler)

Устанавливает очередность в которой должны выполняться операции при параллельно протекающих транзакциях

Обеспечивает заданный уровень изолированности. Если результат выполнения операций не зависит от их очередности, то такие операции коммутативны (Permutable). Коммутативны операции чтения и операции над разными данными. Операции чтения-записи и записи-записи не коммутативны. Задача планировщика чередовать операции выполняемые параллельными транзакциями так, чтобы результат выполнения был эквивалентен последовательному выполнению транзакций.

Механизмы контроля параллельных заданий (Concurrency Control)

Оптимистический основан на обнаружении и разрешении конфликтов, пессимистический на предотвращении возникновения конфликтов

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

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

Блокировка (Locking)

Если одна транзакция заблокировала данные, то остальные транзакции при обращении к данным обязаны ждать разблокировки

Блок может накладываться на базу данных, таблицу, ряд или аттрибут. Совместный захват (Shared Lock) может быть наложен на одни данные несколькими транзакциями, разрешает всем транзакциям (включая наложившую) чтение, запрещает изменение и монопольный захват. Монопольный захват (Exclusive Lock) может быть наложен только одной транзакцией, разрешает любые действия наложившей транзакции, запрещает любые действия остальным.

Взаимоблокировкой считается ситуация когда транзакции оказываются в режиме ожидания, длящемся бесконечно долго

Пример. Первая транзакция ждет освобождения данных захваченных второй, в то время как вторая ждет освобождения данных, захваченных первой.

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

С определенной периодичностью производится поиск взаимоблокировок. Один из способов обнаружения — по времени, то есть считать что взаимоблокировка произошла если транзакция выполняется слишком долго. Когда взаимоблокировка найдена, то одна из транзакций откатывается, что дает возможность другим транзакциям участвующим во взаимоблокировке завершиться. Выбор жертвы может быть основан на стоимости транзакций или их старшинстве (Wait-Die и Wound-wait схемы).

Каждой транзакции T присваивается временная метка TS содержащая время начала выполнения транзакции.

Wait-Die.

Если TS(Ti) < TS(Tj), то Ti ждет, иначе Ti откатывается и начинается заново с той же временной меткой.

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

Wound-wait.

Если TS(Ti) < TS(Tj), то Tj откатывается и начинается заново с той же временной меткой, иначе Ti ждет.

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

Пессимистическое решение проблемы взаимоблокировок не позволяет транзакции начать выполнение если есть риск возникновения взаимоблокировки

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

Двухфазная блокировка — предотвращение взаимоблокировок путем захвата всех ресурсов используемых транзакцией в начале транзакции и освобождения их в конце

Все блокирующие операции должны предшествовать первой разблокирующей. Имеет две фазы — Growing Phase при которой происходит накопление захватов и Shrinking Phase при которой происходит освобождение захватов. При невозможности захвата одного из ресурсов транзакция начинается сначала. Возможна ситуация когда транзакция не сможет захватить требуемые ресурсы, например если несколько транзакций будут конкурировать за одни ресурсы.

Двухфазный коммит обеспечивает выполнение коммита на всех репликах БД

Каждая БД вносит информацию о данных которые будут изменены в лог и отвечает координатору ОК (Voting Phase). После того как все ответили ОК координатор отсылает сигнал обязывающий всех произвести коммит. После коммита сервера отвечают ОК, если хоть один не ответил ОК, то координатор отсылает сигнал отмены изменений всем серверам (Completion Phase).

Метод временных меток

Более старая транзакция откатывается при попытке доступа к данным, задействованным более молодой транзакцией

Каждой транзакции назначается временная метка TS соответствующая времени начала выполнения. Если Ti старше Tj, то TS(Ti)TS(Tj).

Когда транзакция откатывается, ей назначается новая временная метка. Каждый объект данных Q задействованный транзакцией помечается двумя метками. W-TS(Q) — временная метка самой молодой транзакции, успешно выполнившей запись над Q. R-TS(Q) — временная метка самой молодой транзакции, выполнившей запись чтения над Q.

Когда транзакция T запрашивает чтение данных Q возможны два варианта.

Если TS(T) < W-TS(Q), то есть данные были обновлены более молодой транзакцией, то транзакция T откатывается.

Если TS(T) >= W-TS(Q), то чтение выполняется и R-TS(Q) становится MAX(R-TS(Q), TS(T)).

Когда транзакция T запрашивает изменение данных Q возможны два варианта.

Если TS(T) < R-TS(Q), то есть данные уже были прочитаны более молодой транзакцией и если произвести изменение, то возникнет конфликт. Транзакция T откатывается.

Если TS(T) < W-TS(Q), то есть транзакция пытается перезаписать более новое значение, транзакция T откатывается. В остальных случаях изменение выполняется и W-TS(Q) становится равным TS(T).

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

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

Thomas write rule — вариация метода временных меток при которой данные обновленные более молодой транзакцией запрещено перезаписывать более старой

Транзакция T запрашивает изменение данных Q. Если TS(T)W-TS(Q), то есть транзакция пытается перезаписать более новое значение, транзакция T не откатывается как в методе временных меток.

Транзакция (информатика) — Википедия

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

Различают последовательные (обычные), параллельные и распределённые транзакции. Распределённые транзакции подразумевают использование более чем одной транзакционной системы и требуют намного более сложной логики (например, two-phase commit — двухфазный протокол фиксации транзакции). Также в некоторых системах реализованы автономные транзакции, или под-транзакции, которые являются автономной частью родительской транзакции.

Пример: необходимо перевести с банковского счёта номер 5 на счёт номер 7 сумму в 10 денежных единиц. Этого можно достичь, к примеру, приведённой последовательностью действий:

1. Прочесть баланс на счету номер 5.
2. Уменьшить баланс на 10 денежных единиц.
3. Сохранить новый баланс счёта номер 5.
4. Прочесть баланс на счету номер 7.
5. Увеличить баланс на 10 денежных единиц.
6. Сохранить новый баланс счёта номер 7.

Эти действия представляют собой логическую единицу работы «перевод суммы между счетами», и таким образом, являются транзакцией. Если прервать данную транзакцию, к примеру, в середине, и не аннулировать все изменения, легко оставить владельца счёта номер 5 без 10 единиц, тогда как владелец счета номер 7 их не получит.

Основная статья: ACID

Одним из наиболее распространённых наборов требований к транзакциям и транзакционным системам является набор ACID (Atomicity, Consistency, Isolation, Durability). Требования ACID были в основном сформулированы в конце 1970-х годов Джимом Греем^[1]. Вместе с тем существуют специализированные системы с ослабленными транзакционными свойствами^[2].

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

Уровни описаны в порядке увеличения изолированности транзакций и, соответственно, надёжности работы с данными.

• 0 — Чтение неподтверждённых данных (грязное чтение) (Read Uncommitted, Dirty Read) — чтение незафиксированных изменений как своей транзакции, так и параллельных транзакций. Нет гарантии, что данные, изменённые другими транзакциями, не будут в любой момент изменены в результате их отката, поэтому такое чтение является потенциальным источником ошибок. Невозможны потерянные изменения (lost changes), возможны грязное чтение, неповторяемое чтение и фантомы.
• 1 — Чтение подтверждённых данных (Read Committed) — чтение всех изменений своей транзакции и зафиксированных изменений параллельных транзакций. Потерянные изменения и грязное чтение не допускается, возможны неповторяемое чтение и фантомы.
• 2 — Повторяемое чтение (Repeatable Read, Snapshot) — чтение всех изменений своей транзакции, любые изменения, внесённые параллельными транзакциями после начала своей, недоступны. Потерянные изменения, грязное и неповторяемое чтение невозможны, возможны фантомы.
• 3 — Сериализуемый (Serializable) — сериализуемые транзакции. Результат параллельного выполнения сериализуемой транзакции с другими транзакциями должен быть логически эквивалентен результату их какого-либо последовательного выполнения. Проблемы синхронизации не возникают.

Чем выше уровень изоляции, тем больше требуется ресурсов, чтобы его обеспечить. Соответственно, повышение изолированности может приводить к снижению скорости выполнения параллельных транзакций, что является «платой» за повышение надёжности.

В СУБД уровень изоляции транзакций можно выбрать как для всех транзакций сразу, так и для одной конкретной транзакции. По умолчанию в большинстве баз данных используется уровень 1 (Read Committed). Уровень 0 используется в основном для отслеживания изменений длительных транзакций или для чтения редко изменяемых данных. Уровни 2 и 3 используются при повышенных требованиях к изолированности транзакций.

Полноценная реализация уровней изоляции и свойств ACID представляет собой нетривиальную задачу. Обработка поступающих данных приводит к большому количеству маленьких изменений, включая обновление как самих таблиц, так и индексов. Эти изменения потенциально могут потерпеть неудачу: закончилось место на диске, операция занимает слишком много времени (timeout) и т. д. Система должна в случае неудачи корректно вернуть базу данных в состояние до транзакции.

Первые коммерческие СУБД (к примеру, IBM DB2), пользовались исключительно блокировкой доступа к данным для обеспечения свойств ACID. Но большое количество блокировок приводит к существенному уменьшению производительности. Есть два популярных семейства решений этой проблемы, которые снижают количество блокировок:

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

При упреждающей журнализации, используемой в Sybase и MS SQL Server до версии 2005, все изменения записываются в журнал, и только после успешного завершения — в базу данных. Это позволяет СУБД вернуться в рабочее состояние после неожиданного падения системы. Теневые страницы содержат копии тех страниц базы данных на начало транзакции, в которых происходят изменения. Эти копии активизируются после успешного завершения. Хотя теневые страницы легче реализуются, упреждающая журнализация более эффективна^[4].

Дальнейшее развитие технологий управления базами данных привело к появлению безблокировочных технологий. Идея контроля над параллельным доступом с помощью временных меток (timestamp-based concurrency control) была развита и привела к появлению многоверсионной архитектуры MVCC. Эти технологии не нуждаются ни в журнализации изменений, ни в теневых страницах. Архитектура, реализованная в Oracle 7.х и выше, записывает старые версии страниц в специальный сегмент отката, но они все ещё доступны для чтения. Если транзакция при чтении попадает на страницу, временная метка которой новее начала чтения, данные берутся из сегмента отката (то есть используется «старая» версия). Для поддержки такой работы ведётся журнал транзакций, но в отличие от «упреждающей журнализации», он не содержит данных. Работа с ним состоит из трёх логических шагов:

1. Записать намерение произвести некоторые операции
2. Выполнить задание, копируя оригиналы изменяемых страниц в сегмент отката
3. Записать, что всё сделано без ошибок

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

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

Firebird вообще не имеет ни журнала изменений, ни сегмента отката, а реализует MVCC, записывая новые версии строк таблиц прямо в активное пространство данных. Так же поступает MS SQL 2005. Теоретически это даёт максимальную эффективность при параллельной работе с данными, но ценой является необходимость «сборки мусора», то есть удаления старых и уже не нужных версий данных.

1. ↑ Gray, Jim. The Transaction Concept: Virtues and Limitations. Proceedings of the 7th International Conference on Very Large Databases: pages 144—154, 1981 (англ.)
2. ↑ Advanced Transaction Models and Architectures (англ.)
3. ↑ Семейство алгоритмов ARIES Архивировано 20 сентября 2008 года.
4. ↑ Gray, J., McJones, P., Blasgen, M., Lindsay, B., Lorie, R., Price, T., Putzolu, F., and Traiger, I. The recovery manager of the System R database manager. ACM Comput. Surv. 13, 2 (June 1981).

SQL — Транзакция | ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)

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

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

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

Свойства транзакций

Транзакции имеют следующие четыре стандартных свойств, как правило, называют аббревиатурой ACID.

• Atomicity – гарантирует, что все операции в рамках единицы работы завершены успешно. В противном случае, транзакция прерывается в точке выхода из строя , и все предыдущие операции откатываются в прежнее состояние.
• Consistency – гарантирует, что база данных правильно изменяет состояния на более успешное совершенные транзакции.
• Isolation – позволяет транзакции работать независимо и прозрачно друг с другом.
• Durability – гарантирует, что результат или эффект зафиксированной транзакции сохраняется в случае сбоя системы.

Управление транзакцией

Следующие команды используются для управления операциями.

• COMMIT – для сохранения изменений.
• ROLLBACK – откат изменений.
• SAVEPOINT – создает точки внутри групп операций, которые следует откатить.
• SET TRANSACTION – размещает имя транзакции.

Команды управления транзакциями

Транзакционные команды управления используются только с командами DML, такие как – INSERT, UPDATE и DELETE. Они не могут быть использованы при создании таблиц или опускают их, потому что эти операции автоматически фиксируются в базе данных.

Команда COMMIT

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

Синтаксис команды COMMIT выглядит следующим образом:

COMMIT;

 

Пример

Рассмотрим таблицу клиентов, имеющих следующие записи:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Maxim    |  35 | Moscow    | 21000.00 |
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 31500.00 |
|  5 | Ruslan   |  34 | Omsk      | 43000.00 |
|  6 | Dima     |  32 | SP        | 45000.00 |
|  7 | Roma     |  34 | SP        | 10000.00 |
+----+----------+-----+-----------+----------+

 

Ниже приведен пример, который удалит эти записи из таблицы, которые имеют возраст = 34, а затем зафиксировать изменения в базе данных.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 34;
SQL> COMMIT;

 

Таким образом, две строки из таблицы будет удалены, и оператор SELECT произведет следующий результат.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Maxim    |  35 | Moscow    | 21000.00 |
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 31500.00 |
|  6 | Dima     |  32 | SP        | 45000.00 |
+----+----------+-----+-----------+----------+

Команда ROLLBACK

Команда ROLLBACK является транзакционной командой и используется для отмены операций, которые еще не были сохранены в базе данных. Эта команда может быть использована только для отката транзакции после последних команд commit или rollback.

Синтаксис команды ROLLBACK выглядит следующим образом:

ROLLBACK;

 

Пример

Рассмотрим таблицу клиентов, имеющую следующие записи:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Maxim    |  35 | Moscow    | 21000.00 |
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 31500.00 |
|  5 | Ruslan   |  34 | Omsk      | 43000.00 |
|  6 | Dima     |  32 | SP        | 45000.00 |
|  7 | Roma     |  34 | SP        | 10000.00 |
+----+----------+-----+-----------+----------+

 

Ниже приведен пример, который удалит эти записи из таблицы, которые имеют возраст = 34, а затем откатим изменения в базе данных.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 34;
SQL> ROLLBACK;

 

Таким образом, операция удаления не будет влиять на таблицу и оператор SELECT покажет следующий результат:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Maxim    |  35 | Moscow    | 21000.00 |
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 31500.00 |
|  5 | Ruslan   |  34 | Omsk      | 43000.00 |
|  6 | Dima     |  32 | SP        | 45000.00 |
|  7 | Roma     |  34 | SP        | 10000.00 |
+----+----------+-----+-----------+----------+

Команда SAVEPOINT

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

Синтаксис команды SAVEPOINT является таким, как показано ниже:

SAVEPOINT SAVEPOINT_NAME;

 

Эта команда служит только в создании SAVEPOINT среди всех транзакционных заявлений. Команда ROLLBACK используется для отмены группы операций.

Синтаксис для отката SAVEPOINT является таким, как показано ниже:

ROLLBACK TO SAVEPOINT_NAME;

 

Ниже приведен пример, в котором вы собираетесь удалить три записи из таблицы Customers. Вы хотите создать SAVEPOINT перед каждым удалением, так что вы можете откатить к любому SAVEPOINT в любое время, чтобы вернуть соответствующие данные в исходное состояние.

Пример

Рассмотрим таблицу клиентов, имеющих следующие записи:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Maxim    |  35 | Moscow    | 21000.00 |
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 31500.00 |
|  5 | Ruslan   |  34 | Omsk      | 43000.00 |
|  6 | Dima     |  32 | SP        | 45000.00 |
|  7 | Roma     |  34 | SP        | 10000.00 |
+----+----------+-----+-----------+----------+

 

Следующий блок кода содержит ряд операций.

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

 

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

SQL> ROLLBACK TO SP2;
Rollback complete.

 

Обратите внимание на то, что только первое удаление имело место, так как вы откатили к SP2.

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 31500.00 |
|  5 | Ruslan   |  34 | Omsk      | 43000.00 |
|  6 | Dima     |  32 | SP        | 45000.00 |
|  7 | Roma     |  34 | SP        | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

 

Команда RELEASE SAVEPOINT

Команда RELEASE SAVEPOINT используется для удаления SAVEPOINT, который вы создали.

Синтаксис команды RELEASE SAVEPOINT выглядит следующим образом.

RELEASE SAVEPOINT SAVEPOINT_NAME;

 

После того, как SAVEPOINT был опубликован, вы больше не можете использовать команду ROLLBACK, чтобы отменить транзакции, выполненные с момента последнего SAVEPOINT.

Команда SET TRANSACTION

Команда SET TRANSACTION может быть использована для инициирования транзакции базы данных. Эта команда используется для определения характеристик для транзакции, которая следует. Например, вы можете указать транзакцию только для чтения или чтения и записи.

Синтаксис команды SET TRANSACTION выглядит следующим образом.

SET TRANSACTION [ READ WRITE | READ ONLY ];

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.