事務具有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.005610s2）查看事務的隔離級別
> 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巨杉資料庫分布式事務管理的演示，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/19672.html
標籤：大數據
上一篇：hadoop 多次格式化format namenode 導致節點不能啟動解決
下一篇：【趙強老師】搭建Hadoop環境