【数据库】聊聊MySQL事务隔离级别与锁机制

概述

针对事务来说，其实主要解决的就是数据的一致性，对于任何的存储中间件来说，都会存在并发访问数据的问题，编程语言层面 juc、go等机制 使用编程上的方式，加锁、无锁编程等。而数据库也存在多个连接访问修改同一个数据，那么是如何通过事务来保证数据的安全问题的？带着这个问题，我们来深入了解下。
其实本质上就是数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制机制。一整套的机制解决多事务并发问题。ACID只是一个理论模型。

事务

ACID特性

这个就不多说，分别是原子性、一致性、隔离性、持久性。
推荐阅读 -【分布式理论】聊一下 ACID、BASE、CAP、FLP

并发事务带来的问题

更新丢失、脏读、不可重复读、幻读。

隔离级别

常看当前数据库的事务隔离级别: show variables like 'tx_isolation';
设置事务隔离级别:set tx_isolation='REPEATABLE-READ'; Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔 离级别，如果Spring设置了就用已经设置的隔离级别

锁

锁是计算机协调多线程、进程访问同一共享资源的机制，可以保证数据安全。但是锁冲突也是影响数据库并发性能的一个因素。

锁分类

• 性能：乐观锁、悲观锁
• 数据库操作：读锁、写锁
• 操作粒度：表锁、行锁

表锁

lock table 表名称 read(write),表名称2 read(write); // 手动增加表锁
show open tables; // 查看表上加过的锁
unlock tables;  // 删除表锁

【数据库之美】表锁演示

1、对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当 读锁释放后,才会执行其它进程的写操作。
2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进 程的读写操作

说白了就是，读读不互斥，读写互斥，写写互斥。表锁锁的粒度大，并发度低，锁冲突概率高，一般针对的是表数据迁移的场景

行锁

行锁，开销大，加锁慢，容易出现死锁，并发度高。MyIASM不支持行锁，InnoDB支持行锁。以及事务。

隔离级别演示

【数据库之美】脏读演示以及解决方案
【数据库之美】不可重复读演示及解决方案
【数据库之美】幻读演示及解决方案
死锁演示

间隙锁

间隙锁其实就是锁的两个值之间的间隙，MySQL默认是可重复读，间隙锁在某些情况下可以解决幻读问题。

那么锁的间隙(3,5),(5,10),(10,+∞)。这三个间隙。

如果一个session锁住一个区间，那么其他session没有办法在这个范围包含的所有行记录(包括间隙行记录)以及行记录所在的间隙插入或修改任何数据。

间隙锁是在可重复读隔离级别下才生效

临键锁 （next-key Locks）

Next-Key Locks是行锁与间隙锁的组合。（3，10] 整个区间叫做临键锁。

无索引的行锁会升级为表锁
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

lock in share mode(共享锁) 和for update 

会话1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update tuser set name = 'qxlxiiii' where name = 'qxlxii';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> 

会话2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update tuser set name = 'qxlxiiii' where name = 'qxlxiiii'; // 这里阻塞

锁分析以及优化

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;

 ‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
 kill trx_mysql_thread_id
 ‐‐ 查看锁等待详细信息
 show engine innodb status\G;

Innodb_row_lock_current_waits: 当前正在等待锁定的数量 Innodb_row_lock_time: 从系统启动到现在锁定总时间长度 Innodb_row_lock_time_avg: 每次等待所花平均时间 Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数

锁优化建议
尽可能所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，缩小锁的范围。
尽可能减少检索条件范围，避免间隙锁。
控制事务大小，减少锁定资源量和时间长度，设计事务加锁的SQL放在最后执行。

留一个问题，在可重复读的情况下，是如何解决幻读问题。答案就是通过MVCC。这个我们下一篇文章讲解。