[SQL系列] 从头开始学PostgreSQL 事务锁子查询

最新推荐文章于 2024-09-12 17:49:07 发布

Edward.W

最新推荐文章于 2024-09-12 17:49:07 发布

阅读量623

点赞数

分类专栏： sql 大数据文章标签： sql postgresql 数据库

本文链接：https://blog.csdn.net/u013379032/article/details/131841058

版权

sql 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

大数据

7 篇文章 0 订阅

订阅专栏

[SQL系列] 从头开始学PostgreSQL 索引修改视图_Edward.W的博客-CSDN博客https://blog.csdn.net/u013379032/article/details/131818865

事务

事务是一系列逻辑相关的数据库操作，可以作为一个整体进行操作或者回滚。事务通常会包含一个序列的读或者写操作，通过提交给数据库管理系统，从而保证事务操作完成并且永久保存在数据库中。如果没有操作完成，则整个事务都会回滚，不会造成其他影响。

属性

我们日常所说的ACID指的就是它的属性：

        1. 原子性 (Atomicity)：事务中的所有操作在提交或回滚时都是作为一个整体进行的，不会中途停止或被中断。这意味着事务中的每个操作都会被完全执行，要么全部成功，要么全部失败。
        2. 一致性 (Consistency)：事务在执行过程中必须保持数据一致性。这意味着在事务开始之前和结束之后，数据都必须处于一致状态。在事务执行过程中，如果数据发生了变化，那么这些变化必须在事务结束时被正确地应用到数据库中。
        3. 隔离性 (Isolation)：事务的执行不能被其他事务干扰。这意味着在事务执行期间，其他事务无法访问事务所使用的数据或资源，从而避免了并发执行时的数据冲突和脏读等问题。
        4. 持久性 (Durability)：事务的执行结果必须永久地保存在数据库中。这意味着即使在事务提交之后，事务的结果也不能被丢失或更改。

控制

在PostgreSQL中，事务是通过Begin、Commit和Rollback命令用来创建，提交和回滚。EGIN 命令用于开始一个新的事务，COMMIT 命令用于提交当前的事务，ROLLBACK 命令用于回滚当前的事务。在事务期间，可以执行各种数据库操作，例如插入、更新、删除数据，查询数据等等。

通过begin 和 commit来标记一整个事务，中间是事务的具体操作命令。

BEGIN;  
SELECT id FROM users WHERE username = 'john';  
INSERT INTO orders (user_id, product, quantity, price)   
VALUES (1, 'apple', 2, 2.50);  
INSERT INTO orders (user_id, product, quantity, price)   
VALUES (1, 'orange', 1, 1.50);  
COMMIT;

Commit命令可以把事务调用的更改保存到数据库中，也作为一整个事务的结尾。

而当我们提交了错误的事务的时候，可以通过rollback撤销尚未保存到数据库的事务命令。

BEGIN;

DELETE FROM ORDERS WHERE quality = 1;

ROLLBACK;

锁

数据库中，锁的存在是为了保证数据库的一致性，在并发较高的数据库中，可以避免同时修改表产生的冲突。

一般来讲，有两种基本的锁：排它锁和共享锁。

如果数据对象加上了排他锁，那么其他的事务既不能读也不能修改它。

如果数据对象加上了共享锁，那么该对象可以被读，但是不能被修改。

还有更细粒度的行级锁：

1. 行共享锁，用于Select ... for update语句，允许多个并发事务访问同一行数据，但是只有一个事务可以更新该行数据。

2. 行独占锁，用于insert, update和delete语句，在事务完成前，其他事务无法访问该行数据

示例：

#共享锁
testdb=# begin;
BEGIN
testdb=*# lock table students in access share mode;
LOCK TABLE
testdb=*# commit;
COMMIT

#独占锁

testdb=# begin;
BEGIN
testdb=*# lock table students in access exclusive mode;
LOCK TABLE
testdb=*# commit;
COMMIT

锁必须加在事务中，不能随随便便的就写了lock，否决则会报错。

子查询

子查询就是一种嵌套在另一个查询中的查询，反应会一个结果集，用于主查询中的条件或者数据比对。

子查询还可以嵌套多层，用于实现复杂数据操作和数据过滤，从而提高查询效率和减少数据冗余。

举个例子：



#原始表格
testdb=# select * from engineer;
 id | name  | age | gender |    address    |     created_at      
----+-------+-----+--------+---------------+---------------------
  1 | John  |  30 | M      | New York      | 2023-02-18 10:00:00
  2 | Mary  |  25 | F      | Los Angeles   | 2023-02-18 10:00:00
  3 | Peter |  35 | M      | Chicago       | 2023-02-18 10:00:00
  4 | Jane  |  28 | F      | San Francisco | 2023-02-18 10:00:00
  5 | Bob   |  40 | M      | Boston        | 2023-02-18 10:00:00
  6 |       |     | F      | Washington DC | 2023-02-18 10:00:00
  7 |       |     | M      | Atlanta       | 2023-02-18 10:00:00
  8 |       |     | F      | Miami         | 2023-02-18 10:00:00
  9 |       |     | M      | Philadelphia  | 2023-02-18 10:00:00
 10 |       |     | F      | Dallas        | 2023-02-18 10:00:00
(10 rows)

#经过了子查询的表格，子查询筛选了所有gender是'M'的，主查询将它都输出来
testdb=# select * from engineer where id in (select id from engineer where gender = 'M');
 id | name  | age | gender |   address    |     created_at      
----+-------+-----+--------+--------------+---------------------
  1 | John  |  30 | M      | New York     | 2023-02-18 10:00:00
  3 | Peter |  35 | M      | Chicago      | 2023-02-18 10:00:00
  5 | Bob   |  40 | M      | Boston       | 2023-02-18 10:00:00
  7 |       |     | M      | Atlanta      | 2023-02-18 10:00:00
  9 |       |     | M      | Philadelphia | 2023-02-18 10:00:00
(5 rows)


#我们再来一个
#原始表格
testdb=# select * from students;
 id |  name  | age | gender | class_id 
----+--------+-----+--------+----------
  1 | 张三   |  18 | 男     |        1
  2 | 李四   |  19 | 女     |        1
  3 | 王五   |  20 | 男     |        2
  4 | 赵六   |  18 | 女     |        2
  5 | 陈七   |  19 | 男     |        3
  6 | 孙八   |  20 | 女     |        3
  7 | 周九   |  18 | 男     |        4
  8 | 吴十   |  19 | 女     |        4
  9 | 郑十一 |  20 | 男     |        5
 10 | 王十二 |  18 | 女     |        5
 11 | 何一   |  23 | 女     |        5
 12 | 和二   |  32 | 男     |        5
(12 rows)

#作为列选项
testdb=# select name, (select count(*) from students where students.age > 20) as male_count from students;
  name  | male_count 
--------+------------
 张三   |          2
 李四   |          2
 王五   |          2
 赵六   |          2
 陈七   |          2
 孙八   |          2
 周九   |          2
 吴十   |          2
 郑十一 |          2
 王十二 |          2
 何一   |          2
 和二   |          2
(12 rows)

当然，除了select，也可以用于delete和Update，总之，子查询，实际上就是一种嵌套查询方式。