事务具有ACID特性，本篇对Sequoia DB巨杉数据库的分布式事务进行介绍，并对当前数据库版本支持的RU（读未提交）、RC（读已提交）和RS（读稳定性）三种隔离级别进行设置和验证。

1 部署架构

本实验Sequoia DB巨杉数据库集群拓扑结构为单副本三分区，包括1个SequoiaSQL-MySQL数据库实例节点、1个存储引擎节点、1个编目节点和3个数据节点。

2 MySQL实例层创建库和表

2.1 连接MySQL

[sdbadmin@sdbserver1 mysql]$ mysql -h 127.0.0.1 -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.25 Source distribution



Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2.2 查看数据库和表

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Database changed

mysql> show create table emp\G;

*************************** 1. row ***************************

Table: emp

Create Table: CREATE TABLE `emp` (

`empno` int(11) NOT NULL AUTO_INCREMENT,

`ename` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`empno`)

) ENGINE=SEQUOIADB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

1 row in set (0.00 sec)



ERROR:

No query specified

mysql> select * from emp;

+-------+-------+------+

| empno | ename | age |

+-------+-------+------+

| 1 | Alen | 23 |

| 2 | Lucy | 25 |

| 3 | Tom | 30 |

| 4 | Jack | 35 |

+-------+-------+------+

4 rows in set (0.01 sec)

在SequoiaSQL-MySQL实例中创建的表将会默认使用SEQUOIADB存储引擎，包含主键或唯一键的表将会默认以唯一键作为分区键自动分区。

3 查看SequoiaDB事务隔离级别

1）进入SequoiaDB Shell交互界面并获取数据库连接

[sdbadmin@sdbserver1 mysql]$ sdb

Welcome to SequoiaDB shell!

help() for help, Ctrl+c or quit to exit

> var db=new Sdb('localhost',11810)

Takes 0.005610s

2）查看事务的隔离级别

> db.snapshot(SDB_SNAP_CONFIGS,{},{NodeName:'',transactionon:'',transisolation:''})

{

"NodeName": "sdbserver1:11800",

"transactionon": "TRUE",

"transisolation": 0

}

{

"NodeName": "sdbserver1:11810",

"transactionon": "TRUE",

"transisolation": 0

}

{

"NodeName": "sdbserver1:11820",

"transactionon": "TRUE",

"transisolation": 0

}

{

"NodeName": "sdbserver1:11830",

"transactionon": "TRUE",

"transisolation": 0

}

{

"NodeName": "sdbserver1:11840",

"transactionon": "TRUE",

"transisolation": 0

}

Return 5 row(s).

Takes 0.002294s.

transisolation参数指定隔离结拜，0表示隔离级别为读未提交。

transactionon为true表示开启事务功能。

4 验证事务隔离级别

4.1 验证RU读未提交隔离级别

1）开启会话1

[sdbadmin@sdbserver1 mysql]$ mysql -h 127.0.0.1 -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.25 Source distribution



Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> update test.emp set age=30 where empno=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0



mysql> select * from test.emp;

+-------+-------+------+

| empno | ename | age |

+-------+-------+------+

| 1 | Alen | 30 |

| 2 | Lucy | 25 |

| 3 | Tom | 30 |

| 4 | Jack | 35 |

+-------+-------+------+

4 rows in set (0.00 sec)

可看到empno=1的年龄已更改为30.

2）开启会话2

[sdbadmin@sdbserver1 ~]$ mysql -h127.0.0.1 -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.25 Source distribution



Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> select * from test.emp;

+-------+-------+------+

| empno | ename | age |

+-------+-------+------+

| 1 | Alen | 30 |

| 2 | Lucy | 25 |

| 3 | Tom | 30 |

| 4 | Jack | 35 |

+-------+-------+------+

4 rows in set (0.01 sec)



mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from test.emp;

+-------+-------+------+

| empno | ename | age |

+-------+-------+------+

| 1 | Alen | 30 |

| 2 | Lucy | 25 |

| 3 | Tom | 30 |

| 4 | Jack | 35 |

+-------+-------+------+

4 rows in set (0.01 sec)

可以看到，即使会话1没有提交，会话2都可以看到已修改过的数据。

3）会话1和会话2提交事务

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

4.2 验证RC读已提交隔离级别

1）修改事务隔离级别为RC

[sdbadmin@sdbserver1 ~]$ sdb

Welcome to SequoiaDB shell!

help() for help, Ctrl+c or quit to exit

> var db=new Sdb('localhost',11810)

Takes 0.005333s.

> db.updateConf({transisolation:1},{Global:true})

Takes 0.034368s.

2）查看节点事务隔离级别

> db.snapshot(SDB_SNAP_CONFIGS,{},{NodeName:'',transactionon:'',transisolation:''})

{

"NodeName": "sdbserver1:11800",

"transactionon": "TRUE",

"transisolation": 1

}

{

"NodeName": "sdbserver1:11810",

"transactionon": "TRUE",

"transisolation": 1

}

{

"NodeName": "sdbserver1:11820",

"transactionon": "TRUE",

"transisolation": 1

}

{

"NodeName": "sdbserver1:11830",

"transactionon": "TRUE",

"transisolation": 1

}

{

"NodeName": "sdbserver1:11840",

"transactionon": "TRUE",

"transisolation": 1

}

Return 5 row(s).

Takes 0.005910s.

transisolation为1 表示隔离级别为读已提交。

3）开启会话1

mysql> begin ;

Query OK, 0 rows affected (0.00 sec)



mysql> update test.emp set age=25 where empno=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

将age从30改为25。

mysql> select * from test.emp;

+-------+-------+------+

| empno | ename | age |

+-------+-------+------+

| 1 | Alen | 25 |

| 2 | Lucy | 25 |

| 3 | Tom | 30 |

| 4 | Jack | 35 |

+-------+-------+------+

4 rows in set (0.00 sec)

4）开启会话2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from test.emp;

+-------+-------+------+

| empno | ename | age |

+-------+-------+------+

| 1 | Alen | 30 |

| 2 | Lucy | 25 |

| 3 | Tom | 30 |

| 4 | Jack | 35 |

+-------+-------+------+

4 rows in set (0.01 sec)

可以看到，会话2无法看到修改后的数据。

5）会话1提交

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

6）会话2可以看到修改后的数据

mysql> select * from test.emp;

+-------+-------+------+

| empno | ename | age |

+-------+-------+------+

| 1 | Alen | 25 |

| 2 | Lucy | 25 |

| 3 | Tom | 30 |

| 4 | Jack | 35 |

+-------+-------+------+

4 rows in set (0.00 sec)

7）会话2提交

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

4.3 验证RS读稳定性隔离级别

1）修改事务隔离级别为RS

> db.updateConf({transisolation:2},{Global:true})

Takes 0.028669s.

2）查看节点事务隔离级别

> db.snapshot(SDB_SNAP_CONFIGS,{},{NodeName:'',transactionon:'',transisolation:''})

{

"NodeName": "sdbserver1:11800",

"transactionon": "TRUE",

"transisolation": 2

}

{

"NodeName": "sdbserver1:11810",

"transactionon": "TRUE",

"transisolation": 2

}

{

"NodeName": "sdbserver1:11820",

"transactionon": "TRUE",

"transisolation": 2

}

{

"NodeName": "sdbserver1:11830",

"transactionon": "TRUE",

"transisolation": 2

}

{

"NodeName": "sdbserver1:11840",

"transactionon": "TRUE",

"transisolation": 2

}

Return 5 row(s).

Takes 0.004875s.

transisolation为2 表示隔离级别为读稳定性。

3）开启会话1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from test.emp;

+-------+-------+------+

| empno | ename | age |

+-------+-------+------+

| 1 | Alen | 25 |

| 2 | Lucy | 25 |

| 3 | Tom | 30 |

| 4 | Jack | 35 |

+-------+-------+------+

4 rows in set (0.00 sec)

4）开启会话2更新数据

mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> update test.emp set age=30 where ename='Alen';

会话2的update操作发生等待，只有等会话1执行commit或rollback后，会话2才能执行成功。

5）会话1执行提交操作

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

以上就是对Sequoia DB巨杉数据库分布式事务管理的演示。