mysql学习笔记--基础篇

最新推荐文章于 2024-11-27 17:31:37 发布

原创最新推荐文章于 2024-11-27 17:31:37 发布 · 260 阅读

0 ·

CC 4.0 BY-SA版权

本文深入探讨MySQL的事务处理机制、不同的隔离级别及其影响，包括脏读、不可重复读和幻读等问题的解决方案。同时介绍了MySQL的逻辑架构、存储引擎特性及InnoDB如何利用MVCC解决并发控制。

mysql逻辑架构（三层

最上层为客户端负责进行链接，认证等

第二层负责解析优化（解析树）缓存分析以及所有的内置函数（日期时间数学和加密）

第三层为存储引擎数据存储和提取只是相应上层服务器请求

每个客户端链接会在服务器进程中拥有一个线程这个链接的查询只会在这个线程中执行

服务器缓存线程不需要为每个新建的连接创建或销毁进程

隔离级别

未提交读（读取到为提交数据如若回滚出现脏读

提交读（执行两次查询可能因为其他事务的提交而导致两次读取的数据不一致不可重复读

可重复读（解决了上面两个问题执行两次查询可能因为其他事务提交的插入导致读取行数不一样（幻行）InnoDB 依靠MVCC解决

（mysql默认级别

可串行化

（针对数据库）

脏读：读未提交-》回滚（写时不加任和锁）

不可重复读：读-》修改-》读（锁定只在游标位于该行上时保留）

幻行：读-》插入-》读（锁定所有检索用到的行）（1000行里读10行 10锁）

可串行化：锁定该事物引用的所有行（1000行里读10行 1000全锁）

死锁 InooDB之类的引擎有死锁检测将持有最少行级锁的事务回滚（返回-1)

show variables like 'autocommit';

不显示的开启一个事务 mysql每个查询都会被当作一个事务执行提交（可修改查看设置 autocommit

set autocommit=

你可以用下列语句查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

隔离级别

tx_isolation | REPEATABLE-READ （默认）

修改当前会话隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

tx_isolation | READ-COMMITTED

退出再次登入之后就还是 tx_isolation | REPEATABLE-READ （默认）

SET[SESSION | GLOBAL]TRANSACTIONISOLATIONLEVEL {READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE}

默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。

如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。

锁定

锁分为读锁（共享锁）和写锁（排他锁）

共享锁(S锁)--读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

和写锁（排他锁）若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。

锁定分为隐式和显式

引擎（InnoDB）会根据隔离级别在需要的时候自动加锁

也支持通过特定语句显示锁定

select.... lock in share mode

锁只有在commit 和 roll back时回滚

InnoDB还进行显示的锁定非常的不好不推荐使用

如今的事务型存储引擎实现的都不是简单的行级锁而是实现MVCC

通过快照实现但是不同的数据库实现机制不同没有一个统一标准

InnoDB实现MVCC是通过每行记录后面保存两个列实现的（创建时间和过期（删除）时间）

每开始一个事务系统版本号都会递增事务开始时刻的系统版本号作为事务的版本号

select

只查找版本号早于当前事务的行

行的删除版本未定义或大于当前事务版本

insert 新插入一行版本号为当前系统版本号

delete 当前系统版本号为删除标识

update 新插入一行版本号为当前系统版本号当前系统版本号为原来的行的删除标识

分引擎描述

show table status like 'user' \G

得到表信息 (\G) 设置显示格式

*************************** 1. row ***************************
Name: user
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 33
Avg_row_length: 496
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 50
Create_time: 2015-06-15 18:27:01
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)