小白复习笔记(第三天)-------------------------MySQL锁、事务和并发控制
MySQL事务及特性
数据库事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。事务的使用是数据库管理系统区别文件系统的重要特征之一
事务的重要特性
(1)原子性：事务开始后所有的操作，要么全部做完，要么全部不做，不可能停滞在中间环节
(2)一致性：事务将数据库从一种状态转变为另一种一致的状态(例如 身份证带有唯一属性，如果经过一个修改身份证的事务后，身份证号变的非唯一了，则表明一致性遭到了破坏)
(3)隔离性：要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务不可见(列如 用户修改自己的个人信息的同事，是不能看到系统管理员也在更新该用户的个人信息 PS：此时更新事务还未提交)
PS：MySQL通过锁机制来保证事务的隔离性
(4)持久性：事务一旦提交，则其结果就是永久性的。即使发生宕机的故障，数据库也能将数据恢复，也就是说事务完成后，事务对数据库的索引更新将被保存到数据库，不能回滚。
PS：MySQL使用redo log 来保证事务的持久性
事务的隔离级别
READ UNCOMMITTED(读未提交)
该隔离级别的事务会读到其他未提交事务的数据，此现象也称之为脏读
场景：事务B在事务A做了数据更新操作未提交的时候，获取了事务A的更新前的数据
READ COMMITTED(读提交,不可重复读)
一个事物可以读取另一个已提交的事务，多次读取会造成不一样的结果，此现象称为不可重复度读，幻读
场景：事务B在事务A未提交的时候读取一次，事务A提交后事务B又读取了一次
PS：Oracle和SQL Server的默认隔离级别，MySQL不是
REPEATABLE READ(可重复度)
该隔离级别是MySQL的默认隔离级别，在同一个事务里，select的结果是事务开始时间点的状态，因此同样的select操作读到的结果会一直的，但是会有幻读现象。InnoDB可以通过next-key locks机制来避免幻读
场景：事务A，事务B开启事务，事务A添加数据并提交事务，事务B查不到事务A添加的数据，需要事务B提交事务后在查询才可以
SERIALIZABLE(序列化)
改隔离级别下事务都是串行顺序执行的，MySQL数据库的InnoDB引擎会给读操作隐式加一把读共享锁，从而避免脏读、不可重复读、重复度问题。
场景：事务A添加数据，事务B查询数据，事务B需要等事务A提交事务才能查询到数据，否则一直等待
MySQL的并发控制(悲观锁、乐观锁)
乐观锁
用数据版本(version)记录机制实现,乐观锁最常见的一种实现方式
取出记录时获取当前version，更新时带上version执行更新，如果version不对更新失败
update table set name = 'aa',version = version+1 where id = #{id} and version = #{version}
悲观锁(共享锁、排它锁)
共享锁
共享锁也称为读锁，读锁允许多个连接可以同一个时刻并发的°取同一资源，互不干扰，但任何事物都不能对数据进行修改(获取数据上的排它锁),知道已释放所以共享锁。如果事物对读锁进行了修改操作，很可能会造成死锁
第一个查询窗口
select * from table where id = 1 lock in share mode; (lock in share mode 共享锁)
另一个查询窗口对id为1的数据进行更新
update table set name='aa' where id = 1;
这时候操作界面进入卡顿状态，过了很久超时，提示错误信息，如果在超时前第一个窗口commit，此更新语句就会成功
PS：在查询语句加了共享锁，mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排它锁时，可以成功申请共享锁，否侧会被阻塞。加上共享锁后，对于update，insert，delete语句会自动加排它锁
排它锁
排它锁也成为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时预防其他用户对这个数据的读写
select * from table where id = 1 for update;(for update)
PS:InnoDB会自动给设计数据集加排他锁，对于普通SELCT语句，InnoDB不会加任何锁；当然我们也可以查询加锁
意向共享锁
事务即将给表中的各行设置共享锁，事务给数据行加S锁前必须获得该表的IS锁
意向排他锁
事务即将给表中的各个行设置排他锁，事务给数据行加X锁必须获得该表的IX锁
.Mysql/InnoDB不同锁等级的区别(查看数据库拥有的存储引擎类型： SHOW ENGINES)
行级锁
(1)行锁的劣势：开销大，加锁慢；会出现死锁。
(2)行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发能力强
(3)加锁的方式：自动加锁。InnoDB会自动给设计数据集加排他锁，对于普通SELCT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁
PS：InnoDB的行锁是针对索引加的锁，不针对记录加锁。并且该索引不能失效，否则都会升级会表锁
行锁-间隙锁
当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在范围内但不存在的记录，叫做'间隙(GAP)',InnoDB也会对这个‘间隙’加锁，这种锁机制就是所谓的间隙锁(Next-key锁)
间隙锁阻塞案例
select * from table where id = 1 for update; (窗口1)
inset into table(id,...) values(1010,...); (窗口2)
窗口1加锁查询 这时候窗口2插入id为1010的记录(注意这条记录并不存在),也会出现锁等待，需要等窗口1回退后释放间隙锁，窗口2才能获得锁并成功插入
PS:若执行的条件是范围过大，则InnoDB会将整个范围内所有的索引键值会员锁定，很容易对性能造成影响
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
如果一条SQL语句用不到索引或者索引失效就会使用表级锁
页面锁：开销和加锁时间界于表锁于行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般(不常用)
死锁
两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象
产生死锁的必要条件
(1)互斥条件：一个资源每次只能被一个进程使用
(2)请求与保持条件：一个进程因请求次元而阻塞时，对已获得的资源保持不放
(3)不剥夺条件：进程已获得的资源，在未使用完之前，不能强行剥夺
(4)循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系
解决正在死锁的状态方法
第一种方法：
(1)查询是否锁表 show OPEN TABLES where In_use > 0;
(2)查询进程(拥有SUPER权限可以看到所以线程，否则只能看见自己的线程) show processlist
(3)杀死进程id (show processlist 会显示id列) kill id
第二种方法：
(1)查看当前的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
(2)查看当前锁定的事务 SELECT * FROM INFORMATION_SWCHEMA.INNODB_LOCKS
(3)查看当前等锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
(4)杀死进程 kill 进程ID
有助于最大限度降低死锁的方法
(1)按同一顺序访问对象
(2)避免事务中的用户交互
(3)保持事务简短并在一个批处理中
(4)使用低隔离级别
(5)使用绑定连接