博客

MySQL高级—B站学习总结—MySQL锁机制

什么是锁：

1. 锁是计算机协调多个进程或线程并发访问某一资源的机制。
2. 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的例子：

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？

这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类：

1. 从对数据操作的类型（读\写）分
   1. 
读锁(共享锁)：

      针对同一份数据，多个读操作可以同时进行而不会互相影响。
   2. 
写锁（排它锁）：

      当前写操作没有完成前，它会阻断其他写锁和读锁。
2. 从对数据操作的粒度分
   
(为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。一种提高共享资源并发发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。)

   1. 表锁
   2. 行锁

表锁(偏读)

1. 特点

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

2. 案例分析——–>表级锁分析

建表语句：

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' );

手动增加表锁

LOCK TABLE 表名字 1 READ ( WRITE )，表名字 2 READ ( WRITE )，其它;

查看哪些表被加锁了

SHOW OPEN TABLES;

查看哪些表被加锁了

SHOW OPEN TABLES WHERE in_use >0;

查询指定数据库指定表是否被加锁

SHOW OPEN TABLES FROM db2020 WHERE `table` = '表名';

增加锁的sql语句：

给mylock表上一个读锁，book表上一个写锁

Lock table mylock read，book write

执行

SHOW OPEN TABLES;

查看哪个表有锁，In_use > 0就表示有锁

3. 案例分析——–>加读锁

我们为mylock表加read锁(读阻塞写例子) 简述：

1. session_1和session_2获得表mylock的READ锁定连接终端当前session可以查询该表记录 ，其他session也可以查询该表的记录
2. 当前session不能查询其它没有锁定的表 其他session可以查询或者更新未锁定的表
3. 当前session中插入或者更新锁定的表都会提示错误： 其他session插入或者更新锁定表会一直等待获得锁： 释放锁 Session2获得锁，插入操作完成：

4. 案例分析——–>加写锁

5. 案例总结

1. MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
2. MySQL的表级锁有两种模式：
   1. 表共享读锁（Table Read Lock）
   2. 表独占写锁（Table Write Lock）
      
      

结论：

结合上表，所以对MyISAM表进行操作，会有以下情况：

1. 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2. 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
   
   
简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞


6. 表锁分析

通过SQL

SHOW STATUS LIKE 'table%'

检查

Table_locks_immediate

和

Table_locks_waited

状态变量来分析系统上的表锁定



通过

Table_locks_immediate

和

Table_locks_waited

状态变量记录MySQL内部表级锁定情况，变量如下：

1. Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
2. Table_locks_waited：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在较严重表级锁的争用情况
   
   
此外Myisam的读写锁调度是写优先，这也是Myisam不适合做 写为主的 表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久堵塞，这也Myisam要让他偏读，不让他偏写的原因