7. 数据库并发控制

作者: BeautifulSoulpy | 来源:发表于2020-11-18 20:35 被阅读0次

7. 数据库并发控制
你应该了解的MySQL锁分类
一文理解Mysql MVCC
数据库的隔离级别和 MVCC
一篇讲透如何理解数据库并发控制
数据库的锁机制
数据库并发控制技术总结
你真的懂MVCC吗？来手动实践一下？
Kubernetes并发控制和资源变更
Mvcc 学习笔记

1.并发

锁

锁粒度：
表级锁
行级锁
锁：
读锁：共享锁，只读不可写，多个读互不阻塞
写锁：独占锁,排它锁，一个写锁会阻塞其它读和它锁
实现
存储引擎：自行实现其锁策略和锁粒度
服务器级：实现了锁，表级锁；用户可显式请求
分类：
隐式锁：由存储引擎自动施加锁
显式锁：用户手动请求

锁策略：在锁粒度及数据安全性寻求的平衡机制
显式使用锁
LOCK TABLES 加锁
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type: READ ， WRITE
UNLOCK TABLES 解锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
查询时加写或读锁

读锁
# 所有 用户读 都会 卡住
MariaDB [hellodb]> LOCK table teachers read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> insert teachers values(5, 'a',20,'M');
ERROR 1099 (HY000): Table 'teachers' was locked with a READ lock and can't be updated

MariaDB [hellodb]> unlock tables;

写锁
# 写锁会堵塞所有的读写操作；
MariaDB [hellodb]> lock table teachers write;
MariaDB [hellodb]> unlock tables;

# 再次锁，访问就是查询缓存；
MariaDB [hellodb]> lock table teachers write;

# 设置锁住，查询缓存不可以；
MariaDB [hellodb]> set query_cache_wlock_invalidate=on;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show variables like 'query%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_alloc_block_size       | 8192     |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | ON       |
| query_prealloc_size          | 8192     |
+------------------------------+----------+

2. 事务

事务Transactions：一组原子性的SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
ACID特性：
A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部
失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性
状态（转账一致性）
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务
所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中（签合同）

事务日志不完整就撤销，

启动事务：
BEGIN
BEGIN WORK
START TRANSACTION
结束事务：
COMMIT：提交
ROLLBACK: 回滚
注意：只有事务型存储引擎中的DML语句方能支持此类操作
自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交
建议：显式请求和提交事务，而不要使用“自动提交”功能
事务支持保存点：savepoint
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

# 非自动提交状态0下，增删改都不是真正生效，
MariaDB [hellodb]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0|
+--------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> rollback;    # 回滚
Query OK, 0 rows affected (0.00 sec)

开启事务，只要没有commit，都不会提交；

MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> insert teachers values(9,'e',30,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   9 | e             |  30 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

MariaDB [hellodb]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+

2.1 事务隔离级别

事务隔离级别：从上至下更加严格
 1. READ UNCOMMITTED 可读取到未提交数据，产生脏读(中间状态事务)
 2. READ COMMITTED 可读取到提交数据，但未提交数据不可读，产
生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
 3. REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即
读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改
前的旧数据。此为MySQL默认设置(秒杀商品)
 4. SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未
提交的修改事务阻塞读事务。导致并发性能差（就自己用加读写锁，安全）

MVCC: 多版本并发控制，和事务级别相关

指定事务隔离级别：
设置方法有两种；
1. 服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和
SESSION级进行设置
SET tx_isolation=''
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE
2.服务器选项中指定
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

3. 并发控制

死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
事务日志：
事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称
为：预写式日志 write ahead logging
事务日志文件： ib_logfile0， ib_logfile1

谁的影响小，就牺牲谁；

死锁状态1，故意加锁导致的 不可写
MariaDB [hellodb]> show processlist;
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| Id | User | Host      | db      | Command | Time | State | Info             | Progress |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
|  5 | root | localhost | hellodb | Query   |    0 | NULL  | show processlist |    0.000 |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)

# 可以使用kill ；
MariaDB [hellodb]> kill 5;

死锁状态2： 互相等待状态；