MySQL读写问题(锁)

一.概述

读-读：并发不存在问题,不需要加锁
写-写：并发存在问题,可能会造成脏写(一个事务没有写完,另一个事务也对相同的数据进行写),但是这种情况,任何一种隔离级别都不允许发生,在隔离级别的时候就解决了。
读-写/写-读：会造成脏读,幻读,不可重复读的问题。每个数据厂商对它的支持也是不相同的
- 解决方案：
  - 方案一：读操作利用MVCC,写进行加锁。
  - 方案二：读写都加锁。

怎么加锁：数据库自己就进行加锁，不需要手动加锁。除非想演示效果，就自己开事务自己加锁

表锁：
- lock tables 表名 read //表级读锁
- lock tables 表名 write //表级写锁
- unlock //释放锁
行锁：
- SQL语句 for share //行级读锁
- SQL语句 for update //行级写锁
- commit //释放锁

二.锁

基于锁的属性分类:

共享锁(S):读锁,可以并发的读数据
排他锁(X):写锁,独占锁

基于锁的粒度分类：(MyISAM使用表锁，innodb一般使用行锁)

表级锁
- 表级读锁：在执行select 之前，会给涉及的所有表加读锁。
- 表级写锁：在执行增删改之前，会给涉及的所有表加写锁。
- 意向锁：就是一个标志，某一行加上锁之后，就给整个表加一个意向锁，有新的锁加入表锁，就需要等待获得意向锁。
- 自增锁：就是表中的主键自增(auto-increment)也需要加锁。
- 元数据锁(MDL)：就是对表进行DDL的时候，需要进行加锁。
行级锁
- 记录锁：锁住每一条记录，就是行级读锁/行级写锁。
- 间隙锁(Gap)：就是为了解决幻读而提出的。(幻读除了使用间隙锁解决，还可以使用MVCC解决)，间隙锁还可能造成死锁。

- 临界锁：是记录锁和gap锁的合体，即锁住了某条记录，又阻止在该记录前插入新纪录。
- 插入意向锁：是一种间隙锁，专门针对的是数据行的插入操作，多个事务插入相同的索引间隙时，只要不是插入到相同的位置，则不需要进行锁等待。插入意向锁锁定了索引之间的间隙，但是插入意向锁之间没有互相阻塞。(间隙锁让范围之间不能插入，插入意向锁虽然也锁住了一个范围，但是不是同一个位置的就可以插入)　
页锁：锁着一页，是介于表锁和行锁之间，并发度一般。会出现死锁。

加锁方式分类

隐式锁：自动添加的，在insert的时候就会添加一个隐式锁。比如事务A在insert一条数据后,还没有提交,此时另一个事务B要修改这条新添加的数据,就不会成功。

显示锁：就是我们上面学的锁

其他的锁

死锁：两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
- 导致死锁的场景：
  - 间隙锁导致的死锁：比如两个事务A,B，都锁了(3,8)之间的范围，事务A进行插入数据到(3,8)发生阻塞，同理事务B进行插入数据到(3,8)也发生阻塞。但是它俩都僵持着，互相进行阻塞，就发生死锁
  - 页锁导致的死锁：事务A,B。A先锁住页1然后锁了页2，B先锁了页2然后锁了页1。
- 出现死锁，有2种策略
  - 方式一：直接进入等待，直到超时。这个超时时间可以通过innodb_lock_wait_timeout来设置。
  - 方式二：死锁检测，发现死锁，主动回滚死锁链中的某一个事务，让其他事务继续执行。通过innodb_deadlock_detect设置为on来开启。
- 如何避免出现死锁：(了解)
  - 如果不同的程序并发存取多个表，尽量以相同的顺序访问表。
  - 在程序以批量方式处理数据的时候，如果已经对数据排序，尽量保证每个线程按照固定的顺序来处理记录。
  - 在事务中，如果需要更新记录，应直接申请足够级别的排他锁，而不应该先申请共享锁，更新时在申请排他锁，因为在当前用户申请排他锁时，其他事务可能已经获得了相同记录的共享锁，从而造成锁冲突或者死锁。
  - 尽量使用较低的隔离级别。
  - 尽量使用索引访问数据，使加锁更加准确，从而减少锁冲突的机会。
  - 合理选择事务的大小，小事务发生锁冲突的概率更低。
  - 尽量用相等的条件访问数据，可以避免Next-Key锁对并发插入的影响。
  - 不要申请超过实际需要的锁级别，查询时尽量不要显示加锁。
  - 对于一些特定的事务，可以表锁来提高处理速度或减少死锁的概率。
全局锁：让整个数据库实例都加锁，不管是DDL还是DML都加锁。
- 使用场景：全库逻辑备份。
- 命令Flush tables with read lock；开启

三.MVCC

MVCC:多版本并发控制：多版本：undo链实现；控制：readView。主要针对于RC和RR两种事务。

为了提高数据库并发性能，用更好的方式去处理读-写冲突。读就是采用快照读，写就是采用当前读(加锁)。
前提知识：

快照读：不加锁的非阻塞读，比如select * from ...
当前读：读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。不仅限于读数据，修改数据也属于当前读。
行格式的隐藏列

undo 版本链：每一个数据行的隐藏指针指向自己undo版本链，undo版本链中应该存储的之前的逆操作，但是为了好理解，下图就画成数据原来的样子

ReadView：就是每一个事务在使用MVCC机制进行快照读操作时产生的读视图。每一个事务都会产生一个ReadView。

MVCC的实现依赖于隐藏字段，undo Log，ReadView

说白了，就是事务生成的ReadView与要修改的这条记录的隐藏列的tri_id进行比较，看是读取undo链中的历史呢，还是读取当前最新的数据呢。

MVCC如何解决幻读？

A事务先进行读，然后B事务中途插入一条数据后提交。A事务再次读，根据MVCC，发现trx_id大于low_limit_id，说明实在readView生成之后，才出现的事务B，所以不能读取该数据。就不会出现幻读。

寄语：生活像一只蝴蝶，没有破茧的勇气，哪来飞舞的美丽

巴特西

MySQL读写问题(锁)

最新文章

热门文章