MySQL事务与锁

news/2024/6/29 12:15:33 标签: mysql, 事务, 锁

事务

数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作。事务是一组不可分割的操作集合

MySQL中开启事务:

begin/start transaction//开启事务

Commit/rollback//提交事务或回滚

set session autocommit=on/off //开启自动提交或关闭自动提交

事务特性ACID

  • 原子性（Atomicity）
    最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚
  • 一致性（Consistency）
    事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则，
    不会因为出现系统意外等原因导致状态的不一致
  • 隔离性（Isolation）
    一个事务所操作的数据在提交之前，对其他事务的可见性设定（一般设定为不可见）
  • 持久性（Durability）
    事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失

事务并发带来的问题

脏读

在这里插入图片描述

事务A读取了事务B尚未提交的数据，导致脏读(可以通过为事务B的操作加排他锁来解决)

幻读

在这里插入图片描述

同一个事务A中，多次读取相同的数据结果应该是一样的。 幻读常见于另一个事务提交了update操作(可以通过对事务A的查询加上共享锁来解决)

不可重复读

在这里插入图片描述

幻读与不可重复读很类似，只是不可重复读是另一个事务提交了insert、delete操作，而幻读是另一个事务提交了update操作(可以通过为事务A加上临键锁来解决)

事务隔离级别

  • Read Uncommitted 未提交读-- 未解决并发问题

    事务未提交对其他事务也是可见的 —>脏读

  • Read Commited 提交读 – 解决脏读问题

    一个事务开始之后，只能看到自己提交的事务所做的修改，不可重复读

  • Repeatable read 可重复读 – 解决不可重复读

    在同一个事务中多次读取同样的数据，结果是一样；这种隔离级别未定义解决幻读的问题

  • Serializable 串行化–>解决所有问题

    最高的隔离级别，通过强制事务的串行执行

在这里插入图片描述

可能是可能出现，不可能是不可能出现

锁

锁是用于管理不同事务对共享资源的并发访问

表锁与行锁的区别：

锁定粒度：表锁>行锁

加锁效率: 表锁>行锁

冲突概率：表锁>行锁

并发性能：表锁<行锁

InnoDB存储引擎支持行锁与表锁(通过行锁实现)

MySQL InnoDB锁类型

  • 共享锁(行锁)：Shared Locks
  • 排他锁(行锁): Exclusive Locks
  • 意向共享锁： Intention Shared Locks
  • 意向排他锁: Intention Exclusive Locks
  • 自增锁： AUTO-INC Locks

行锁算法：

  • 记录锁 Record Locks
  • 间隙锁 Gap Locks
  • 临键锁 Next-key Locks
共享锁

又称读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问数据，但是只能读不能修改

加锁方式：

select * from users where id=1 Lock in share mode

释放锁:

Commit/rollback

//设置为手动提交的方式 事务1
set session autocommit=off;
select * from users where id=1 lock in shard mode;//开启读锁


//另一个事务 事务2
set session autocommit=off;
-- 修改操作
update users set name='wojiushiwo' where id=1;// 由于id=1的数据已经被别的事务加上了读锁，所以这里修改操作则被阻塞，直到该修改终止或者另一个事务commit或rollback
commit;
--如果该事务2执行的是对id=1的事务查询操作 则不会阻塞 正常返回查询结果

--如果事务2是对不是id=1的数据执行操作 则与事务1不冲突 不发生阻塞。共享锁是行锁
排它锁

又称写锁，简称X锁，排它锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁(包括共享锁/排他锁),只有该获取了排他锁的事务是可以对数据行进行获取和修改的（其他事务要读取数据可根据快照）

-- delete/update/insert 均会对操作数据加X锁，X锁会对操作该条数据的其他事务互斥，无论是读操作还是写操作
-- select * from table-name where ... for update 会加X锁

这几种锁的实现前提是不同事务且事务手动提交；如果不开启手动提交，一个事务对数据行进行操作之后会立刻对持久化数据造成影响，另一个事务紧接着操作 也就不会有什么跟第一个事务有阻塞的情况了。

如果事务1开启了自动提交事务，事务2关闭了自动提交，那么如果事务1先执行，事务2后执行，那么不会出现阻塞问题，否则会出现阻塞情况

加锁方式:

Delete/update/insert 默认加上X锁

select * from table-name where … for update

释放锁的方式：

commit、rollback

InnoDB的行锁是通过给索引上的索引项加锁来实现的

只有通过索引条件进行数据检索InnoDB才使用行锁，否则InnoDB将使用表锁(锁住索引的所有记录)

-- 二级索引，可以看做是普通索引
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(32) NOT NULL,
  `userLevel` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `phoneNum` char(11) NOT NULL,
  `createTime` datetime NOT NULL,
  `lastUpdate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_union_uname&userLevel&age` (`uname`,`userLevel`,`age`) USING BTREE,
  KEY `idx_name` (`uname`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 如果是通过索引来进行数据操作的，InnoDB会给该
-- 事务1 由于uname是索引列，所以会为uname加一把锁，同时会为该条数据的id加上一把锁
 set session autocommit=off;
update users set uname='wojiushiwo' where uname='wojiushiwo';
rollback
-- 事务2 数据中id=2的uname=“我就是我”
 set session autocommit=off
 delete from users where id=2；//执行到这里的时候 会被阻塞 因为事务1的执行为id也加了一把锁；这里也可以用X锁原理解释
 commit;
 //如果不是操作该条数据，则不会阻塞了
 -----------------------------------------------------------------------
 -- 事务1 由于phoneNum不是索引列，所以会为整张表加上一把锁
 set session autocommit=off;
update users set uname='wojiushiwo' where phoneNum='13888888888';
rollback
-- 事务2 数据中id=3的phoneNum不是“13888888888”
 set session autocommit=off
 delete from users where id=3；//由于事务1加上了表锁 所以事务2的任何操作均被阻塞

表锁：lock tables xx read或lock tables xx write;

--解锁
unlock tables；
--解锁
show processlist;
kill {id}

意向共享锁IS

表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的

意向排它锁IX

表示事务准备给数据行加入排它锁，即一个数据行加排它锁前必须先获得该表的IX锁，意向排他锁之间是可以相互兼容的

IS、IX是InnoDB数据操作之前自动加的，不需要用户干预

当事务想去进行锁表操作时，可以先判断意向锁是否存在，如果存在则可快速返回该表不能启用表锁

自增锁

针对自增列AUTO_INCREMENT的一个特殊的表级别锁

show variables like 'innodb_autoinc_lock_mode'查看自增锁属性值

默认是1，代表连续；事务未提交ID会丢失，意思是设置了自增列的数据中，如果该条数据未持久化到数据库被回滚，则这个自增数字就丢了，下一次的持久化操作，自增数还会+1；

锁算法

临键锁 Next-key locks

临键锁=间隙锁+记录锁

锁住记录+区间(左开又闭)

当sql执行按照索引进行数据的检索时，查询条件为范围查找(between and 、<、>)并有数据命中则此SQL语句加上的锁是Next-key locks

--事务1 
set session autocommit=off;
select * from users where id>=1 and id<=5 for update;//数据中有id=1，3，4，5，6...的数据
rollback
--事务2
set session autocommit=off;

delete from users where id=1;
delete from users where id=3;
delete from users where id=4;
delete from users where id=5;
delete from users where id=7;
rollback

事务1为数据加锁，加锁范围是当前查询的范围以及下一个区间，即[1,3],(3,4],(4,5],(5,6]。虽然范围是左开右闭，但是这里id有取区间值，所以1是闭区间

因为默认的算法是临键锁算法，所以事务1为这些范围的数据进行加X锁，事务2中在这个范围的数据均被锁上，例如id=1,3,4,5,6;而id=7由于不在区间内，所以不被锁

以id为例，由于索引是采用B+树，所以叶子节点的数据均是有序的，因此对当前区间+下一区间进行加锁，可以有效避免幻读问题。因为不可重复读 着意于对insert、delete操作而言，而当前数据的下一区间也锁定了，所以当另一事务新建数据时，可以有效避免。

间隙锁Gap

当范围查询或等值查询记录不存在，临键锁退化成Gap锁。

--事务1 id>1&id<3 根据之前来看 不存在该条数据，所以临键锁变成Gap锁，锁住(1,3)区域，开区间
set session autocommit=off;
select * from users where id>1 and id<3 for update;
rollback
--事务2 在(1,3)之间插入id=2的数据，由于(1，3)已经被加Gap锁，所以这里插入操作会被锁住
set session autocommit=off;
insert into users(id,uname,userLevel,age,phoneNum,createTime,lastUpdate) values(2,'wojiushiwo',2,2,'xxxx','2018-12-01 15:39:46','2018-12-01 15:39:46')
rollback

--事务1 id=2 根据之前来看 不存在该条数据，所以临键锁变成Gap锁，锁住(1,3)区域，开区间
set session autocommit=off;
select * from users where id=2 for update;
rollback
--事务2 在(1,3)之间插入id=2的数据，由于(1，3)已经被加Gap锁，所以这里插入操作会被锁住
set session autocommit=off;
insert into users(id,uname,userLevel,age,phoneNum,createTime,lastUpdate) values(2,'wojiushiwo',2,2,'xxxx','2018-12-01 15:39:46','2018-12-01 15:39:46')
delete from users where id=4;//这条数据不会被锁住
rollback
记录锁 Record Lock

唯一索引等值查询或精准匹配

set session autocommit=off;
select * from users where id=1 for update;//由于这是精准匹配 所以会锁住id=1的数据，与X锁的基本原理一样了

http://www.niftyadmin.cn/n/550932.html

相关文章

Chrome扩展开发常见问题汇总

Chrome扩展开发常见问题汇总

如何输出和查看调试信息 有时我们需要看看我们的程序是否按照我们预想的那样执行了&#xff0c;可以输出一些调试信息&#xff0c;如下&#xff1a; console.log("clicked"); 那这样信息在哪里查看呢&#xff1f;其实有两个地方&#xff0c;这取决于log语句在后台执行…
阅读更多...
(1)个人使用小爬虫---------关于一次被论坛封号而搜索的思考

(1)个人使用小爬虫---------关于一次被论坛封号而搜索的思考

前段时间上了某论坛的技术讨论区&#xff0c;习惯性的打开搜索看有没有我需要的内容&#xff0c;一登陆账号&#xff0c;发现自己被禁言了&#xff0c;连基本的搜索功能也被限制了。无奈只能手动的一个一个会找帖子。我去&#xff0c;竟然有200多页&#xff0c;每页有40第数据&…
阅读更多...
慢查询日志

慢查询日志

慢查询日志 slow_query_log 启动/停止记录慢查询日志&#xff0c;默认是OFF slow_query_log_file 指定慢查询日志的存储路径&#xff0c;默认存放路径与data存放路径一致 long_query_time 指定记录慢查询日子SQL执行时间的阈值&#xff0c;默认是10s&#xff0c;通常改为1ms…
阅读更多...
[职场晋升术 傻坚持好过不坚持]读书笔记

[职场晋升术 傻坚持好过不坚持]读书笔记

坚持下来就是资本转机孕育在坚持路上&#xff0c;坚持到底才能有收获成功就是不断地重复 转载于:https://www.cnblogs.com/bluescorpio/archive/2012/11/21/2780257.html
阅读更多...
[Netty学习笔记]一、I/O模型

[Netty学习笔记]一、I/O模型

I/O模型 I/O模型简单的理解&#xff1a;就是用什么样的通道进行数据的发送和接收&#xff0c;很大程度上决定了程序通信的性能。 Java共支持是三种网络编程模型&#xff1a;BIO/NIO/AIO BIO模型 Java BIO&#xff1a;同步阻塞(传统阻塞型),服务器实现模式是一个连接一个线程…
阅读更多...
来！一起给 Visual Studio 2012 找找茬

来！一起给 Visual Studio 2012 找找茬

您在使用 VS2012 的过程中&#xff0c;有什么不爽的地方&#xff0c;分享出来让大家“爽”一下&#xff0c;同时也可以减轻自己的不爽。我来起个头。 1. 添加新类时不自动加上 public 关键字 这个问题在 VS2010 中就已经存在&#xff0c;在开发中多数时候都需要在 class 前面加…
阅读更多...
[Netty学习笔记]二、NIO核心组件Buffer

[Netty学习笔记]二、NIO核心组件Buffer

通过上一节我们了解NIO的三大核心组件分别是Buffer、Channel、Selector。 三大核心部分的关联图: 每个channel都会对应一个BufferSelector对应一个线程&#xff0c;一个线程对应多个channel上图显示有三个channel注册到selector上Selector是由事件驱动的&#xff0c;程序切换到…
阅读更多...
java 理解实例和类成员(翻译自Java Tutorials)

java 理解实例和类成员(翻译自Java Tutorials)

原文出自 http://www.cnblogs.com/ggjucheng/archive/2012/11/30/2796085.html 这个章节&#xff0c;我们主要介绍static关键字创建类的字段和实例&#xff0c;而不是讲类的实例。 类变量 从类的蓝图中创建一个对象&#xff0c;每个对象都有自己的变量的唯一拷贝。例如Bicycle类…
阅读更多...
最新文章

Copyright @ 2022~2023