程序员经验分享-hwhale

七、MySql-锁与事物

2023-11-13

锁

锁的简介

为什么需要锁？

到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是 你买到还是另一个人买到的问题？

锁的概念

  • 锁是计算机协调多个进程或线程并发访问某一资源的机制。
  • 在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、 有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个 重要因素。
  • 锁对数据库而言显得尤其重要，也更加复杂。

MySQL 中的锁

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发 度最低。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发 度也最高。
  • 页面锁(gap 锁,间隙锁)：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度 界于表锁和行锁之间，并发度一般。

表锁与行锁的使用场景

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 OLAP 系统
行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如 一些在线事务处理（OLTP）系统。
很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适

MyISAM 锁

MySQL 的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
在这里插入图片描述

共享读锁

语法：lock table 表名 read

  1. lock table testmysam READ 启动另外一个 session select * from testmysam 可以查询
  2. insert into testmysam value(2); update testmysam set id=2 where id=1; 报错
    3.在另外一个 session 中
    insert into testmysam value(2); 等待
    4.在同一个 session 中
    insert into account value(4,'aa',123); 报错
    select * from account ; 报错
    5.在另外一个 session 中
    insert into account value(4,'aa',123); 成功
    6.加锁在同一个 session 中 select s.* from testmysam s 报错
    lock table 表名 as 别名 read;

独占写锁

1.lock table testmysam WRITE
在同一个 session 中

insert testmysam value(3);
delete from testmysam where id = 3 
select * from testmysam

2.对不同的表操作（报错）

select s.* from testmysam s 
insert into account value(4,'aa',123);

3.在其他 session 中 （等待）

select * from testmysam
总结：
  • 读锁，对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一 表的写请求
  • 读锁，对 MyISAM 表的读操作，不会阻塞当前 session 对表读，当对表进行修改会报错
  • 读锁，一个 session 使用 LOCK TABLE 命令给表 f 加了读锁，这个 session 可以查询锁定表 中的记录，但更新或访问其他表都会提示错误；
  • 写锁，对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；
  • 写锁，对 MyISAM 表的写操作，当前 session 可以对本表做 CRUD,但对其他表进行操作 会报错

InnoDB 锁

在 mysql 的 InnoDB 引擎支持行锁

  • 共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但 不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
  • 排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允 许其他事务给这几行上任何锁。包括写锁。

语法

上共享锁的写法：lock in share mode
例如： select * from 表 where 条件 lock in share mode；
上排它锁的写法：for update
例如：select * from 表 where 条件 for update；

注意：

1.两个事务不能锁同一个索引。
2.insert ，delete ， update 在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

  CREATE TABLE testdemo ( 
  `id` int(255) NOT NULL , 
  `c1` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , 
  `c2` int(50) NULL DEFAULT NULL , 
  PRIMARY KEY (`id`), INDEX `idx_c2` (`c2`) USING BTREE )ENGINE=InnoDB; 
  insert into testdemo VALUES(1,'1',1),(2,'2',2);
  1. BEGIN select * from testdemo where id =1 for update
    在另外一个 session 中 update testdemo set c1 = '1' where id = 2 成功
    update testdemo set c1 = '1' where id = 1 等待
  2. BEGIN update testdemo set c1 = '1' where id = 1
    在另外一个 session 中 update testdemo set c1 = '1' where id = 1 等待
  3. BEGIN update testdemo set c1 = '1' where c1 = '1'
    在另外一个 session 中 update testdemo set c1 = '2' where c1 = '2' 等待
  4. 第一个 session 中 select * from testdemo where id =1 for update
    第二个 session select * from testdemo where id =1 lock in share mode
    回到第一个 session UNLOCK TABLES 并不会解锁
    使用 commit 或者 begin 或者 ROLLBACK 才会解锁
  5. 再来看下表锁
    lock table testdemo WRITE
    使用 commit，ROLLBACK 并不会解锁
    使用 UNLOCK TABLES 或者 begin 会解锁

锁的等待问题

好了，掌握了上面这些，你对锁的了解已经超过了很多同学，那么现在来说一个实际的问 题，在工作中经常一个数据被锁住，导致另外的操作完全进行不下去。

你肯定碰到过这问题，有些程序员在 debug 程序的时候，经常会锁住一部分数据库的数据， 而这个时候你也要调试这部分功能，却发现代码总是运行超时，你是否碰到过这问题了，其 实这问题的根源我相信你也知道了。
举例来说，有两个会话。
程序员甲，正直调试代码
BEGIN
SELECT * FROM testdemo WHERE id = 1 FOR UPDATE
你正直完成的功能也要经过那部分的代码，你得上个读锁
BEGIN
SELECT * FROM testdemo WHERE id = 1 lock in share mode

这个时候很不幸，你并不知道发生了什么问题，在你调试得过程中永远就是一个超时得异常， 而这种问题不管在开发中还是在实际项目运行中都可能会碰到，那么怎么排查这个问题呢？ 这其实也是有小技巧的。

select * from information_schema.INNODB_LOCKS;

在这里插入图片描述
真好，我通过这个 sql 语句起码发现在同一张表里面得同一个数据有了 2 个锁其中一个是 X （写锁），另外一个是 S（读锁），我可以跳过这一条数据，使用其他数据做调试
可能如果我就是绕不过，一定就是要用这条数据呢？吼一嗓子吧（哪个缺德的在 debug 这个 表，请不要锁这不动），好吧，这是个玩笑，其实还有更好的方式来看

select * from sys.innodb_lock_waits

在这里插入图片描述
我现在执行的这个 sql 语句有了，另外看下最下面，kill 命令，你在工作中完全可以通过 kill 吧阻塞了的 sql 语句给干掉，你就可以继续运行了，不过这命令也要注意使用过，如果某同 事正在做比较重要的调试，你 kill，被他发现可能会被暴打一顿。

上面的解决方案不错，但如果你的 MySQL 不是 5.7 的版本呢？是 5.6 呢，你根本就没有 sys 库，这个时候就难办了，不过也是有办法的。

SELECT r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query, 
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread 
FROM 
information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id 
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

在这里插入图片描述
看到没有，接下来你是否也可以执行 kill 29 这样的大招了。

事务

现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保 持数据的一致性，所以提出了事务的概念。

A 给 B 要划钱，A 的账户-1000 元， B 的账户就要+1000 元，这两个 update 语句必须作为 一个整体来执行，不然 A 扣钱了，B 没有加钱这种情况很难处理。

什么存储引擎支持事务

  1. 查看数据库下面是否支持事务（InnoDB 支持）？
show engines;
  1. 查看 mysql 当前默认的存储引擎？
 show variables like '%storage_engine%';
  1. 查看某张表的存储引擎？
  show create table 表名 ;
  1. 对于表的存储结构的修改？
    建立 InnoDB 表：Create table … type=InnoDB； Alter table table_name type=InnoDB;

事务特性

事务应该具有 4 个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特 性。

  • 原子性（atomicity）
  • 一致性（consistency）
  • 隔离性（isolation）
  • 持久性（durability）

原子性（atomicity）

一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功， 要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作
小明给 小红 打钱
1.小明银行卡扣除 500 元
2小红银行卡增加 500
整个事务要么全部成功，要么全部失败

一致性（consistency）

一致性是指事务将数据库从一种一致性转换到另外一种一致性状态，在事务开始之前和事务 结束之后数据库中数据的完整性没有被破坏
小红给小明打钱
1、小红银行卡扣除500
2、小明银行卡增加500
2、小明银行卡增加1000
扣除的钱（-500） 与增加的钱（500） 相加应该为 0

持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的 修改数据也不会丢失
并不是数据库的角度完全能解决

隔离性（isolation）

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事 务是隔离的，并发执行的各个事务之间不能互相干扰。 （对数据库的并行执行，应该像串行执行一样）

  • 未提交读（READ UNCOMMITED）脏读
  • 已提交读 （READ COMMITED）不可重复读
  • 可重复读（REPEATABLE READ）
  • 可串行化（SERIALIZABLE）
    mysql 默认的事务隔离级别为 repeatable-read
show variables like '%tx_isolation%';
事务并发问题
  • 脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏 数据
  • 不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数 据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。
  • 幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系 统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现 还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不 可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

未提交读（READ UNCOMMITED）脏读
show variables like '%tx_isolation%'; 
set SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;

一个 session 中

start TRANSACTION 
update account set balance = balance -50 where id = 1

另外一个 session 中查询

select * from account

回到第一个 session 中 回滚事务

ROLLBACK
在第二个 session 中

select * from account

在另外一个 session 中读取到了为提交的数据，这部分的数据为脏数据

已提交读 （READ COMMITED）不可重复读
show variables like '%tx_isolation%';
 set SESSION TRANSACTION ISOLATION LEVEL read committed;

一个 session 中

start TRANSACTION 
update account set balance = balance -50 where id = 1

另外一个 session 中查询 (数据并没改变)

select * from account

回到第一个 session 中 回滚事务
commit
在第二个 session 中
select * from account (数据已经改变)

可重复读（REPEATABLE READ）
show variables like '%tx_isolation%';
 set SESSION TRANSACTION ISOLATION LEVEL repeatable read;

一个 session 中

start TRANSACTION 
update account set balance = balance -50 where id = 1

另外一个 session 中查询 (数据并没改变)

select * from account

回到第一个 session 中 回滚事务

commit

在第二个 session 中
select * from account (数据并未改变)

可串行化（SERIALIZABLE）
show variables like '%tx_isolation%'; 
set SESSION TRANSACTION ISOLATION LEVEL serializable;

1.开启一个事务

begin select * from account #发现 3 条记录

2.开启另外一个事务

begin select * from account #发现 3 条记录 也是 3 条记录

insert into account VALUES(4,'deer',500) #发现根本就不让插入
  1. 回到第一个事务 commit
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库

mysql

锁

事物

学习记录

七、MySql-锁与事物 的相关文章

  • WAMP/MySQL 错误语言不正确

    我已经多次重新安装WAMP 搜索了数百页 但仍未解决此问题 我查看了 phpmyadmin 配置文件 设置 cfg Lang en utf 8 卸载了多次 如上所述 似乎没有运气 任何帮助 将不胜感激 要更改 MySQL 在报告时使用的语言

  • 使用csv文件-PHP创建表到mysql时添加反引号

    我有一个 php 代码 它将使用 csv 文件创建一个到 mysql 数据库的表 然而 某些列标题没有被 mysql 读取 mysql 唯一一次读取查询是当我添加反引号 您能帮助我在查询中的何处添加反引号吗 这是我的代码 file C Us

  • 如何正确转义 mysql“搜索/喜欢”查询？

    Summary 我目前正在使用 search field LIKE this gt db gt escape like str search string 逃避动态创建的搜索查询 创建的 SQL 语句结果不会产生任何错误 也不会产生任何结果

  • 如何将 PHP 会话数据保存到数据库而不是文件系统中？

    我有两个网站 一个是 TLS 一个不是 两个都适用于同一个客户端 但我需要这些网站彼此 并且仅彼此 共享通用数据users orders accounts etc 这通常可以通过以下方式完成 SESSION数据 但我显然这些不能跨其他站点工

  • 计算 MySQL 中每个唯一 ID 与前一行的日期差异

    我是一名 SQL 初学者 正在学习查询的诀窍 我正在尝试查找同一客户购买之间的日期差异 我有一个如下所示的数据集 ID Purchase Date 1 08 10 2017 1 08 11 2017 1 08 17 2017 2 08 09

  • MySQL将多个表中的记录插入到新表中

    我正在尝试使用另外 2 个表中的记录填充一个新表 period states 是新的 空的 period states id period id sla id period 包含 15 条记录 periods id slas 包含 84 条

  • PHP 从表行中检索数据并将其存储到变量

    我想这些问题已经说明了一切 我的查询结果会生成与条件匹配的行 我想从每个表列中获取每个数据并将其放入一个变量中 getinfo select user firstname user middlename user lastname from

  • 如何在 MySQL 查询编辑器中对列重新排序？

    我想移动专栏OtherSupport below Amount2 是否有捷径可寻 ALTER TABLE myTable MODIFY OtherSupport VARCHAR 50 AFTER Amount2

  • 将日期格式（在数据库或输出中）更改为 dd/mm/yyyy - PHP MySQL

    MySQL 将日期存储在我的数据库中 默认情况下 为 YYYY MM DD 我的日期的字段类型是 DATE 我不需要任何时间存储 有没有一种简单的方法可以默认更改它到 DD MM YYYY 我在两个不同的表中调用不同的日期 并且在我的任何代

  • 如何在codeigniter中引用数据库连接？

    如何在 CodeIgniter 数据库处理程序对象中手动调用 PHP 数据库函数 如何检索连接 dbc 或者调用类似的函数mysql real escape string dbc variable 您可以调用任何 mysql 本机函数并访问

  • MySQL 和 PHP：多关键字搜索

    我有一个包含逗号分隔关键字的字符串 例如 keywords keyword1 keyword2 keyword3 我的表架构 名为tbl address是这样的 简化的 id INT 11 PRIMARY KEY AUTO INCREMEN

  • CodeIgniter Active Record - 组 OR 语句

    这是我的问题 MySQL 或 条件 https stackoverflow com questions 8604380 mysql or condition 解决方案是将 OR 语句分组 但我正在使用 CodeIgniters Active

  • MySQL C++ 连接器未解决的依赖关系（VS 2015）

    我正在尝试在 Windows Visual Studio 2015 上编译 MySQL Connector C 我根据以下内容使用CMake生成了项目文件官方说明 https dev mysql com doc connector cpp

  • MySQL中Join同表临时表

    我喜欢在 MySQL 中加入一个失败的临时表 这个想法很简单 CREATE TEMPORARY TABLE temp table LIKE any other table srsly it does not matter which tab

  • 查询查找表中姓名和号码之间的重复项

    SELECT count lower name number FROM tbl GROUP BY lower name number HAVING count gt 1 输入表1 slno name number 1 aaa 111 2 A

  • PHP strtotime返回Mysql UNIX_TIMESTAMP的不同值

    我在 stackoverflow 上搜索过帖子 发现了一些类似的帖子 但我认为这是一篇不同的帖子 我的 PHP 和 Mysql 服务器的时区全部设置为 UTC 在表中我使用时间戳字段 值为 2010 11 08 02 54 15 我使用这样

  • 通过“SELECT”命令选择每组的前两条记录的最佳方法是什么？

    例如我有下表 id group data 1 1 aaa 2 1 aaa 3 2 aaa 4 2 aaa 5 2 aaa 6 3 aaa 7 3 aaa 8 3 aaa 通过 SELECT 命令选择每组的前两条记录的最佳方法是什么 如果没有

  • 编写多个mysql脚本

    是否可以在复合脚本中包含其他 mysql 脚本 理想情况下 我不想为包含的脚本创建存储过程 对于较大的项目 我想分层维护几个较小的脚本 然后根据需要组合它们 但现在 我很乐意学习如何包含其他脚本 source是一个内置命令 您可以在 MyS

  • 如何在具有动态列的表中插入值 Jdbc/Mysql

    我想在具有动态列的表中添加值 我设法创建一个包含动态列的表 但我不知道如何插入数据 Create Table sql CREATE TABLE MyDB myTable level INTEGER 255 int columnNumber

  • Mysql对三表多列的连接查询

    我有三个这样的表 专业化 sid s name 1 test 1 2 test 2 Person pid name sid 1 ABC 1 2 XYZ 2 Timing tid time from time to pid 1 08 00 0

随机推荐

  • Spring 循环依赖

    Spring 循环依赖 1 什么是循环依赖 抛开 Spring 框架不说 先来了解循环依赖的本质 从上图总可以看出依赖关系 A 依赖 B B 依赖 C C 依赖 A 这种依赖的关系就造成了循环依赖的发生 让我们从代码的角度来看看循环依赖是如

  • 代码失效 java_异常：java.io.StreamCorruptedException：类型代码无效：00

    java rmi UnmarshalException failed to unmarshal MY ENTITY nested exception is java io StreamCorruptedException invalid t

  • Qt：在widget的外部进行绘制带有坐标轴的图像

    Widget h ifndef WIDGET H define WIDGET H include

  • idea 使用相对路径报错：FileNotFoundException

    idea默认工作环境目录是当前项目 可以通过Run gt edit configurations gt working directory配置 MODULE WORKING DIR 命令可以自动定位到当前项目目录 注意当项目有不同模块 即对

  • 问题：java.lang.UnsatisfiedLinkError: no jniopencv_core in java.library.path

    一 问题 今天的程序在一台新电脑上运行 出现java lang UnsatisfiedLinkError no jniopencv core in java library path异常 具体报错日志 Exception in thread

  • Windows 仍在设置此设备的类配置。 (代码 56)

    家里电脑有线网卡出现 Windows 仍在设置此设备的类配置 代码 56 解决方法 键盘按win r 弹出运行窗口 输入 redegit 进入注册表 删除HKEY CLASSES ROOT CLSID 3d09c1ca 2bcc 40b7

  • JavaScript的OO思想(一)

    类class是Object Oriented面向对象的语言有一个标志 通过类我们可以创建任意多个具有相同属性和方法的对象 JavaScript中没有类的概念 但它也是面向对象的 只是实现方法会有所不同 创建单个对象有两种基本方法 1 使用O

  • 从平面设计转行软件测试，喜提11K+13薪，回头看看我很幸运

    如何能够成为一个优秀的人 答 一以贯之的努力 不得懈怠的人生 每天的微小积累会决定最终结果 对待未来 人生与知识的敬意 永远值得我们学习 前言 我是2020年数字媒体技术专业毕业的 转行软件测试之前做的是平面设计 毕业的时候 我并不知道有软

  • transformers库的使用【一】——pipeline的简单使用

    transformers库的使用 使用pipeline API来快速使用一些预训练模型 使用预训练模型最简单的方法就是使用pipeline transformers提供了一些任务 1 情感分析 Sentment analysis 分析文本是

  • upload-labs

    在打本靶场之前 首先写一个一句话木马 关闭计算机的安全防护 不然计算机会杀掉 配合蚁剑可进行进一步操作 注 为避免部分题目可能无法实现 这里推荐使用phpstudy2016进行操作 目录 Pass 01 Pass 02 Pass 03 Pa

  • ethereumjs/ethereumjs-util

    ethereumjs ethereumjs util Most of the string manipulation methods are provided by ethjs util 更多的字符串处理方法可以看ethjs util ad

  • 几个常用的图片处理和图像识别API

    鸟类识别 http www inspirvision cn www product 支持鸟类品种鉴别 不同种类鸟类检测 鸟类数量统计 提供API Camera360 https github com pinguo PGSkinPrettif

  • [ kubernetes ] 基础名词解释

    Service RC RS和Deployment只是保证了支撑服务的微服务Pod的数量 但是没有解决如何访问这些服务的问题 一个Pod只是一个运行服务的实例 随时可能在一个节点上停止 在另一个节点以一个新的IP启动一个新的Pod 因此不能以

  • 跨域性的常识性推理

    跨域性的常识性推理是指在不同领域或知识领域之间进行推理和迁移的能力 它涉及将已有的知识和经验应用于新的情境或领域 以生成新的推理和理解 以下是关于跨域性常识性推理的一些常见观点 基础知识迁移 跨域性常识性推理可以帮助我们将基础知识从一个领域

  • 深度学习的发展方向： 深度强化学习！

    点击上方 小白学视觉 选择加 星标 或 置顶 重磅干货 第一时间送达 作者 莫凡 马晶敏 上海交通大学 转载自 Datawhale 深度学习不够智能 强化学习又太抽象 深度强化学习是两套理论体系乘风破浪以后的成团产物 其骨架来自强化学习 而

  • 整理了适合新手的20个Python练手小程序

    100个Python练手小程序 学习python的很好的资料 覆盖了python中的每一部分 可以边学习边练习 更容易掌握python 本文附带基础视频教程 私信回复 基础 就可以获取的 程序1 题目 有1 2 3 4个数字 能组成多少个互

  • 通过poi+java实现Excel表格的列宽度自适应

    新建sheet同时往sheet加入数据后 进行列宽设置 代码如下 固定首行 下拉时实现首行固定不动 sheet createFreezePane 0 1 0 1 列宽自适应 outputList get 0 size 为首行的列数 根据首行

  • web容器与servlet容器的区别

    servlet容器 负责管理servlet生命周期 web容器 负责管理和部署web应用 其本身可能具备servlet容器组件 如果没有 一般能将第三方servlet容器作为组件整合进web容器 1 web容器好比电视机 servlet容器

  • Hibernate（二）——一对多查询

    1 前言 本章节我们讨论Hibernate一对多查询的处理 在上一章节中 Hibernate 一 入门 我们探讨了Hibernate执行最基本的增删改查操作 现在我们将情况复杂化 加入我们在查询用户信息的时候需要同时查询其登录日志 这样就涉

  • 七、MySql-锁与事物

    MySql 锁与事物 锁 锁的简介 为什么需要锁 锁的概念 MySQL 中的锁 表锁与行锁的使用场景 MyISAM 锁 共享读锁 独占写锁 总结 InnoDB 锁 语法 注意 锁的等待问题 事务 什么存储引擎支持事务 事务特性 原子性 at

热门标签