文章目录
数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问时变得有序所设计的一种规则。
总结在前
标准情况下事务四种隔离级别:都是针对读的
读未提交:
|——排他写锁实现
读已提交:解决了脏读(读到未提交事务)
|——共享读锁和排他写锁实现，读数据后立刻释放共享锁
可重复读:解决了可重复读，但可出现幻读情况(读到已提交事务_update+delete)
|——共享读锁和排他写锁实现，读数据后事务结束后再释放共享锁
序列化:解决了幻读(读到已提交事务_insert)
|——需要其他机制保证
Mysql和Oracle为了性能考虑并不完全按照理论来实现的。
MVVC多版本并发控制是mysql中基于乐观锁理论实现隔离级别的方式，用于实现读已提交，可重复读隔离级别的实现。
系统版本号：每开始一个新事务，系统版本号就自增
事务版本号：事务开始时的系统版本号
在Mysql中每一条数据后面都会添加两个字段
创建版本号：当insert一条数据，创建版本号为系统版本号
删除版本号：当delete一条数据，删除版本号为系统版本号
当update一条数据，实质通过delete和insert来实现的
select快照读：Innodb默认执行快照读，读取历史数据，即使其他事务提交也不会影响快照的数据，从而实现了可重复读
创建版本号<=当前事务版本号，保证查询数据不会查出来后来添加的数据(旧数据)
删除版本号>当前事务版本号，保证查询数据不会查出前面删除的数据
Mysql的RR级别中，select操作时快照读(历史数据)，update操作采用当前读(最新数据)—事务a删除了数据T且提交，而事务b要update数据T，所以在修改时需要知道数据的最新情况
Mysql解决幻读：串行化隔离级别，MVCC+next-key locks，它是由record locks和gap locks锁组成的，next-key锁对当前读进行加锁，锁住了行以及可能产生幻读的插入位置，阻止新的数据插入产生幻读。
使用间隙锁的目的：防止幻读
Innodb的【MVVC+RR级别】下，next key lock自动开启，解决了可重复读，幻读问题。
使用索引查询唯一行的语句，不产生间隙锁定。
例如：select * from table from id=100 //如果id没有唯一索引，则该语句将锁定前面的间隙。
show engine innodb status;
三种锁定机制
MySQL 各存储引擎使用了三种类型（级别）的锁定机制：
- 表级锁定：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
- 行级锁定：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 页级锁定：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用
行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
表级锁定
是Mysql最大粒度的锁定机制，实现简单，获取锁和释放锁速度快，很好的避免了死锁问题。
缺点是资源竞争的概率也会增大，降低了并发度
使用表级锁定的主要是 MyISAM，MEMORY，CSV 等一些非事务性存储引擎
行级锁定 InnoDb
锁定资源粒度小，资源竞争的概率小，并发度高
缺点是实现复杂，容易发生死锁
行级锁定的主要是 InnoDB 存储引擎。
页级锁定
多顶粒度介于行级锁定与表级锁定之间
页级锁定的主要是 BerkeleyDB 存储引擎。
mysql的myism存储引擎
表级锁有两种模式
- 表共享读锁（Table Read Lock）
- 表独占写锁（Table Write Lock）
表锁，读锁会阻塞写，不会阻塞读。而写锁会把读写都阻塞。
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，所以用户不需要通过lock table命令给表加锁。
MyIsam表锁优化
MyIsam加锁消耗的资源小，但锁定的粒度比较大，造成锁定资源的争用，降低了并发能力。
优化 MyISAM 存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。
一个是尽可能让锁定时间变短，另一个是尽可能让其并发执行
查询表级锁争用情况
- Table_locks_immediate：产生表级锁定的次数。
- Table_locks_waited：出现表级锁定争用而发生等待的次数；此值越高则说明存在着越严重的表级锁争用情况。
MyISAM 的读写锁调度是写优先，写锁锁定后，其他线程不能做任何操作，大量更新很难得到锁，造成等待。
缩短锁定时间
- 减小复杂查询，将复杂查询分拆
- 建立高效索引，让数据查询更快
- 让myisam存储必要信息
- 业务上减少myisam表数据文件
分离能并行操作
MyISAM 的存储引擎还有一个非常有用的特性，那就是 Concurrent Insert（并发插入）的特性
MyISAM 存储引擎有一个控制是否打开 Concurrent Insert 功能的参数选项：concurrent_insert，可以设置为 0，1 或者 2。
- concurrent_insert=2，无论 MyISAM 表中有没有空洞，都允许在表尾并发插入记录。
- concurrent_insert=1，如果 MyISAM 表中没有空洞（即表的中间没有被删除的行），MyISAM
允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是 MySQL 的默认设置。 - concurrent_insert=0，不允许并发插入。
例如，将 concurrent_insert 系统变量设为 2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE 语句来整理空间碎片，收回因删除记录而产生的中间空洞。
合理利用读写优先级
MyISAM 存储引擎的读写是互相阻塞的，而且写锁优先级大于读锁优先级
我们可以根据各自系统环境的差异决定读与写的优先级：
- 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接读比写的优先级高。这适合以读为主的系统
- 设置参数 max_write_lock_count ，当一个表的读锁达到这个值后，MySQL 就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
InnoDb存储引擎
InnoDB 默认采用行锁，在未使用索引字段查询时升级为表锁。
即便你在条件中使用了索引字段，MySQL 会根据自身的执行计划，考虑是否使用索引（所以 explain 命令中会有 possible_key 和 key）
如果 MySQL 认为全表扫描效率更高，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。
所以在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。
关于执行计划：
- 全表更新
如果事务需要更新大部分或全部数据，而且表又比较大，这种情况不适合使用行锁，因为它会导致事务执行效率低 - 多表级联
如果事务涉及多个表的关联查询，可能引起死锁，造成大量事务回滚。
这种情况若能一次性锁定全表，就可以很容易的避免死锁，减少数据库回滚事务带来的开销。
InnoDb锁定模式及实现机制
InnoDb行级锁分为共享锁和排他锁，而为了让行级锁定和表级锁定共存，又添加了意向共享锁和意向排他锁这两种锁。
当某事务需要访问某资源时，若已经存在共享锁锁定时，可以再次进行加共享锁操作，但不能加排他锁
若已经存在排他锁时，该事务只能等待资源释放后，才能访问资源
意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。
当某事物需要给某资源加锁时，若其已经被其他锁锁定时，该事务还可以在其上加合适的意向锁。
- 共享锁（S），又称为读锁，获得共享锁之后，可以查看但无法修改和删除数据
- 排他锁（X），又称为写锁，获得写锁之后，既能读又能写
- 意向共享锁（IS）
- 意向排他锁（IX）
InnoDb对记录加锁的方式
对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（X)。
对于普通 SELECT 语句，InnoDB 不会加任何锁。
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE
InnoDb实现行锁的三种算法
1.Record Lock：单个行记录上的锁。
2.Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。
mysql> select * from emp where empid > 100 for update; //InnoDb会锁定empid>100的所有记录
3.Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身，InnoDb默认
那么什么时候使用记录锁，什么时候使用Next-key锁呢？
由两方面的因素决定：1.数据库的隔离级别 2.索引项的索引类型，例如：
-
在(READ COMMITTED)读已提交隔离级别下
a) 只要查询条件为索引时，那么加记录锁;；
a) 如果是非索引，那么加表锁。 -
在REPEATABLE READ（可重复读）隔离级别下，
a) 如果查询条件能使用上唯一索引或是聚簇索引，或者是一个唯一的查询条件，那么加记录锁;
a) 如果是普通索引，或者是范围查询，那么加Next-Key锁（InnoDB使用这个锁解决可重复读隔离级别下的幻读问题）；
a) 如果是非索引，那么加表锁。 -
行锁的优缺点
a) 行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强
b) 行锁的劣势：开销大；加锁慢；会出现死锁
使用间隙锁的目的
- 防止幻读，以满足相关隔离级别的要求
- 为了满足恢复和复制的需要
InnoDB 除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁。
InnoDb行锁的特点
- Mysql的行锁是针对索引加的锁，而不是针对记录加的锁，所以虽然访问不同行记录，使用相同索引键会出现锁冲突的
- 只有通过索引查询时，InnoDb才会使用行锁，索引又分为主键索引，唯一索引，普通索引
- 当表有多个索引时，不同的事务可以使用不同的索引锁定不同的行
- 即使在条件中使用了索引字段，但还得通过执行计划来确定最终是否真正使用了，因为若全表扫描更高就不会使用索引
InnoDb死锁
当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
在 InnoDB 的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。
当 InnoDB 检测到系统中产生了死锁之后，InnoDB 会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。
怎样避免死锁
-
若不同的程序会并发存取多张表，应该尽量约定以相同顺序来访问表
-
在批量处理数据时，如果事先对数据排序，保证每个线程按固定顺序处理记录
-
在 REPEATABLE-READ 隔离级别下，如果两个线程同时对相同条件记录用 SELECT…FOR UPDATE 加排他锁，在没有符合该条件记 录情况下，两个线程都会加锁成功。
两个事务发现记录不存在，然后同时尝试插入记录，就会陷入循环等待的境地 -
当隔离级别为 READ COMMITTED 时，如果两个线程都先执行 SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。
此时之恶能有一个线程插入成功，另一个线程只能等待，当地一个线程成功后，第二个线程出错但仍然会获得一个排他锁，。这时如果再来第三个线程，就会出现死锁
解决：捕获异常然后进行rollback -
在多个事务中，尽可能按照相同访问顺序进行访问
-
在一个事务中，尽可能左到一次锁定所有资源
-
对于容易产生死锁的业务部门，尝试提升行锁定粒度，甚至使用表锁
InnoDB行锁优化建议
- 尽量让所有数据的检索都通过索引完成，避免升级到表锁
- 合理涉及索引，尽可能减少锁定范围
- 尽可能减少基于范围的过滤条件，从而避免使用间隙锁带来的负面影响
- 尽量使用较低级别的事务隔离机制
通过InnoDB_row_lock分析行锁的争夺情况
当发现等待次数很高，而且等待时间很长得时候，我们就需要进行相应优化了。
我们可以通过设置 InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
## 设置监听器观察发生冲突得表和数据，每15s像日志中记录监控内容
mysql> create table InnoDB_monitor(a INT) engine=InnoDB;
## 停止监听器查看
mysql> drop table InnoDB_monitor;
解除正在死锁得状态
第一种方式
1. 查询是否锁表
show open tables where in_use>0;
2. 查询进程
show processlist;
3. 杀死进程id
kill id
第二种方式
1. 查看被锁定得事务
select * from Information_schema.innodb_trx;
2. 杀死进程
kill id
查看当前事务
select * from Information_schema.innodb_trx;
查看当前锁定的事务
select * from information_schema.innodb_locks;
查看当前等待锁的事务
select * from information_schema.innodb_lock_waits;
定义事务保存点，实现部分回滚
- 定义保存点
savepoint 保存点名称； - 回顾到指定保存点
rollback to savepoint 保存点名称；
扫一扫
571
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
