MySQL锁机制
2021-08-23 17:36:39 2 举报AI智能生成
MySQL锁机制
作者其他创作
大纲/内容
锁机制
分类
操作粒度
表级锁
每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低
应用在MyISAM、InnoDB、BDB等存储引擎中
行级锁
每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高
应用在InnoDB存储引擎中
页级锁
每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般
应用在BDB存储引擎中
操作类型
读锁 S锁
共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响
事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，需要追加X锁，要等记录的S锁全部释放
写锁 X锁
排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁
事务A对记录添加了X锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操作
IS锁、IX锁
意向读锁，意向写锁，属于表级锁，S和X主要针对行级锁
在对表记录添加S或X锁之前，会先对表添加IS或IX锁
操作性能
乐观锁
一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息
悲观锁
在对一条数据修改时，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式
共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁的范畴
行锁
InnoDB引擎中，可以使用行锁和表锁，其中行锁又分为共享锁和排他锁
InnoDB行锁是通过对索引数据页上的记录加锁来实现的，主要实现算法有
Record Lock
锁定单个行记录的锁
记录锁，RC、RR都支持
Gap Lock
间隙锁，锁定索引记录间隙，确保索引记录的间隙不变
范围锁，RR支持
Next-key Lock
行锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围
记录锁+范围锁，RR支持
在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key_Lock，但是当SQL操作含有唯一索引时，会对Next-Key_Lock进行优化，降级为Record_Lock，仅锁住索引本身而非范围
机制原理
1. select .... from
InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁
2. select ... from lock in share mode
追加了共享锁，InnoDB会使用Next-Key_Lock锁进行处理，如果扫描发现唯一索引，可以降级为Record_Lock锁
3. select ... from for update
追加了排他锁，InnoDB会使用Next-Key_Lock锁进行处理，如果扫描发现唯一索引，可以降级为Record_Lock锁
4. update .... where
InnoDB会使用Next-Key_Lock锁进行处理，如果扫描发现唯一索引，可以降级为Record_Lock锁
5. delete .... where
InnoDB会使用Next-Key_Lock锁进行处理，如果扫描发现唯一索引，可以降级为Record_Lock锁
6. insert
InnoDB会在将要插入的那一行设置一个排他的Record_Lock
加锁行为（以update ... where id = xx为例）
主键加锁
仅在主键id主键索引记录上加X锁
唯一键加锁
先在唯一索引id上加X锁，然后再在其对应的主键索引记录上加X锁
非唯一键加锁
对满足条件的记录和主键分别加X锁，然后再在索引的前、后、中间间隙上加Gap_Lock
无索引加锁
表里所有行和间隙都会加X锁
没有索引时，会导致全表锁定，因为InnoDB引擎锁机制是基于索引实现的记录锁定
悲观锁
是指在数据处理过程、将数据处于锁定状态，一般使用数据库的锁机制实现
主要用于保证事务的完整性
从广义上来讲，行锁、表锁、读锁、写锁、共享锁、排他锁等，都属于悲观锁范畴
表锁
每次操作都锁住整张表，并发度最低
常用命令
手动增加表锁
lock table [tname] read|write, [tname2] read|write
查看表上加过的锁
show open tables
伤处表锁
unlock tables
表级读锁
当前表追加read锁，当前连接和其他的连接都可以读操作，但是增删改都会报错
其他连接增删改会被阻塞
表级写锁
当前表追加write锁，当前连接可以对表做增删改查
其他连接对该表的所有操作都被阻塞
共享锁
又称为读锁，S锁。指多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改
使用
select ... lock in share mode
只适用查询语句
事务使用了共享锁，只能读取，不能修改，修改操作被阻塞
排他锁
又称为写锁，X锁。指不能与其他锁共存，如一个事务获取了一个数据行的排他锁，其他事务就不能对该行记录做其他操作，也不能获取改行的锁
使用
在SQL末尾加上for Update
innoDB引擎默认会在update、delete语句加上for update
行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引，会锁住全表记录
事务使用了排他锁，当前事务可以读取和修改，其他事务不能修改，也不能获取记录锁（select.... for_update）
如果查询没有使用到索引，将会锁住整个表记录
乐观锁
乐观锁是相对悲观锁而言，不是数据库提供的功能，需要开发者自己实现
在数据库操作时，想法很乐观，认为这次操作不会导致冲突，因此在数据库操作时不做任何特殊处理，即不加锁，而是在进行事务提交时再去判断是否有冲突
实现的关键点：冲突检测
悲观锁和乐观锁都可以解决事务写写并发，在应用中可以根据并发处理能力选择区分，比如对病发率要求高的选择乐观锁，对于并发率要求低的选择悲观锁
实现原理
使用版本字段version
先给数据表增加一个版本字段，每操作一次，将对应记录版本号加1
version用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改
使用时间戳timestamp
和version类似，也需要在数据库增加一个字段，字段类型使用timestamp时间戳
在更新提交时检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一直则提交更新，否则就是版本冲突，取消操作
注
许多数据库访问框架封装了乐观锁的实现
hibernate框架
MyBatis的OptimisticLocker插件
死锁
1. 表级死锁
原因
用户A访问表A（锁住了表A），然后又访问表B；用户B访问表B（锁住了表B），然后又企图访问表A；
用户A由于用户B已经锁住表B，必须等待用户B释放表B才能继续，用户B同理，死锁就产生了
解决
这种死锁比较常见，是由于程序的bug产生的，除了调整程序逻辑没有其他的办法
对于数据库多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作表A和B时，总是按照先A后B的顺序处理，必须同时锁定两个资源时，要保证任何时刻都应该按照相同的顺序来锁定资源
2. 行级死锁
原因1：
如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞
解决
SQL语句中不要使用太复杂的关联多表的查询
使用explain执行计划对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化
原因2
两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁
解决
在同一个事务中，尽可能做到一次锁定所需要的所有资源
按照id对资源排序，然后按顺序进行处理
3. 共享锁转换为排他锁
原因
事务A查询一条记录，然后更新该记录；此时事务B也更新该记录，这是事务B的排他锁由于事务A有共享锁，必须等A释放共享锁后才可以获取，只能排队等待；事务A在执行更新操作时，发生死锁
事务A需要排他锁来做更新操作，但是事务A已经有了一个排他锁请求，并且在等待事务A释放共享锁
解决
对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作
使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能
注意：由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中
4. 死锁排查
查看死锁日志
show engin innodb status \G
查看锁状态变量
show status like ‘innodb_row_lock%’
Innodb_row_lock_current_waits
当前正在等待锁的数量
Innodb_row_lock_time
从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg
每次等待锁的平均时间
Innodb_row_lock_time_max
从系统启动到现在等待最长的一次锁的时间
Innodb_row_lock_waits
系统启动后到现在总共等待的次数
如果等待次数高，且每次等待时间长，需要分析系统中为什么有如此多的等待，然后着手定制优化
0 条评论
下一页
