mysql调优-事务及锁

最新推荐文章于 2023-12-04 09:59:49 发布

catch that elf

最新推荐文章于 2023-12-04 09:59:49 发布

阅读量411

点赞数 1

分类专栏： mysql 文章标签： mysql调优事务排它锁共享锁死锁

本文链接：https://blog.csdn.net/zhangxm_qz/article/details/88187970

版权

学习笔记同时被 2 个专栏收录

140 篇文章 0 订阅

订阅专栏

mysql

10 篇文章 0 订阅

订阅专栏

事务特性及隔离级别

什么是事务

事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；
事务是一组不可再分割的操作集合（工作逻辑单元）;
典型事务场景(转账)：
update user_account set balance = balance - 1000 where userID = 3;
update user_account set balance = balance +1000 where userID = 1;

mysql中如何开启事务：
sql开启事务：
begin / start transaction – 手工
commit / rollback – 事务提交或回滚
set session autocommit = on/off; – 设定事务是否自动开启
JDBC 编程：
connection.setAutoCommit（boolean）;
Spring 事务AOP编程：
expression=execution（com.gpedu.dao..(…)）

事务的特性
原子性（Atomicity）
最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚
一致性（Consistency）
事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则，不会因为出现系统意外等原因导致状态的不一致
隔离性（Isolation）
一个事务所操作的数据在提交之前，对其他事务的可见性设定（一般设定为不可见）
持久性（Durability）
事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失

事务并发带来的问题
脏读
事务A执行期间事务b更新了数据但未提交，A进行了查询应不应该读到 B更新后的数据？
个人理解脏读是B 更新或插入数据时允许 A读取造成的。B更新时加上排它锁就能解决
在这里插入图片描述
幻读
事务A执行期间事务b插入了数据并提交，A应不应该读到？
幻读问题主要是区间内的记录可能变化造成。
临键锁算法可以锁定区间+行记录别人无法对区间内的记录进行修改可以解决幻读问题。

在这里插入图片描述
不可重复读事务A执行期间事务b更新了数据并提交，A应不应该读到？
不可重复读是因为读到了数据还允许别人更新造成的。加入读锁（共享锁）督导了不允许别人修改就可以解决

事务四种隔离级别标准
SQL92 ANSI/ISO标准：
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Read Uncommitted（未提交读） --未解决任何事务并发问题
事务未提交对其他事务也是可见的，脏读（dirty read）
这种隔离级别相当于没有任何隔离，任何事务未提交的改变都能被其他事务读到，如果事务回滚就会有脏读。

Read Committed（提交读） --解决脏读问题
一个事务开始之后，只能看到自己提交的事务所做的修改，不可重复读（nonrepeatableread）
为啥不可重复？

Repeatable Read (可重复读) --解决不可重复读问题
在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题

Serializable（串行化） --解决所有问题
最高的隔离级别，通过强制事务的串行执行,事务必须一个个执行，解决了并发的问题。

innodb引擎对隔离级别的支持程度
在这里插入图片描述
可以看到innodb引擎采用rr隔离级别是解决了三种问题。比sql92中定义的标准更完善。（sql92标准中只有串行化隔离级别才能解决幻读问题）

查看mysql的设置的事务隔离级别
select global.@@tx_isolation; select @@tx_isolation;

通过锁解决事务并发问题

理解表锁、行锁
锁是用于管理不同事务对共享资源的并发访问
表锁与行锁的区别：
锁定粒度：表锁 > 行锁
加锁效率：表锁 > 行锁
冲突概率：表锁 > 行锁
并发性能：表锁 < 行锁
InnoDB存储引擎支持行锁和表锁（另类的行锁通过对所有行加行锁来实现表锁）

mysql锁的类型

共享锁（行锁）：Shared Locks
又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改;
加锁释锁方式：
select * from users WHERE id=1 LOCK IN SHARE MODE;
commit/rollback
示例：
dbever session1 中对 user 表 id=1 的记录加锁
在这里插入图片描述
在另外一个session中执行更新语句查询语句可以查询出记录，但是执行更新语句 update user set name =‘name13’ where id =‘1’，发现被阻塞无法拿到锁，如果在等待锁的过程中 session1中执行了 commit /rollback 则更新sql 可以执行如下：
在这里插入图片描述
注意：如果session1中没有使用 LOCK IN SHARE MODE; 进行加锁，则session 2 中可以正常更新，更新并提交后，session1再次执行查询数据，查询到的是 session2更新前的数据。应该是读的快照数据。

排它锁（行锁）：Exclusive Locks
又称为写锁，简称X锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对数据行进行读取和修改，（其他事务要读取数据可来自于快照）
加锁释锁方式：
delete / update / insert 默认加上X锁
SELECT * FROM table_name WHERE … FOR UPDATE
commit/rollback

示例
session1 通过update 语句对数据加排它锁，

update  user set name ='name15' where id ='1';

如下：
在这里插入图片描述

session2 中可以读取到快照数据（session1更新前的备份数据），但是无法拿到共享锁和排它锁，执行以下语句都阻塞，只有等session1提交或回滚之后才能执行

select * from user where id='1' LOCK IN SHARE MODE;
select * from user where id='1'  for update;
update  user set name ='name14' where id ='1';

在这里插入图片描述
InnoDB的行锁到底锁了什么
InnoDB的行锁是通过给索引上的索引项加锁来实现的。
只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB将使用表锁（锁住索引的所有记录）
表锁：lock tables xx read/write
示例1：
session1 通过示 udpate 获取排它锁

在这里插入图片描述
session 2 中可以更新id为2 的记录，但是不能跟新 id =1 的记录如下 ,此时由于通过id(索引)来过滤数据所以加的是行锁只对 id=1 的记录加锁。（使用其他唯一索引效果相同）

示例2：
session1 通过更新name=‘name2’ 数据获取排它锁如下：
在这里插入图片描述

session2 所有的数据都不能更新，此时由于name字段没有加索引所以 session1加的是表锁，表中所有的数据都不能更新。
在这里插入图片描述
所以获取排它锁的时候尽量使用索引获取行锁，不要获取表锁。

意向锁共享锁（表锁）：Intention Shared Locks
表示事务准备给一个数据行加共享锁前必须先取得该表的IS锁，这样加行锁的事务同时拥有了意向共享锁（表锁），其他事务就无法加获取IS,从而无法对全表加锁。

意向锁排它锁（表锁）：Intention Exclusive Locks
表示事务准备给一个数据行加排他锁前必须先取得该表的IX锁，这样加行锁的事务同时拥有了意向排它锁（表锁），其他事务就无法加获取IX,从而无法对全表加排它锁。

意向锁(IS 、IX) 是InnoDB 数据操作之前自动加的，不需要用户干预。
意义：当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁就是为了实现有行锁的时候不允许加表锁
示例：
session1 通过update user set sex =‘3’ where id =‘1’; 语句获取id=‘1’ 的行排它锁，在加锁之前还获取了user表的意向排它锁。
在这里插入图片描述
session2 想通过update user set sex=‘3’ where name=‘name2’ 语句获取user表排它锁时会先检查是否已经有人获取了表的排它锁，本例中sesson1已经获取了表排它锁，所以session无法对user表加表排它锁。如果没有意向排它锁那么session想获取锁时必须检查所有的记录是否有加排它锁，效率低下。这就是意向锁的意义。
在这里插入图片描述

自增锁：AUTO-INC Locks
针对自增列自增长的一个特殊的表级别锁
show variables like ‘innodb_autoinc_lock_mode’;
默认取值1 （一般不要改），代表连续，事务未提交ID 永久丢失

行锁的算法

上边我们知道了，InnoDB的行锁是通过给索引上的索引项加锁来实现的，一个加锁操作会锁定哪些索引呢？分以下几种情况：
通过索引等值匹配 where id=‘1’，且匹配到数据按照记录锁 Record Locks 算法加锁。
通过索引列区间匹配 where id>5 and id<9 且匹配到记录则按照临建锁算法加锁
通过索引列区间匹配 where id>20 且未匹配到记录则按照间隙锁算法加锁

记录锁 Record Locks
锁住具体的索引项
当sql执行按照唯一性（Primary key、Unique key）索引进行数据的检索时，查询条件等值匹配且查询的数据是存在，这时SQL语句加上的锁即为记录锁Record locks，
在这里插入图片描述
如果是非索引等值匹配，则会锁区间。

间隙锁 Gap Locks
锁住数据不存在的区间（左开右开）
当sql执行按照索引进行数据的检索时，查询条件的数据不存在，这时SQL语句加上的锁即为
Gap locks，锁住索引不存在的区间（左开右开）
如下：id共有四个值 1、4、7、10 根据这四个值将 id分成五个区间，当执行 select * from t where id>4 and id<6 for update 的时候没有命中的记录。然后就锁住4-7 这个区间（左开右开）
gap锁只在rr隔离级别存在。

在这里插入图片描述
临键锁 Next-key Locks
锁住记录+ 区间（左开右闭）
当sql执行按照索引进行数据的检索时,查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+左右的两个区间（左开右闭）
如下：id共有四个值 1、4、7、10 根据这四个值将 id分成五个区间，当执行 select * from t where id>5 and id<9 for update 的时候命中了id=7的记录。然后就锁住4-7 7-10 两个区间（左闭右开）锁住后 id为 4、5、6、7、8、9的记录不允许新增和修改

在这里插入图片描述
为什么锁区间+记录？innodb引擎选择临键锁作为默认行锁算法？
为了防止幻读的问题。一旦锁住区间就没有幻读的问题了。

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

利用锁解决脏读
在这里插入图片描述

利用锁解不可重复读
在这里插入图片描述
利用锁解幻读

死锁的产生
多个并发事务（2个或者以上）；
每个事务都持有锁（或者是已经在等待锁）;
每个事务都需要再继续持有锁；
事务之间产生加锁的循环等待，形成死锁

死锁的避免建议
1）类似的业务逻辑以固定的顺序访问表和行。
2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3）降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择
4）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）

catch that elf

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql调优-事务及锁

事务特性及隔离级别什么是事务事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；事务是一组不可再分割的操作集合（工作逻辑单元）;典型事务场景(转账)：update user_account set balance = balance - 1000 where userID = 3;update user_account set balance = balance +...
复制链接

扫一扫