1.事务

事务它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作。

1.1目的

  • 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  • 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

当事务被提交给了数据库管理系统（DBMS），则 DBMS 需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要回滚，回到事务执行前的状态；同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。

1.2属性

事务具有以下四个标准属性，通常根据首字母缩写为 ACID：

  • 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  • 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
  • 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

  • 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

    1.3事务控制

    1.3.1开始一个事务 BEGIN TRANSACTION

  • COMMIT：事务确认，或者可以使用 END TRANSACTION 命令。

  • ROLLBACK：事务回滚。

事务控制命令只与 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。
事务启动后通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。

1.3.2 COMMIT命令

COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令，即确认事务。

1.3.3 ROLLBACK 命令

ROLLBACK 命令是用于撤消尚未保存到数据库的事务命令，即回滚事务。

1.4示例

  1. --以company表为操作对象
  3. --现在开始一个事务，并从表中删除age=25的记录，然后ROLLBACK命令撤消所有的更改
  4. runoobdb=# BEGIN;
  5. BEGIN
  6. runoobdb=# DELETE FROM company WHERE age = 25;
  7. DELETE 2
  9. runoobdb=# SELECT * FROM COMPANY;
  10.  id | name  | age |                      address                       | salary
  11. ----+-------+-----+----------------------------------------------------+--------
  12.   1 | Paul  |  32 | California                                         |  20000
  13.   3 | Teddy |  23 | Norway                                             |  20000
  14.   5 | David |  27 | Texas                                              |  85000
  15.   6 | Kim   |  22 | South-Hall                                         |  45000
  16.   7 | James |  24 | Houston                                            |  10000
  17. (5 行记录)
  20. runoobdb=# ROLLBACK;
  21. ROLLBACK
  22. runoobdb=# SELECT * FROM COMPANY;
  23.  id | name  | age |                      address                       | salary
  24. ----+-------+-----+----------------------------------------------------+--------
  25.   1 | Paul  |  32 | California                                         |  20000
  26.   2 | Allen |  25 | Texas                                              |  15000
  27.   3 | Teddy |  23 | Norway                                             |  20000
  28.   4 | Mark  |  25 | Rich-Mond                                          |  65000
  29.   5 | David |  27 | Texas                                              |  85000
  30.   6 | Kim   |  22 | South-Hall                                         |  45000
  31.   7 | James |  24 | Houston                                            |  10000
  32. (7 行记录)
  35. --现在开始另一个事务，从表中删除age = 25的记录，然后使用COMMIT命令提交所有修改
  36. runoobdb=# BEGIN;
  37. BEGIN
  38. runoobdb=# DELETE FROM company WHERE age = 25;
  39. DELETE 2
  40. runoobdb=# SELECT * FROM company;
  41.  id | name  | age |                      address                       | salary
  42. ----+-------+-----+----------------------------------------------------+--------
  43.   1 | Paul  |  32 | California                                         |  20000
  44.   3 | Teddy |  23 | Norway                                             |  20000
  45.   5 | David |  27 | Texas                                              |  85000
  46.   6 | Kim   |  22 | South-Hall                                         |  45000
  47.   7 | James |  24 | Houston                                            |  10000
  48. (5 行记录)
  50. --提交
  51. runoobdb=# COMMIT;
  52. COMMIT
  54. --age = 25的数据已被删除
  55. runoobdb=# SELECT * FROM company;
  56.  id | name  | age |                      address                       | salary
  57. ----+-------+-----+----------------------------------------------------+--------
  58.   1 | Paul  |  32 | California                                         |  20000
  59.   3 | Teddy |  23 | Norway                                             |  20000
  60.   5 | David |  27 | Texas                                              |  85000
  61.   6 | Kim   |  22 | South-Hall                                         |  45000
  62.   7 | James |  24 | Houston                                            |  10000
  63. (5 行记录)

2.锁（LOCK）

锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。

2.1分类

排它锁（Exclusive Locks）：如果数据对象加上排它锁，则其他的事务不能对它读取和修改
共享锁（Share Locks）：如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。

2.2语法

LOCK [ TABLE ]
name
 IN
lock_mode

--name：要锁定的现有表的名称（可选模式限定）。
  --如果只在表名之前指定，则只锁定该表。如果未指定，则锁定该表及其所有子表（如果有）。

--lock_mode：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。
  --可能的值是：ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， 
  --SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。

一旦获得了锁，锁将在当前事务的其余时间保持。没有解锁表命令；锁总是在事务结束时释放。

2.3死锁

当两个事务彼此等待对方完成其操作时，可能会发生死锁。为了防止应用程序遇到这个问题，请确保将应用程序设计为以相同的顺序锁定对象。

2.4示例

--以company表为操作对象
runoobdb=# SELECT * FROM company;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
  6 | Kim   |  22 | South-Hall                                         |  45000
  7 | James |  24 | Houston                                            |  10000
(7 行记录)

--现在将COMPANY表锁定为ACCESS EXCLUSIVE模式
runoobdb=# BEGIN;
BEGIN
runoobdb=# LOCK TABLE company IN ACCESS EXCLUSIVE MODE;
LOCK TABLE