Обработка транзакций в SQLite (SQLite Transaction Handling)

Введение

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

Эта лабораторная работа проведет вас через процесс запуска транзакции с помощью BEGIN TRANSACTION, внесения изменений в базу данных, а затем постоянного сохранения этих изменений с помощью оператора COMMIT. Вы также узнаете, как отменить изменения с помощью ROLLBACK и использовать SAVEPOINT для более детального контроля. Этот практический опыт укрепит ваше понимание того, как транзакции гарантируют атомарность (atomicity), согласованность (consistency), изолированность (isolation) и долговечность (durability) (ACID-свойства) в SQLite.

Создание базы данных и таблицы (Create a Database and Table)

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

Сначала откройте свой терминал в LabEx VM. Ваш путь по умолчанию - /home/labex/project.

Теперь давайте создадим базу данных SQLite с именем mydatabase.db. Выполните следующую команду, чтобы создать файл базы данных и открыть инструмент командной строки SQLite:

sqlite3 mydatabase.db

Вы увидите приглашение, указывающее на то, что вы находитесь внутри оболочки SQLite:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Далее создайте таблицу с именем users для хранения основной информации о пользователях. Эта таблица будет иметь три столбца: id (уникальный идентификатор), name и balance. Введите следующую SQL-команду в командной строке sqlite> и нажмите Enter:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

Эта команда настраивает таблицу users, где:

• id - это целое число, которое служит первичным ключом (primary key) для каждого пользователя.
• name - это текстовое поле, представляющее имя пользователя.
• balance - это вещественное число, представляющее баланс счета пользователя.

Теперь вставьте некоторые начальные данные в таблицу users:

INSERT INTO users (name, balance) VALUES ('Alice', 100.0);
INSERT INTO users (name, balance) VALUES ('Bob', 200.0);

Эти команды добавляют двух пользователей, Alice и Bob, с начальными балансами 100.0 и 200.0 соответственно.

Чтобы подтвердить, что данные были добавлены правильно, выполните эту команду, чтобы просмотреть все записи в таблице:

SELECT * FROM users;

Ожидаемый результат:

1|Alice|100.0
2|Bob|200.0

Этот вывод показывает id, name и balance для каждой записи. Команда SELECT * извлекает все столбцы из указанной таблицы.

Начало и фиксация транзакции (Begin and Commit a Transaction)

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

Чтобы начать транзакцию, используйте команду BEGIN TRANSACTION:

BEGIN TRANSACTION;

Эта команда сообщает SQLite о необходимости начать отслеживание изменений, но не применять их к базе данных окончательно, пока вы явно не зафиксируете транзакцию (commit the transaction).

Теперь давайте переведем 50.0 от Alice к Bob:

UPDATE users SET balance = balance - 50.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 50.0 WHERE name = 'Bob';

Эти команды обновляют балансы Alice и Bob в рамках транзакции. Баланс Alice уменьшается на 50.0, а баланс Bob увеличивается на 50.0.

Чтобы сохранить изменения, используйте команду COMMIT:

COMMIT;

Эта команда фиксирует транзакцию, делая изменения постоянными.

Проверьте изменения, снова запросив таблицу:

SELECT * FROM users;

Ожидаемый результат:

1|Alice|50.0
2|Bob|250.0

Этот вывод подтверждает, что транзакция была успешно зафиксирована, и балансы Alice и Bob были обновлены соответствующим образом.

Откат транзакции (Rollback a Transaction)

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

Сначала начните новую транзакцию:

BEGIN TRANSACTION;

Теперь давайте попытаемся перевести 100.0 от Alice к Bob, но на этот раз мы смоделируем состояние ошибки:

UPDATE users SET balance = balance - 100.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 100.0 WHERE name = 'Bob';

Поскольку у Alice всего 50.0, первое обновление приведет к отрицательному балансу. Хотя SQLite не применяет ограничения по умолчанию, мы смоделируем возникновение ошибки после этой операции.

Чтобы отменить изменения, используйте команду ROLLBACK:

ROLLBACK;

Эта команда откатывает транзакцию, отменяя любые изменения, внесенные с момента оператора BEGIN TRANSACTION.

Убедитесь, что изменения были отменены, запросив таблицу users:

SELECT * FROM users;

Ожидаемый результат:

1|Alice|50.0
2|Bob|250.0

Этот вывод подтверждает, что транзакция была успешно отменена (rolled back), и балансы Alice и Bob остались без изменений.

Реализация точек сохранения (Savepoints)

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

Сначала начните новую транзакцию:

BEGIN TRANSACTION;

Создайте точку сохранения с именем savepoint1:

SAVEPOINT savepoint1;

Теперь давайте переведем 20.0 от Alice к Bob:

UPDATE users SET balance = balance - 20.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 20.0 WHERE name = 'Bob';

Создайте еще одну точку сохранения с именем savepoint2:

SAVEPOINT savepoint2;

Давайте добавим нового пользователя с именем 'Charlie' с начальным балансом 300.0:

INSERT INTO users (name, balance) VALUES ('Charlie', 300.0);

Теперь, допустим, мы решили, что добавление Charlie было ошибкой. Мы можем выполнить откат к savepoint1, который отменит оператор INSERT и перевод 20.0 между Alice и Bob:

ROLLBACK TO SAVEPOINT savepoint1;

Проверьте изменения после отката к savepoint1:

SELECT * FROM users;

Ожидаемый результат:

1|Alice|50.0
2|Bob|250.0

Вы должны увидеть, что Charlie отсутствует в таблице, а балансы Alice и Bob вернулись к своим значениям до savepoint1.

Теперь давайте переведем 10.0 от Alice к Bob:

UPDATE users SET balance = balance - 10.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 10.0 WHERE name = 'Bob';

Наконец, зафиксируйте транзакцию (commit the transaction):

COMMIT;

Добавление ограничения для предотвращения отрицательных балансов

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

Поскольку SQLite не поддерживает добавление ограничений к существующим таблицам с помощью ALTER TABLE ADD CONSTRAINT, нам необходимо создать таблицу заново с ограничением. Сначала начните транзакцию, чтобы гарантировать атомарность всех операций:

BEGIN TRANSACTION;

Создайте новую таблицу с той же структурой, но включающую ограничение CHECK:

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL CHECK (balance >= 0)
);

Скопируйте все данные из старой таблицы в новую таблицу:

INSERT INTO users_new SELECT * FROM users;

Удалите старую таблицу:

DROP TABLE users;

Переименуйте новую таблицу в исходное имя:

ALTER TABLE users_new RENAME TO users;

Завершите транзакцию, чтобы сделать изменения постоянными:

COMMIT;

Теперь таблица users имеет ограничение CHECK, которое гарантирует, что столбец balance всегда должен быть больше или равен 0.

Попробуем перевести 1000 с Алисы на Боба, что нарушит ограничение:

BEGIN TRANSACTION;
UPDATE users SET balance = balance - 1000 WHERE name = 'Alice';

На этот раз оператор UPDATE вызовет ошибку, потому что нарушает ограничение CHECK. Вы увидите сообщение об ошибке, подобное: Ошибка: Ограничение CHECK не выполнено: balance >= 0.

Откатите транзакцию:

ROLLBACK;

Проверьте, что изменения были отменены:

SELECT * FROM users;

Ожидаемый результат:

1|Alice|40.0
2|Bob|260.0

Это подтверждает, что ROLLBACK успешно вернул базу данных в исходное состояние до неудачной транзакции.

Резюме

В этой лабораторной работе вы изучили основы обработки транзакций (transaction handling) в SQLite. Вы рассмотрели, как начинать и фиксировать транзакции (commit transactions), откатывать изменения (rollback changes), реализовывать точки сохранения (savepoints) для более детального контроля и добавлять ограничения (constraints) для обеспечения целостности данных (data integrity). Эти навыки имеют решающее значение для создания надежных и отказоустойчивых (robust and reliable) баз данных.