Notes on MySQL
Setup A Demo Instance
start service via docker docker run --name mysql -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 -d mysql:8.0-debian
interactive with mysql CLI docker exec -it mysql /bin/bash
ACID
- atomicity: all done or un-do
- consistency: all things consistency between one transaction
- isolation: separate transactions work well
- durability: once done no rollback even if process exit
事务隔离级别概念、意义、对应场景
概括
| Isolation level | dirty-read | non-repeatable-read | phantom-read |
|---|---|---|---|
| READ-UNCOMMITTED | possible | possible | possible |
| READ-COMMITTED | not possible | possible | possible |
| REPEATABLE-READ | not possible | not possible | possible |
| SERIALIZABLE | not possible | not possible | not possible |
复现 dirty-read、non-repeatable-read、phantom-read 三种数据不一致情况见同目录下 Python 脚本程序示例。
查版本 select version();
查当前会话隔离级别 select @@transaction_isolation;
change isolation level set global transaction isolation level read uncommitted;
setup for demo
sql
SET autocommit = 0;
create database test;
use test;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
insert into user (id,name,age) values (1,'old kate', 1);Isolation examples Python runtime requirement: pip install --user pymysql cryptography
https://zhuanlan.zhihu.com/p/117476959
MVCC
- https://stackoverflow.com/questions/68443220/how-mvcc-works-with-lock-in-mysql
- https://stackoverflow.com/questions/27499/what-is-multiversion-concurrency-control-mvcc-and-who-supports-it
- https://zhuanlan.zhihu.com/p/676793594
为何 InnoDB 引擎内部使用 B+ 树实现
查增删整体性能优、n log (n)？
https://zhuanlan.zhihu.com/p/149287061
TBD.
High Availability Solutions
https://developer.aliyun.com/article/1335819
- mmm multi-master replication manager
- mha master high availability
- mgr multi group replication