南大通用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的同一个表的并发更新还是不支持的。