PostgreSQL08-并行查询_事务_并发控制

8.并行查询&事务&并发控制

并行查询

并行查询相关配置参数

max_worker_processes   最大后台进程数，默认8。备库参数值>=主库，重启DB生效
max_parallel_workers   支持的最大并行查询进程数，默认8
max_parallel_workers_per_gather   允许启用的并行进程数，默认2，设置0表示禁用并行查询
max_worker_processes > max_parallel_workers > max_paralllel_workers_per_gather

parallel_setup_cost            优化器启动并行进程的成本，默认1000
parallel_tuple_cost             优化器通过并行查询处理一行数据的成本，默认0.1
min_parallel_table_scan_size         能启用并行查询的表的最小空间，默认8M
min_parallel_index_scan_size        能启用并行查询的索引的最小大小，默认512KB
force_parallel_mode                      on/off  强制开启并行查询，生产环境off

并行扫描

explain analyze select * from output where id=10000 来分析

并行顺序扫描（全表扫描）
一张表没有索引，就会用这种模式

并行索引扫描
表有索引

并行index-only扫描
只扫描索引，仅根据索引就能获得需要检索的数据，比如slect count(*)

并行bitmap heap扫描
where 语句中有 or，可能用到这种扫描，将 Bitmap Index 扫描获取的索引项合并后，回表查询

释放缓存

sync
echo 1 > /proc/sys/vm/drop_caches

---

事务

并发引发的现象

脏读：一个事务读取了第二个事务中 已经修改但还未提交的数据。（DML）
当第二个事务不提交并执行ROLLBACK后，第一个事务所读取到的数据是不正确的，这种读现象称作脏读

不可重复读：同一事务中，读取两次数据不一致。
当一个事务第一次读取数据之后，被读取的数据被另一个己提交的事务进行了修改 ，事务再次读取时两次查询的结果不一致，这种读现象称为不可重复读

幻读：可以理解为不可重复读的特殊情况，即将修改数据改为插入或删除数据

序列化异常：书里没写

事务隔离级别

为了避免事务与事务之间并发执行引发的副作用，而引入事务隔离级别

隔离级别越高，越能保障数据的完整性和一致性，越消耗资源，性能越低

Read Uncommitted （读未提交）：所有事务都可以看到其他未提交事务的执行结果
Read Committed   （读己提交）：一个事务只能看见已经提交事务对关联数据所做的改变。PG的默认隔离级别
Repeatable Read  （可重复读）：确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
Serializable     （可序列化）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突。
它是在每个读的数据行上加上共享锁。该级别可能导致大量的超时现象和锁竞争

查看和设置全局事务隔离级别

查看
select name,setting from pg_settings where name = 'default_transaction_isolation';
或
select current_setting('default_transaction_isolation');

设置
vim postgresql.conf,修改default_transaction_isolation,重启数据库
或
alter system set default_transaction_isolation to 'repeatable read';
select pg_reload_conf();

查看和设置当前会话事务隔离级别

查看
show transaction_isolation;
或
select current_setting('transaction_isolation');

设置
set session characteristics as transaction isolation level read uncommitted;

characteristics  特征

设置当前事务隔离级别

在启动事务的同时设置事务隔离级别

start transaction isolation level read uncommitted;
或
begin  isolation level read uncommitted read write;

start transaction 和 begin 都是开启一个事务，具有相同的功能

---

并发控制

基于锁的并发控制    （Lock-Based Concurrency Control, LBCC)
基于多版本的并发控制 （Multi-Version Concurrency Control, MVCC)
乐观并发控制（乐观锁, Optimistic Concurrency Control, OCC）
悲观并发控制（悲观锁, Pessimistic Concurrency Control, PCC）

基于锁的并发控制

排它锁（X锁）：被加锁的对象只能被持有锁的事务读取和修改，其他事务无法在该对象上加其他锁，也不能读取和修改该对象
共享锁（S锁）：被加锁的对象可以被持锁事务读取，但是不能被修改，其他事务也可以在上面再加共享锁
PG特有的 Advisory Lock（咨询锁）
锁粒度（ Granularity ）： 封锁的对象可以是逻辑单元，也可以是物理单元
    逻辑单元：属性值、属性值的集合、元组、关系、索引项、整个索引项甚至整个数据库
    物理单元：页（数据页或索引页）、物理记录等
封锁策略：是一组规则 ，这些规则阐明了事务何时对数据项进行加锁和解锁，也称为封锁协议（ Locking Protocol ）

采用封锁策略，一次只能执行一个事务，所以只会产生串行调度，因此基于锁的并发控制机制导致性能低下，并发程度低。

基于多版本的并发控制（MVCC）

一般把基于锁的并发控制机制称成为悲观机制，而把 MVCC 机制称为乐观机制 。 这是因为锁机制是一种预防性的机制，读会阻塞写，写也会阻塞读，当封锁粒度较大，时间较长时并发性能就不会太好；而 MVCC 是一种后验性的机制，读不阻塞写 ， 写也不阻塞读，等到提交的时候才检验是否有冲突，由于没有锁，所以读写不会相互阻塞，避免了大粒度和长时间的锁定，能更好地适应对读的响应速度和并发性要求高的场景，大大提升了并发性能

PG为每一个事务设置唯一的事务ID，称为 xid。

创建新的快照时（有数据修改时会创建快照），将收集当前正在执行的xid和已提交的最大xid。

在PG中 ，每个元组（行记录）有4个隐藏列，xmin、xmax、cmin、cmax。

cmin 和 cmax 用于同一个事务中的可见性判断。

xmin 和 xmax 分别用来记录插入和删除该元组的命令的xid。

通过pageinspect观察MVCC

create extension pageinspect;   创建扩展
\dx+ pageinspect                查看该扩展有哪些函数

表膨胀问题

使用vacuum或autovacuum命令，命令会阻塞现有数据库操作

pg_repack插件包
它是一个可以在线重建表和索引的扩展。
它会在数据库中建立一个与目标表一样的临时表，将目标表中的数据COPY到临时表，并在临时表上建立与目标表一样的索引，
然后通过重命名的方式用临时表替换目标表。
yum install pg_repack10
create extension pg_repack;
/usr/pgsql-10/bin/pg_repack -t my_table -j 2 -D -k -h 192.168.13.113 -U postgres -d my_db