MySQL：（十）MySQL锁和事务

最新推荐文章于 2021-10-12 14:00:37 发布
最新推荐文章于 2021-10-12 14:00:37 发布
阅读量454 收藏 5
点赞数 1
分类专栏： mysql 文章标签： MySQL锁和事务
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wdirdo/article/details/101063873
版权

MySQL：（十）MySQL锁和事务

（一）关系型数据库基础
（二）MySQL安装
（三）管理数据库和表
（四）用户和权限管理
（五）函数，存储过程和触发器
（六）MySQL架构
（七）存储引擎
（八）MySQL服务器选项，系统和状态变量
（九）优化查询和索引管理
（十）锁和事务管理
（十一）日志管理
（十二）备份还原
（十三）MySQL集群

MySQL 锁

MySQL锁的概述

1）MySQL锁
**锁是计算机协调多个进程或纯线程并发访问某一资源的机制。**即锁的目前是为了实现并发控制。相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。MySQL大致可归纳为以下3种锁：

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

2）上述关于锁的概念

  • 锁粒度：
    即通常所说的锁级别。数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。 为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。
    ①表级锁
    ②行级锁
如果两个用户同时对数据库中的某个表的某一个记录修改时：
==>存储引擎自动加锁的锁粒度
    myisam：表级锁  别的用户不能对表中其他记录进行修改，影响并发
    innodb：行级锁  别的用户只是不能对当前记录进行修改

  • 锁：
    读锁：共享锁，只读不可写（包括当前事务），多个读互不阻塞
    写锁：独占锁，排它锁，写锁会阻塞其他事务（不包括当前事务）

  • 锁的分类：
    隐式锁：由存储引擎自动施加的锁
    显示锁：用户手动请求

  • 锁的实现：
    存储引擎：自行实现其锁策略和锁粒度
    服务器级：实现了锁，表级锁，用户可显示请求

  • 锁策略：在锁粒度及数据安全性寻求的平衡机制

3）下面主要实现显示加锁（存储引擎加锁是mysql自动实现的功能不能干预）

  • LOCK TABLES 加锁
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] …
    lock_type: READ ， WRITE
  • UNLOCK TABLES 解锁
  • FLUSH TABLES [tb_name[,…]] [WITH READ LOCK]
    关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
  • SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
    查询时加写或读锁
由上述命令可知：人为加锁默认只能加表级锁
    lock tables
        其中加锁类型有：read  和 write

示例：终端：/dev/pts/0 
> lock tables students read; 对studens加锁
> select stuid,name,age from students where stuid<3; == ok
+-------+-------------+-----+
| stuid | name        | age |
+-------+-------------+-----+
|     1 | Shi Zhongyu |  22 |
|     2 | Shi Potian  |  22 |
+-------+-------------+-----+
> update students set age=25 where stuid=1; == 直接报错
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated

终端：/dev/pts/1
> select stuid,name,age from students where stuid<3; == ok
+-------+-------------+-----+
| stuid | name        | age |
+-------+-------------+-----+
|     1 | Shi Zhongyu |  22 |
|     2 | Shi Potian  |  22 |
+-------+-------------+-----+    
> update students set age=25 where stuid=1;  == 阻塞
    此时会被阻塞在此处，直至表级锁被释放

总结：人为添加read的表级锁时，从表中读取数据是影响的，但是对表进行修改时会被阻塞.

> unlock tables; 
    ==>unlock tables;只能解锁当前终端加锁的所有表

==>实验出现这样了的现象：
    在终端：/dev/pts/0  
        对其中一张表：>lock tables teachers read;

    在终端：/dev/pts/1  
        对其中一张表：>lock tables students read;
    
    然后在各自的终端访问对方加锁的表就不能访问啦？直接报错？    

示例：添加write锁：
终端：/dev/pts/0 
    > lock tables teachers write;
    > select * from teachers;  == ok
    > select * from teachers;  == ok
终端：/dev/pts/1
    > select * from teachers;  == 阻塞
    此时将会被阻塞

总结：write锁是独占锁，仅能自己查询或者修改，其他用户不能的操作均会别阻塞

示例：FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
    > flush tables; 全局读锁
    整个实例的全局读锁！
    > flush tables students with read lock;
        注: <即使这样明确写明对students表加读锁也是全局读锁>

    解锁：> unlock tables;

事务

事务

  • 事务Transactions：一组原子性的SQL语句，或一个独立工作单元

  • 事务日志：记录事务信息，实现undo,redo等故障恢复功能

  • 事务的特性：
    A：atomicity原子性；整个事务中的所有操作要么全部成功执行，
    要么全部失败后回滚

    C：consistency一致性;数据库总是从一个一致性状态转换为另
    一个一致性状态

    I：isolation隔离性；一个事务所做出的操作在提交之前，是不
    能为其它事务所见；隔离有多种隔离级别，实现并发

    D：durability持久性；一旦事务提交，其所做的修改会永久保
    存于数据库中

事务的生命周期

  • 启动事务：
    BEGIN
    BEGIN WORK
    START TRANSACTION
  • 结束事务：
    COMMIT：提交
    ROLLBACK: 回滚
    注意： 只有事务型存储引擎中的DML语句方能支持此类操作，myisam不支持事务
  • 自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交
    建议：显式请求和提交事务，而不要使用“自动提交”功能
  • 事务支持保存点：savepoint
    SAVEPOINT identifier
    ROLLBACK [WORK] TO [SAVEPOINT] identifier
    RELEASE SAVEPOINT identifier
启动一个事务：begin
    增删改....
结束事务：commit和rollback

> select @@autocommit 自动提交;
+---------+
| 自动提交 | @@autocommit
+---------+
|       1 |
+---------+
==>MySQL系统中默认启动的是自动提交机制
    即对表的增删改在SQL语句最后的分号就当做事务提交了。

> set autocommit=0;会话将autocommit改为0

以students表为例

session1session2
> set autocommit=0;将事务改为显示，但是临时修改
>delete from students where stuid=31;
> select * from hellodb.students;可查询到stuid=31的记录
> commit;显示提交事务
> select * from hellodb.students;不能查询stuid=31的记录
同时执行多个增删改操作，如果rollback将全部撤销
显示提交事务时，如果掉电系统故障均视为rollback
只有显示提交即>commit;才算事务的提交

注意： 事务的rollback只能针对DDL(insert,delete,update)即对表的增删改可以在事务未提交时撤销，DDL(create,drop,alter)不能rollback即删库删表不能使用rollback撤销。

事务的意义

  • 事务可以保证数据的完整性和安全性。eg：转账必须以事务方式进行
  • 多个DDL语句作为整体执行，比单个DDL语句独立执行速度更快。
示例：

定义如下存储过程，create test表，insert 100000 条记录
    快速清空表中记录：truncate table testlog

create table test (id int auto_increment primary key,name char(10));
delimiter $$

create procedure  proc_test() 
begin  
declare i int;
set i = 1; 
while i < 100000 
do  insert into test(name) values (concat('bob',i)); 
set i = i +1; 
end while; 
end$$

delimiter ;
session1 以事务整体执行存储过程session2 独立执行insert
> begin;> call proc_test;> commit;> call proc_test;
耗时如下：耗时如下：
Query OK, 1 row affected (2.99 sec)Query OK, 1 row affected (2 min 18.05 sec)

事务的隔离级别

  • READ UNCOMMITTED 可读取到未提交数据，产生脏读
  • READ COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
  • REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
  • SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差
  • MVCC: 多版本并发控制，和事务级别相关
  • 说明： REPEATABLE READ最适合数据库的备份。备份是希望备份某个点的数据库状态。
事务隔离级别脏读可能性不可重复读可能性幻读可能性加锁读
读未提交（read-uncommitted）是是是否
不可重复读（read-committed）否是是否
可重复读（repeatable-read）否否是否
串行化（serializable）否否否是
脏数据：脏数据在临时更新(脏读)中产生。即一个事务读取了另一个事务未提交的数据。
READ-UNCOMMITTED：脏读
    脏数据可读
READ-COMMITTED：不可重复读
    即可能不同时间读取的记录不相同，出现幻读
REPEATABLE READ：可重复读
    读取事务开始时刻的数据库状态，在整个事务过程中记录不变化
SERIALIZABILE 可串行化
    未提交的读事务阻塞修改事务以及未提交的修改事务阻塞读事务

事务隔离级别相关的设置

  • 事务隔离级别系统变量：tx_isolation
  • 事务隔离级别服务器选项：
    transaction-isolation=[READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE]
  • 系统默认的事务隔离级别是REPEATABLE-READ

实验：READ-UNCOMMITTED

  • [mysqld]
    transaction-isolation=READ-UNCOMMITTED
session1session2
>begin;>begin;
> insert students(stuid,name,age)values(32,‘Wu Ming’,35);> select * from students where name like ‘W%’;
事务未提交记录可查询
>rollback;记录不不可查询
commit;

实验：READ-UNCOMMITTED

  • ransaction-isolation=READ-COMMITTED
session1session2
>begin;>begin;
> insert students(stuid,name,age)values(35,‘Duan lang’,35);
还未提交的事务
> select * from students where name like ‘duan%’;> select * from students where name like ‘duan%’;
可查询不可查询
> commit;提交事务
> select * from students where name like ‘duan%’;> select * from students where name like ‘duan%’;
可查询可查询
>begin; 再次开启一个事务
> insert students(stuid,name,age)values(36,‘Duan Yu’,30);
> commit;提交事务
> select * from students where name like ‘duan%’;
可查询的记录增加
此session中同样的查询，结果不同，即不可重复读
> commit;

实验：REPEATABLE-READ

  • ransaction-isolation=REPEATABLE-READ
session1session2
>begin;>begin;
>insert students(stuid,name,age)values(36,‘Qiao Feng’,40);
可查询> select * from students;
不可查询
> commit; 提交事务
不可查询
可重复读，但实际上数据已经改变，幻读。
> commit;

实验：SERIALIZABLE

session1session2
>begin;>begin;
> select * from students;> select * from students;
可同时读可同时读
> select * from students;
>insert students(stuid,name,age)values(38,‘Liu Bei’,50);
读阻塞改，阻塞一定时间，若其他人还不提交事务，报错
> commit;> commit;
> begin;> begin;
>insert students(stuid,name,age)values(38,‘Liu Bei’,50);
> select * from students;
更改阻塞读,阻塞一定时间，若其他人还不提交事务，报错
阻塞时间内提交事务可执行，包含立即更改的数据均可查询

死锁

  • 两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
session1session2
> begin;> begin;
> update teachers set age=50 where tid=4;> update students set age=10 where stuid=39;
修改成功，innodb会加行级锁修改成功，innodb会加行级锁
此时session1对session2事务中修改的行stuid=39再修改时此时session2对session1事务中修改的行tid=4再修改时
死锁死锁
> update students set age=100 where stuid=39;> update teachers set age=10 where tid=4;
阻塞阻塞
系统会发现死锁，然后将其中一个事务进行回滚系统会发现死锁，然后将其中一个事务进行回滚
——
Query OK, 1 row affected (7.46 sec)Lock wait timeout exceeded; try restarting transaction

可能出现这种场景：当一个事务执行改操作时，innodb默认添加行锁，但是事务一直未提交
show processlist and kill #

session1session2session3
> begin;--
> update teachers set age=50 where tid=4;--
session1一直不提交事务，行锁一直存在--
-session2想对同一行进行修改时-
-> update teachers set age=30 where tid=4;-
-因为行级锁的原因会阻塞-
--> show processlist;
--查询哪些线程正在运行
--需要干预，将session1的事务关闭
--> kill id（kill session1对应的id）
--释放行级锁
--不能允许这种事务一直不提交，将行锁一直开启，影响其他session访问

事务日志简介

  • 事务日志文件： ib_logfile0， ib_logfile1

  • 事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging

  • 注：事务即使没有提交，事务日志仍会记录

  • 默认 ib_logfile0， ib_logfile1 两个文件固定大小为5M，文件被写满，前面的日志将会被覆盖

  • 查询不会记录事务日志，事务日志只记录增删改操作即DDL（insert,update,delete）

  • 事务日志记录的是对应具体修改的哪个磁盘块，并不是记录SQL语句

  • 关于事务日志详情见mysql日志

Hskds
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【笔记】SQL 6-2
m0_59348479的博客
08-20 223
InnoDB 锁的种类之意向锁、记录锁和间隙锁 意向锁（Intention Locks; table-level lock） 意向锁是一种特殊的表级锁，意向锁是为了让 InnoDB 多粒度的锁能共存而设计的。取得行的共享锁和排他锁之前需要先取得表的意向共享锁（IS）和意向排他锁（IX），意向共享锁和意向排他锁都是系统自动添加和自动释放的，整个过程无需人工干预。 意向锁就是指未来的某一个时刻事务可能要加共享锁或者排它锁，提前声明一个意向，分为两种： 意向共享锁（Intention Shared Loc
MySQL事务和锁
m0_52789121的博客
05-15 384
1.事务 1.什么是事务？ 事务： 要么全部成功，要么全部失败 事务是数据库管理系统（DBMS）执行过程中的一个 逻辑单位 ，由一个 有限的数据库操作序列 组成。 逻辑单位：最小的操作单位，不可再分割。 有限的数据库操作序列：包含一个或多个DML语句。 支持事务的引擎： InnoDB、NDB 2.事务的四大特性是啥？ ACID： 最终目的——保证数据的一致性。 原子性（Atomicity）： 事务是不可分割的工作单位，DML语句要么全部成功，要么全部失败。 一致性（Consistency）： 事务执行前
MySQL（二）锁 ----- 表锁
JavaTeachers的博客
10-12 1689
前言 MySQL 里面的锁大致可以分成全局锁，表级锁，行锁 这儿将讲一下表级锁 表级锁 MySQL 里面表级别的锁有两种： 表锁， 元数据锁（meta data lock，MDL)。 表锁 lock tables 表名 read; #该表可以读，不能ddl 和 dml 中增删改，只能读取表数据 lock tables 表名 write; #既不能读，也不能写 表锁的语法是 lock tables … read/write。 与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可
Linux高级篇--MYSQL数据库之索引管理、锁和事务管理以及日志管理
天空飘过的鱼的博客
10-15 355
Linux高级篇–MYSQL数据库之索引管理、锁和事务管理以及日志管理 一、 优化查询和索引管理 查询缓存 查询缓存（ Query Cache ）原理 缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写 优缺点 不需要对SQL语句做任何解析和执行，当然语...
Oracle 问答题 PLSQL块
peter20082008的专栏
04-28 252
1.未命名的PL/SQL块是匿名块，已命名的PL/SQL块是什么？ --过程 --------------------------------------------------------- 2.有表student(name varchar2(20),age number(3,0),address varchar2(30)) 在插入部分记录之后，请建立存储过程getInfo,并可通过...
Mysql锁以及Mysql事务
baidu_38900596的博客
01-05 73
Mysql锁以及Mysql事务 一、事务 数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作，是一组不可再分割的操作单元 手动开启事务 通过begin/start开启事务 commit/rollback结束事务 set session autocommit = OFF 设置关闭自动提交 java jdbc 代码 conn.setAutoCommit(false) conn.commit()/conn.rollback() 二、事务四大特性 原子性（最小工作单元、要
MySQL数据库：事务与锁机制
最新发布
07-11
MySQL数据库：事务与锁机制
mysql中的事务、锁讲解和操作
07-04
MySQL中的事务和锁是数据库管理中的关键概念，对于保证数据的一致性和完整性至关重要。事务是数据库操作的基本单元，它确保一组操作要么全部成功，要么全部失败，从而避免数据的不一致状态。在MySQL中，事务支持ACID...
MySQL8.0锁机制和事务
03-07
MySQL 8.0 锁机制和事务 MySQL 8.0 中的锁机制和事务是数据库系统中非常重要的概念。锁机制是指数据库系统中对数据访问的控制机制，而事务是指数据库系统中的一组操作单元。了解锁机制和事务是非常关键的，因为它们...
MySQL之锁和事务
12-14
MySQL数据库在处理并发和保证数据安全性方面，使用了锁机制和事务管理，以确保数据的一致性和完整性。这里我们将深入探讨锁的分类、事务的特性以及多版本并发控制（MVCC）。 一、锁分类 1. **表级锁**：这种锁对...
MySQL锁与事务脑图.pdf
07-26
MySQL锁与事务知识脑图
mysql性能指标和压力测试
wdirdo的博客
09-27 992
mysql性能指标和压力测试 mysql性能指标和压力测试工具 数据库服务衡量指标： Qps：query per second Tps：transaction per second 压力测试工具： mysqlslap Sysbench：功能强大 https://github.com/akopytov/sysbench tpcc-mysql MySQL Benchmark Suite M...
MySQL：MySQL复制
wdirdo的博客
09-25 853
MySQL：（十三）MySQL集群其一 （一）关系型数据库基础 （二）MySQL安装 （三）管理数据库和表 （四）用户和权限管理 （五）函数，存储过程和触发器 （六）MySQL架构 （七）存储引擎 （八）MySQL服务器选项，系统和状态变量 （九）优化查询和索引管理 （十）锁和事务管理 （十一）日志管理 （十二）备份还原 （十三）MySQL集群 壹.MySQL复制 为什么要mysql复制 1....
生产中my.cnf配置示例
wdirdo的博客
09-27 641
生产环境 my.cnf 配置示例 1.硬件：内存32G 2.my.cnf innodb_file_per_table = 1 打开独立表空间 max_connections = 8000 #MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值 open_files_limit = 10240 #所有线程所打开表的...
MySQL：（十一）MySQL日志
wdirdo的博客
09-23 560
MySQL：（十一）MySQL日志 （一）关系型数据库基础 （二）MySQL安装 （三）管理数据库和表 （四）用户和权限管理 （五）函数，存储过程和触发器 （六）MySQL架构 （七）存储引擎 （八）MySQL服务器选项，系统和状态变量 （九）优化查询和索引管理 （十）锁和事务管理 （十一）日志管理 （十二）备份还原 （十三）MySQL集群 mysql日志类型 事务日志 transactio...
MySQL数据备份与恢复之mysqldump
wdirdo的博客
09-21 543
MySQL数据备份与恢复之mysqldump mysqldump备份数据库 1.mysqldump备份数据库属于完全备份，属于文本备份，本质是对数据库执行建库建表创建记录。 2.mysqldump参考： https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html mysqldump工具语法 1.mysqldump [OPTIONS] databa...
MySQL（三）管理数据库和表
wdirdo的博客
09-17 491
MySQL（三）管理数据库和表 （一）关系型数据库基础 （二）MySQL安装 （三）管理数据库和表 （四）用户和权限管理 （五）函数，存储过程和触发器 （六）MySQL架构 （七）存储引擎 （八）MySQL服务器选项，系统和状态变量 （九）优化查询和索引管理 （十）锁和事务管理 （十一）日志管理 （十二）备份还原 （十三）MySQL集群 管理数据库和表的常见概念 关系型数据库的常见组件 数...
MySQL：（十三）MySQL高可用
wdirdo的博客
09-27 476
MySQL：（十三）MySQL集群其二 （一）关系型数据库基础 （二）MySQL安装 （三）管理数据库和表 （四）用户和权限管理 （五）函数，存储过程和触发器 （六）MySQL架构 （七）存储引擎 （八）MySQL服务器选项，系统和状态变量 （九）优化查询和索引管理 （十）锁和事务管理 （十一）日志管理 （十二）备份还原 （十三）MySQL集群 MySQL读写分离 读写分离应用 mysql-...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值