GBase 8a在V95版本支持session事务内内对一张表的多次更新变动DML *** 作

GBase 8a在V95版本支持session事务内内对一张表的多次更新变动DML *** 作

Gbase 8a数据库集群，V8版本不支持一个session事务内对同一个表的多次DML *** 作，必须先commmit或rollback才可以。在V95版本里，提供了这个功能。

事务配置参数
管理节点增加或修改如下配置

gcluster_transaction_disable=0

数据节点增加或修改如下配置

gbase_tx_log_mode=USE,STANDARD_TRANS

测试用例
如下提供一个事务内，对单表和多表做多个变动的样例。 其中多表更新一旦有并发时，请一定注意【死锁】问题，毕竟严格角度讲，8a支持的事务还是非常非常弱的。

单表多次DML变动
如下演示了一个表，开启事务后，连续做insert,update,delete,load的效果。

[gbase@localhost ~]$ gccli testdb

Gbase client 9.5.2.26.121440. Copyright (c) 2004-2020, Gbase.  All Rights Reserved.

gbase> set autocommit=0;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
+------+
1 row in set (Elapsed: 00:00:00.01)

gbase> insert into t1 values(1);
Query OK, 1 row affected (Elapsed: 00:00:00.00)

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
|    1 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> rollback;
Query OK, 0 rows affected (Elapsed: 00:00:00.51)

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
+------+
1 row in set (Elapsed: 00:00:00.01)

gbase> insert into t1 values(1);
Query OK, 1 row affected (Elapsed: 00:00:00.00)

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
|    1 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> update t1 set id=98 where id=99;
Query OK, 1 row affected (Elapsed: 00:00:00.08)
Rows matched: 1  Changed: 1  Warnings: 0

gbase> select * from t1;
+------+
| id   |
+------+
|   98 |
|    1 |
+------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> rollback;
Query OK, 0 rows affected (Elapsed: 00:00:00.51)

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
+------+
1 row in set (Elapsed: 00:00:00.01)

gbase> insert into t1 values(1);
Query OK, 1 row affected (Elapsed: 00:00:00.00)

gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.101/home/gbase/t.txt' into table t1;
Query OK, 1 row affected (Elapsed: 00:00:01.17)
Task 1028 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
| 8888 |
|    1 |
+------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> rollback;
Query OK, 0 rows affected (Elapsed: 00:00:00.52)

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
+------+
1 row in set (Elapsed: 00:00:00.01)

gbase> insert into t1 values(1);
Query OK, 1 row affected (Elapsed: 00:00:00.00)

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
|    1 |
+------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.101/home/gbase/t.txt' into table t1;
Query OK, 1 row affected (Elapsed: 00:00:01.06)
Task 1029 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
|    1 |
| 8888 |
+------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> commit;
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
|    1 |
| 8888 |
+------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> rollback;
Query OK, 0 rows affected (Elapsed: 00:00:00.50)

gbase> select * from t1;
+------+
| id   |
+------+
|   99 |
|    1 |
| 8888 |
+------+
3 rows in set (Elapsed: 00:00:00.00)

多表各自多次DML变动
如下演示一个事务内，对多个表各自做DML *** 作的效果。

gbase> set autocommit=0;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> insert into t2 select * from t1;
Query OK, 3 rows affected (Elapsed: 00:00:00.06)
Records: 3  Duplicates: 0  Warnings: 0

gbase> select * from t2;
+------+---------------------+
| id   | name                |
+------+---------------------+
|    1 | 2019-08-23 10:02:23 |
|    3 | third               |
|  456 | '456456'            |
+------+---------------------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> update t1 set name='333333' where id=3;
Query OK, 1 row affected (Elapsed: 00:00:00.02)
Rows matched: 1  Changed: 1  Warnings: 0

gbase> select * from t1;
+------+---------------------+
| id   | name                |
+------+---------------------+
|    1 | 2019-08-23 10:02:23 |
|    3 | 333333              |
|  456 | '456456'            |
+------+---------------------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> update t2,t1 set t2.name=t1.name where t1.id=t2.id;
Query OK, 3 rows affected (Elapsed: 00:00:00.18)
Rows matched: 3  Changed: 3  Warnings: 0

gbase> select * from t2;
+------+---------------------+
| id   | name                |
+------+---------------------+
|    1 | 2019-08-23 10:02:23 |
|    3 | 333333              |
|  456 | '456456'            |
+------+---------------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> update t2,t1 set t2.name=t1.name where t1.id=t2.id;
Query OK, 3 rows affected (Elapsed: 00:00:00.28)
Rows matched: 3  Changed: 3  Warnings: 0

gbase> select * from t2;
+------+---------------------+
| id   | name                |
+------+---------------------+
|    1 | 2019-08-23 10:02:23 |
|    3 | 333333              |
|  456 | '456456'            |
+------+---------------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> select * from t1;
+------+---------------------+
| id   | name                |
+------+---------------------+
|    1 | 2019-08-23 10:02:23 |
|    3 | 333333              |
|  456 | '456456'            |
+------+---------------------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> commit;
Query OK, 0 rows affected (Elapsed: 00:00:00.10)

总结
非必要的前提下，还是不建议使用事务功能，特别是并发使用。

对同一个表，一个事务内的多次更新可以支持，但多个连接session的同一个表的并发更新还是不支持的。