【MySQL】锁和事务

目录

事务：

---特性：

---使用流程：

---事务并发带来的问题：

1. 更新丢失（Lost Update）

2. 脏读（Dirty Reads）：

3. 不可重复读（NOn-Repeatable Reads）

4. 幻读（Phantom Reads）

---隔离性级别：

---事务隔离级别解决方案：

锁：

---按照粒度划分：

表锁与行锁的区别：

---行锁（偏写）：

---行锁算法：

记录锁：

间隙锁：

临键锁：

优化建议：

---表锁（偏读）：

---表级锁的两种模式：

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

---按照级别划分：

共享锁：

排他锁：

意向共享锁：Intention Shared Locks

意向排他锁：Intention Exclusive Locks

---

事务：

定义：数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；事务是一组不可再分割的操作集合（工作逻辑单元）

mysql命令行的默认设置下，事务都是自动提交的。即执行 SQL 语句后就会马上执行 COMMIT 操作。即对于独立的每条sql语句，mysql会自动提交或者回滚。

---特性：

• 原子性(Atomicity):都成功，都失败
• 一致性(Consistency):你少100，我增100。是通过原子性来保证的。
• 隔离性(Isolation)：不同事务相互隔离，互不干扰。
• 持续性(Durablity)：事务一旦提交，对数据所做的任何改变都要记录到永久存储器中，就是保存进物理数据库

---使用流程：

开启事务 begin/start transaction   --手工方式

set session autocommit = on/of   --设定事务是否自动开启或者（set autocommit = 0;//将自动提交事务关闭  set autocommit =1;//将自动提交事务开启

）

执行查询

提交事务、回滚事务：commit/rolback

 

show GLOBAL VARIABLES LIKE 'tx_isolation';   //查看事务级别

show VARIABLES like 'autocommit';  // 查看事务是否默认开启

---事务并发带来的问题：

都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

1. 更新丢失（Lost Update）

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新

2. 脏读（Dirty Reads）：

事务A读到事务B已修改但尚未提交的数据，还在这个数据基础上做了操作。此时，事务B事务回滚，A读取的数据无效，不符合一致性要求

3. 不可重复读（NOn-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。

事务A读取到了事务B已经提交的修改数据，不符合隔离性。

4. 幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的数据，这种现象称为“幻读”。

事务A读取到了事务B提交的新增数据，不符合隔离性。

幻读和脏读的区别：

脏读是：事务B里面修改了数据，

幻读是：事务B里面新增了数据。

---隔离性级别：

以上“脏读”、“不可重复读”、“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

show GLOBAL VARIABLES LIKE 'tx_isolation';   //查看事务级别

set session transaction isolation level read uncommitted;   // 设置事务隔离级别

• read uncommitted:读未提交事务内容，显然不符合原子性，会出现“脏读”，在业务中没人这么做。（未解决任何并发问题）
• read committed：在一个事务进行过程中，读不到另一个进行事务的操作，但是，可以读到另一个结束事务的操作影响。（解决脏读）
• repeatable read:可重复读即在一个事务过程中，所有消息都来自事务开始那一瞬间的信息，不受其他已提交事务的影响（解决了不可重复读问题）（大多数的系统，用此隔离级别）
• repeatable read[serializable]：串行化（解决了所有问题），但并发效率低

 

---事务隔离级别解决方案：

 

在InnoDB的事务中，对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据行加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁（通过多版本并发控制实现，同时有效的解决了幻读的问题）

 

锁：

定义：用于管理不同事务对共享资源的并发访问

mysql所得机制比较简单，但是不同的存储引擎支持不同的锁机制。MyISAM存储引擎采用表级锁，Innodb支持行级锁、表级锁但默认采用行级锁。BDB是页面锁。

mysql的锁其实是对索引加锁，如果查询语句中没有索引，则对表加锁。

---按照粒度划分：

表锁与行锁的区别：

Innodb：行锁（主要）、表锁

MyISAM：表锁

---行锁（偏写）：

• 偏向InnoDB存储引擎，开销大，加锁慢；
• 会出现死锁；
• 锁粒度最小，发生锁冲突的概率最低，并发度也最高；
• 支持事务

show STATUS like 'innodb_row_lock%';

 

---行锁算法：

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

 

记录锁：

间隙锁：

间隙锁危害：

因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内的任何数据。某些场景下这可能对性能造成很大的危害。

临键锁：

锁定范围加记录：

 

set autocommit = 0;//将自动提交事务关闭

set autocommit =1;//将自动提交事务开启

 

优化建议：

• 尽可能让所有数据检测都通过索引来完成，避免无索引行升级为表锁
• 合理设计索引，尽量缩小锁的范围。
• 尽可能较少检索条件，避免间隙锁。
• 尽量控制事务大小，减少锁定资源量和时间长度。
• 尽可能低级别事务隔离

---表锁（偏读）：

特点：

• 偏读；
• 偏向MyISAM存储引擎，开销小，加锁快；
• 无死锁；
• 锁粒度大，发生锁冲突的概率高，并发度最低。
• 不支持事务

show open tables;// 查看哪些表加锁情况

show status like 'table%';

lock tables 表名 read/write; // 加锁

unlock tables 表名； // 解锁

---表级锁的两种模式：

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。

对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

表共享读锁（Table Read Lock）

session1	session2
获得表user的READ锁定 lock table user read;	连接终端
当前session可以查询该表记录	其他session也可以查询该表记录
当前session不能查询其他没有锁定的表	其他session可以查询或者更新未锁定的表
当前session插入或者更新锁定的表都会提示错误	其他session插入或者更新锁定表会一直等待获得锁
释放锁 unlock tables；	session2获得锁，插入操作完成

表独占写锁（Table Write Lock）

session1	session2
获得表user的READ锁定 lock table user write;	连接终端
当前session可以查询、更新、插入该锁定的表	其他session对锁定的表的查询被阻塞，需要等待锁被释放；
当前session不能查询其他没有锁定的表	其他session可以查询或者更新未锁定的表
释放锁 unlock tables；	session2获得锁，插入操作完成

---按照级别划分：

共享锁：

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

加锁释锁方式：

select * from student wheree id = 1 LOCK IN SHARE MODE;

commit/rollback;

排他锁：

又称写锁，X锁。不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对数据进行读取和修改。

加锁释锁方式：

自动：delete/update/insert 默认加上X锁；

手动：select * from student where id =1 FOR UPDATE;

commit/rollback;

意向共享锁：Intention Shared Locks

意向锁室友数据引擎自己维护的，用户无法手动操作意向锁

IS锁：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁：Intention Exclusive Locks

意向锁室友数据引擎自己维护的，用户无法手动操作意向锁

IX锁：表示事务准备给数据加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

 

其他：

1.为什么表上没有索引或者查询未命中索引时，会锁定整张表？

一张表一定会有主键索引的，如果没有说动添加，会自动添加一个隐藏的聚集索引。所以会扫描全表

2.为什么通过唯一索引锁住一行数据，通过主键索引页不能加锁？

索引的存储结构有关，辅助索引也会去查找主键的ID