MySQL锁机制

悠悠 2024-03-27 18:59 4阅读 0赞

### **一、概述** ###

#### **1，锁的定义** ####

*  锁是计算机协调多个进程或线程并发访问某一资源的机制
 *  在数据库中，除传统的计算机资源(如CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资源
 *  如何保证数据并发访问的一致性、有效性是所有数据库必须解決的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

#### **2，锁的分类** ####

##### a）数据操作的类型 #####

*  读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响
 *  写锁(排它锁)：当前写操作没有完成前，它会阻断其他写锁和读锁

##### b）数据操作的颗粒度 #####

*  行锁
 *  表锁
 *  页锁

### **二、表锁** ###

#### **1，特点** ####

偏向MyISAM存储引擎，开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。

#### **2，案例分析** ####

#加锁方式
    lock table 表名1 read(write),表名2 read(write);
    #查看表上加过的锁 0表示未上锁
    show open tables;
    #释放锁
    unlock tables;

##### a)建表 #####

create table mylock (
        id int not null primary key auto_increment,
        name varchar(20) default ''
    ) 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;

##### b)加读锁 #####

*  在session 1中加锁

#给mylock表加读锁
    lock table mylock read;
    #查询mylock表  可以展示所有数据
    select * from mylock;
    #在当前会话中不能读取别的表:Table 'tbl_emp' was not locked with LOCK TABLES
    select * from tbl_emp;

*  在session 2中读取和修改

#可以查询
    select * from mylock;
    #修改mylock表会出先等待，直到mylock表释放锁
    update mylock set name='aaa' where id = 1;

*  结论：当前session为当前表加写读锁

1.  当前 session只能读取当前表，不能更新当前表。并且不能操作其他表的读和写
2.  其他 session只能读取当前表，不能更新当前表(阻塞)。可以操作其他表的读和写。

##### c)加写锁 #####

*  在session 1中添加写锁

#给mylock表加写锁
    lock table mylock write;
    #查询mylock表  可以展示所有数据
    select * from mylock;
    #在当前会话中不能读取别的表:Table 'tbl_emp' was not locked with LOCK TABLES
    select * from tbl_emp;

*  在session 2中读取和更新

#在session 2中无法读取数据和更新数据，一直阻塞；直到session中unlock tables解锁
    select * from mylock;

*  结论：当前session为当前表加写锁

1.  当前session只能操作当前表的读和写，不能操作其他表的读和写
2.  其他session不能操作当前表的读和写(阻塞)，可以操作其他表。

##### d)总结 #####

*  MyIsam在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
 *  对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
 *  对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
 *  简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

### **三、行锁** ###

#### **1，特点** ####

*  偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁(间隙锁)；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
 *  InnoDB与MyISam的最大不同有两个：1.支持事务；2.采用行级锁。

#### **2，事务** ####

##### a）事务（Transation）及其ACID #####

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

1.  原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
2.  一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
3.  隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
4.  持久性（Durability）：事务院成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

##### b）事务隔离级别 #####

MySQL查看事务隔离级别：show variables like 'tx\_isolation';

*  脏读：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。
 *  不可重复读：事务A读取到了事务B已经提交的修改数据，不符合隔离性
 *  幻读：第一个事务对一定范围的数据进行批量修改，第二个事务在这个范围内增加一条数据，这时候第一个事务就会丢失对新新增数据的修改

<table> 
 <tbody> 
 <tr> 
 <td> 隔离级别 </td> 
 <td> 隔离级别的值 </td> 
 <td> 导致的问题 </td> 
 <td> 用例 </td> 
 </tr> 
 <tr> 
 <td> Read-Uncommitted(读未提交) </td> 
 <td> 0 </td> 
 <td> 有脏读 </td> 
 <td></td> 
 </tr> 
 <tr> 
 <td> Read-committed(读提交) </td> 
 <td> 1 </td> 
 <td> 无脏读,允许不可重复读和幻读 </td> 
 <td> SqlServer、Oracle </td> 
 </tr> 
 <tr> 
 <td> Repeatable-read(重复读) </td> 
 <td> 2 </td> 
 <td> 无脏读和不可重复读,允许幻读 </td> 
 <td> Mysql InnoDB </td> 
 </tr> 
 <tr> 
 <td> Serializable&nbsp;(序列化) </td> 
 <td> 3 </td> 
 <td> 都可以避免,执行效率慢,慎用 </td> 
 <td></td> 
 </tr> 
 </tbody> 
</table>

#### **3，行锁案例** ####

##### a）创建表 #####

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(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(9,'9000');
    INSERT INTO test_innodb_lock VALUES(1,'b1');
    
    CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
    CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);

##### b）操作同一行数据 #####

操作同一行数据时：当上一个事务修改未提交时，第二个事务也去修改会处于阻塞。

![图片][96ebb875d243919a53457a648c7f2116.png]

##### c）操作不同行数据 #####

操作不同行时：即使上一个事务修改未提交，第二个事务也能修改，互不影响。

![图片][b2a5e81e3f896e01a17cf93734c17ab7.png]

##### d）索引失效，表锁 #####

修改 test\_innodb\_lock 中的数据，varchar 不用 ’ ’ ，导致系统自动转换类型，导致索引失效，会出现表锁。

![图片][525e7eaef98e2e87ebb7ec5a3b5a898f.png]

#### **4，间隙锁** ####

##### a)介绍 #####

*  当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”
 *  InnoDB也会对这个“间隙”加锁，这种锁机制是所谓的间隙锁（Next-Key锁）

##### b)示例 #####

![图片][cc2f5ed8c54003fe6251c69e219ef3c3.png]

#### **4，锁定某一行** ####

select xxx ... for update 锁定某一行后，其它的操作会被阻塞，直到锁定行的会话提交commit。

![图片][4168199d0ef499289198804694cbd508.png]

#### **5，总结** ####

*  Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。
 *  当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
 *  但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候（索引失效，导致行锁变表锁），可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

#### **6，分析** ####

show status like 'innodb_row_lock%';

![图片][9b25d81bcfc3d42c59a614a736f0f78a.png]

*  Innodb\_row\_lock\_current\_waits：当前正在等待锁定的数量；
 *  Innodb\_row\_lock\_time：从系统启动到现在锁定总时间长度；
 *  Innodb\_row\_lock\_time\_avg：每次等待所花平均时间；
 *  Innodb\_row\_lock\_time\_max：从系统启动到现在等待最常的一次所花的时间；
 *  Innodb\_row\_lock\_waits：系统启动后到现在总共等待的次数；

#### **7，行锁优化** ####

*  尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
 *  合理设计索引，尽量缩小锁的范围
 *  尽可能减少检索条件，避免间隙锁
 *  尽量控制事务大小，减少锁定资源量和时间长度
 *  尽可能低级别事务隔离

### **四、页锁** ###

*  开销和加锁时间界于表锁和行锁之间：会出现死锁；
 *  锁定粒度界于表锁和行锁之间，并发度一般。

[96ebb875d243919a53457a648c7f2116.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/9093ca92917d4785a37296b22b24f351.png
[b2a5e81e3f896e01a17cf93734c17ab7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/dc2b5440eccb464783f5fd21fe3d9058.png
[525e7eaef98e2e87ebb7ec5a3b5a898f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/f4d0015be3024a818ed7518da1647aaf.png
[cc2f5ed8c54003fe6251c69e219ef3c3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/6fda275963314b689cc4af71b46bebc2.png
[4168199d0ef499289198804694cbd508.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/01e5447b134847a79345cd08f7b09ef6.png
[9b25d81bcfc3d42c59a614a736f0f78a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/4734e0d26a1640b3aae5d136600c63bb.png