MYSQL锁机制

1、准备

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');
insert into mylock(name) values ('f');

select * from mylock;

2、表锁（记得打开两个会话）

• 手动增加表锁

lock table 表名字 read/write，表名字2 read/write，其他
mysql> lock table mylock read,book write;
Query OK, 0 rows affected (0.00 sec)

• 查看表上加过的锁,观察in_use列的变化

show open tables;

• 手动解锁

unlock tables;

试试两个表的查询以及更新，这里记得在两个会话里进行操作
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GfENsGmx-1570525452233)(https://i.vgy.me/LqfxLo.png)]
可以看到读锁可以共享，写锁不可共享一直阻塞

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

解锁后，立马往下执行。。。。

• 接下来试试更新操作?记得把刚才解锁后，再次加上锁

session1：
mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)
mysql> update mylock set name='a2' where id=1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
|  6 | f    |
+----+------+
6 rows in set

mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
|      1 |   10 |

可以看到加锁会话只能操作加锁的表，而且根据锁的类型只能操作一部分权限，而另一个会话则不受影响，可以操作其他表，但是被另一个会话锁住的表是不能操作的
加了写锁的表，当前会话可以查询和更新，另一个会话则既不能查询也不能更新
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4xZDPG61-1570525452234)(https://i.vgy.me/l6lAUd.png)]

3、表锁分析

• 如何分析表锁定
  可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定：

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 59    |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 8     |
| Table_open_cache_misses    | 9     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
table_locks_waited:出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1）。
此外，myisam的读写锁调度是写优先，这也是myisam不适合做写为主的引擎，因为写锁后其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

4、行锁

偏向InnoDB引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高，innodb与myisam的不同就是支持事务以及行级锁。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GXEnBiYl-1570525452235)(https://i.vgy.me/irMSWr.png)]

4.1 案例

create table test_innodb_lock(
	a int(11),
	b varchar(16)
) ENGINE=innodb;
insert into test_innodb_lock values (1,'b2');
insert into test_innodb_lock values (2,'3');
insert into test_innodb_lock values (3,'4000');
insert into test_innodb_lock values (4,'5000');
insert into test_innodb_lock values (5,'6000');
insert into test_innodb_lock values (6,'7000');
insert into test_innodb_lock values (7,'8000');
insert into test_innodb_lock values (8,'9000');
insert into test_innodb_lock values (9,'1245');
insert into test_innodb_lock values (1,'b1');
create index idx_a on test_innodb_lock(a);
create index idx_b on test_innodb_lock(b);
select * from test_innodb_lock;

会话1：

mysql> set auto_commit=0;
ERROR 1193 (HY000): Unknown system variable 'auto_commit'
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='4001' where a=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from test_innodb_lock where a=3;
+------+------+
| a    | b    |
+------+------+
|    3 | 4001 |
+------+------+
1 row in set (0.00 sec)

会话2：

mysql> set autocommit=0;
Query OK, 0 rows affected

mysql> select * from test_innodb_lock where a=3;
+---+------+
| a | b    |
+---+------+
| 3 | 4000 |
+---+------+
1 row in set
mysql> update test_innodb_lock set b='4002'
 where a=3;

可以看到，在innodb行锁级别下，会话1更新了a=3的记录，自己立马可以看到，但是会话2不仅看不到更新后的值（隔离级别），而且当更新a=3的时候会一直锁住，这就是行锁，如果此时会话1一直不提交释放锁，那么会话2将会一直阻塞。但是如果不是操作同一条记录，则不会阻塞

4.1 索引失效导致行锁变表锁

会话1(强制类型转换会导致索引失效）:

mysql> update test_innodb_lock set a=45 where a=5000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

会话2:

mysql> update test_innodb_lock set a=91 where b='1245'
 ;

本来会话2不会被锁，因为不同行，但是由于索引失效导致行锁变表锁，所以会话2将会一直阻塞，直到会话1commit

4.2 间隙锁危害

会话1，没有提交：

mysql> delete from test_innodb_lock where a=2;
Query OK, 1 row affected (0.00 sec)

mysql> update test_innodb_lock set b='gap006' where a>1 and a<6;
Query OK, 3 rows affected (13.50 sec)
Rows matched: 3  Changed: 3  Warnings: 0

会话2：

mysql> insert into test_innodb_lock(a,b) values (2,'2000');
1205 - Lock wait timeout exceeded; try restarting transaction

可以看到，本来2并不是行锁，但是也会被阻塞这就是因为间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享排它锁（比如行锁）时，InnoDB会给符合条件的已有数据记录的索引项添加锁；对于键值在条件返回内但不存在的记录，叫做"间隙（GAP）"。
Innodb也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）

【危害】

因为Query执行过程中通过返回查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定而造成在锁定的时候无法插入锁定键值范围内的任何数据，在某些场景下这可能会对性能造成很大的危害

4.3 手动锁定某行数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rmnpEgzV-1570525452235)(https://i.vgy.me/KllQhN.png)]

4.4 行锁分析

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 129827 |
| Innodb_row_lock_time_avg      | 32456  |
| Innodb_row_lock_time_max      | 51473  |
| Innodb_row_lock_waits         | 4      |
+-------------------------------+--------+
5 rows in set (0.00 sec)

innodb_row_lock_current_watis:当前正在等待锁定的数量
innodb_row_lock_time:从系统启动到现在锁定总时间长度
innodb_row_lock_time_avg:每次等待所花费平均时间
innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花费的时间
innodb_row_lock_waits:从系统启动到现在总的等待的次数

4.5 总结

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来d额性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远由于myisam的表级锁定的。当系统并发量较高的时候，innodb的整体性能和myisam相比就会有比较明显的优势了。
但是innodb的行级锁定也有不足的一面，使用不当时，可能性能会更差。