mysql数据库锁需要显示配置么 mysql数据库锁机制

转载

漫步云端的猪 2023-10-22 23:07:04

文章标签 mysql数据库锁需要显示配置么请简述mysql数据库的锁机制读锁共享锁数据 文章分类 MySQL 数据库

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据库中，除传统的计算资源的争用以外，数据也是一种供许多用户共享的资源，保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素

锁类型分类

按操作类型分

读锁 (共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁 (排它锁)：当前写操作没有完成前，它会阻断其他绘画进行写锁和读锁

按操作粒度分

介绍

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情，涉及获取、检查和释放锁等动作，因此数据库系统需要在高并发响应和系统性能两方面进行平衡，产生了锁粒度 (Lock Granularity) 的概念

分类

表锁

行锁

表锁

特点

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

基本操作

增加表锁，读锁或写锁

lock table tablename read/write;

查看表加过的锁

show open tables;

释放表锁

unlock tables;

小结

MyISAM 在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁

锁类型

本会话其他表操作

其他会话可读

其他会话可写

读锁

否

是

否

写锁

否

对 MyISAM 表加读锁，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求，只有当该读锁释放后，其它进程的写操作才会执行

对 MyISAM 表加写锁，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，其它进程的读写操作才会执行

行锁

特点

偏向 InnoDB 存储引擎，开销大、加锁慢、会出现死锁、锁定粒度最小，但发生锁冲突的概率最低，并发度也最高

InnoDB 与 MyISAM 的主要的两点不同是，InnoDB 支持事务，并且采用了行锁

开启事务操作时，默认使用的是行锁，直到 commit; 提交，释放锁资源

当索引字段使用不当时，会导致数据操作从行锁变为表锁，大幅拖低效率

共享锁和排他锁

增加共享锁

示例

begin;

select * from tablename where field = 1 lock in share mode;

直到 commit; 提交后才会释放锁

共享锁又称读锁，是读取操作创建的锁，其他会话可以并发读取数据，但任何事务都不能对数据进行修改，被阻塞直到释放共享锁

如果事务 T 对数据 A 加上共享锁后，则其他事务只能对 A 再加共享锁，不能加排他锁，获得共享锁资源的事务只能读数据，不能更新数据

增加排他锁

示例

begin;

select * from tablename where field = 1 for update;

直到 commit; 提交后才会释放锁

排他锁又称写锁，如果事务 T 对数据 A 加上排他锁后，则其他事务不能再对 A 增加任何类型的锁，获得排他锁的事务既能读数据，也能更新数据

间隙锁

介绍

当使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙 (Gap)， InnoDB 也会对这个间隙加锁，这种锁机制就是间隙锁 (Gap Lock)

危害

因为查询执行过程中通过范围查找会锁定整个范围内所有的索引键值，即使这个键值并不存在，造成在锁定的时候无法插入锁定键值范围内的任何数据，在某些场景下这可能会对性能造成很大的危害

行锁分析

检查命令

通过检查 innodb_row_lock 状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+

状态量说明

状态量

说明

Innodb_row_lock_current_waits

当前正在等待锁定的数量

Innodb_row_lock_time

从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg

每次等待所花平均时间

Innodb_row_lock_time_max

从系统启动到现在等待最久一次等待所花的时间

Innodb_row_lock_waits