MySQL 锁机制

引擎MyISAM和innoDB对比：     

对比项	MYISAM	INNODB的比较
主外键	不支持	支持
持事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发操作	行锁，操作只锁某一行，不对其他行产生影响，适合高并发
表空间	小	大
关注点	性能	事务

一、MySQL 锁机制：     

解释：

读锁(共享锁):针对同一份数据，多个读取操作可以同时进行而不会相互影响
写锁(排它锁)：当前写操作没有完成之前，它会阻断其他写锁和读锁。
InnoDB与MyISAM的最大不同有两点：一是支持事务，而是采用了行级锁

mysql> lock table student read;    -- 锁表操作 lock table 表名 read/write; 可以多表加锁,分隔
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables;	-- 查看表状态，有没有被锁
+--------------------+----------------------------------------------+--------+-------------+
| Database           | Table                                        | In_use | Name_locked |
+--------------------+----------------------------------------------+--------+-------------+
| mysql              | time_zone_transition_type                    |      0 |           0 |
| demo               | course                                       |      0 |           0 |
| mysql              | tables_priv                                  |      0 |           0 |
| mysql              | procs_priv                                   |      0 |           0 |
| mysql              | proc                                         |      0 |           0 |
| mysql              | proxies_priv                                 |      0 |           0 |
| demo               | student                                      |      1 |           0 |
+--------------------+----------------------------------------------+--------+-------------+
37 rows in set (0.00 sec)

mysql> unlock tables;  -- 解锁所有表
Query OK, 0 rows affected (0.00 sec)

二、myISAM表锁 ：案例分析

表锁：
1、对MyISAM表的读操作(加读锁) ,不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁
释放后,才会执行其它进程的写操作。
2、对MyISAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的
读写操
============================================================================================
开两个session，模拟场景
对mylock表加读锁（在session1加），两个session都能读
	在session1尝试进行写操作(update)
	session1 更新失败
	session2在执行update mylock表的操作后在阻塞、等待
-----------------------------------------------------	
在session1 执行lock table mylock write;
	session1给mylock表加锁之后，session1 的mylock表能读能写
	但是session1仍然不能读其它的表。
	session2 能读其它的表，mylock表的查询，结果是阻塞。读mylock是阻塞，update操作也是一样的。
	
如果是写锁，任何读数据、修改数据都不行。
如果是读锁，谁都可以读操作，但是修改了一个表就会让后面的写就会阻塞状态

①简而言之,就是读锁会阻塞写,但是不会堵塞读。而写锁则会把读和写都堵塞 。
②写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

mysql> show status like 'table%';  -- 分析表锁定
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 86    |
| Table_locks_waited    | 0     |  --  等待次数，每等待一次加一
+-----------------------+-------+
2 rows in set (0.00 sec)

三、行锁 ：InnoDB 默认支持行锁，语句走索引时是行级索，不然是表级索。

行锁：innoDB引擎数据表，在多个连接操作同一行时出现阻塞状态，多个连接操作的行不同就不会导致阻塞。

============================================================================================
建两个索引
	create INDEX test_innodb_a_ind on test_innodb_lock(a);
	create INDEX test_innodb_b_ind on test_innodb_lock(b);
要演示，先把auto commit关了
	set autocommit=0;
	连接1 update一行记录(但并不会自动提交)
	UPDATE test_innodb_lock SET b='4001' WHERE a=4;
	连接2可以立刻查出update了的内容，但是另外一个连接2并没有读取到update后的内容
	原因是：连接1没有提交。
	commit;两个连接都把auto commit关了后
	如果两个session update同一行，会造成阻塞。

多个连接update不同的行，不会有冲突
============================================================================================
分析行锁状态：
	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:系统启动到现在总共等待的次数；
	
	对于这5个状态变量，比较重要的是：
	Innodb_row_lock_waits、Innodb_row_lock_time、Innodb_row_lock_time_avg
尤其是当等待次数很高，而且每次等待时长也很大的时候，我们就要分析系统中为什么有这么多的等待，然后根据分析结果来制定优化。

行锁不一定比表锁好，表级索直接在表的头部加锁，行级索还需要扫描到该行对其上锁，带来了更大的开销。

四、索引失效导致行锁变表锁：由于发生了类型转换导致了行锁表表锁

五、间隙锁
当更新一个范围数据的时候，出现中间没有的键值，然而在更新操作未提交的情况下，其他链接进行更新操作就会发生阻塞现象，从而导致性能下降（因为该行被锁定了）

5、如何手动锁定一行
开启了手动锁定一行，其他链接修改改行数据就会处于阻塞状态，一旦commit，阻塞状态就执行了。

六、乐观锁实现

乐观锁：根据比较更新机制，如下是根据版本号实现的，每次更新都会版本号加一，不同版本号将会更新失败

CREATE TABLE `user_info` (
  `id` varchar(32) NOT NULL,
  `username` varchar(32) DEFAULT '',
  `password` varchar(32) DEFAULT '',
  `openid` varchar(64) DEFAULT '' COMMENT '微信openid',
  `role` tinyint(1) NOT NULL COMMENT '1买家2卖家',
  `version` int(3) NOT NULL DEFAULT 0，
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 先查询出duiying 版本号
select version from user_info where id=1;
-- 每次更新数据时(version版本也加一)，为了防止冲突，先检查version在做更新
update user_info set username='xiao',version = 1+1 where id=1 and version =1;