Mysql锁模块

最新推荐文章于 2023-02-05 14:25:10 发布

chushuaihong0463

最新推荐文章于 2023-02-05 14:25:10 发布

阅读量164

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/u/4000872/blog/3094190

版权

一、InnoDB和MyISAM引擎锁方面的区别

1. InnoDB

InnoDB默认支持行级锁，也支持表级锁。

InnoDB在sql中用到索引时使用的时行级锁；没有用到索引的时候，或者索引不明确时（>,<,like）使用的是表级锁。

InnoDB（增删改）时会自动加锁，select操作时不加锁（默认是用的非锁定读），需要显示加写锁（for update）和读所（lock in share mode）

2. MyISAN

MyISAM默认支持表级锁，不支持行级锁。

MyISAM（增删改查）都会自动上锁。

MyISAM不支持事务

二、数据库事务四大特效

1.原子性

2.隔离性

3.一致性

4.持久性

三、Mysql事务隔离级别,以及各级别下的并发访问问题

更新丢失--mysql所有事务隔离级别在数据库层面上均可避免

脏读--READ COMMITTED事务隔离级别以上可避免

不可重复读--REPEATABLE READ事务隔离级别以上可避免

幻读--SERIALIZABLE事务隔离级别可避免

1. READ UNCOMMITTED (未提交读)

事务中的修改，即使没有提交，对其他事务也都是可见的。

事务可以读取未提交的数据，出现脏读（dirty read）(读取别的事务还未提交的数据)

2. READ COMMITTED (提交读)

一个事务从开始直到提交前，所做的任何修改对其他事务都是不可见的；也叫不可重复读（nonrepeatable read），因为两次相同的查询可能会得到不一样的结果。

大多数数据库系统（Sql Server , Oracle）的默认隔离级别 READ COMMITTED(msyql不是)。

出现不可重复读问题: 在当前事务中多次读取同一数据结果不一致（侧重与同一数据的修改）

A: start transaction .	B: start transaction
A: select 100;
	B: update 100 + 100
A: select 100 ;
	B: commit;
A: select 200;
A: commit;

3. REPEATABLE READ (可重复读）

mysql默认事务隔离级别

InnoDB和XtraDB引擎通过多版本并发控制（MVCC, Multiversion Concurrency Control)解决了幻读

解决了不可重复读；

A: start transaction .	B: start transaction
A: select 100;
	B: update 100 + 100
A: select 100 ;
	B: commit;
A: select 100;
A: update + 100
A: select 300

出现幻读（侧重于新增和删除），当某个事务在读取某个范围内数据时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内记录时，会产生幻行。

repeatable read隔离级别下

A: start transaction .	B: start transaction
A: select ... lock in share mode（当前读）;
	B: insert into .. value() A使用的是当前读，阻塞无法插入（避免了幻读）
A: commit

A: start transaction .	B: start transaction
A: select 10条 (当前读)
	B: insert into ...value()
	B: commit
A: update ...(修改了11条)
A: commit

select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

A: start transaction .	B: start transaction
A: select where id = 1; empty
	B: insert into value(1)
	B: commit
A: insert into value(1); error
A: select where id = 1; empty
A: commit

read committed隔离级别下

A: start transaction .	B: start transaction
A: select ... lock in share mode;(3行)
	B: insert into .. value()（插入1行) （实际情况：read committed 自动变为repeatable read 级别，阻塞无法插入）
	B: commit;
A: update (更新了4行）
A: commit

4. SERIALIZABLE (可串行化)

SERIALIZABLE 最高的隔离级别；

通过强制事务串行执行，避免了幻读问题；SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。

实际应用中很少用到这种隔离级别，只有在非常需要确保数据一致性而且接受没有并发的情况下，才考虑采用该级别

四、死锁

死锁：指两个或者多个事务在同一资源上相互占有，并请求对方占用的资源

InnoDB目前处理死锁的方法是：将持有最少行级锁的事务进行会滚

五、RC、RR级别下的InnoDB的非阻塞读（快照读）如何实现

数据行里隐藏字段的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段

DB_TRX_ID：该字段用来标识最近一次对本行记录做修改（update ,delete）（最后一次修改本行记录的事务id）

DB_ROLL_PTR: 回滚指针

DB_ROW_ID: 隐示创建的自增主键字段

undo日志：对记录变更操作时产生undo记录，存储的是老版数据，当一个久的事务需要读取事务时，为了能够读取老版本的数据，需要顺着undo链找到其满足可见性的记录，分为insert(事务回滚时需要，并在事务提交后立即丢弃)和update undo log

insert undo log: 事务回滚时需要，并在事务提交后立即丢弃

update undo log:事务对记录进行delete 或update操作时产生的undo log, 在事务回滚时和快照读时需要

1. 锁住该行日志，

2. 并将数据拷贝一份到undo log,

3. 修改当前行的值，

4. 填写事务id(DB_TRX_ID)，

5. 使用回滚指针（DB_ROLL_PTR）指向undo log中修改前的行

read view ：做可见性判断，当执行快照读select时，会针对查询的数据创建一个read view,来决定当前事务能看到的是哪个版本的数据（可能是最新数据，也可能只允许看undo log某个版本的数据），read view遵循一个可见性算法，将要修改的数据DB_TRX_ID取出来与系统其他活跃事务id做对比，如果大于或等于这些事务id，就通过DB_ROLL_PTR指针去取出undo log上一层的DB_TRX_ID直到小于这些活跃事务id为止，保证了获取到的数据版本是当前可见的最稳定的版本。

mysql源码，保存活动事务的地方，m_low_limit_id(活动事务最大id),m_up_imit_id(活动事务最小id)。start transtaction,越新开启的事务id越多，递增

六、InnoDB可重复读隔离级别下如何避免幻读

表象：快照读（非阻塞读）--伪MVCC，（并不是多版本共存，undo log是串行化的结果），读不加锁，读写不冲突

内在：next-key锁（行锁+gap锁）

（无论是当前读还是快照读，在innodb的RR的事务隔离级别下都可以避免幻读。在快照读的情况下，innodb通过mvcc来避免幻读；在当前读的情况下，innodb通过next-key锁来避免幻读）

当前读：加了锁的增删改查，（不管是共享锁还是排他锁均为当前读，insert, delete, update, select ... for update, select ... lock in share mode）

读取的是记录的最新版本，并且读取之后还需要保证其他并发事务不能修改当前记录，对读取的记录加锁。

快照读：不加锁的非阻塞读，select；；

serializable隔离级别下是串行读，快照读退化成当前读；

快照读的实现基于多版本并发控制（MVCC），避免了加锁操作，提高并发性能, 开销更低；

快照读有可能读到的数据并不是最新版本，可能是之前的历史版本

read committed级别下，当前读和快照读的结果一样的，数据版本一样,(读取的是另一个事务提交后的结果)

A: start transaction .	B: start transaction
A: select 100 (快照读); 创建新的快照read view
	B: update 100 + 100
	B: commit
A: select(快照读) 200; 创建新的快照read view
A: select lock in share mode(当前读) 200
A: commit

repeatable read级别下，快照读（读取的可能是数据未修改前的版本，创建快照读的时机决定了读取数据的版本），当前读（读取的是数据的最新版本）

A: start transaction .	B: start transaction
A: select 100（快照读），会创建一个快照read view,将当前系统活跃的事务id记录起来
	B: update 100 + 100
	B: commit
A: select (快照读) 100,读到的是历史版本；事务开启后未做读取操作，则此时读取的是最新版本 200 使用的是前一个快照的read view
A: select lock in share mode(当前读) 200，最新版本
A: commit

READ COMMITTD在每一次进行普通SELECT操作前都会
生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查
询操作都重复使用这个ReadView就好了

next-key锁（行锁 + gap锁）

gap锁，间隙锁，锁定一个范围但不包括记录本身，防止同一事务的两次当前读出现幻读的情况

gap锁, read uncommitted， read committed 级别下没有，无法避免幻读

gap lock会用在非唯一索引和不走索引的当前读，以及仅命中检索条件的部分结果集；主键索引和唯一索引的当前读中；

repeatable read级别下当前读（删改查）对主键索引或者唯一索引会用gap锁吗？

1.对于主键索引和唯一索引的当前读，如果where条件全部命中（精确查询时所有记录都有），则不会用gap锁，只会加行锁，如果范围条件部分命中或者都不命中，则使用Gap锁。

2.对于主键索引和唯一索引的当前读，用相等条件检索数据时，存在使用行锁，不存在使用gap锁///

3.对于非唯一索引的当前读：使用Gap锁

4.对于不走索引的当前读：使用Gap锁（相当于锁表）

总结：插入操作对查询结果有影响就gap锁 + 行锁，没有只加行锁

select * from table where id in (1,3,5), id为1，3，5均在该table中存在,全部命中, 事务B新增一条数据在事务A查询的范围之外，事务B提交后，事务A再做当前读还是原来的数据，不会出现幻读。

A: start transaction .	B: start transaction
A: delete from tb where id=9 (主键索引或唯一索引);
	B: insert into tb value(10)， id=9数据存在，插入成功；不存在失败阻塞，id=9数据周围的间隙被锁住了
A: rollback

数据库中数据：4，5，80， 100

当前读要查询数据90；（80，90] 和（90，100] 区间会被gap锁住