|
事务具有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巨杉数据库分布式事务管理的演示。
|
