MySQL/InnoDB锁解析

作者: 莫问以 | 来源:发表于2018-10-29 16:07 被阅读1次

MySQL/InnoDB锁解析
淘宝MySQL文档整理
Innodb的锁
Mysql
Mysql存储引擎Innodb小结
MySQL面试题 | 附答案解析（十）
InnoDB介绍
MySQL innodb锁
MySQL InnoDB 锁
MySql Innodb 锁

今天听同事说起MySql锁的问题，感觉很是神奇，以前只听过死锁，结果他们在那吧啦吧啦地说出一大堆锁，什么乐观锁、悲观锁，表示很是疑惑，特此补充学习下。
基本SQL语句：

DROP TABLE IF EXISTS `t_letou`;
CREATE TABLE `t_letou` (
  `le_qihao` varchar(10) NOT NULL COMMENT '期号',
  `hong_one` varchar(10) NOT NULL COMMENT '红球1',
  `hong_two` varchar(10) NOT NULL COMMENT '红球2',
  `hong_three` varchar(10) NOT NULL COMMENT '红球3',
  `hong_four` varchar(10) NOT NULL COMMENT '红球4',
  `hong_five` varchar(10) NOT NULL COMMENT '红球5',
  `lan_one` varchar(10) NOT NULL COMMENT '蓝球1',
  `lan_two` varchar(10) NOT NULL COMMENT '蓝球2',
  PRIMARY KEY (`le_qihao`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_letou` VALUES ('18122', '08', '09', '21', '30', '31', '05', '12');

我们都知道，多线程访问某统一数据时，一般都要加锁，数据库中，同样有锁机制。那么，什么是锁呢？
锁可以简单理解为保证数据并发访问的一致性、有效性的关键机制，就比如是保证钱不轻易被偷走的安全柜锁。
本文主讲针对基于InnoDB存储引擎的MySQL，查看是什么引擎，可用如下命令：

SHOW ENGINES

1）乐观锁
乐观锁其实不上锁，只是用数据版本（Version）记录机制实现，一般通过 “version” 字段来实现。当读取数据时，将version的值一同读出，数据每更新一次，就对version值加1。当Update时候，判断当前版本信息与第一次取出来的version值进行比对，值相等认为一致，则予以更新，否则认为是过期数据，不给予更新。

假设有表，有三字段：id,value、version
select id,value,version from TABLE where id=#{id}

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};

2）悲观锁
可以这么理解，悲观锁就是天生悲观，认为别人每次拿数据的时候都会修改数据，所以在每次拿的时候都给数据上锁。其他线程想要拿数据，就会阻塞，直到给数据上锁的线程将事务提交或者回滚。

说到悲观锁，就要先理解——共享锁与排它锁。共享锁和排它锁是悲观锁的不同实现，都属于悲观锁。
要使用悲观锁，必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式：

set autocommit=0;

# 取消自动提交后，就可以执行正常业务了，具体如下：

1. 开始事务
begin;/begin work;/start transaction; (三者选一就可以)

2. 查询表信息
select status from TABLE where id=1 for update;

3. 插入一条数据
insert into TABLE (id,value) values (2,2);

 4. 修改数据为
update TABLE set value=2 where id=1;

 5. 提交事务
commit;/commit work;

3）共享锁
一个线程给数据加上共享锁后，其他线程只能读，不能改。

先加一个共享锁
begin;/begin work;/start transaction;  (三者选一就可以)

SELECT * from t_letou where le_qihao='18122'  lock in share mode;

另一个窗口执行UPDATE语句：（另一线程）
UPDATE t_letou SET hong_one="01" where le_qihao='18122'

一执行，好了，卡顿，过了设置超时时间，提示错误信息

在查询语句后面增加** LOCK IN SHARE MODE**，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。
PS:加上共享锁后，对于update,insert,delete语句会自动加排它锁。

4）排它锁
排他锁又称为写锁，和共享锁的区别在于，其他线程既不能读也不能改。

5）行锁
行锁只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

6）表锁
表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MyISAM 与 InnoDB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

7）页锁
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。

8）死锁（Deadlock）
所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。可以理解为拔河时两边持平，都拿不到挂在中间的香蕉。
解除死锁状态方法有：

第一种：
1.查询是否锁表
show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist

3.杀死进程id（就是上面命令的id列）
kill id

第二种：
1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

4.杀死进程
kill 线程ID

产生死锁的四个必要条件：
1）互斥条件：一个资源每次只能被一个进程使用。
2）请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
3）不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
4）循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。
将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

下列方法有助于最大限度地降低死锁：
1）按同一顺序访问对象。
2）避免事务中的用户交互。
3）保持事务简短并在一个批处理中。
4）使用低隔离级别。
5）使用绑定连接。