Sqlite 简明教程
SQLite - Transactions
事务是对数据库执行的一个工作单元。事务是按照逻辑顺序完成的工作单元或序列,无论是由用户手动完成还是由某种数据库程序自动完成。
A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.
事务是将一个或多个更改传播到数据库的过程。例如,如果你正在创建、更新或从表中删除记录,那么你就在对表执行事务。控制事务以确保数据完整性和处理数据库错误非常重要。
A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.
实际上,你将把许多 SQLite 查询合并到一个组中,并将全部作为事务的一部分一起执行。
Practically, you will club many SQLite queries into a group and you will execute all of them together as part of a transaction.
Properties of Transactions
事务具有以下四种标准属性,通常称为 ACID。
Transactions have the following four standard properties, usually referred to by the acronym ACID.
-
Atomicity − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.
-
Consistency − Ensures that the database properly changes states upon a successfully committed transaction.
-
Isolation − Enables transactions to operate independently of and transparent to each other.
-
Durability − Ensures that the result or effect of a committed transaction persists in case of a system failure.
Transaction Control
以下是用于控制事务的命令:
Following are the following commands used to control transactions:
-
BEGIN TRANSACTION − To start a transaction.
-
COMMIT − To save the changes, alternatively you can use END TRANSACTION command.
-
ROLLBACK − To rollback the changes.
事务控制命令仅与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。在创建表或删除表时不能使用这些命令,因为这些操作会在数据库中自动提交。
Transactional control commands are only used with DML commands INSERT, UPDATE, and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.
BEGIN TRANSACTION Command
可以使用 BEGIN TRANSACTION 或只需 BEGIN 命令来启动事务。此类事务通常会持续到遇到下一个 COMMIT 或 ROLLBACK 命令。但是,如果数据库关闭或发生错误,事务也会回滚。以下是启动事务的简单语法。
Transactions can be started using BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. However, a transaction will also ROLLBACK if the database is closed or if an error occurs. Following is the simple syntax to start a transaction.
BEGIN;
or
BEGIN TRANSACTION;
COMMIT Command
COMMIT 命令是用于将事务调用的更改保存到数据库的事务命令。
COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
COMMIT 命令会将所有事务保存到数据库,因为上次的 COMMIT 或 ROLLBACK 命令。
COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
以下是 COMMIT 命令的语法。
Following is the syntax for COMMIT command.
COMMIT;
or
END TRANSACTION;
ROLLBACK Command
ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令。
ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.
ROLLBACK 仅可用于撤消上次 COMMIT 或 ROLLBACK 命令发出以来的事务。
ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
以下是 ROLLBACK 命令的语法。
Following is the syntax for ROLLBACK command.
ROLLBACK;
Example
考虑 COMPANY 表和以下记录。
Consider COMPANY table with the following records.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
现在,让我们启动事务并从表中删除 age = 25 的记录。然后,使用 ROLLBACK 命令撤消所有更改。
Now, let’s start a transaction and delete records from the table having age = 25. Then, use ROLLBACK command to undo all the changes.
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;
现在,如果您检查 COMPANY 表,它仍然有以下记录 −
Now, if you check COMPANY table, it still has the following records −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
让我们启动另一个事务并从表中删除 age = 25 的记录,最后我们使用 COMMIT 命令提交所有更改。
Let’s start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes.
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;
如果您现在检查 COMPANY 表,仍然有以下记录 −
If you now check COMPANY table is still has the following records −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0