Ежедневно люди переводят друг другу со счета на счет деньги. Иногда даже несколько раз в день, а порой совершают эти операции подряд или параллельно. Никто не хочет, чтобы с его счета деньги списались несколько раз из-за сбоя системы.
В этой статье расскажем о том, как транзакции в базах данных помогут переводить деньги со счета на счет без ошибок. Транзакции помогают не только правильно выполнять операции, но и воспроизводить их в случае сбоев и обеспечивать надежность данных.
Рассмотрим банковский сценарий на примере истории. Игорь хочет перевести на счет Юре 100 ₽. Для него это выглядит просто — ввести сумму и перевести деньги. В упрощенном сценарии в банке база данных выполняет как минимум две операции:
1) Списать 100 ₽ со счета Игоря.
2) Зачислить 100 ₽ на счет Юры.
Однако, если после выполнения первой операции произойдет технический сбой, возникнет риск того, что средства спишутся со счета Игоря, но не зачислятся на счет Юры.
Транзакции обеспечивают предотвращение подобных ситуаций. У них есть ключевая особенность — атомарность. Это означает, что если хотя бы одна из операций внутри транзакции завершится ошибкой, то никакие изменения не вносятся в базу данных. Если все операции пройдут успешно, изменения сохранятся и обеспечат надежность и целостность данных. Транзакции базы данных sql работают по принципу «Все или ничего».
Операторы управления транзакциями
Давайте изучим механизм управления транзакциями в контексте СУБД PostgreSQL.
В PostgreSQL транзакция определяется набором команд, которые окружены операторами BEGIN и COMMIT. Если вернемся к примеру с Игорем и Юрой, то можем представить транзакцию в следующем виде:
В PostgreSQL каждый оператор рассматривается как отдельная транзакция. Это означает, что если вы выполняете операцию SELECT, INSERT или другую команду без начала транзакции (оператор BEGIN), то эта команда автоматически запустится внутри собственной транзакции. Что, если бы Игорю не хватило средств на счете, и его баланс стал отрицательным? В таком случае, необходимо отменить изменения, внесенные транзакцией. Для этого используется оператор ROLLBACK.
Точки сохранения
Оператор ROLLBACK помогает отменить изменения, внесенные транзакцией, но как быть, если хотим отменить не все изменения?
Точки сохранения — это инструмент для управления транзакциями в базе данных. Оператор SAVEPOINT позволяет создавать своего рода «закладки» внутри текущей транзакции и фиксировать состояние базы данных на момент создания точек. Это дает возможность разделить сложные транзакции на логические этапы. Если необходимо откатить часть изменений в рамках транзакции, то можно воспользоваться точкой сохранения вместо полного отката всей транзакции.
SAVEPOINT работает в паре с оператором ROLLBACK TO. При создании точки сохранения даем ей название, и , если необходимо откатить изменения, выбираем точку до которой хотим вернуться. Этот механизм предоставляет контроль над откатом изменений в базе данных.
Рассмотрим применение точек сохранения на примере. Для иллюстрации снова обратимся к Игорю и Юре и добавим в сценарий новые детали:
1) У Юры в банке есть две карты.
2) Игорь переводит деньги Юре по номеру телефона. Банк автоматически выбирает карту, на которую нужно зачислить средства.
В реальной банковской системе, помимо обновления баланса, происходят изменения в других таблицах, например, в регистрации историй переводов. Из-за этого могут возникнуть непредвиденные ошибки транзакции базы данных.
Давайте представим, что Игорь переводит 100 ₽ Юре. В рамках этой транзакции сначала списываются 100 ₽ с баланса Игоря, затем счет Юры пополняется на 100 ₽. Однако, при попытке записать эту операцию в историю переводов, возникает ошибка. Воспользуемся точкой сохранения, чтобы вернуться к состоянию до этой операции. Затем транзакция успешно пополняет счет Юры на 100 ₽.
Пример использования точки сохранения в коде:
Аномалии параллельного выполнения транзакций
Рассмотрим аномалии, которые возникают при одновременном выполнении транзакций, и как они могут нарушить надежность данных.
Одновременно в базе данных может выполняться сразу ряд транзакций. Параллельное выполнение обеспечивает более высокую эффективность в работе с данными. Однако это также может стать источником некоторых аномалий. Существует четыре типа аномалий:
Грязное чтение
Новая транзакция считывает данные, записанной незавершенной старой.
Рассмотрим эту аномалию на примере. Игорь переводит на пустой счет Юры 100 ₽, а Юра сразу же снимает эти деньги со счета. Порядок такой:
1) Начинается первая транзакция.
2) Игорь переводит Юре 100 ₽.
3) Начинается вторая транзакция.
4) Вторая транзакция считывает баланс Юры и видит на нем 100 ₽.
5) Юра выводит 100 ₽ со счета (помним, что первая транзакция продолжает выполняться).
6) Первая транзакция завершается по причине ошибки, а изменения, которые она внесла, отменяются.
Состояние счета Игоря | Транзакция 1 | Время | Транзакция 2 | Состояние счета Юры |
|---|---|---|---|---|
500 ₽ | SELECT balance FROM accounts WHERE name 'Igor'; | t1 | - | 0 ₽ |
400 ₽ | UPDATE accounts SET balance = balance - 100.00 WHERE name 'Igor'; | t2 | - | 0 ₽ |
400 ₽ | UPDATE accounts SET balance = balance + 100.00 WHERE name 'Yura'; | t3 | SELECT balance FROM accounts WHERE name 'Yura'; | 100 ₽ |
400 ₽ | - | t4 | UPDATE accounts SET balance = balance + 100.00 WHERE name 'Yura'; | 0 ₽ |
500 ₽ | Происходит ошибка. Транзакция откатывается | t5 | - | 0 ₽ |
За сутки таких инцидентов может произойти множество, что не понравится банку.
Неповторяющееся чтение
Запрос с одинаковыми условиями в рамках одной транзакции дает разные результаты.
Снова зовем на помощь Игоря и Юру. У Игоря на счету лежит 500 ₽. Он решает перевести Юре 100 ₽. Транзакция проверяет состояние счета Игоря и видит на нем 500 ₽, поэтому решает, что списание возможно. Игорь забыл, что пользуется сервисом по подписке, который ежемесячно списывает со счета 500 ₽. Начинается вторая транзакция, которая тоже проверяет состояние счета, обнуляет его и завершается.
Если бы первая транзакция решила повторно проверить состояние счета, то обнаружила бы, что теперь на счету у Игоря нет денег. Тем не менее, она уже приняла решение о списании и завершила свое выполнение. Баланс Игоря стал отрицательным.
Состояние счета Игоря | Транзакция 1 | Время | Транзакция 2 |
|---|---|---|---|
500 ₽ | SELECT balance FROM accounts WHERE name 'Igor'; | t1 | - |
500 ₽ | - | t2 | SELECT balance FROM accounts WHERE name 'Igor'; |
0 ₽ | - | t3 | UPDATE accounts SET balance = balance - 500.00 WHERE name 'Igor'; |
0 ₽ | - | t4 | COMMIT |
-100 ₽ | UPDATE accounts SET balance = balance - 100.00 WHERE name 'Igor'; | t5 | - |
-100 ₽ | UPDATE accounts SET balance = balance + 100.00 WHERE name 'Yura'; | t6 | - |
Фантомное чтение
В результате одинакового запроса появляются и исчезают записи, которые изменяет другая транзакция.
Допустим, по правилам банка, которым пользуется Игорь, клиент не может совершать более 50 операций в сутки. Сегодня Игорь был настолько щедрым, что отправил по 100 ₽ уже 49 людям. Игорь решает перевести 100 ₽ Юре. Транзакция считает количество операций Игоря за сутки и получает результат — 49 операций. Следом Игорь решает перевести 100 ₽ еще и Денису. Вторая транзакция считает количество операций за сутки и получает тот же результат. Первая транзакция завершается.
Если бы вторая транзакция снова проверила количество операций, то получила бы результат — 50 операций, но она уже выполняет перевод Денису. В итоге, Игорь превысил лимит операций за сутки.
Количество операций Игоря | Транзакция 1 | Время | Транзакция 2 | Количество операций Игоря |
|---|---|---|---|---|
49 | SELECT count(*) FROM operations_history WHERE name 'Igor'; | t1 | - | 49 |
49 | UPDATE accounts SET balance = balance - 100.00 WHERE name 'Igor'; | t2 | SELECT count(*) FROM operations_history WHERE name 'Igor'; | 49 |
50 | INSERT INTO operations_history VALUES (...); | t3 | - | 49 |
50 | COMMIT | t4 | - | 50 |
50 | - | t5 | UPDATE accounts SET balance = balance - 100.00 WHERE name 'Igor'; | 50 |
50 | - | t6 | INSERT INTO operations_history VALUES (...); | 51 |
51 | - | t7 | COMMIT | 51 |
Потерянное обновление
Две параллельные транзакции изменяют одни и те же данные, в итоге одно из изменений теряется.
Юра и Денис поняли, что Игорь перевел им больше, чем нужно. Поэтому решили вернуть Игорю по 100 ₽. У Игоря на счете лежит 500 ₽. Первым перевод совершает Юра. Транзакция проверяет баланс Игоря и получает 500 ₽. Затем прибавляет к нему 100 ₽. В итоге на счете лежит 600 ₽. В то же время перевод совершает Денис. Вторая транзакция проверяет баланс Игоря и получает 500 ₽, так как первая еще выполняется. Затем она прибавляет к нему 100 ₽. На счете снова лежит 600 ₽. Игорю не дошло 100 ₽.
Состояние счета Игоря | Транзакция 1 | Время | Транзакция 2 | Состояние счета Игоря |
|---|---|---|---|---|
500 ₽ | SELECT balance FROM accounts WHERE name 'Igor'; | t1 | SELECT balance FROM accounts WHERE name 'Igor'; | 500 ₽ |
600 ₽ | UPDATE accounts SET balance = balance + 100.00 WHERE name 'Igor'; | t2 | UPDATE accounts SET balance = balance + 100.00 WHERE name 'Igor'; | 600 ₽ |
600 ₽ | COMMIT | t3 | COMMIT | 600 ₽ |
Требования ACID
Требования ACID — основа обеспечения надежности и целостности данных при одновременном выполнении транзакций.
ACID — это акроним, который включает в себя четыре требования:
1) Атомарность. Транзакция — атомарная единица работы. Если одна часть транзакции не может быть выполнена (например, из-за сбоя), то все изменения, которые сделаны другими частями транзакции, откатываются. Нет промежуточных состояний.
2) Согласованность. Все ограничения целостности базы данных должны быть соблюдены в конце каждой транзакции. Если транзакция не удовлетворяет ограничениям целостности, она откатывается. База данных остается в прежнем состоянии.
3) Изолированность. Изменения, внесенные внутри одной транзакции, не видны другим до ее завершения. Это предотвращает конфликты между транзакциями, когда они пытаются получить доступ к одним и тем же данным одновременно.
4) Долговечность. Данные, которые записаны в базу данных при успешной транзакции, должны быть устойчивыми к сбоям. Данные должны оставаться неизменными даже после перезапуска системы.
Уровни изоляции баз данных
Уровни изоляции баз данных стандартны. Они определяют как одна транзакция взаимодействует с другими в контексте параллельного выполнения. Изоляции определяют, какие виды аномалий могут возникнуть при параллельной обработке транзакций и насколько обеспечивается изоляция между транзакциями.
Ниже представлена таблица, в которой указаны уровни изоляции и устранение аномалий :
Уровень изоляции | Грязное чтение | Неповторяющееся чтение | Фантомное чтение | Потерянное обновление |
|---|---|---|---|---|
READ UNCOMMITED | СУБД предотвращает | Может произойти | Может произойти | Может произойти |
READ COMMITED | СУБД предотвращает | СУБД предотвращает | Может произойти | Может произойти |
REPEATABLE READ | СУБД предотвращает | СУБД предотвращает | СУБД предотвращает | Может произойти |
SERIALIZABLE | СУБД предотвращает | СУБД предотвращает | СУБД предотвращает | СУБД предотвращает |
Read uncommited (чтение незафиксированных данных)
Транзакции видят незафиксированные изменения других переводов. Транзакция может читать данные, которые начали меняться из-за других переводов, но еще не закончили. Этот уровень обеспечивает максимальную производительность, но низкую изоляцию и может привести к проблемам с целостностью данных.
Read committed (чтение фиксированных данных)
Гарантирует, что транзакции видят только зафиксированные изменения других переводов. Транзакции на этом уровне могут видеть изменения в реальном времени, но только после того, как они были зафиксированы. Это предотвращает грязное чтение, но может вызвать неповторяемые чтения.
Repeatable read (повторяющееся чтение)
Каждая транзакция видит состояние данных с момента начала и в течение всей жизни. Это предотвращает неповторяемые чтения, но не фантомные чтения.
Serializable (упорядочиваемость)
Обеспечивает самую высокую изоляцию. Гарантирует, что транзакции не могут взаимодействовать друг с другом. Транзакции выполняются последовательно, исключается параллелизм. Это предотвращает неповторяемые чтения и фантомные чтения.
Заключение
В мире современных баз данных обеспечить надежность, целостность и согласованность данных — задача первостепенной важности. Транзакции и требования ACID играют ключевую роль в достижении этой цели. Обеспечивают правильное выполнение операций в базе данных и гарантируют надежность даже при одновременном выполнении множества операций.
Уровни изоляции транзакций предоставляют гибкие инструменты для балансировки между целостностью данных и производительностью. Выбор уровня изоляции зависит от требований приложения и является важным аспектом при проектировании надежных информационных систем.
Влияние транзакций на базы данных и их понимание — ключевой аспект. Разработчики и архитекторы стремятся создать приложения, которые способны управлять данными и обеспечивать непрерывную работу системы.
