事务与并发控制
当多个事务并发执行时，即使每个单独的事务都正确执行，数据库的一致性也可能被破坏。
为了控制并发事务之间的相互影响，解决并发可能带来的资源争用及数据不一致性问题，数据库的并发控制系统引入了:
基于锁的并发控制机制（Lock-Based Concurrency Control）
基于多版本的并发控制机制MVCC (Multi-Version Concurrency Control）。
事务和并发控制的概念
事务的基本概念和性质
1. 事务是数据库系统执行过程中最小的逻辑单位。
2. 当事务被提交时，数据库管理系统要确保一个事务中的所有操作都成功完成，并且在数据库中永久保存操作结果。如果一个事务中的一部分操作没有成功完成，则数据库管理系统会把数据库回滚到操作执行之前的状态。
3. 在PostgreSQL 中，显式地指定BEGIN ... END/COMMIT/ROLLBACK 包括的语句块或一组语句为一个事务，未指定BEGIN ... END/COMMIT/ROLLBACK 的单条语句也称为一个事务。
事务有四个重要的特性： 原子性、一致性、隔离性、持久性。
原子性（Atomicity） ： 一个事务的所有操作， 要么全部执行，要么全部不执行。
一致性（Consistency）：执行事务时保持数据库从一个一致的状态变更到另一个一致的状态。
隔离性（Isolation） ：即使每个事务都能确保一致性和原子性，如果并发执行时会交叉运行，导致不一致的情况发生。确保事务与事务并发执行时，每个事务都感觉不到有其他事务在并发地执行。
持久性（ Durability ）： 一个事务完成之后， 即使数据库发生故障，它对数据库的改变应该永久保存在数据库中
这四个特性分别取它们名称的首字母，通常习惯称之为ACID 。
其中，事务一致性由主键、外键这类约束保证
持久性由预写日志（WAL）和数据库管理系统的恢复子系统保证，
原子性、隔离性则由事务管理器和MVCC 来控制。
并发引发的现象
脏读（Dirty read）
不可重复读（Non-repeatable read）
幻读（Phantom Read）
序列化异常（Serialization Anomaly）
注意：PostgreSQL 内部将READ UNCOMMITTED 设计为和READ COMMITTED 一样，因此在PostgreSQL 数据库中无论如何都无法产生脏读
【脏读（Dirty read）】
当第一个事务读取了第二个事务中已经修改但还未提交的数据，包括INSERT、UPDATE、DELETE，当第二个事务不提交并执行ROLLBACK 后，第一个事务所读取到的数据是不正确的，这种读现象称作脏读。
pg=# set session transaction isolation level read uncommitted;
pg=# start transaction;
START TRANSACTION
.......
pg=# rollback;
ROLLBACK
【不可重复读（Non-repeatable read）】
当一个事务第一次读取数据之后，被读取的数据被另一个己提交的事务进行了修改，事务再次读取这些数据时发现数据已经被另一个事务修改，两次查询的结果不一致，这种读现象称为不可重复读。
pg=# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
事务1操作数据中间，事务2进行了修改，且进行了提交，事务1在事务2提交前后读取的数据不一致。
【幻读（Phantom Read）】
幻读（Phantom Read），指一个事务的两次查询的结果集记录数不一致。例如一个事务第一次根据范围条件查询了一些数据，而另一个事务却在此时插入或删除了这个事务的查询结果集中的部分数据，
这个事务在接下来的查询中， 会发现有一些数据在它先前的查询结果中不存在，或者第一次查询结果中的一些数据不存在了，两次查询结果不相同，这种读现象称为幻读。
幻读可以认为是受INSERT 和DELETE 影响的不可重复读的一种特殊场景。
注意：不可重复读和幻读很相似，它们之间的区别主要在于
1. 不可重复读主要受到其他事务对数据的UPDATE 操作，
2. 幻读主要受到其他事务INSERT 和DELETE 操作的影响。
ANSI SQL 标准的事务隔离级别
ANSI SQL 标准定义了四类隔离级别，用来限定事务内外的哪些改变对其他事务是可见的，哪些是不可见的，也就是允许或不允许出现脏读、不可重复读，幻读的现象。
这四类事务隔离级别包括：
1. Read Uncommitted （读未提交） ： 所有事务都可以看到其他未提交事务的执行结果，在多用户数据库中，脏读是非常危险的，在并发情况下，查询结果非常不可控，性能也并不比其他事务隔离级别好多少
Read Uncommitted （读未提交）这一事务隔离级别很少使用。
2. Read Committed （读己提交） ：PostgreSQL 的默认隔离级别，一个事务只能看见已经提交事务对关联数据所做的改变的隔离需求。
3. Repeatable Read （可重复读）：确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
4. Serializ able （可序列化） ： 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。可能导致大量的超时现象和锁竞争。
ANSI SQL 标准定义的事务隔离级别与读现象的关系：
【隔离级别】 【脏读 】 【不可重复】 【读幻读】
Read Uncommitted 可能 可能 可能
Read Committed 不可能 可能 可能
Repeatable Read 不可能 不可能 可能
Serializable 不可能 不可能 不可能
低级别的隔离级别一般支持更高的并发处理，并拥有更低的系统开销，但增加了并发引发的副作用的影响。
对于多数应用程序，优先考虑Read Committed 隔离级别。它能够避免脏读，而且具有较好的并发性能。
Read Committed 会导致不可重复读、幻读和丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。
PostgreSQL 的事务隔离级别
PostgreSQL 默认提供了非阻塞读， 因此在PostgreSQL内部只实现了三种不同的隔离级别，
PostgreSQL 的Read Uncommitted 模式的行为和ReadCommitted 相同，
PostgreSQL 的Repeatable Read 实现不允许幻读。
序列化异常
序列化异常是指成功提交的一组事务的执行结果与这些事务按照串行执行方式的执行结果不一致。
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
......
ROLLBACK
PostgresQL中不同的事务隔离级别与读现象的关系:
【隔离级别】 【脏读 】 【不可重复读】 【幻 读】 【序列化异常】
Read Uncommitted 不可能 可能 可能 可能
Read Committed 不可能 可能 可能 可能
Repeatable Read 不可能 不可能 不可能 可能
Serializable 不可能 不可能 不可能 不可能
【查看和设置数据库的事务隔离级别】
查看全局事务隔离级别：
1. pg=# SELECT name, setting FROM pg_settings WHERE name ='default_transaction_isolation';
name | setting
-------------------------------+----------------
default_transaction_isolation | read committed
(1 row)
2. pg=# SELECT current_setting('default_transaction_isolation');
current_setting
-----------------
read committed
修改全局的事务隔离级别
l ： 通过修改postgresql.conf 文件中的default_transaction_isolation 参数修改全局事务隔离级别， 修改之后reload 实例使之生效；
~]# grep -irn default_transaction_isolation /opt/pg/data/9.5/postgresql.conf
482:#default_transaction_isolation = 'read committed'
reload:
1. 操作系统命令行： pg_ctl reload (配置环境变量后)
2. pg=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
2 ：通过ALTER SYSTEM 命令修改全局事务隔离级别：
mydb=# ALTER SYSTEM SET default_transaction_isolation TO 'REPEATABLE READ';
ALTER SYSTEM
pg=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
查看当前会话的事务隔离级别
pg=# SHOW transaction_isolation;
transaction_isolation
-----------------------
read committed
(1 row)
pg=# SELECT current_setting('transaction_isolation') ;
current_setting
-----------------
read committed
(1 row)
设置当前会话的事务隔离级别
mydb=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
设置当前事务的事务隔离级别
mydb=# START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
......
mydb=# END;
mydb=# BEGIN ISOLATION LEVEL READ UNCOMMITTED READ WRITE;
......
mydb=# END/COMMIT/ROLLBACK;
PostgreSQL 的并发控制
数据库管理系统中并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性、数据的一致性以及数据库的一致性，也就是解决丢失更新、脏读、不可重复读、幻读、序列化异常的问题。
并发控制模型有基于锁的并发控制（Lock- Based Concurrency Control)和基于多版本的并发控制（Multi-Version Concurrency Control） 。
并发控制采用的主要技术手段:
封锁、
时间戳
乐观并发控制（又名“乐观锁”， Optimistic Concurrency Control ，缩写为“ occ ”）
悲观并发控制（又名“悲观锁”， Pessimistic Concurrency Control ，缩写为“ PCC ”）
【基于锁的并发控制】
基本的封锁类型有两种：
排它锁(Exclusive locks , X 锁）
共享锁（ Share locks, S 锁）
排它锁：被加锁的对象只能被持有锁的事务读取和修改，其他事务无法在该对象上加其他锁，也不能读取和修改该对象。=======阻塞读写？？？？
共享锁：被加锁的对象可以被持锁事务读取，但是不能被修改，其他事务也可以在上面再加共享锁。=======不阻塞读，阻塞写？？？？
封锁对象的大小称为封锁粒度（ Granularity ） 。
封锁的对象可以是逻辑单元，也可以是物理单元。
以关系数据库为例子，封锁对象可以是这样一些逻辑单元：属性值、属性值的集合、元组、关系、索引项、整个索引项甚至整个数据库；
也可以是这样的一些物理单元：
页（数据页或索引页）、物理记录等。
封锁的策略是一组规则，这些规则阐明了事务何时对数据项进行加锁和解锁，通常称为封锁协议（ Locking Protocol ） 。
由于采用了封锁策略，一次只能执行一个事务，所以只会产生串行调度，迫使事务只能等待前面的事务结束之后才可以开始，所以基于锁的并发控制机制导致性能低下，并发程度低。
Postgre SQL 特有的Advisory Lock （咨询锁）
【基于多版本的并发控制】
基于多个旧值版本的并发控制即MVCC 。
一般把基于锁的并发控制机制称成为悲观机制，而把MVCC 机制称为乐观机制。
因为锁机制是一种预防性的机制，读会阻塞写，写也会阻塞读，当封锁粒度较大，时－间较长时并发性能就不会太好；
MVCC 是一种后验性的机制，读不阻塞写， 写也不阻塞读，等到提交的时候才检验是否有冲突，由于没有锁，所以读写不会相互阻塞，避免了大粒度和长时间的锁定，能更好地适应对读的响应速度和并发性要求高的场景，大大提升了并发性能
常见的数据库如Oracle 、PostgreSQL 、MySQL(lnnodb）都使用MVCC 并发控制机制。
在MVCC 中每一个写操作创建一个新的版本。当事务发出一个读操作时，并发控制管理器选择一个版本进行读取。也就是为数据增加一个关于版本的标识，在读取数据时，连同版本号一起读出，在更新时对此版本号加一。
MVCC 通过保存数据在某个时间点的快照，并控制元组的可见性来实现。快照记录READ COMMITTED 事务隔离级别的事务中的每条SQL 语句的开头和SERIALIZABLE 事务隔离级别的事务开始时的元组的可见性。一个事务无论运行多长时间，在同一个事务里都能够看到一致的数据。根据事务开始的时间不同，在同一个时刻不同事务看到的相同表里的数据可能是不同的。
PostgreSQL 为每一个事务分配一个递增的、类型为int32 的整型数作为唯一的事务ID ，称为xid 。
创建一个新的快照时，将收集当前正在执行的事务id 和已提交的最大事务id 。根据快照提供的信息， PostgreSQL 可以确定事务的操作是否对执行语句是可见的。
PostgreSQL 在系统里的每一行记录上都存储了事务相关的信息， 这被用来判断某一行记录对于当前事务是否可见。
在PostgreSQL 的内部数据结构中，每个元组（行记录）有4 个与事务可见性相关的隐藏列，分别是xmin 、xmax 、cmin 、cmax ，
其中cmin 和cmax 分别是插入和删除该元组的命令在事务中的命令序列标识
xmin 、xmax 与事务对其他事务的可见性相关，用于同一个事务中的可见性判断。
可以通过SQL 直接查询到它们的值，如下所示：
mydb=# SELECT xmin,xmax,cmin,cmax FROM tbl_mvcc WHERE id = l ;
xmin 保存了创建该行数据的事务的xid,
xmax 保存的是删除该行的xid ,
PostgreSQL 在不同事务时间使用xmin 和xmax 控制事务对其他事务的可见性。
1. 通过xmin 决定事务的可见性
当插入一行数据时， PostgreSQL 会将插入这行数据的事务的xid 存储在xmin 中。通过xmin 值判断事务中插入的行记录对其他事务的可见性有两种情况：
1 ）由回滚的事务或未提交的事务创建的行对于任何其他事务都是不可见的
mydb=# BEGIN ;
mydb=# SELECT txid_current() ;
txid current
1937
(1 row)
mydb=# INSERT INTO tbl_mvcc(id,ival) VALUES( 7 , 7) ;
INSERT 0 1
mydb=# SELECT xmin , xmax , cmin , cmax , id, ival FROM tbl_mvcc WHERE id= 7 ;
(1 row)
通过SELECT txid_current（） 语句我们查询到当前的事务的xid 是1937 ，插入一条id 等于7 的数据，查询这条新数据的隐藏列可以看到xmin 的值等于1937 ， 也就是插入这行数据的事务的xid 。
开启另外一个事务:
mydb=# BEGIN;
BEGIN
mydb=# SELECT txid_current();
txid_current
1938
(1 row)
mydb=# SELECT * FROM tbl_mvcc WHERE id= 7;
id | ival
------+-- ----
(0 rows)
mydb=# END ;
COMMIT
可以看到由于第一个事务并没有提交，所以第一个事务对第二个事务是不可见的。
2 ）无论提交成功或回滚的事务， xid 都会递增。对于Repeatable Read 和Serializable 隔离级别的事务，如果它的xid 小于另外一个事务的xid ，也就是元组的xmin 小于另外一个事务的x min ，那么另外一个事务对这个事务是不可见的。。
mydb=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
mydb=# SELECT txid_current();
txid_current
1939
(1 row)
以上语句开启了一个事务这个事务的xid 是1939 。再开始另外一个事务如下所示：
mydb=# BEGIN;
BEGIN
mydb=# SELECT txid_current();
txid current
1940
(1 row)
mydb=# INSERT INTO tbl mvcc (id,ival) VALUES (7 , 7);
INSERT 0 1
mydb=# SELECT xmin , xmax , cmin , cmax , id,ival FROM tbl mvcc WHERE id= 7 ;
xmin I xmax I cmin I cmax I id I ival
---------+----- +----+------+----+ -----
1940 I
第二个事务的x id 是1940 ，并在这个事务中在表中插入一条新的数据， xmin 记录了第二个事务的x id ， 第二个事务提交成功。此时第一事务中查询不到第二个事务插入的数据
2. 通过xmax 决定事务的可见性
通过xmax 值判断事务的更新操作和删除操作对其他事务的可见性有这几种情况：
1 ）如果没有设置xmax 值，该行对其他事务总是可见的；
2 ）如果它被设置为回滚事务的xid ，该行对其他事务也是可见的；
3 ）如果它被设置为一个正在运行，没有COMMIT 和ROLLBACK 的事务的xid ， 该行对其他事务是可见的；
4 ）如果它被设置为一个已提交的事务的xid ，该行对在这个己提交事务之后发起的所有事务都是不可见的。
【通过pageinspect 观察MVCC】
在PostgreSQL 中，可以使用pageinspect 这个外部扩展来观察数据库页面的内容。
使用之前先创建扩展
mydb=# CREATE EXTENSTION pageinspect ;
CREATE EXTENSION
mydb=# \dx+ pageinspect
Objects in extension "pageinspect ”
Object description
get_raw_page get_raw page(relname text, fork text, blknoint）
重载函数
get_raw_page(relname text,blkno int），用于读取relation 中指定的块的值，其中
relnam是relation name
参数fork 可以有main 、vm 、fsm 、init 这几个值， 默认值是main
main 表示数据文件的主文件，
vm 是可见性映射的块文件，
fsm 为freespace map 的块文件，
init 是初始化的块。
get_raw_page 以一个bytea 值的形式返回一个拷贝。
heap_page_items heap_page_ items 显示一个堆页面上所有的行指针
https://momjian.us/main/blogs/pgblog/2021.html
DROP VIEW IF EXISTS v_pageinspect;
CREATE VIEW v_pageinspect As SELECT
'(0,' || 1p || ')' As ctid,
CASE lp flags
WHEN 0 THEN 'Unused'
WHEN 1 THEN 'Normal'
WHEN 2 THEN 'Redirect to ' || lp_off
WHEN 3 THEN 'Dead'
END,
t_xmin::text::int8 AS xmin,
t_xmax::text::int8 AS xmax,
t_ctid
FROM heap_page_items(get_raw_page('tbl_mvcc', 0)) ORDER BY lp;
使用pg_repack 解决表膨胀问题
在PostgreSQL 中数据采用堆表保存，并且MVCC 的旧版本和新版本存储在同一个地方，如果更新大量数据，将会导致数据表的膨胀。
例如一张一万条数据的表，如果对它进行一次全量的更新，根据PostgreSQL 的MVCC 的实现方式，在数据文件中每条数据实际会有两个版本存在，一个版本是更新之前的旧版本，一个版本是更新之后的新版本，这两个版本并存必然导致磁盘的使用率是实际数据的一倍，对性能也略有影响。
使用VACUUM 命令或者autovacuum 进程将旧版本的磁盘空间标记为可用，尽管VACUUM 已经被实现得非常高效，但是没有办法把已经利用的磁盘空间释放给操作系统，
VACUUM FULL 命令可以回收可用的磁盘空间，但它会阻塞所有其他的操作。
pg_repack 是一个可以在线重建表和索引的扩展。它会在数据库中建立一个和需要清理的目标表一样的临时表，将目标表中的数据COPY 到临时表，并在临时表上建立与目标表一样的索引，然后通过重命名的方式用临时表替换目标表。
这个小工具使用非常简单，可以下载源码编译安装，也可以通过yum 源安装，这里以
通过yum 源安装为例。首先安装pg_repack ，如下所示：
[root@pghostl ~]# yum install -y pg_repack10
然后在数据库中创建pg_repack 扩展：
mydb=# CREATE EXTENSION pg_repack;
在命令行中，使用pg_repack 对tbl_mvcc 表进行重建：
[postgres@pghostl ~]# /usr/pgsql-10/bin／pg_repack -t tbl_mvcc - j 2 -D -k -h pghostl -U postgres -d mydb
支持事务的DDL
PostgreSQL 事务通过预写日志设计来执行事务性的DDL。即把DDL 语句放在一个事务中， 比如创建表、TRUNCATE 表等。
扫一扫
1651
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
