postgres-xl分布式数据库测试1已经测试了pgxl的分布式存储（REPLICATION、ROUNDROBIN、HASH(key) 、MODULO(key)）、跨分片join查询、各种聚合运算、分布式事务回滚/提交等操作，接下来我们会测试分布式事务的强一致性。
这个测试，利用了上一篇文章的结论：一个事务修改记录时，会在该事务修改的记录上加上 write lock，直至事务结束（提交或者回滚）其他事务才能修改这部分记录。
测试环境
4台以下配置的虚拟机
- CPU：4核
- 内存：8GB
- 硬盘：200GB
- 系统：CentOS7.6 minimal
主机规划
server0 192.168.80.100 gtm
server1 192.168.80.101 gtm-proxy1,coordinator1,datanode1
server2 192.168.80.102 gtm-proxy2,coordinator2,datanode2
server3 192.168.80.103 gtm-proxy3,coordinator3,datanode3
测试规划：
- a). sessionA、sessionB 连接 coordinator1，sessionC连接 coordinator2，sessionD连接 coordinator3；
- b). 创建一个 t_session_lock ,并增加一条记录1，作为事务间的锁;
- c). sessionA 启动事务，在 t_session_lock表 上加 access exclusive 锁;
- d). sessionA 修改 其它表的数据;
- e). sessionB、sessionC，sessionD 执行脚本
# 查询 t_session_lock表 的数据（目的是为了hold住后面的操作）;
# select now();
# 做一个 步骤d修改数据的查询
- f). sessionA 执行脚本
# rollback/commit;
# select now();
# 做一个 步骤d修改数据的查询
- g). 对比4个session的输出的最后查询结果是否一致。
初始化 t_session_lock 脚本
postgres=# CREATE TABLE t_session_lock (id int primary key,val int) DISTRIBUTE BY HASH(id);
CREATE TABLE
postgres=# insert into t_session_lock (id,val ) values(1,1);
INSERT 0 1
postgres=# select * from t_session_lock;
id | val
----+-----
1 | 1
(1 row)
测试脚本
- sessionA
# 提前做一次查询，作为数据基础，如果回滚，需要用于结果对比
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
begin;
lock table t_session_lock in access exclusive mode;
update t_student set class_id=class_id+1;
update t_grades set record=record+5 where grade_id between 20000 and 50000;
# 事务结束前查询，如果提交，需要对比这个结果
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
# 两次测试，一次是回滚事务，一次是提交事务
rollback/commit;
select now();
# 最终数据
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
- sessionB、sessionC、sessionD
# 提前做一次查询，作为数据基础，如果回滚，需要用于结果对比
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
select * from t_session_lock where id=1;
select now();
# 最终数据
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
测试过程——事务回滚
更新前的数据对比
每个session的继续执行时间
最终结果对比
sessionA：
[postgres@xCloud ~]$ psql -h server1 -p 20004
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type "help" for help.
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=# begin;
BEGIN
postgres=# lock table t_session_lock in access exclusive mode;
LOCK TABLE
postgres=# update t_student set class_id=class_id+1;
UPDATE 50000
postgres=# update t_grades set record=record+5 where grade_id between 20000 and 50000;
UPDATE 30001
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 66455.79 | 159.57 | 0.14 | 65.7978118811881188
92 | 54803.99 | 157.35 | 0.02 | 62.9930919540229885
163 | 68855.25 | 159.16 | 0.02 | 63.1699541284403670
194 | 57095.21 | 152.89 | 0.18 | 62.7419890109890110
235 | 77933.95 | 151.70 | 0.02 | 64.9449583333333333
296 | 69351.39 | 159.00 | 0.01 | 62.4787297297297297
337 | 67968.84 | 157.38 | 0.16 | 62.3567339449541284
368 | 73681.44 | 158.76 | 0.02 | 66.3796756756756757
439 | 62588.58 | 158.35 | 0.10 | 61.9688910891089109
490 | 65240.53 | 155.75 | 0.12 | 64.5945841584158416
(10 rows)
postgres=# rollback;
ROLLBACK
postgres=# select now();
now
-------------------------------
2018-12-06 13:46:54.582986+08
(1 row)
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=#
sessionB：
[postgres@xCloud ~]$ psql -h server1 -p 20004
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type "help" for help.
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=# select * from t_session_lock where id=1;
select now();
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
id | val
----+-----
1 | 8
(1 row)
postgres=# select now();
now
-------------------------------
2018-12-06 13:46:54.586793+08
(1 row)
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=#
sessionC：
[postgres@xCloud ~]$ psql -h server2 -p 20005
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type "help" for help.
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=# select * from t_session_lock where id=1;
select now();
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
id | val
----+-----
1 | 8
(1 row)
postgres=# select now();
now
-------------------------------
2018-12-06 13:46:54.772295+08
(1 row)
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=#
sessionD：
[postgres@xCloud ~]$ psql -h server3 -p 20004
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type "help" for help.
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=# select * from t_session_lock where id=1;
select now();
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
id | val
----+-----
1 | 8
(1 row)
postgres=# select now();
now
-------------------------------
2018-12-06 13:46:54.942099+08
(1 row)
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=#
测试过程——事务提交
更新前的数据对比
每个session的继续执行时间
最终结果对比
sessionA:
[postgres@xCloud ~]$ psql -h server1 -p 20004
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type "help" for help.
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=# begin;
BEGIN
postgres=# lock table t_session_lock in access exclusive mode;
LOCK TABLE
postgres=# update t_student set class_id=class_id+1;
UPDATE 50000
postgres=# update t_grades set record=record+5 where grade_id between 20000 and 50000;
UPDATE 30001
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 66455.79 | 159.57 | 0.14 | 65.7978118811881188
92 | 54803.99 | 157.35 | 0.02 | 62.9930919540229885
163 | 68855.25 | 159.16 | 0.02 | 63.1699541284403670
194 | 57095.21 | 152.89 | 0.18 | 62.7419890109890110
235 | 77933.95 | 151.70 | 0.02 | 64.9449583333333333
296 | 69351.39 | 159.00 | 0.01 | 62.4787297297297297
337 | 67968.84 | 157.38 | 0.16 | 62.3567339449541284
368 | 73681.44 | 158.76 | 0.02 | 66.3796756756756757
439 | 62588.58 | 158.35 | 0.10 | 61.9688910891089109
490 | 65240.53 | 155.75 | 0.12 | 64.5945841584158416
(10 rows)
postgres=# commit;
select now();
COMMIT
postgres=# select now();
now
-------------------------------
2018-12-06 13:58:13.875002+08
(1 row)
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 66455.79 | 159.57 | 0.14 | 65.7978118811881188
92 | 54803.99 | 157.35 | 0.02 | 62.9930919540229885
163 | 68855.25 | 159.16 | 0.02 | 63.1699541284403670
194 | 57095.21 | 152.89 | 0.18 | 62.7419890109890110
235 | 77933.95 | 151.70 | 0.02 | 64.9449583333333333
296 | 69351.39 | 159.00 | 0.01 | 62.4787297297297297
337 | 67968.84 | 157.38 | 0.16 | 62.3567339449541284
368 | 73681.44 | 158.76 | 0.02 | 66.3796756756756757
439 | 62588.58 | 158.35 | 0.10 | 61.9688910891089109
490 | 65240.53 | 155.75 | 0.12 | 64.5945841584158416
(10 rows)
postgres=#
sessionB:
[postgres@xCloud ~]$ psql -h server1 -p 20004
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type "help" for help.
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=# select * from t_session_lock where id=1;
select now();
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
id | val
----+-----
1 | 8
(1 row)
postgres=# select now();
now
-------------------------------
2018-12-06 13:58:13.879005+08
(1 row)
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 66455.79 | 159.57 | 0.14 | 65.7978118811881188
92 | 54803.99 | 157.35 | 0.02 | 62.9930919540229885
163 | 68855.25 | 159.16 | 0.02 | 63.1699541284403670
194 | 57095.21 | 152.89 | 0.18 | 62.7419890109890110
235 | 77933.95 | 151.70 | 0.02 | 64.9449583333333333
296 | 69351.39 | 159.00 | 0.01 | 62.4787297297297297
337 | 67968.84 | 157.38 | 0.16 | 62.3567339449541284
368 | 73681.44 | 158.76 | 0.02 | 66.3796756756756757
439 | 62588.58 | 158.35 | 0.10 | 61.9688910891089109
490 | 65240.53 | 155.75 | 0.12 | 64.5945841584158416
(10 rows)
postgres=#
sessionC:
[postgres@xCloud ~]$ psql -h server2 -p 20005
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type "help" for help.
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=# select * from t_session_lock where id=1;
select now();
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
id | val
----+-----
1 | 8
(1 row)
postgres=# select now();
now
-------------------------------
2018-12-06 13:58:14.063537+08
(1 row)
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 66455.79 | 159.57 | 0.14 | 65.7978118811881188
92 | 54803.99 | 157.35 | 0.02 | 62.9930919540229885
163 | 68855.25 | 159.16 | 0.02 | 63.1699541284403670
194 | 57095.21 | 152.89 | 0.18 | 62.7419890109890110
235 | 77933.95 | 151.70 | 0.02 | 64.9449583333333333
296 | 69351.39 | 159.00 | 0.01 | 62.4787297297297297
337 | 67968.84 | 157.38 | 0.16 | 62.3567339449541284
368 | 73681.44 | 158.76 | 0.02 | 66.3796756756756757
439 | 62588.58 | 158.35 | 0.10 | 61.9688910891089109
490 | 65240.53 | 155.75 | 0.12 | 64.5945841584158416
(10 rows)
postgres=#
sessionD:
[postgres@xCloud ~]$ psql -h server3 -p 20004
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type "help" for help.
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 58952.04 | 154.56 | 0.50 | 64.7824615384615385
92 | 68456.71 | 153.52 | 0.16 | 63.9782336448598131
163 | 67983.32 | 149.90 | 0.20 | 62.9475185185185185
194 | 62714.90 | 151.45 | 0.00 | 64.6545360824742268
235 | 73089.31 | 149.80 | 0.06 | 64.6808053097345133
296 | 63664.81 | 151.19 | 0.21 | 65.6338247422680412
337 | 59510.79 | 149.64 | 0.21 | 62.6429368421052632
368 | 54241.03 | 149.83 | 0.18 | 63.0709651162790698
439 | 58996.90 | 152.02 | 0.08 | 60.2009183673469388
490 | 73640.25 | 154.75 | 0.17 | 62.9403846153846154
(10 rows)
postgres=# select * from t_session_lock where id=1;
select now();
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
id | val
----+-----
1 | 8
(1 row)
postgres=# select now();
now
-------------------------------
2018-12-06 13:58:14.232027+08
(1 row)
postgres=# select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id;
class_id | sum | max | min | avg
----------+----------+--------+------+---------------------
31 | 66455.79 | 159.57 | 0.14 | 65.7978118811881188
92 | 54803.99 | 157.35 | 0.02 | 62.9930919540229885
163 | 68855.25 | 159.16 | 0.02 | 63.1699541284403670
194 | 57095.21 | 152.89 | 0.18 | 62.7419890109890110
235 | 77933.95 | 151.70 | 0.02 | 64.9449583333333333
296 | 69351.39 | 159.00 | 0.01 | 62.4787297297297297
337 | 67968.84 | 157.38 | 0.16 | 62.3567339449541284
368 | 73681.44 | 158.76 | 0.02 | 66.3796756756756757
439 | 62588.58 | 158.35 | 0.10 | 61.9688910891089109
490 | 65240.53 | 155.75 | 0.12 | 64.5945841584158416
(10 rows)
postgres=#
可以看到，无论是相同的coordinator还是不同的coordinator间，更新后立马查询的数据是一致。符合了强一致性原则。
同时，我们也看到，用这种锁的形式，同一个coordinator间（例如sessionA和sessionB都是连接 coordinator1 ）一个session释放到另一个session活得锁时间差在0.004秒左右。不同的coordinator 间（例如sessionA和sessionB都是连接 coordinator1，sessionC连接 coordinator2，sessionD连接 coordinator3 ）一个session释放到另一个session活得锁时间差在0.16秒左右。为了减少这种时间差造成影响，后面会使用编程形式，减少时间差。
使用java代码pgxl事务一致性
主要测试步骤：
sessionA(连接coordinator1) 更新数据
sessionA(连接coordinator1) 提交事务
sessionD(连接coordinator3) 查询这批数据相关信息得到【结果D】
sessionA(连接coordinator1) 查询这批数据相关信息得到【结果A】
对比【结果D】和【结果A】是否一致。
- 代码如下：
package cn.hemny.test.test_pg;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
* 使用java代码pgxl全局事务强一致性。
* @author hemny
* @Package cn.hemny.test.test_pg
* @Title EventBusThread.java
* @Email hemny@qq.com
* @Description 使用java代码pgxl全局事务强一致性。
* 主要测试步骤：
* sessionA(连接coordinator1) 更新数据；
* sessionA(连接coordinator1) 提交事务；
* sessionD(连接coordinator3) 查询这批数据相关信息得到【结果D】；
* sessionA(连接coordinator1) 查询这批数据相关信息得到【结果A】；
* 对比【结果D】和【结果A】是否一致。
*
* 说明：本次测试使用pgxl 10r1版本
* 部署结构
* server0 192.168.80.100 gtm
* server1 192.168.80.101 gtm-proxy1,coordinator1,datanode1
* server2 192.168.80.102 gtm-proxy2,coordinator2,datanode2
* server3 192.168.80.103 gtm-proxy3,coordinator3,datanode3
*/
public class TestPgxlFullyACID {
private static final String JDBC_DRIVER = "org.postgresql.Driver";
private static final String JDBC_URL1 = "jdbc:postgresql://192.168.80.101:20004/postgres";
private static final String JDBC_URL2 = "jdbc:postgresql://192.168.80.102:20005/postgres";
private static final String JDBC_URL3 = "jdbc:postgresql://192.168.80.103:20004/postgres";
private static final String JDBC_USERNAME = "postgres";
private static final String JDBC_PASSWORD = "postgres";
public static Connection getConnection(String url, String username, String password)
throws ClassNotFoundException, SQLException {
Connection connection = null;
try {
Class.forName(JDBC_DRIVER);
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void executeUpdate(Connection conn, String sql) throws SQLException {
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate(sql);
}
}
public static List<List<Object>> executeQuery(Connection conn, String sql) throws SQLException {
try (Statement stmt = conn.createStatement()) {
stmt.setFetchSize(50);
try (ResultSet rs = stmt.executeQuery(sql)) {
return convertList(rs);
}
}
}
private static List<List<Object>> convertList(ResultSet rs) throws SQLException {
List<List<Object>> list = new ArrayList<>();
ResultSetMetaData md = rs.getMetaData();// 获取键名
int columnCount = md.getColumnCount();// 获取行的数量
List<Object> listColName = new ArrayList<>();
for (int i = 1; i <= columnCount; i++) {
listColName.add(md.getColumnName(i));
}
list.add(listColName);
while (rs.next()) {
List<Object> listRow = new ArrayList<>();
for (int i = 1; i <= columnCount; i++) {
listRow.add(rs.getObject(i));
}
list.add(listRow);
}
return list;
}
private static void printList(List<List<Object>> list) throws SQLException {
for (List<Object> row : list) {
for (Object cell : row) {
System.out.print(cell);
System.out.print("\t");
}
System.out.println();
}
System.out.println();
}
public static void testFullyACID(boolean commit) {
try (Connection sessionA = getConnection(JDBC_URL1, JDBC_USERNAME, JDBC_PASSWORD);
Connection sessionB = getConnection(JDBC_URL1, JDBC_USERNAME, JDBC_PASSWORD);
Connection sessionC = getConnection(JDBC_URL2, JDBC_USERNAME, JDBC_PASSWORD);
Connection sessionD = getConnection(JDBC_URL3, JDBC_USERNAME, JDBC_PASSWORD)) {
sessionA.setAutoCommit(false);
sessionB.setAutoCommit(false);
sessionC.setAutoCommit(false);
sessionD.setAutoCommit(false);
String sql = "select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id";
System.out.println(" 统计信息SQL:");
System.out.println(sql);
System.out.println("\n 更新前的统计信息");
List<List<Object>> listA = executeQuery(sessionA, sql);
printList(listA);
String updatesql1 = "update t_student set class_id=class_id+1";
String updatesql2 = "update t_grades set record=record+5 where grade_id between 20000 and 50000";
System.out.println("\n sessionA 执行更新SQL:");
System.out.println(updatesql1);
System.out.println(updatesql2);
executeUpdate(sessionA, updatesql1);
executeUpdate(sessionA, updatesql2);
System.out.println("\n 更新后(事务结束前)的统计信息：");
listA = executeQuery(sessionA, sql);
printList(listA);
long commitTime = System.currentTimeMillis();
System.out.println(" currentTimeMillis:" + commitTime);
if (commit) {
System.out.println(" sessionA commit ");
sessionA.commit();
} else {
System.out.println(" sessionA rollback ");
sessionA.rollback();
}
long currentTime = System.currentTimeMillis();
System.out.println(" 通过 sessionD 获取的统计信息：currentTimeMillis:" + currentTime + ",time difference(ms):"
+ (currentTime - commitTime));
List<List<Object>> listD = executeQuery(sessionD, sql);
currentTime = System.currentTimeMillis();
System.out.println(" 通过 sessionC 获取的统计信息：currentTimeMillis:" + currentTime + ",time difference(ms):"
+ (currentTime - commitTime));
List<List<Object>> listC = executeQuery(sessionC, sql);
currentTime = System.currentTimeMillis();
System.out.println(" 通过 sessionB 获取的统计信息：currentTimeMillis:" + currentTime + ",time difference(ms):"
+ (currentTime - commitTime));
List<List<Object>> listB = executeQuery(sessionB, sql);
currentTime = System.currentTimeMillis();
System.out.println(" 通过 sessionA 获取的统计信息：currentTimeMillis:" + currentTime + ",time difference(ms):"
+ (currentTime - commitTime));
listA = executeQuery(sessionA, sql);
System.out.println("\n sessionA 获取的统计信息：");
printList(listA);
System.out.println("\n sessionB 获取的统计信息：");
printList(listB);
System.out.println("\n sessionC 获取的统计信息：");
printList(listC);
System.out.println("\n sessionD 获取的统计信息：");
printList(listD);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println("\n\n\n测试 rollback 的事务一致性");
testFullyACID(false);
System.out.println("\n\n\n测试 commit 的事务一致性");
testFullyACID(true);
}
}
- 执行结果：
测试 rollback 的事务一致性
统计信息SQL:
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id
更新前的统计信息
class_id sum max min avg
31 52791.57 164.41 0.00 62.8471071428571429
92 61404.44 154.07 0.10 64.6362526315789474
163 62794.69 158.28 0.18 61.5634215686274510
194 57102.65 162.49 0.03 63.4473888888888889
235 55942.69 149.90 0.12 64.3019425287356322
296 67018.50 160.36 0.09 65.0665048543689320
337 58588.59 164.04 0.03 66.5779431818181818
368 67174.81 162.97 0.06 62.1988981481481481
439 60358.30 156.47 0.07 63.5350526315789474
490 64538.23 164.50 0.07 65.1901313131313131
sessionA 执行更新SQL:
update t_student set class_id=class_id+1
update t_grades set record=record+5 where grade_id between 20000 and 50000
更新后(事务结束前)的统计信息：
class_id sum max min avg
31 62520.97 168.37 0.31 63.7318756371049949
92 73695.64 165.07 0.11 64.0831652173913043
163 64017.59 149.84 0.03 64.6642323232323232
194 66819.13 149.91 0.10 66.8191300000000000
235 68856.35 149.91 0.07 66.8508252427184466
296 64840.53 165.95 0.14 63.5691470588235294
337 60443.11 168.30 0.05 64.9925913978494624
368 69326.67 164.02 0.02 64.1913611111111111
439 60284.25 167.12 0.29 62.7960937500000000
490 57130.31 169.76 0.13 65.6670229885057471
currentTimeMillis:1544089230808
sessionA rollback
通过 sessionD 获取的统计信息：currentTimeMillis:1544089230812,time difference(ms):4
通过 sessionC 获取的统计信息：currentTimeMillis:1544089232288,time difference(ms):1480
通过 sessionB 获取的统计信息：currentTimeMillis:1544089233218,time difference(ms):2410
通过 sessionA 获取的统计信息：currentTimeMillis:1544089234922,time difference(ms):4114
sessionA 获取的统计信息：
class_id sum max min avg
31 52791.57 164.41 0.00 62.8471071428571429
92 61404.44 154.07 0.10 64.6362526315789474
163 62794.69 158.28 0.18 61.5634215686274510
194 57102.65 162.49 0.03 63.4473888888888889
235 55942.69 149.90 0.12 64.3019425287356322
296 67018.50 160.36 0.09 65.0665048543689320
337 58588.59 164.04 0.03 66.5779431818181818
368 67174.81 162.97 0.06 62.1988981481481481
439 60358.30 156.47 0.07 63.5350526315789474
490 64538.23 164.50 0.07 65.1901313131313131
sessionB 获取的统计信息：
class_id sum max min avg
31 52791.57 164.41 0.00 62.8471071428571429
92 61404.44 154.07 0.10 64.6362526315789474
163 62794.69 158.28 0.18 61.5634215686274510
194 57102.65 162.49 0.03 63.4473888888888889
235 55942.69 149.90 0.12 64.3019425287356322
296 67018.50 160.36 0.09 65.0665048543689320
337 58588.59 164.04 0.03 66.5779431818181818
368 67174.81 162.97 0.06 62.1988981481481481
439 60358.30 156.47 0.07 63.5350526315789474
490 64538.23 164.50 0.07 65.1901313131313131
sessionC 获取的统计信息：
class_id sum max min avg
31 52791.57 164.41 0.00 62.8471071428571429
92 61404.44 154.07 0.10 64.6362526315789474
163 62794.69 158.28 0.18 61.5634215686274510
194 57102.65 162.49 0.03 63.4473888888888889
235 55942.69 149.90 0.12 64.3019425287356322
296 67018.50 160.36 0.09 65.0665048543689320
337 58588.59 164.04 0.03 66.5779431818181818
368 67174.81 162.97 0.06 62.1988981481481481
439 60358.30 156.47 0.07 63.5350526315789474
490 64538.23 164.50 0.07 65.1901313131313131
sessionD 获取的统计信息：
class_id sum max min avg
31 52791.57 164.41 0.00 62.8471071428571429
92 61404.44 154.07 0.10 64.6362526315789474
163 62794.69 158.28 0.18 61.5634215686274510
194 57102.65 162.49 0.03 63.4473888888888889
235 55942.69 149.90 0.12 64.3019425287356322
296 67018.50 160.36 0.09 65.0665048543689320
337 58588.59 164.04 0.03 66.5779431818181818
368 67174.81 162.97 0.06 62.1988981481481481
439 60358.30 156.47 0.07 63.5350526315789474
490 64538.23 164.50 0.07 65.1901313131313131
测试 commit 的事务一致性
统计信息SQL:
select class_id,sum(record),max(record),min(record),avg(record) from t_student s left join t_student_course sc on sc.student_id=s.student_id left join t_grades g on g.grade_id=sc.id where class_id in (31,92,163,194,235,296,337,368,439,490) group by s.class_id order by s.class_id
更新前的统计信息
class_id sum max min avg
31 52791.57 164.41 0.00 62.8471071428571429
92 61404.44 154.07 0.10 64.6362526315789474
163 62794.69 158.28 0.18 61.5634215686274510
194 57102.65 162.49 0.03 63.4473888888888889
235 55942.69 149.90 0.12 64.3019425287356322
296 67018.50 160.36 0.09 65.0665048543689320
337 58588.59 164.04 0.03 66.5779431818181818
368 67174.81 162.97 0.06 62.1988981481481481
439 60358.30 156.47 0.07 63.5350526315789474
490 64538.23 164.50 0.07 65.1901313131313131
sessionA 执行更新SQL:
update t_student set class_id=class_id+1
update t_grades set record=record+5 where grade_id between 20000 and 50000
更新后(事务结束前)的统计信息：
class_id sum max min avg
31 62520.97 168.37 0.31 63.7318756371049949
92 73695.64 165.07 0.11 64.0831652173913043
163 64017.59 149.84 0.03 64.6642323232323232
194 66819.13 149.91 0.10 66.8191300000000000
235 68856.35 149.91 0.07 66.8508252427184466
296 64840.53 165.95 0.14 63.5691470588235294
337 60443.11 168.30 0.05 64.9925913978494624
368 69326.67 164.02 0.02 64.1913611111111111
439 60284.25 167.12 0.29 62.7960937500000000
490 57130.31 169.76 0.13 65.6670229885057471
currentTimeMillis:1544089241513
sessionA commit
通过 sessionD 获取的统计信息：currentTimeMillis:1544089241518,time difference(ms):5
通过 sessionC 获取的统计信息：currentTimeMillis:1544089244709,time difference(ms):3196
通过 sessionB 获取的统计信息：currentTimeMillis:1544089247823,time difference(ms):6310
通过 sessionA 获取的统计信息：currentTimeMillis:1544089250748,time difference(ms):9235
sessionA 获取的统计信息：
class_id sum max min avg
31 62520.97 168.37 0.31 63.7318756371049949
92 73695.64 165.07 0.11 64.0831652173913043
163 64017.59 149.84 0.03 64.6642323232323232
194 66819.13 149.91 0.10 66.8191300000000000
235 68856.35 149.91 0.07 66.8508252427184466
296 64840.53 165.95 0.14 63.5691470588235294
337 60443.11 168.30 0.05 64.9925913978494624
368 69326.67 164.02 0.02 64.1913611111111111
439 60284.25 167.12 0.29 62.7960937500000000
490 57130.31 169.76 0.13 65.6670229885057471
sessionB 获取的统计信息：
class_id sum max min avg
31 62520.97 168.37 0.31 63.7318756371049949
92 73695.64 165.07 0.11 64.0831652173913043
163 64017.59 149.84 0.03 64.6642323232323232
194 66819.13 149.91 0.10 66.8191300000000000
235 68856.35 149.91 0.07 66.8508252427184466
296 64840.53 165.95 0.14 63.5691470588235294
337 60443.11 168.30 0.05 64.9925913978494624
368 69326.67 164.02 0.02 64.1913611111111111
439 60284.25 167.12 0.29 62.7960937500000000
490 57130.31 169.76 0.13 65.6670229885057471
sessionC 获取的统计信息：
class_id sum max min avg
31 62520.97 168.37 0.31 63.7318756371049949
92 73695.64 165.07 0.11 64.0831652173913043
163 64017.59 149.84 0.03 64.6642323232323232
194 66819.13 149.91 0.10 66.8191300000000000
235 68856.35 149.91 0.07 66.8508252427184466
296 64840.53 165.95 0.14 63.5691470588235294
337 60443.11 168.30 0.05 64.9925913978494624
368 69326.67 164.02 0.02 64.1913611111111111
439 60284.25 167.12 0.29 62.7960937500000000
490 57130.31 169.76 0.13 65.6670229885057471
sessionD 获取的统计信息：
class_id sum max min avg
31 62520.97 168.37 0.31 63.7318756371049949
92 73695.64 165.07 0.11 64.0831652173913043
163 64017.59 149.84 0.03 64.6642323232323232
194 66819.13 149.91 0.10 66.8191300000000000
235 68856.35 149.91 0.07 66.8508252427184466
296 64840.53 165.95 0.14 63.5691470588235294
337 60443.11 168.30 0.05 64.9925913978494624
368 69326.67 164.02 0.02 64.1913611111111111
439 60284.25 167.12 0.29 62.7960937500000000
490 57130.31 169.76 0.13 65.6670229885057471
这是事务更新t_student 的全部500条记录， t_grades中的部分记录（3W）。 看这次输出结果，无论是 rollback测试 还是 commit测试 中，sessionD 在 sessionA 操作rollback或者commit操作后3~5毫秒内查询数据的结果和最后sessionA的查询结果一致。
总结
此次更新通过多种方案，验证了【Fully ACID】特性在跨coordinator时也是有效的。
