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