（三）MySQL锁机制 + 事务
转：
（三）MySQL锁机制 + 事务
表锁（偏读）
偏向MyISAM存储引擎。开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低。
- 查看当前数据库中表的上锁情况，0表示未上锁。
show open tables;- 添加读/写锁
lock table 表名 read(write) , 表名2...- 释放表锁
unlock table;加读锁（共享）
- 当前会话和其他会话均可读取加了读锁的表
- 当前会话不能读取其他表（先把自己的事干好）
- 其他会话要修改加了读锁的表，必须等待锁释放（阻塞状态）
加写锁（独占）
- 当前会话可以读取和修改加了写锁的表
- 当前会话也不能读取其他的表（先把自己的事干好）
- 其他会话想要读取加了写锁的表，必须等待锁释放（阻塞状态）
结论
MyISAM在执行查询语句时，会自动给涉及的所有表加读锁。再执行增删改操作时，会自动给涉及的表加写锁。
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
表锁的分析
- 查看哪些表被锁了，0表锁未锁：
show open table 通过
show status like 'table%'命令- Table_locks_immediate：产生表级锁定的次数，每立刻获取一次，锁值加1
- Table_locks_waited：不能立刻获取锁的次数，每等待一次锁值加1。此值高说明存在严重的表级锁争用情况。
- 此外，MyISAM是写锁优先调度，这也是不适合做主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
行锁（偏写）
偏向InnoDB存储引擎。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大两个不同点：①支持事务，②采用了行级锁
- 操作同一行数据
修改表中的同一行数据，将导致 session2 发生阻塞，一旦 session1 提交事务，session2 将执行更新操作。
- 操作不同行数据
由于采用行锁，session2 和 session1 互不干涉，所以 session2 中的修改操作没有阻塞
无索引导致行锁升级为表锁
session1
set autocommit=0; #关闭自动提交事务 #varchar数据类型不用‘’，导致系统自动转换类型，导致索引失效
update test_innodb_lock set a=44 where b=4000;session2
set autocommit=0; update test_innodb_lock set b='9001' where a=9;
#执行发现发生了阻塞- session中的SQL语句索引失效，导致行锁变为表锁。session2需要等待session1提交事务释放锁。
为什么建表后要创建索引？因为不创建索引的话，更新操作的时候，行锁会因为没有索引而变为表锁。
想要锁定某一行时，在后面加上：
for update，其他的操作会被阻塞，知道锁定行的会话提交。
总结
- InnoDB实现了行级锁定，带来的性能损耗比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM
- InnoDB的行级锁也有脆弱的一面，当我们使用不当的时候（索引失效变为表锁），可能会让InnoDB的整体性能比MyISAM的差。
事务
事务的特性
| 特性 | 说明 |
|---|---|
| 原子性（Atomicity） | 一个事务中的所有操作，要么全部成功，要么全部失败 |
| 一致性（Consistency） | 事务开始之前和结束之后，数据库的完整性没有被破坏 |
| 隔离性（Isolation） | 不同事务之间互不影响（有四种隔离级别） |
| 持久性（Durability） | 事务一旦提交，其结果就会持久化，就算发生宕机也能恢复数据 |
一致性的例子：转账来说，A和B的钱一共5000，不管A和B如何转账，事务结束后A和B的钱加起来还得是5000。
数据库层面怎么实现事务？
- InnoDB已经帮我们开启了事务，并且每一条语句都会自动提交。如果要保证多条语句的事务性，就要手动开启事务。
InnoDB存储引擎对ACID的实现方式
利用回滚日志（undo log） 和 重做日志（redo log） 两种表实现事务，并实现 MVCC (多版本并发控制)；
redo log（重做日志）
- 用于记录数据修改后的状态
- 每当有操作执行前，先将相关操作写入redo log。这样当断电等意外发生时，系统恢复后，可以继续完成这些更改。
Binlog日志的两个最重要的使用场景？
- MySQL主从复制
- 数据恢复（通过
mysqlbinlog工具）redolog 和 binlog的区别？
- redolog是InnoDB特有的，binlog是MySQL的Server层实现的，所有引擎都能使用。
- redolog是物理日志，binlog是逻辑日志
- redolog是循环着写的，会覆盖，因为空间是固定的会用完。binlog是追加写的，写到一定大小后会切换到下一个。
- redolog在事务执行过程中不断写入，binlog是在事务最终提交前写入的。
undo log（回滚日志）
- 用于记录数据修改前的状态
- 当一些更改执行一半发生意外时，而无法完成，则可以根据undo log恢复到之前的版本。（undolog记录的是逻辑日志）
- 相反的记录。当delete一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然。当 update 一条记录时，它记录一条对应相反的 update 记录。
例如某一时刻数据库宕机了，有两个事务，一个事务已经提交，另一个事务正在处理。数据库重启后，就要根据日志进行前滚或回滚。把已提交事务的更改写到数据文件，未提交的事务更改恢复到事务开始前的状态。
MySQL中ACID底层实现
- 原子性：主要依靠undo.log日志实现，即在事务失败时执行回滚。
- 持久性：主要依靠redo.log日志实现。首先，MySQL持久化通过缓存来提高效率。但由于缓存断电就没了，所以需要redo.log日志。在执行修改操作时，sql会先写入到redo.log日志，再写入缓存中。这样即使断电，也能保证数据不丢失，达到持久性
- 隔离性：数据库四种隔离级别，通过MVCC和锁实现。
- 一致性：而上面三种特性就是为了保证数据库的有一致性（redolog+undolog）
事务并发带来的问题
- 丢失更新：（更新操作 加上排它锁来避免）
- 某一个事务的回滚或提交，导致另一个事务已更新数据的丢失
- 脏读：读取了其他并发事务未提交的数据。破坏了事务的隔离性。
- 不可重复读：读取了其他并发事务提交的数据。针对update和delete，也是破坏了事务的隔离性。
- 幻读：读取了其他并发事务提交的数据。针对insert
隔离级别：读未提交—>读已提交RC—>可重复读RR—>可串行化
- MySQL5.5+的InnoDB，在RR级别下能解决幻读。
V1、V2、V3的值在不同隔离级别下分别是多少？
- 读未提交：20、20、20
- 读已提交：18、20、20
- 可重复读：18、18、20
- 可串行化：18、18、20
事务隔离级别的实现方式
LBCC（Lock-Based Concurrent Control）基于锁的并发控制
InnoDB锁：
- 共享锁又名读锁，共享锁是可以重入的，但无法修改。
- 排它锁又名写锁
- 意向锁：意向锁是表锁，是创建共享锁或排它锁时自动创建的，无法手动创建。作用是，用来告诉你有没有已经锁定的数据，能提高加表锁的效率。
InnoDB锁的底层实现
这是一个只有主键id为1、5、9、11的表，锁的区间如下：
Gap是开区间，Next-Key右边是闭区间。
- Recrod Locks——记录锁（等值匹配，精准匹配）
select * from user where id=1 for update;锁住本条 id = 1 的记录
- Gap Locks——间隙锁（范围匹配）
select * from user where id>5 and id<9 for update;锁住 (5,9)的区间，这时候就不能插入了。间隙锁只存在于RR隔离级别。
思考：
select * from user where id>15 for update;锁哪里？范围匹配，触发间隙锁，因为表里最大只有11，索引间隙锁会把
(11,+oo)的区间锁住，插入id=12的数据也不行。
- Next-Key Locks——临键锁（范围匹配，且命中某一条记录）
临键锁是 记录锁和间隙锁的结合。
select * from user where id>5 and id<11 for update;范围匹配，且命中表中的记录 9，触发临键锁 。锁住了(5,9]和(9,11]，即(5,11]。所以插入id=11的数据也不行。
MVCC（Multi-Version Concurrent Control） 多版本的并发控制。
- 一条记录在系统中可以存在多个版本，是InnoDB实现事务并发的重要功能。
具体的实现时是，在数据库的每一行，添加额外的三个字段。
DB_TRX_ID：记录更新该行的最后一个事务IDDB_ROLL_PTR：指向该行对应的 undo log 的指针DB_ROW_ID：单调递增的行ID，就是AUTO_INCREMENT的主键id
快照读与当前读
InnoDB拥有一个自增的全局事务ID，每一个事务开启，都会记录当前事务的唯一ID。同时，新事务创建时，事务系统会将当前未提交的所有事务id 组成的数组 提供 给这个新事务，这个数组我们称为 TRX_ID 集合
- 快照读
单纯的select操作。
每一个事务更新数据时，都会记录最后更新的事务 DB_TRX_ID。
如果这一行数据的DB_TRX_ID在TRX_ID集合中 大于 当前事务的事务ID，那么就说明这行数据是在当前事务开启后提交的。否则说明这行数据是在当前事务开启之前提交的。
如果遇到了当前事务开启后提交的数据，当前事务会通过DB_ROLL_PTR找到回滚日志，然后进行逻辑上的回滚拿到事务开启时的原数据。
这个通过 undolog+数据行 获取到事务开启时的原始数据的过程就是“快照读”。
详细解释：
BD_TRX_ID是别人的事务ID，我们的事务ID和别人的事务ID都是在未提交事务集合里的，如果别人的事务ID大于我的事务ID，说明这个事务是比我后开启的，那么就说明这行数据是我开启事务后，其他事务改的
- 当前读
很多时候，我们读取数据库的时候，需要读取的是行的当前数据，不是事务开启时的原始数据。
当前读是通过锁实现的，通过next-key算法将这区域锁上了，其他事务无法修改。
主要包含以下操作：
insert
update
select ... lock in share mode #共享锁
select ... for update #排它锁Innodb在RR级别如何避免幻读？
- 在快照读情况下（select），通过MVCC来避免幻读
通过undo log，找到原始数据。
- 在当前读情况下，通过next-Key来避免幻读
问题：InnoDB能解决幻读，但是解决的并不完美。用MVCC实现快照读存在缺陷，就是一旦某个事务的修改操作，覆盖到了其他事务插入的“幻行”，那么这些“幻行”在下次查询时就会再次出现，从而出现幻象问题。
转：
（三）MySQL锁机制 + 事务
最新文章
- 用div和css样式控制页面布局
- C# 属性、索引
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)--MySQL错误
- Crystal Report 遇到需要登录的问题
- ArchLinux KDE安装中文输入法
- 使用java代码，动态给TextView设置drawable
- centos用yum安装mysql-server
- c#基础班笔记
- 文件操作类CFile
- Kruskal(克鲁斯卡尔)
- Servlet 中为多项选择题判分---String类的indexOf()方法妙用
- Git 深入浅出
- 8.20.1 图形化：弹窗JOptionPane
- Java NIO学习笔记---Channel
- tarjan 求割点
- hdu 1754 I Hate It（树状数组区间求最值）2007省赛集训队练习赛（6）_linle专场
- Xcode 常用调试技巧总结
- clipboard.js 实现web端---> 复制到剪切板功能
- java多线程知识点汇总（四）多线程知识点脉络图
- Android studio通过连接蓝叠模拟器大幅提高速度！