点击蓝色“潭时录”关注我丫
每天和小潭一起快乐的学习~
你好，我是在学mysql的小潭。在上一期09-mysql高级篇-Show Profile分析工具推文中我们学习了show profile分析工具的使用方法。今天，我们将开始对数据库的锁机制进行认识和学习。
一、是什么
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源(如CPU，RAM，I/O等)的争用之外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性和有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，锁对数据库而言既重要又复杂。
二、实际应用
数据库锁的应用，一个很典型的例子就是网上购物。例如我们在线上商城购买一件商品，且该商品当前仅剩一件库存，而此时其他人对此商品也有购买需求。如何解决该商品是由谁购买到，自然会用到事务，宽泛来讲也就是我们先从库存表中取出商品数量，然后插入订单，付款后插入信息到付款表，最后更新商品数量。在这个过程中，锁的使用可以对有限的资源进行保护，从而解决隔离和并发的矛盾。
三、锁的分类
数据库锁对数据的操作，从操作的类型和操作的粒度上可以分为两个层面，具体分类和特点如下。
| 数据操作类型层面 | 读锁(共享锁) | 针对同一份数据，多个读操作可以同时进行而不会互相影响 |
| 写锁(排他锁) | 当前写操作没有完成前，它会阻塞其他写锁和读锁 | |
| 数据操作粒度层面 | 表锁(偏读) | 偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低 |
| 行锁(偏写) | 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高 | |
| 页锁【了解】 | 开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间；并发度一般 |
InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)，二是采用了行级锁。
从数据操作粒度层面来讲，需要考虑开销、加锁速度、死锁、粒度和并发性能等因素，只能根据具体应用的特点来决定哪种锁更合适。
概念回顾：
| 事务(Transaction)及其ACID属性 | 事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常称为事务的ACID属性： 原子性(Atomicity)：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全部不执行 一致性(Consistent)：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性，事务结束时，所有的内部数据结构(如B树索引或双向链表)也都必须是正确的 隔离性(Isolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然 持久性(Durable)：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能保持 | |
| 并发事务处理带来的问题 | 更新丢失(Lost Update) A和B同时修改一组数据，但都是独立更改其副本，然后保存更改后的副本以覆盖原始版本，此时后一个人更改的副本会覆盖前一个人所作的更改 | 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生更新丢失问题，最后的更新覆盖了由其他事务所做的更新 |
脏读(Dirty Reads) 事务A读取到了事务B已修改但尚未提交的数据，还在这个数据基础上做了操作，此时如果事务B回滚，A读取的数据无效，不符合一致性要求 | 一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态，这时另一个事务也来读取这条记录，如果不加控制，第二个事务读取了这些脏数据，并据此做进一步处理，就会产生未提交的数据依赖关系，这种现象被称为脏读 | |
不可重复读(Non-Repeatable Reads) 事务A读取到事务B已经提交的修改数据，不符合隔离性 | 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变，或某些记录已经被删除，这种现象被称为不可重复读 | |
幻读(Phantom Reads) 事务A读取到事务B提交的新增数据，不符合隔离性 | 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象被称为幻读 | |
事务隔离级别 show variables like 'tx_isolation'; (sql8) show variables like 'transaction_isolation'; | 未提交读(Read uncommitted)：最低级别，只能保证不读取物理上损坏的数据 [存在脏读，不可重复度和幻读] 已提交读(Read committed)：语句级 [存在不可重复读和幻读] 可重复读(Repeatable read)：事务级 [存在幻读] 可序列化(Serializable)：最高级别，事务级 |
四、锁的案例
接下来我们结合实例来具体认识下不同锁的作用及其影响。
表锁案例：
1.建表SQL
# 建表create table mylock(id int not null primary key auto_increment,name varchar(20))engine myisam;# 插入数据insert into mylock(name) values('a');insert into mylock(name) values('b');insert into mylock(name) values('c');insert into mylock(name) values('d');insert into mylock(name) values('e');select * from mylock;
2.手动增加/释放表锁和查看表锁状况
# 添加表锁lock table 表名字 read|write, 表名字2 read|write, ...;# 释放锁unlock tables;# 查看锁的状况show open tables;
3.读锁
为mylock表加read锁(读阻塞写) | |
| session_1 | session_2 |
获得表mylock的READ锁 myslq> lock table mylock read;
| 连接终端 |
当前session可以查询该表记录 myslq> select * from mylock;
| 其他session也可以查询该表的记录 myslq> select * from mylock;
|
当前session不能查询其他没有锁定的表记录 myslq> select * from book;
| 其他session可以查询或更新其他未锁定的表记录 myslq> select * from book;
|
当前session不能更新该被锁定表的记录或插入新记录到该被锁定表 myslq> update mylock set name='a2' where id=1;
| 其他session更新该表或向该被锁定表插入记录时产生阻塞，会一直等待获得锁 myslq> update mylock set name='a3' where id=2;
|
当前session释放锁 myslq> unlock tables;
| session2获得锁，插入或更新操作完成(更新操作耗时19.00 sec)
|
4.写锁
| 为mylock表加write锁(写阻塞写) | |
| session_1 | session_2 |
获得表mylock的WRITE锁 myslq> lock table mylock write;
| 待session_1开启写锁后，session_2再连接终端 |
当前session对该被锁定表的查询/更新/插入操作都可以正常执行 myslq> select * from mylock;
myslq> update mylock set name='a3' where id=3;
| 其他session对该被锁定表的查询被阻塞，需要等待锁被释放
如果可以查询，请更换成不同的id来进行测试，因为mysql存在缓存，第二次的结果会从缓存中获取，从而影响锁的效果 |
当前session释放锁 myslq> unlock tables;
| session2获得锁，查询操作完成(查询操作耗时8.62 sec)
|
当前session加写锁时，不能查询其他没有锁定的表记录 myslq> select * from book;
| 其他session可以查询或更新其他未锁定的表记录 myslq> select * from book;
|
5.案例结论
MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：
表共享读锁(Table Read Lock)
表独占写锁(Table Write Lock)
| 锁类型 | 是否兼容 | 读锁 | 写锁 |
| 读锁 | 是 | 是 | 否 |
| 写锁 | 是 | 否 | 否 |
对于MyISAM表进行操作，会有以下情况：
对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。
对MyISAM表的写操作(加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。
即读锁会阻塞写，但是不会阻塞读。而写锁会把读和写都阻塞。
6.表锁分析
了解表被锁的情况，将更有助于我们更好的优化sql。我们可以通过以下命令查看哪些表被加锁。
show open tables;
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。
show status like 'table%';
这里有两个状态变量记录MySQL内部表级锁定的情况，变量的说明如下：
Table_locks_immediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。
Table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在严重的表级锁争用情况。
此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做以写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
行锁案例
1.建表SQL
create table test_innodb_lock(a int(11),b varchar(16))engine=innodb;insert into test_innodb_lock values(1, 'b3');insert into test_innodb_lock values(2, 'b2');insert into test_innodb_lock values(3, '3');insert into test_innodb_lock values(4, '4000');insert into test_innodb_lock values(5, '5000');insert into test_innodb_lock values(6, '6000');insert into test_innodb_lock values(7, '7000');insert into test_innodb_lock values(8, '8000');insert into test_innodb_lock values(10, '0100');create index test_innodb_a_ind on test_innodb_lock(a);create index test_innodb_lock_b_ind on test_innodb_lock(b);select * from test_innodb_lock;
2.行锁定基本演示
| 行锁定案例 | |
| Session_1 | Session_2 |
关闭自动提交 myslq> set autocommit=0;
| 关闭自动提交 myslq> set autocommit=0;
|
session_1更新但不提交，没有手写commit; myslq> update test_innodb_lock set b = 'b1' where a = 1;
myslq> select * from test_innodb_lock;
| session_2查询仍为原值(可重复读) myslq> select * from test_innodb_lock;
更新操作被阻塞，只能等待 myslq> update test_innodb_lock set b = 'b4' where a = 1;
|
session_1提交更新 myslq> commit;
| session_2解除阻塞，更新正常进行(更新操作耗时20.30 sec) myslq> update test_innodb_lock set b = 'b4' where a = 1;
|
session_2执行commit命令 myslq> commit;
| |
session_1更新a=3(与session2互不影响) myslq> update test_innodb_lock set b = 'test3' where a = 3;
myslq> select * from test_innodb_lock;
myslq> commit;
| session_2更新a=10(与session1互不影响) myslq> update test_innodb_lock set b = 'test10' where a = 10;
myslq> select * from test_innodb_lock;
myslq> commit;
|
3.无索引行锁升级为表锁
已知建立过的索引：
show index from test_innodb_lock;
| 索引失效导致行锁变为表锁 | |
| Session_1 | Session_2 |
关闭自动提交 myslq> set autocommit=0;
| 关闭自动提交 myslq> set autocommit=0;
|
索引正常情况下，session_1更新b='5000' myslq> update test_innodb_lock set a = 55 where b = '5000';
myslq> commit;
myslq> select * from test_innodb_lock;
| 索引正常情况下，session_2更新b='6000' myslq> update test_innodb_lock set a = 66 where b = '6000';
myslq> commit;
myslq> select * from test_innodb_lock;
|
索引发生类型转换(字段b的varchar型不加引号，误写为int型)，导致索引失效，升级为表锁 myslq> update test_innodb_lock set a = 77 where b = 7000;
| 索引正常情况下，session_2更新b='8000'，产生阻塞 myslq> update test_innodb_lock set a = 88 where b = '8000';
(若session_1一直不提交更新，session_2将提示锁等待超时，ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction) |
session_1提交更新 myslq> commit;
| session_2解除阻塞，更新正常进行(更新操作耗时5.47 sec)
|
4.间隙锁危害
间隙和间隙锁概念：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，被称作'间隙(GAP)'。InnoDB也会对这个间隙加锁，这种锁机制被称为间隙锁(Next-Key锁)。
间隙锁危害：因为Query执行过程中通过对范围的查找，会锁定整个范围内所有的索引键值，即使这个键值不存在。
间隙锁存在一个比较致命的弱点，也就是当锁定一个范围键值后，即使某些不存在的键值也会被无辜锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
| 间隙锁案例 | |
| Session_1 | Session_2 |
关闭自动提交 myslq> set autocommit=0;
| 关闭自动提交 myslq> set autocommit=0;
|
session_1按范围执行更新操作 myslq> update test_innodb_lock set b = 'test122' where a>7 and a<11;
| session_2此时插入一条a=9的记录，会发生阻塞 myslq> insert into test_innodb_lock values(9, '9000');
|
session_1提交更新 myslq> commit;
| session_2解除阻塞，插入操作正常进行(插入操作耗时16.17 sec) myslq> insert into test_innodb_lock values(9, '9000');
|
session_2执行commit命令 myslq> commit;
|
5.如何锁定一行
锁定某一行(显示加锁) select xxx for update锁定某一行后，其他的操作会被阻塞，直到锁定行的会话提交commit | |
| Session_1 | Session_2 |
myslq> begin; myslq> select * from test_innodb_lock where a=8 for update;
| myslq> update test_innodb_lock set b = 'test88' where a = 8;
|
session_1提交更新 myslq> commit;
| session_2解除阻塞，更新操作正常进行(更新操作耗时24.48 sec) myslq> update test_innodb_lock set b = 'test88' where a = 8;
|
6.案例结论
InnoDB存储引擎实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定更高一些，但在整体并发处理能力上要远胜于MyISAM的表级锁定。当系统并发量较高时，InnoDB的整体性能和MyISAM相比会有很明显的优势。但是InnoDB的行级锁定同样存在脆弱的一面，当使用不当时可能会使InnoDB的整体性能表现不及MyISAM，甚至可能会更差。
7.行锁分析
对于行锁定可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁争夺情况。
show status like 'innodb_row_lock%';
对各个状态量的说明如下：
Innodb_row_lock_current_waits: 当前正在等待锁定的数量。
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度。【重要】
Innodb_row_lock_time_avg: 每次等待所花的平均时间。【重要】
Innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花的时间。
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数。【重要】
尤其是当等待次数较高，且每次等待时间较长时，就需要分析其中的等待原因，并着手指定优化计划。
五、优化建议
针对以上对锁的认识和学习，共有以下建议供优化sql时做参考：
尽可能让所有数据检索都通过索引来完成，避免无索引时的行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能使用较小范围的检索条件，避免间隙锁的产生
尽量控制事务大小，减少锁定资源量和时间长度
尽可能使用低级别事务隔离
下期预告：MySQL的主从复制
