【Mysql事务及锁机制】

一、ACID事务特性

Atomic： 多个操作像一个操作一样，成功或失败

Consistent： 在数据层面 ，保证全部成功或失败

Isolation：一个事务内的数据不受其他事务的操作所影响 

Durable：事务提交完成后，数据的修改是永久的，即使发生故障也能够保持

二、事务隔离级别

概念：脏读 、重复读、幻读   、当前读（读已提交的最新版本）

REPEATABLE READ隔离级别：不同事务中查询不到其他事务提交的数据行，但update更新时仍然会生效，更新仍然存在幻读现象。

隔离级别	脏读	不可重复读	幻读
读未提交 read uncommited	可能	可能	可能
读已提交 read commited	不可能	可能	可能
可重复读 repeatable read	不可能	不可能	可能
串行化 serializable	不可能	不可能	不可能

当前数据库事务隔离级别查询及设置命令参考：

1.查看当前会话隔离级别

select @@tx_isolation;

2.查看系统当前隔离级别

select @@global.tx_isolation;

3.设置当前会话隔离级别

set session transaction isolatin level repeatable read;

4.设置系统当前隔离级别

set global transaction isolation level repeatable read;

5.命令行，开始事务时

set autocommit=off 或者 start transaction

三、锁机制

• 读锁、写锁（属于悲观锁）

• MVCC多版本并发控制机制（乐观锁）

1. 读锁（共享锁 S锁）：多操作可同时读取同一份数据

2. 写锁（排它锁 X锁）：同一份数据，当一个用户线程正在写时，阻断其他线程对这份数据的读写操作

• 锁的颗粒度：表锁、行锁

1. 行锁： myisam存储引擎没有行锁，innodb支持

   特点：开销大、加锁慢、粒度小、冲突低。

   使用场景：高并发

2. 表锁：innodb、myisam都支持，

   特点：开销小，加锁快，粒度大，冲突高。

   使用场景：并发低，一般用于整表数据迁移的场景   EX：lock table tablename  [read | write];

• 存储引擎锁对比：

1. myisam select 操作会对整张表加读锁、update、insert、delete 操作 对整张表加写锁

2. Innodb  select 操作不会加锁，update、insert、delete操作时会加行锁，mvcc锁行不会阻塞读操作，因为事务上开启了多版本

• 间隙锁（Gap Lock）

概念描述：表数据[1,2,3,5,10,20] , 会有4个间隙锁区间  (3~5)    (5~10)   (10~20)   (20 ~ +∞)

间隙锁加锁原理：加在索引的页节点上并标记范围，这样在插入数据时肯定要插入到页上进行扩展，校验锁时就能判断是否触发间隙锁（个人理解！欢迎指正！）

1. update xx where   id >4 and id<18;   这个sql范围 在前3个区间上都会加锁，称为间隙锁（其中第1个 和 第3个区间为临间锁）

2. 临间锁（Next-key Lock）：对间隙锁重叠的区间加锁，称为临间锁  （EX：where  id>11 and id<26 ; 那么后两个区间都会被加锁）

3. 更新无索引行会升级会表锁，对性能影响非常大

4. 可重复读事务级别，可用间隙锁 在一定程度上解决幻读问题

• 锁问题定位分析

死锁：

事务1 已经获取A行锁，locking(B)

事务2 已经获取B行锁，locking(A)   -- 这样就会相互等待 （Mysql会自动检测简单的死锁，抛出异常终止事务）

死锁日志查看方式： show  engine innodb status\G;

行锁分析：show status like 'innodb_row_lock%'

# 事务及锁状态查询

SELECT * FROM information_schema.INNODB_TRX ;                    -- 事务  

SELECT * FROM information_schema.INNODB_LOCKS;                -- 锁

SELECT * FROM information_schema.INNODB_LOCK_WAITS;      -- 锁等待

kill trx_mysql_thread_id   -- 释放锁，thread_id 可以从INNODB_TRX 表查看

事务超时时间innodb_lock_wait_timeout：默认是50s

查询： SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

设置： SET GLOBAL innodb_lock_wait_timeout=120;

四、锁优化

1. 避免无索引行更新升级为表锁的问题；解决方案：可以查询出来后，通过id循环更新；

2. 合理设计索引，减少锁范围

3. 减少索引条件的范围，避免间隙锁

4. 尽量减小缩短事务，缩短锁时间，涉及加锁的sql尽可能放到事务最后执行

5. 合理使用隔离级别，尽可能使用低级别的事务隔离