Введение
В этой лабораторной работе мы изучим обработку транзакций 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) баз данных.
