MySQL——锁和事务管理
2023-02-18 16:34:37 时间
MySQL的锁 Lock
保证数据安全的一种手段。防止数据文件同时被多个用户同时修改，从而造成数据的破坏。
MySQL锁的分类：
-
读锁：S锁，例如给一个表加了读锁以后，大家都能读表里面的内容（只能读不能改）
-
写锁:X锁。加了写锁以后，只有自己才能访问，其他人不行（读和写都不行）
读锁和写锁是不兼容了，加了写锁就不能加读锁。
MySQL锁的范围：
-
表级锁:myisam吗，整个表全部都加锁，例如改其中一条记录会把整个表都锁起来。
-
行级锁：innodb，只锁定自己正在修改的那条记录。
实现锁这个功能：
-
显式实现：手动设置
-
隐式实现：通过存储引擎自动添加
手动实现锁的功能
加锁：
lock tables tb_name lock_type
lock_type:
READ #读锁
WRITE #写锁
解锁：
unlock lock_name
查询锁：
select claue
加全局读锁：
关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁来保证数据的只读性。
添加全局读锁：
FLUSH TABLES WITH READ LOCK
解锁：
unlock tables
MySQL事务 Transactions：一组原子性的SQL语句
事务的特性：ACID
-
A：atomicity 原子性；事务中的所有操作要么全部成功执行，要么全部失败后回滚
-
C：consistency 一致性；数据库总是从一个一致性状态转换为另一个一致性状态。
-
I：Isolation隔离性；事务执行操作的期间相互之间是隔离的，互不影响。
事务在执行期间产生的数据叫做脏数据（没有确定下来的数据） -
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
事务的生命周期
-
刚开始数据库处于初始阶段
-
开启一个事务
-
进行相关操作（事务没有结束前都属于脏数据）
-
提交或者取消事务
-
一旦提交以后数据就存到了数据库，取消事务就回到了初始状态。
MySQL事务的管理：
- 启动事务：
三个都表示事务的开始
BEGIN
BEGIN WORK
START TRANSACTION
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from teachers where tid = 5 ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
- 结束事务：
只用DML这种SQL语言才能撤销。其他的不能撤销
#提交,相当于vi中的wq保存退出
COMMIT
#回滚,相当于vi中的q!不保存退出
ROLLBACK
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql> delete from teachers;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from teachers;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
- 自动提交功能：更改变量autocommit的值
set autocommit={1|0}，默认是1
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
MySQL每一条独立的DML语句就是一个完整的事务。执行操作后就默认自动提交了。
- 查看事务：
死锁的概念：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
事务的隔离级别：
隔离级别查看：
select @@tx_isolation;
这个变量支持四个值：
READ UNCOMMITTED：能看到脏数据
READ COMMITTED：不可重复度
REPEATABLE READ：幻读
SERIALIZABLE：可串行化
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
隔离级别比较
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 加读锁 |
|---|---|---|---|---|
| 读未提交 | 可以出现 | 可以出现 | 可以出现 | 否 |
| 读提交 | 不允许出现 | 可以出现 | 可以出现 | 否 |
| 可重复读 | 不允许出现 | 不允许出现 | 可以出现 | 否 |
| 序列化 | 不允许出现 | 不允许出现 | 不允许出现 | 是 |
例如：可重复读，一个事务将数据改变以后，即使提交了，另一个事务里面也不能看到事务发生的变化，只能看到刚开启事务时数据的状态。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into teachers(name,age,gender) values('bob',24,'M');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
这个事务在执行过程中，永远看到的是自己刚开启事务时数据的状态。
但是这个数据早就发生改变了，这就是可重复读（幻读）。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
例如：不可重复读：只要数据发生了改变（已经提交），就能看到数据发生的改变
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> update teachers set age=25 where tid=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 25 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 25 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 30 | M |
| 6 | bob | 24 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
相关文章
- 面试让我手写红黑树？！
- 金3银4面试前，把自己弄成卷王！
- 面试这么撩准拿offer，HashMap深度学习，扰动函数、负载因子、扩容拆分，原理和实践验证，让懂了就是真的懂！
- 面经手册 · 第13篇《除了JDK、CGLIB，还有3种类代理方式？面试又卡住！》
- 使用开源计算引擎提升Excel格式文件处理效率
- 流式结构化数据计算语言的进化与新选择
- tauri+vue开发小巧的跨OS桌面应用-股票体检
- 面霸告诉你这些技术面试的非技术性经验，让你的面试成功率显著提升
- 基于SpringBoot AOP面向切面编程实现Redis分布式锁
- 时序数据库为万物互联打下坚实的数仓基石
- 银丰新融：搭建名单监控管理系统，落实“三反”政策
- 养殖场新来了个“AI管家”
- Open Harmony移植：build lite配置目录全梳理
- 什么是隐私计算，它是怎样保护我们的隐私安全？
- 动手实操丨RC522射频卡模块与IC卡完成充值消费查询的技术实现思路
- 云图说 | 华为云医疗智能体EIHealth，AI赋能基因组研究
- What？构造的查询语句会导致堆栈溢出
- 开发一个不需要重写成Hive QL的大数据SQL引擎
- npm install xxxx --legacy-peer-deps命令是什么？
- 聊聊如何在华为云IoT平台进行产品开发