理解mysql锁和事务


数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问时变得有序所设计的一种规则。

总结在前

标准情况下事务四种隔离级别:都是针对读的
读未提交:
|——排他写锁实现
读已提交:解决了脏读(读到未提交事务)
|——共享读锁和排他写锁实现,读数据后立刻释放共享锁
可重复读:解决了可重复读,但可出现幻读情况(读到已提交事务_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 存储引擎的读写是互相阻塞的,而且写锁优先级大于读锁优先级
我们可以根据各自系统环境的差异决定读与写的优先级:

  1. 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接读比写的优先级高。这适合以读为主的系统
  2. 设置参数 max_write_lock_count ,当一个表的读锁达到这个值后,MySQL 就暂时将写请求的优先级降低,给读进程一定获得锁的机会。

InnoDb存储引擎

InnoDB 默认采用行锁,在未使用索引字段查询时升级为表锁。
即便你在条件中使用了索引字段,MySQL 会根据自身的执行计划,考虑是否使用索引(所以 explain 命令中会有 possible_key 和 key)
如果 MySQL 认为全表扫描效率更高,它就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。
所以在分析锁冲突时,别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。
关于执行计划:

  1. 全表更新
    如果事务需要更新大部分或全部数据,而且表又比较大,这种情况不适合使用行锁,因为它会导致事务执行效率低
  2. 多表级联
    如果事务涉及多个表的关联查询,可能引起死锁,造成大量事务回滚。
    这种情况若能一次性锁定全表,就可以很容易的避免死锁,减少数据库回滚事务带来的开销。

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.索引项的索引类型,例如:

  1. 在(READ COMMITTED)读已提交隔离级别下
    a) 只要查询条件为索引时,那么加记录锁;
    a) 如果是非索引,那么加表锁

  2. 在REPEATABLE READ(可重复读)隔离级别下,
    a) 如果查询条件能使用上唯一索引或是聚簇索引,或者是一个唯一的查询条件,那么加记录锁;
    a) 如果是普通索引,或者是范围查询,那么加Next-Key锁(InnoDB使用这个锁解决可重复读隔离级别下的幻读问题);
    a) 如果是非索引,那么加表锁

  3. 行锁的优缺点
    a) 行锁的优势:锁的粒度小,发生锁冲突的概率低;处理并发的能力强
    b) 行锁的劣势:开销大;加锁慢;会出现死锁

使用间隙锁的目的
  1. 防止幻读,以满足相关隔离级别的要求
  2. 为了满足恢复和复制的需要
    InnoDB 除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB 也会使用间隙锁。

InnoDb行锁的特点

  1. Mysql的行锁是针对索引加的锁,而不是针对记录加的锁,所以虽然访问不同行记录,使用相同索引键会出现锁冲突的
  2. 只有通过索引查询时,InnoDb才会使用行锁,索引又分为主键索引,唯一索引,普通索引
  3. 当表有多个索引时,不同的事务可以使用不同的索引锁定不同的行
  4. 即使在条件中使用了索引字段,但还得通过执行计划来确定最终是否真正使用了,因为若全表扫描更高就不会使用索引

InnoDb死锁

当两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。
在 InnoDB 的事务管理和锁定机制中,有专门检测死锁的机制,会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。
当 InnoDB 检测到系统中产生了死锁之后,InnoDB 会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚,而让另外一个较大的事务成功完成。

怎样避免死锁

  1. 若不同的程序会并发存取多张表,应该尽量约定以相同顺序来访问表

  2. 在批量处理数据时,如果事先对数据排序,保证每个线程按固定顺序处理记录

  3. 在 REPEATABLE-READ 隔离级别下,如果两个线程同时对相同条件记录用 SELECT…FOR UPDATE 加排他锁,在没有符合该条件记 录情况下,两个线程都会加锁成功。
    两个事务发现记录不存在,然后同时尝试插入记录,就会陷入循环等待的境地

  4. 当隔离级别为 READ COMMITTED 时,如果两个线程都先执行 SELECT…FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。
    此时之恶能有一个线程插入成功,另一个线程只能等待,当地一个线程成功后,第二个线程出错但仍然会获得一个排他锁,。这时如果再来第三个线程,就会出现死锁
    解决:捕获异常然后进行rollback

  5. 在多个事务中,尽可能按照相同访问顺序进行访问

  6. 在一个事务中,尽可能左到一次锁定所有资源

  7. 对于容易产生死锁的业务部门,尝试提升行锁定粒度,甚至使用表锁

InnoDB行锁优化建议

  1. 尽量让所有数据的检索都通过索引完成,避免升级到表锁
  2. 合理涉及索引,尽可能减少锁定范围
  3. 尽可能减少基于范围的过滤条件,从而避免使用间隙锁带来的负面影响
  4. 尽量使用较低级别的事务隔离机制

通过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;

定义事务保存点,实现部分回滚

  1. 定义保存点
    savepoint 保存点名称;
  2. 回顾到指定保存点
    rollback to savepoint 保存点名称;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值