数据库操作遇到的问题
- 脏读:一个事务读取了另一个未提交事务写入的数据;
- 不可重复读:一个事务重新读取前面读取过的数据，发现该数据已经被另一个已经提交的事务修改;
- 幻读:一个事务重新执行一个查询，返回符合查询条件的行的集合，发现满足查询条件的行的集合因为其它最近提交的事务而发生了改变。
事务特性
- 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行;
- 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束;
- 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行;
- 持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。
数据库事务隔离级别
- 读未提交(READ UNCOMMITTED)
- 读已提交(READ COMMITTED)
- 可重复读(REPEATABLE READS)
- 可序列化(SERIALIZABLE)
数据库事务隔离级别&&解决问题
参考：http://www.postgres.cn/docs/9.5/transaction-iso.html
乐观锁与悲观锁
参考：http://www.hollischuang.com/archives/934
MVCC(multiversion concurrency control,多版本并发控制)
MVCC(多版本并发控制)与使用锁的优缺点：
在MVCC里，对检索(读)数据的锁请求与写数据的锁请求不冲突，所以读不会阻塞写，而写也从不阻塞读。甚至当通过创新的序列化快照隔离(SSI)级别提供事务隔离的严格等级时，PostgreSQL维持这样的保证。
在PostgreSQL里也有表和行级别的锁定机制，用于给那些无法轻松接受MVCC行为的应用。 不过，恰当地使用MVCC总会提供比锁更好的性能。另外，由应用定义的咨询锁提供了一个获得不依赖于单独事务的锁的机制。
问题:PG什么时候选择MVCC什么时候选择使用锁?
在PostgreSQL里也有表和行级别的锁功能，用于那些通常不需要完整事务隔离并且想要显式管理特定冲突点的应用。不过，恰当地使用MVCC通常会提供比锁更好的性能。MVCC的两种实现方法
写新数据时，把旧数据移到一个单独的地方，如回滚段中，其他人读数据时，从回滚段中把旧的数据读出来;
2.写数据时，旧数据不删除，而是把新数据插入。PostgreSQL数据库使用第二种方法，而Oracle数据库和MySQL中的innodb引擎使用的是第一种方法;-
与oracle数据库和MySQL中的innodb引擎相比较，PostgreSQL的MVCC实现方式的优缺点如下:
- 优点:
- 事务回滚可以立即完成，无论事务进行了多少操作；
- 数据可以进行很多更新，不必像Oracle和MySQL的Innodb引擎那样需要经常保证回滚段不会被用完，也不会像oracle数据库那样经常遇到“ORA-1555”错误的困扰；
- 缺点:
- 旧版本数据需要清理。PostgreSQL清理旧版本的命令成为Vacuum；
- 旧版本的数据会导致查询更慢一些，因为旧版本的数据存在于数据文件中，查询时需要扫描更多的数据块。
- 优点:
PG中delete和update语句机制
在PostgreSQL中，使用delete和update语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行VACUUM操作
- 无VACUUM:只是将删除的数据状态置为已删除,该空间不能记录被重新使用.
- VACUUM:删除的记录位于末端,占用的空间会被物理释放归还操作系统,如果不是位于末端,会将删除数据锁占用的空间置为可用状态.
- VACUUM FULL:不论被删除的数据是否处于数据表末端,这些数据锁占用的空间都将被物理释放并归还操作系统.
下面的blog详细分析了vacuum和vacuum full的区别,以及如何恢复索引和delete后的磁盘空间:
http://www.cnblogs.com/stephen-liu74/archive/2011/12/27/2304155.html
PG的MVCC实现机制(以insert形式展开说明)
在Postgres中，每一个事务都会得到一个被称作为 XID 的事务ID。这里说的事务不仅仅是被 BEGIN - COMMIT 包裹的一组语句，还包括单条的insert、update或者delete语句。当一个事务开始时，Postgrel递增XID，然后把它赋给这个事务。Postgres还在系统里的每一行记录上都存储了事务相关的信息，这被用来判断某一行记录对于当前事务是否可见。
insert的MVCC说明:
- 当插入一行记录时,PG会把当前事务的XID存储在这行数据中的xmin;
- 当插入的数据未COMMIT之前,这行数据对其他数据是不可见的;
- 当插入的数据COMMIT后,这行数据对其他数据是可见的;
- 资料说只有xmin<XID条件,才能查看这条新记录,但测试发现可能存在可重复读;
- 事务1:
事务ID:x
查询数据1;
阻塞;
查询数据2; - 事务2:
事务ID:x+1
插入数据
上面2个示例,如果事务2在事务1阻塞的时候提交,在事务1中的查询数据2是能查看到这条新数据的,因为PG默认的事务隔离级别是读已提交.如果更改事务隔离级别为SERIALIZABLE后,则不会发生可重复读的情况(即查询1和查询2两次的结果一致).
自己测试查看语句:
--获取行xmin,xmax值
select xmin, xmax,* from tablename;
--获取当前事务的XID
select txid_current();
--改变事务隔离级别
BEGIN TRANSACTION ISOLATION LEVEl [READ COMMITTED/REPEATABLE READ/SERIALIZABLE]; --一次启动事务并指定事务隔离级别
对于delete和update来说,机制也是类似的,当对于他们来说PG使用xmax来判断数据的可见性.
自己测试思路可以参考:
http://www.zlovezl.cn/articles/postgresql-concurrency-with-mvcc/
原理也可参考:
说明:https://my.oschina.net/Kenyon/blog/108850
示例:https://my.oschina.net/Kenyon/blog/63668
PG中的事务隔离
- 两个事务，更新的同一行，一次只有一个事务能更新．
- 两个事务，更新不同行，能同时更新．
参考:
PG文档事务隔离:http://www.postgres.cn/docs/9.5/transaction-iso.html
PG的表锁
PG文档表锁机制:http://www.postgres.cn/docs/9.5/explicit-locking.html#LOCKING-TABLES
通过实例较好的说明了PG的锁机制:http://www.oschina.net/translate/postgresql-locking-revealed
PG的行锁
PG文档行级锁:http://www.postgres.cn/docs/9.5/explicit-locking.html#LOCKING-ROWS
pg行锁解读：http://blog.itpub.net/30088583/viewspace-1699315/
PG事务常用操作
- 开启事务
BEGIN;
START TRANSACTION [ transaction_mode [, ...] ]
-这里的 transaction_mode是下列之一：
- ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
- READ WRITE | READ ONLY
- [ NOT ] DEFERRABLE
- 设置事务模式
SET TRANSACTION transaction_mode [, ...]
SET TRANSACTION SNAPSHOT snapshot_idSET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]
-这里的 transaction_mode是下列之一:
- ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
- READ WRITE | READ ONLY
- [ NOT ] DEFERRABLE
--事务隔离级别，定义多个事务时间的隔离级别
BEGIN TRANSACTION ISOLATION LEVEl [READ COMMITTED/REPEATABLE READ/SERIALIZABLE]; --一次启动事务并指定事务隔离级别
BEGIN;
TRANSACTION ISOLATION LEVEl [READ COMMITTED/REPEATABLE READ/SERIALIZABLE]; --先启动事务，再设置事务隔离级别
- 结束事务
commit
rollback
- 预备事务
--预备事务，使得事务分阶段可以提交
PREPARE TRANSACTION 'foobar';
......
COMMIT PREPARE TRANSACTION 'foobar';
ROLLBACK PREPARE TRANSACTION 'foobar';
- 保存点
--保存点savepoint,可以支持事务的部分回滚
insert into lyy values(1,'nn');
savepoint svp1;
insert into lyy values(2,'ff');
rollback to savepoint svp1;
--此时提交的话，第二个insert未被插入，但是第一个插入成功。
- 查看当前事务的事务id
select txid_current();
详情参考：https://my.oschina.net/liuyuanyuangogo/blog/415395
其他参考
pg锁机制理解：http://francs3.blog.163.com/blog/static/40576727201082134343604/
mysql锁机制理解： http://hedengcheng.com/?p=771
pg中mvcc实现机制:http://www.zlovezl.cn/articles/postgresql-concurrency-with-mvcc/
http://blog.itpub.net/30088583/viewspace-1585695/
较好的阐释了pg中mvcc的原理:https://my.oschina.net/Kenyon/blog/108850
pg事务级别查看与变更:http://blog.csdn.net/scugxl/article/details/51126433
mysql与pg对比:https://www.sdk.cn/news/4587
pg维护vacuum解析：http://www.cnblogs.com/stephen-liu74/archive/2011/12/27/2304155.html
Ubuntu安装PG与使用:http://wenzhixin.net.cn/2014/01/12/hello_postgresql
