PostgreSQL:初识(三)

（1）开始事务
postgres=# \h begin
Command:     BEGIN
Description: start a transaction block
Syntax:
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]


where transaction_mode is one of:


    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE


(2)结束事务
# end与commit效果相同，用于提交完成当前事务；rollback用于停止并回滚当前事务
postgres=# \end
Invalid command \end. Try \? for help.
postgres=# \h end
Command:     END
Description: commit the current transaction
Syntax:
END [ WORK | TRANSACTION ]


postgres=# \h commit;
Command:     COMMIT
Description: commit the current transaction
Syntax:
COMMIT [ WORK | TRANSACTION ]
postgres=# \h rollback
Command:     ROLLBACK
Description: abort the current transaction
Syntax:
ROLLBACK [ WORK | TRANSACTION ]

postgres=# begin;
BEGIN
postgres=# select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)


postgres=# commit;
COMMIT
postgres=# begin;
BEGIN
postgres=# select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)


postgres=# rollback;
ROLLBACK


postgres=# begin;
BEGIN
postgres=# select version;
ERROR:  column "version" does not exist
LINE 1: select version;
               ^
postgres=# select version();
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=# commit;
ROLLBACK

# 开启事务并创建t2表及插入数据
test2=> begin;
BEGIN
test2=> create table t2(id int);
CREATE TABLE
test2=> insert into t2 values (1),(2),(3),(4),(5);
INSERT 0 5
# 查看数据
test2=> select * from t2;
 id 
----
  1
  2
  3
  4
  5
(5 rows)


# 执行rollback后再次查看t2表数据，可以看到此时表已经不存在
test2=> rollback;
ROLLBACK
test2=> select * from t2;
ERROR: relation "t2" does not exist
LINE 1: select * from t2;

test2=> begin;
BEGIN
test2=> select * from t1;
 id 
----
  1
  2
  3
(3 rows)


# 插入新数据并创建savepoint
test2=> insert into t1 values(4);
INSERT 0 1
test2=> savepoint a;
SAVEPOINT
test2=> insert into t1 values(5);
INSERT 0 1
test2=> savepoint b;             
SAVEPOINT


# 故意写错SQL创造错误
test2=> insert into t1 values(6; 
test2(> );
ERROR:  syntax error at or near ";"
LINE 1: insert into t1 values(6;
                               ^
# 此时如果没有savepoint就只能rollback整个事务，否则无法继续进行下去
test2=> insert into t1 values(6);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
test2=> rollback to savepoint b;
ROLLBACK


# rollback到保存点b，继续进行插入数据成功
test2=> insert into t1 values(6);
INSERT 0 1
test2=> select * from t1;        
 id 
----
  1
  2
  3
  4
  5
  6
(6 rows)


test2=> commit;
COMMIT


# 注意：rollback到保存点，只能在事务块中实现，也就是说随着事务提交/结束，该事务中的保存点寿命也将被终止
test2=> rollback to savepoint b; 
ERROR:  ROLLBACK TO SAVEPOINT can only be used in transaction blocks

MVCC的实现方法有两种：
1.写新数据时，把旧数据移到一个单独的地方，如回滚段中，其他人读数据时，从回滚段中把旧的数据读出来；
2.写数据时，旧数据不删除，而是把新数据插入。
PostgreSQL数据库使用第二种方法，而ORACLE数据库和MySQL中的innodb引擎使用的是第一种方法。
与ORACLE数据库和MySQL中的innodb引擎相比较，PostgreSQL的MVCC实现方式的优缺点如下。
优点：
1.事务回滚可以立即完成，无论事务进行了多少操作；
2.数据可以进行很多更新，不必像ORACLE和MySQL的Innodb引擎那样需要经常保证回滚段不会被用完，也不会像oracle数据库那样经常遇到“ORA-1555”错误的困扰；
缺点：
1.旧版本数据需要清理。PostgreSQL清理旧版本的命令成为Vacuum；
2.旧版本的数据会导致查询更慢一些，因为旧版本的数据存在于数据文件中，查询时需要扫描更多的数据块。   

# 创建表及测试数据，
test2=> create table t2 (id int);
CREATE TABLE
test2=> insert into t2 select * from generate_series(1,10000);
INSERT 0 10000


# 查看表大小
test2=> select pg_size_pretty(pg_relation_size('t2'));
 pg_size_pretty 
----------------
 360 kB
(1 row)


# 修改全表数据，再次查看大小
test2=> update t2 set id=id *10;
UPDATE 10000
test2=> select pg_size_pretty(pg_relation_size('t2'));
 pg_size_pretty 
----------------
 712 kB
(1 row)


# 可以看到update这种更新/修改数据的操作，在PostgreSQL中是不清除旧数据，而是复制行数据进行修改实现的。

# 会话1：
test2=> select pg_backend_pid();
 pg_backend_pid 
----------------
           2447
(1 row)
test2=> begin;           
BEGIN
test2=> update t1 set id=id+1 where id=6;
UPDATE 1
# 会话2：
test2=> select pg_backend_pid();
 pg_backend_pid 
----------------
           4434
(1 row)
test2=> begin;
BEGIN
test2=> update t1 set id=id+1 where id=6;
--此时update处于等待状态


# 会话3：
test2=# select locktype, relation::regclass as rel,page||','||tuple as ctid,virtualxid as vxid,transactionid as xid,virtualtransaction as vxid2, pid,mode,granted from pg_locks where pid in (2447,4434) order by pid;
   locktype    | rel | ctid |  vxid  | xid  | vxid2  | pid  |       mode       | granted 
---------------+-----+------+--------+------+--------+------+------------------+---------
 relation      | t1  |      |        |      | 3/1414 | 2447 | RowExclusiveLock | t
 virtualxid    |     |      | 3/1414 |      | 3/1414 | 2447 | ExclusiveLock    | t
 transactionid |     |      |        | 1841 | 3/1414 | 2447 | ExclusiveLock    | t
 virtualxid    |     |      | 2/4141 |      | 2/4141 | 4434 | ExclusiveLock    | t
 transactionid |     |      |        | 1841 | 2/4141 | 4434 | ShareLock        | f
 transactionid |     |      |        | 1842 | 2/4141 | 4434 | ExclusiveLock    | t
 tuple         | t1  | 0,10 |        |      | 2/4141 | 4434 | ExclusiveLock    | t
 relation      | t1  |      |        |      | 2/4141 | 4434 | RowExclusiveLock | t
(8 rows)


# 定位锁
test2=# select * from t1 where ctid='(0,10)';
 id 
----
  6
(1 row)


locktype：锁对象的类型，如relation（表），tuple（行数据），virtualxid，transactionid等
rel:对象为表则显式表名，否则为空
ctid:表的ctid字段
mode:锁模式名称
granted:持有锁为“t”（ture），否则w诶“f”（false）


进程2447即会话1获取了t1表上的RowExclusive Lock
进程4434即会话2实际上此时是被阻塞的状态，这是因为行锁会在数据行上加上自己的xid，当另一个会话读到这一行时，
如果发现其上有行锁，此时会在该事务的xid上申请一个share锁，而由于此时上面已经有exclusive，故会话被阻塞。