首页 > 数据库 >MySQL——数据库锁

MySQL——数据库锁

时间:2022-12-20 23:36:18浏览次数:44  
标签:事务 lock 数据库 innodb MySQL test Innodb row

一、锁的定义?

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要,也更加复杂。

 

二、数据库锁的分类

(1)从数据的操作类型可分为:读锁、写锁

  • 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。

  • 写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁。

(2)从数据的操作粒度可分为:表锁、行锁

三、数据库事务

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。

原子性 (Atomicity): 事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。

一致性(Consistent):在事各开始和完成时,数据都必须保持一致状态。这章味着所有相关的数据规则都必须应用于事务的修改以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。

隔离性 (lsolation),数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性 (Durable): 事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持

 

四、并发事务导致的问题

  • 更新丢失(Lost Update)

  • 脏读(Dirty Reads)

  • 不可重复读(Non-Repeatable Reads)

  • 幻读(Phantom Reads)

(1)更新丢失(Lost Update)

当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。

(2)脏读

事务A读取到了事务B已修改但尚未提交的的数据,还在这个数据基础上做了操作。此时,如果B事务回滚,A读取的数据无效,不符合一致性要求。

(3)不可重复读
一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了,这种现象就叫做“不可重复读”。即事务A读取到了事务B已经提交的修改数据,不符合隔离性。

(4)幻读

一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。即事务A读取到了事务B提交的新增数据,不符合隔离性。


读和脏读有点类似:脏读是事务B里面修改的数据,幻读是事务B里面新增的数据。

 

五、事务的隔离级别

 

六、间隙锁

(1)什么是间隙锁?

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录,叫做“间隙 (GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁 (Next-Key锁)。
(2)间隙锁危害

因为Query执行过程中通过过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值并不存在。间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

示例:

#session1会话页面执行SQL

# b为字符串类型
UPDATE test_innodb_lock set b = '6666' where a >1 and a < 6;

#commit之前,session2会话页面新增SQL语句会一直处于阻塞状态
commit;

#session2会话页面

insert
	into
	test_innodb_lock
values(3, '3');

commit;

 

七、如何锁定一行?

(1)新建一个session会话页面

begin;

SELECT * from test_innodb_lock where a = 7 for UPDATE ;

#在commit之前,session2会话页面会一直处于阻塞状态
UPDATE test_innodb_lock set b = '7007' where a = 7;

commit;

(2)同时新建另一个session2会话页面,执行以下操作

SELECT * from test_innodb_lock where a = 7;

UPDATE test_innodb_lock set b = '7008' where a = 7;

commit;

八、行锁失效升级为表锁

(1)session1会话页面执行SQL

# session1会话页面执行sql
SELECT * from test_innodb_lock where a = 7 ;

# b为字符串类型
UPDATE test_innodb_lock set a = 7777 where b = 7777;

commit;

(2)session2会话页面执行SQL

# 索引失效导致行锁升级为表锁

SELECT * from test_innodb_lock ;

UPDATE test_innodb_lock set b = '999900' where a = 9;

commit;

注:字段“a”、“b”均为索引列,a为int类型,b为字符串

mysql> show index from test_innodb_lock;
+------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock |          1 | test_innodb_a_ind |            1 | a           | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_innodb_lock |          1 | test_innodb_b_ind |            1 | b           | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

执行结果:session1会话中字段“b”做类型转换,导致索引失效,造成行锁升级为表锁,session2会话页面执行update 语句时会一直处于阻塞状态,直至session1会话页面 commit。

 

九、如何分析行锁

通过检查“innodb_row_lock”状态变量来分析系统上的行锁争夺情况。

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 93652 |
| Innodb_row_lock_time_avg      | 15608 |
| Innodb_row_lock_time_max      | 51019 |
| Innodb_row_lock_waits         | 6     |
+-------------------------------+-------+

Innodb_row_lock_current_waits: 当前正在等待锁定的数量;

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度;

Innodb_row_lock_time_avg: 每次等待所花平均时间;

Innodb _row_lock time max: 从系统启动到现在等待最常的一次所花的时间

Innodb row_lock waits: 系统启动后到现在总共等待的次数:

 

其中,比较重要的三个参数:

  • Innodb_row_lock_time_avg: 每次等待所花平均时间;

  • Innodb row_lock waits: 系统启动后到现在总共等待的次数:

  • Innodb_row_lock_time: 从系统启动到现在锁定总时间长度;

尤其是当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果制定优化计划。

 

十、行锁优化建议

(1)尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁。

(2)合理设计索引,尽量缩小锁的范围。

(3)尽可能用较少的检索条件,避免间隙锁。

(4)尽量控制事务大小,减少锁定资源和时间长度。

(5)尽可能低级别事务隔离。

标签:事务,lock,数据库,innodb,MySQL,test,Innodb,row
From: https://www.cnblogs.com/huangdh/p/16975169.html

相关文章

  • MySQL
    MySQL运算符本章节我们主要介绍MySQL的运算符及运算符的优先级。MySQL主要有以下几种运算符:算术运算符比较运算符逻辑运算符位运算符算术运算符MySQL支持的算术运算......
  • mysql数据库编码、字段编码、表编码 专题
    CREATEDATABASE`mybatis-subject`/*!40100DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_bin*/其中的/*!40100...*/这部分注释会被MySQL执行,表示服务端版本号大于......
  • Linux下如何安装MySQL?
    目标主机:centos8MySQL安装所有平台的MySQL下载地址为:https://dev.mysql.com/downloads/repo/yum/。挑选你需要的MySQLCommunityServer版本及对应的平台。注意:安......
  • MySQL中这14个牛逼的功能,惊艳到我了!!!
    前言我最近几年用MYSQL数据库挺多的,发现了一些非常有用的小玩意,今天拿出来分享到大家,希望对你会有所帮助。1.group_concat在我们平常的工作中,使用groupby进行分组的场......
  • 数据库路由器 ICX
    实时并发数据库事务处理同步复制器和负载平衡器   ———通向真正数据库高可用性,高可靠性,高性能之路一、产品概述   数据库路由器--ICX是美国宾夕法尼亚大学计算机......
  • 高性能Mysql主从架构的复制原理及配置详解(转)
    温习《高性能​​MySQL​​》的复制篇.1复制概述     Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过......
  • 我说MySQL联合索引遵循最左前缀匹配原则,面试官让我回去等通知
    携手创作,共同成长!这是我参与「掘金日新计划·8月更文挑战」的第6天,点击查看活动详情面试官:我看你的简历上写着精通MySQL,问你个简单的问题,MySQL联合索引有什么特性?心......
  • MYSQL问题解决
    1、MySQL错误日志里出现:14033110:08:18[ERROR]Errorreadingmasterconfiguration14033110:08:18[ERROR]Failedtoinitializethemasterinfostructure14033110:......
  • MySQL 全局锁、表级锁、行级锁,你搞清楚了吗
    大家好,我是小林。最近重新补充了《MySQL有哪些锁》文章内容:增加记录锁、间隙锁、net-key锁增加插入意向锁增加自增锁为innodb_autoinc_lock_mode=2模式时,为什么......
  • c++ 通过ODBC连接达梦数据库
    安装达梦数据库建立数据源,位置:开始》》windows管理工具》》ODBC数据源  测试通过 打开编译器编写代码连接这个数据库 #include"stdafx.h"#include<Windows.......