SQLitetransactionoverview
transaction is datalibraryoperation basic单位, is 一组atomicity SQLoperation序列, 这些operation要么全部成功执行, 要么全部不执行. in SQLitein, transaction for 于确保data consistency and integrity至关 important , 特别 is in processing complex 业务逻辑时.
transaction basicconcepts
- transaction is a 逻辑工作单元, package含一个 or many 个SQL语句
- transaction具 has ACIDfeatures: atomicity (Atomicity) , consistency (Consistency) , isolation (Isolation) , durability (Durability)
- transaction可以确保data from 一个一致status转换 to 另一个一致status
- transaction可以被submitting (COMMIT) or rollback (ROLLBACK)
transaction ACIDfeatures
ACID is transaction 四个关键features, ensureddatalibraryoperation reliability and consistency.
atomicity (Atomicity)
atomicity确保transactionin 所 has operation要么全部成功执行, 要么全部不执行. such as果transactionin 任何一个operation失败, 整个transaction都会被rollback, datalibrary会restore to transaction开始 before status.
consistency (Consistency)
consistency确保transaction执行 before after , datalibrary from 一个一致status转换 to 另一个一致status. transaction执行不会破 bad datalibrary integrity约束 and 业务规则.
isolation (Isolation)
isolation确保 many 个concurrenttransaction之间相互隔离, 一个transaction 执行不会影响othertransaction 执行. SQLitethroughlockmechanism and 隔离级别来implementationisolation.
durability (Durability)
durability确保一旦transactionsubmitting, 其结果就会永久保存 in datalibraryin, 即使system发生failure也不会loss. SQLitethrough将transactionlog写入disk来implementationdurability.
| features | describes | SQLiteimplementation方式 |
|---|---|---|
| atomicity | 要么全部执行, 要么全部不执行 | transactionlog and rollbackmechanism |
| consistency | from 一致status to 一致status | 约束check and 触发器 |
| isolation | concurrenttransaction相互隔离 | lockmechanism and 隔离级别 |
| durability | submitting after 永久保存 | transactionlog and 写入disk |
SQLitetransactioncommands
SQLiteproviding了以 under transaction相关 commands:
BEGIN TRANSACTION
开始一个 new transaction.
-- 开始transaction
BEGIN TRANSACTION;
BEGIN;
-- 简写形式
COMMIT
submittingtransaction, 将transactionin 所 has 更改保存 to datalibrary.
-- submittingtransaction
COMMIT TRANSACTION;
COMMIT;
-- 简写形式
ROLLBACK
rollbacktransaction, 取消transactionin 所 has 更改.
-- rollbacktransaction
ROLLBACK TRANSACTION;
ROLLBACK;
-- 简写形式
SAVEPOINT
in transactionincreation一个保存点, 用于部分rollback.
-- creation保存点
SAVEPOINT savepoint_name;
-- rollback to 保存点
ROLLBACK TO SAVEPOINT savepoint_name;
-- 释放保存点
RELEASE SAVEPOINT savepoint_name;
transactionusingexample
以 under is 一些SQLitetransaction usingexample:
basictransactionexample
-- 开始transaction
BEGIN TRANSACTION;
-- 执行SQLoperation
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
INSERT INTO transactions (user_id, amount, type) VALUES (1, 100, 'withdrawal');
-- submittingtransaction
COMMIT;
-- such as果出现error, rollbacktransaction
-- ROLLBACK;
带保存点 transactionexample
-- 开始transaction
BEGIN TRANSACTION;
-- 执行第一个operation
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
-- creation保存点
SAVEPOINT before_transfer;
-- 执行第二个operation
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- checkoperation is 否成功
-- such as果失败, rollback to 保存点
-- ROLLBACK TO SAVEPOINT before_transfer;
-- submittingtransaction
COMMIT;
errorprocessingexample
-- 开始transaction
BEGIN TRANSACTION;
-- 执行SQLoperation
INSERT INTO orders (customer_id, total_amount) VALUES (1, 1000);
-- check is 否 has 足够 library存
SELECT quantity INTO @stock FROM products WHERE id = 1;
-- such as果library存不足, rollbacktransaction
-- IF @stock < 5 THEN
-- ROLLBACK;
-- SELECT 'Insufficient stock' AS message;
-- ELSE
-- UPDATE products SET quantity = quantity - 5 WHERE id = 1;
-- COMMIT;
-- SELECT 'Order placed successfully' AS message;
-- END IF;
transaction隔离级别
隔离级别定义了 many 个concurrenttransaction之间 隔离程度, SQLitesupport以 under 隔离级别:
SQLite 隔离级别
| 隔离级别 | describes | SQLitesupportcircumstances |
|---|---|---|
| READ UNCOMMITTED | 可以读取未submitting data (脏读) | 不support, 自动提升 for READ COMMITTED |
| READ COMMITTED | 只能读取已submitting data | support |
| REPEATABLE READ | 确保 in transaction期间 many 次读取同一data时结果一致 | support |
| SERIALIZABLE | 完全隔离, transaction串行执行 | 默认级别 |
设置隔离级别
-- 设置隔离级别 for READ COMMITTED
PRAGMA read_unsubmittingted = 1;
-- or 者 in 开始transaction时指定
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- restore默认隔离级别
PRAGMA read_unsubmittingted = 0;
transactionbest practices
遵循以 under best practices可以improvingtransaction performance and reliability:
保持transaction简 short
- transaction应该尽可能简 short , reducinglock定时间
- 避免 in transactionin执行 long 时间run operation
- 避免 in transactioninetc.待user输入
合理using保存点
- for 于 complex transaction, 可以using保存点implementation部分rollback
- 保存点可以improvingtransaction flexible性
- 注意保存点会增加transaction complex 性, 应谨慎using
errorprocessing
- in transactioninimplementation适当 errorprocessing
- in 出现error时及时rollbacktransaction
- 记录transaction执行过程in errorinformation
performance考虑
- for 于 big 量dataoperation, usingtransaction可以improvingperformance
- 批量operation时, 将 many 个SQL语句放 in 一个transactionin执行
- 避免 long 时间持 has lock, 影响othertransaction 执行
transactionperformanceoptimization
transaction performance for 于datalibraryapplication程序至关 important , 以 under is 一些optimizationtransactionperformance method:
批量operation
-- 不usingtransaction ( slow )
INSERT INTO users (name, email) VALUES ('user1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('user2', 'user2@example.com');
INSERT INTO users (name, email) VALUES ('user3', 'user3@example.com');
-- usingtransaction ( fast )
BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('user1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('user2', 'user2@example.com');
INSERT INTO users (name, email) VALUES ('user3', 'user3@example.com');
COMMIT;
避免 long 时间transaction
- long 时间transaction会占用datalibraryresource
- long 时间transaction会增加lock定conflict 可能性
- 将 big 型transaction拆分 for many 个 small 型transaction
using合适 隔离级别
- 根据application程序 requirements选择合适 隔离级别
- for 于读取operation, using较 low 隔离级别
- for 于写入operation, using较 high 隔离级别
实践case: 银行转账system
现 in 让我们through一个practicalcase来练习SQLitetransaction.
caserequirements
creation一个银行转账system, package含以 under functions:
- from 一个account向另一个account转账
- 记录转账交易
- 确保转账 atomicity and consistency
- processing转账过程in error
solution
-- 1. creation表structure
CREATE TABLE IF NOT EXISTS accounts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
balance REAL NOT NULL DEFAULT 0.0
);
CREATE TABLE IF NOT EXISTS transactions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
from_account_id INTEGER,
to_account_id INTEGER,
amount REAL NOT NULL,
transaction_date DATETIME DEFAULT CURRENT_TIMESTAMP,
status TEXT DEFAULT 'completed',
FOREIGN KEY (from_account_id) REFERENCES accounts(id),
FOREIGN KEY (to_account_id) REFERENCES accounts(id)
);
-- 2. 插入testdata
INSERT INTO accounts (name, balance) VALUES ('张三', 10000.0);
INSERT INTO accounts (name, balance) VALUES ('李四', 5000.0);
-- 3. implementation转账functions
-- 开始transaction
BEGIN TRANSACTION;
-- check转出account余额
SELECT balance INTO @from_balance FROM accounts WHERE id = 1;
-- check余额 is 否足够
-- IF @from_balance < 2000 THEN
-- ROLLBACK;
-- SELECT 'Insufficient balance' AS message;
-- ELSE
-- 执行转账operation
UPDATE accounts SET balance = balance - 2000 WHERE id = 1;
UPDATE accounts SET balance = balance + 2000 WHERE id = 2;
-- 记录交易
INSERT INTO transactions (from_account_id, to_account_id, amount) VALUES (1, 2, 2000);
-- submittingtransaction
COMMIT;
SELECT 'Transfer successful' AS message;
-- END IF;
-- 4. 查看结果
SELECT * FROM accounts;
SELECT * FROM transactions;
互动练习
请completion以 under transaction练习:
- creation一个订单system, package含订单表 and library存表
- implementation under 单functions, includingreducinglibrary存 and creation订单
- usingtransaction确保 under 单operation atomicity
- processinglibrary存不足 circumstances
- using保存点implementation部分rollback
提示: using合适 表structure and transactionprocessing逻辑.
本课时summarized
in 本课时in, 我们Learning了SQLite transactionprocessingtechniques:
- understanding了transaction basicconcepts and important 性
- Learning了transaction ACIDfeatures: atomicity, consistency, isolation and durability
- Master了SQLitetransaction basiccommands: BEGIN, COMMIT, ROLLBACK and SAVEPOINT
- Learning了transaction 隔离级别及其 in SQLitein implementation
- Understand了transaction best practices and performanceoptimizationmethod
- through银行转账system case练习了transaction practicalapplication