mysql的并发控制

并发机制

锁：

锁机制

读锁：

• 共享锁，称为 S 锁,只读不可写,自己也不行（包括当前事务） ，多个读互不阻塞

写锁：

• 独占锁，排它锁，只能自己写。称为 X 锁,写锁会阻塞其它事务的读和写

锁冲突：

• S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r 锁的释放，此种情况也成为锁冲突

锁粒度：

• 表级锁：MyISAM
• 行级锁：InnodB

实现

• 存储引擎：自行实现其锁策略和锁粒度
• 服务器级：实现了锁，表级锁，用户可显式请求

分类：

• 隐式锁：由存储引擎自动施加锁
• 显式锁：用户手动请求

锁策略：

在锁粒度及数据安全性寻求的平衡机制

---

隐式锁：

由存储引擎自动施加锁，建议使用此类

显示锁使用：

帮助:https://mariadb.com/kb/en/lock-tables/

加锁：

lock tables 表 [别名] 锁类型 [,表2...]
flush tables [表] with read lock	#全局锁，清楚查询缓存，通常在数据备份前加
锁类型：
  read、write

解锁：

unlock tables;			释放当前会话中加的所有锁

例：

lock tables t1 read;
update t1 set name='qwer' where id=5;	#此时更新数据会报错

unlock tables;
update t1 set name='qwer' where id=5;
select * from t1 where id=5;

事务：

事务Transactions： 一组原子性的SQL语句，或一个独立工作单元
事务日志： 记录事务信息，实现undo,redo等故障恢复功能

事物原理：

• 数据的修改时，先把数据从磁盘读取到内存中，在内存中进行修改，每一次修改的记录，不会直接修改原数据，而是把每次的记录都保存在磁盘上的事务日志中去，数据的所有操作，都被事务日志完整记录下来，直到事物提交后才会把内存中修改的最后结果写入到磁盘中
• 如果事务日志只记录了一半，中途失效了，则会把该事务日志撤回。或者断电时，事务一记录完成，事务二记录一半，数据库启动后会把事务一的数据，根据日志修改完成，此操作为： redo，事务二日志进行撤回，此操作为： undo

说明：

• mysql数据~=vim数据，事物日志~=隐藏文件，事物操作~=隐藏文件操作
• 与vim编辑器有点类似，vim会先把文件读取到内存中，进行修改，每次修改都只是在内存中进行，并在磁盘中复制一个同名的临时的隐藏文件，把内存中的修改记录到隐藏文件，直到确定保存才会把内存中被修改的数据刷新到磁盘原文件，并删除隐藏文件，但如果断电，则不会动原文件，内存中的数据丢失，有修改记录的隐藏文件会保存下来，再次打开原文件，会提示同步到与隐藏文件一样还是不管隐藏文件（撤回，或者删除）

事物特性：

ACID特性：

atomicity原子性（A）：
整个事务中的所有操作要么全部成功执行，要么全部失败后回滚

consistency一致性（C）：
事务开始和完成时，数据必须保持一致的状态，数据库的完整性约束没有被破坏。比如A给B转账，不论转账事务是否成功，两者存款的原始总额不变

Isolation隔离性（I）：
一个事务在提交之前，不能为其它事务所见，也就是在内存中修改时的未存入磁盘的数据（dirty脏数据），不可被其他人看见
隔离有多种隔离级别，实现并发

durability持久性（D）：
一旦事务提交，其所做的修改会永久保存于数据库中，也就是内存的最终结果存入磁盘

Transaction事物生命周期

1. 开启事物
2. 对数据进行修改：insert、update、alter、delete等
3. 对事物进行提交或者回滚

显式启动事务：

三种都是启动方式

BEGIN
BEGIN WORK
START TRANSACTION

结束事务：

注意：

• 只有事务型存储引擎（Innodb）中的DML（增删改）语句支持此类操作。如果进行删表、删库就GG了

commit			提交
rollback		回滚
set autocommit={1|0}	开启自动提交，默认为1（开启）,显式不建议开启自动提交，失误了就GG了

例：

#第一个会话执行
select * from t1;
begin;
update t1 set name='123' where id=5;

#第二个会话查看，是否变化
select * from t1;

#回到第一个会话，提交后，第二个会话在次查看结果
commit;

#第二个会话再次查看，发生变化
select * from t1;

事务保存节点标记：

相当于快照，记录当前数据，回滚时直接回滚到此
需要在事物中标记，不在事物中标记无效

savepoint 标记名;
rollback [work] to 标记名;	回滚到标记的节点
release savepoint 标记名;		取消标记

查看事务：

#查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.innodb_trx;

#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.innodb_locks;

#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.innodb_lock_waits;

#被其他事务锁住时，可以手动杀占用事务，可能出现在其他人忘了，或者进程卡死了，最好先查看进程，并排查登录用户再杀进程
show processlist;	#查看占用的事务进程id（mysql内部）
kill pid		#这是mysql内置的kill命令，不是shell的kill

#事务锁的超时时间
show global variables like 'innodb_lock_wait_timeout'; 

#查看当前innodb引擎的状态，其中：TRANSACTIONS字段就是事物相关状态
show engine innodb status;

例：找到阻塞的事物和被阻塞的事物

#第一个会话执行
begin;
update t1 set name='123' where id=5;

#第二个会话执行
update t1 set name='321qwe' where id=5;

#第三个会话执行
SELECT * FROM INFORMATION_SCHEMA.innodb_trx\G;	显示两个运行的事物
SELECT * FROM INFORMATION_SCHEMA.innodb_locks;	显示两个使用锁的事物
SELECT * FROM INFORMATION_SCHEMA.innodb_lock_waits;	被阻塞的事物是第二个会话的

#找到阻塞的事物，杀了它
show engine innodb status\G;	#找到事物id，以及pid号
show processlist;		#查看pid号
kill 27;	#此时，杀掉一号会话后，二号会话的命令执行成功

事物并发出现的问题：

脏读： 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读：即原始读取不一样， 事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致

幻读： 系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读

小结：

• 不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除
• 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事物隔离级别：

MySQL支持四种隔离级别，事务隔离级别： 依次排序，序列化最严格

隔离级别	说明
read-uncommitted（读未提交）	可读取到未提交数据，产生脏读
read-committed（不可重复读）	解决脏读，只读取提交的数据，未提交数据不可读，产生不可重复读，即可读取到多个提交数据（每提交一次，数据变化一次），导致每次读取数据不一致
repeatable-read（可重复读）	可重复读，多次读取数据都一致，允许产生幻读（被修改的数据看不到，假数据），即读取过程中，即使有其它提交的事务修改数据，只能看到当时读取数据的时间点的数据，别人在该时间点后做的修改数据看不到，类似快照。此为MySQL默认级别
serializable（串行化）	可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞读事务（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

隔离级别对并发问题的支持：

隔离级别	脏读	不可重复读	幻读	加读锁
read-uncommitted（读未提交）	是	是	是	否
read-committed（不可重复读）	否	是	是	否
repeatable-read （可重复读）	否	否	是	否
serializable（串行化）	否	否	否	是

mvcc多版本并发控制机制：

MVCC只在read-commited、repeatable-read模式下工作。之所以
其他两个隔离级别都和MVCC不兼容。因为read-uncommitted，总是读取最新的数据行，而不是符合当前事务版本的数据行。而serializable，会对所有读取的行都加锁

优点：

• MVCC在InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读
• MVCC尽量减少锁使用的情况下高效避免：脏读、不可重复读、幻读

参考链接：https://blog.csdn.net/flying_hengfei/article/details/106965517

mvcc原理：

基于读快照实现（ReadView），在每次事物之前，都会拍一次快照，并记录到undolog日志中（记录事物之前数据，以及事物id），回滚时根据事物id判断，回滚到上一个事物id版本
undolog中会记录一行数据的完整历史版本，如事物id为10的1号版本数据：age=20，被修改后事物id为11的2号版本数据为：age=21，此时2号版本中有一指针字段，指向1号版本（1号就成了历史版本），形成链表（与C++的链表一样）

更改事物隔离级别：

#临时修改
set tx_isolation='READ-UNCOMMITTED';
级别：
  READ-UNCOMMITTED
  READ-COMMITTED
  REPEATABLE-READ
  SERIALIZABLE
  
#永久修改
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

死锁：

两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态