mysql锁机制

最新推荐文章于 2024-10-15 09:42:46 发布
最新推荐文章于 2024-10-15 09:42:46 发布
阅读量295 收藏 1
点赞数 1
分类专栏： mysql mysql锁 mysql存储引擎 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_35619327/article/details/107781197
版权

mysql锁

一、Mysql锁介绍

1. 定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种共享资源，数据库需要保持其高并发下的数据一致性，有效性，所以需要一种锁机制。

mysql中不同的存储引擎支持不同的锁机制，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

2. 分类

表级锁

  • 特点：开销小，加锁快，不会出现死锁，锁粒度大，发生锁冲突的概率高，并发度低。

  • 适用：更适合以查询为主，适合低并发的更新。

行级锁

  • 优点：开销大，加锁慢，可能会出现死锁，锁粒度小，发生锁冲突的概率高，并发度高。

  • 适用：更适合以更新为主，适合高并发下，按照索引并发更新少量不同数据。

3. 存储引擎

MyISAMInnoDB
索引类型非聚族索引聚族索引
支持事物否是
支持表锁是是
支持行锁否是
支持外键否是
支持全文索引是是：5.7以上支持
适合操作类型大量查询大量新增，更新，删除等

二、MyISAM

1. 表锁

  • 表读锁（Table Read Lock）

  • 表写锁（Table Write Lock）

    MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，（锁是自动加，自动释放的）这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁。

    对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

2. 语法

  • 加锁

    # 'mylock' 表名  lock table '表名' write; lock table '表名' read;
lock table 'mylock' write;

  • 解锁

    unlock tables;

  • 读并发插入

    # 'mylock' 表名   lock table '表名' read local;只有读可以加local 
lock table 'mylock' read local;

3. 代码

建表语句：

  CREATE TABLE `mylock` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  
  INSERT INTO `mylock` (`id`, `NAME`) VALUES ('1', 'a');
  INSERT INTO `mylock` (`id`, `NAME`) VALUES ('2', 'b');
  INSERT INTO `mylock` (`id`, `NAME`) VALUES ('3', 'c');
  INSERT INTO `mylock` (`id`, `NAME`) VALUES ('4', 'd');

说明: 例子中显示加锁只是为了演示效果，默认crud,MyISAM会自动加锁。下面的例子同时开启了两个session来模拟多线程并发访问，你自己也可以在cmd开启多个窗口来模拟。

  • 写锁阻塞读锁例子

    session1session2
    1.获取表的write锁定
    lock table mylock write;
    2.当前session对表的查询，插入，更新操作都可以执行
    select * from mylock;
    insert into mylock values(5,‘e’);
    update mylock set name = ‘edg’ where id = 5;    		当前session对表的查询会被阻塞
    select * from mylock；
    3.当前session不能查询没有锁定的表
    select * from person
    Table ‘person’ was not locked with LOCK TABLES    		当前session可以查询或者更新未锁定的表
    select * from mylock
    insert into person values(1,‘zhangsan’);
    4.释放锁：
    unlock tables；    		当前session能够立刻执行，并返回对应结果

    解析：session1获得写锁后，session2，想要获取写锁和读锁都必须等待session1的锁退出。

  • 读锁阻塞写锁例子

    session1session2
    1.获得表的read锁定
    lock table mylock read;
    2.当前session可以查询该表记录：
    select * from mylock;    		当前session可以查询该表记录：
    select * from mylock;
    3.当前session不能查询没有锁定的表
    select * from person
    Table ‘person’ was not locked with LOCK TABLES    		当前session可以查询或者更新未锁定的表
    select * from mylock
    insert into person values(1,‘zhangsan’);
    4.当前session插入或者更新表会提示错误
    insert into mylock values(6,‘f’)
    Table ‘mylock’ was locked with a READ lock and can’t be updated
    update mylock set name=‘aa’ where id = 1;
    Table ‘mylock’ was locked with a READ lock and can’t be updated    		当前session插入数据会等待获得锁
    insert into mylock values(6,‘f’);
    5.释放锁
    unlock tables;    		获得锁，更新成功

    解析：session1获得读锁后，session2，可以也获得读锁（查询结束后马上自动释放），因为读锁是共享的，但是获取不了写锁了，因为写锁是独占的。同时session1获得锁后也不能去操作其他未加锁的表了，如普通表person，也不能对加锁的表进行，更新，删除操作了，相当于mysql不但会校验锁争用，还会检验获取锁线程后的行为，是不是满足锁的定义范围。

  • 读的时候的并发插入例子

    session1session2
    1获取表的read local锁定
    lock table mylock read local
    2当前session不能对表进行更新或者插入操作
    insert into mylock values(6,‘f’)
    Table ‘mylock’ was locked with a READ lock and can’t be updated
    update mylock set name=‘aa’ where id = 1;
    Table ‘mylock’ was locked with a READ lock and can’t be updated    		其他session可以查询该表的记录
    select* from mylock
    3.当前session不能查询没有锁定的表
    select * from person
    Table ‘person’ was not locked with LOCK TABLES    		其他session可以进行插入操作，但是更新会阻塞
    update mylock set name = ‘aa’ where id = 1;
    4.当前session不能访问其他session插入的记录；
    5.释放锁资源：unlock tables当前session获取锁，更新操作完成
    6.当前session可以查看其他session插入的记录

解析：MyISAM表的读和写是串行的，这是就总体而言的，在一定条件下，MyISAM也支持查询和插入操作的并发执行。

session1获得本地读锁后，session2，也可以获得读锁（查询结束后马上自动释放）， 因为读锁是共享的，可以获取写锁了，但是只能用来插入新纪录，mysql会检验其行为。

​

三、InnoDB

1. 事物

1.ACID

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

  • 原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。一条sql默认就是原子操作(事务自动开始，自动提交)

  • 隔离性（Isolation）：事务间并发执行，事务间可能互相会受到影响。可以类比线程, mysql提供了一定的隔离机制来消解这种影响。

  • 持久性（Durable）：事务提交后，对数据的修改就是永久性的。

  • 一致性 （Consistent）: 事务开始和结束时，数据都必须保持一致状态。

2.事务并发问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来以下问题：

  • 脏读：一个事务读到了另一个事务未提交的数据。一个事务在对数据做修改，还未提交，数据处于不一致的状态，另一个事务也读到了这个数据，有可能就是旧数据，并对这个数据进一步的处理，造成了脏读。

  • 不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

  • 幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

    上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

    数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

3.隔离级别
脏读不可重复读幻读
read uncommitted√√√
read committed√√
repeatable read√
serializable

2.锁机制

  • 读锁（共享锁（s））: 又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

  • 写锁（排它锁（x））: 又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

    mysql InnoDB存储引擎默认：update,delete,insert都会自动给涉及到的数据行加上排他锁，select语句默认不会加任何锁类型，显示加锁只能给select语句加锁。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式再去获取锁，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

1. 语法

  • 手动加锁

    # 排他锁（写锁）
select * from table 'tab_no_index' for update;
# 共享锁（读锁）
select * from table 'tab_no_index' lock in share mode;

  • 解锁

    事务提交完自动释放
2. 表锁

创建表

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'23,'3'),(4,'4');
  1. mysql是自动提交事务的，这里为了方便演示，关闭自动提交。在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁。
session1session2
1.禁用自动提交，这样事务没有提交之前，锁就没有释放
set autocommit=0
select * from tab_no_index where id = 1;		set autocommit=0
select * from tab_no_index where id =2
2.id没有加索引所以这里走的表锁
select * from tab_no_index where id = 1 for update
3.处于阻塞状态
select * from tab_no_index where id = 2 for update;
3. 行锁

创建表

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

2、创建带索引的表进行条件查询，innodb使用的是行锁

session1session2
1.set autocommit=0
select * from tab_with_indexwhere id = 1;		set autocommit=0
select * from tab_with_indexwhere id =2
2.加锁
select * from tab_with_index where id = 1 for update
3.select * from tab_with_indexwhere id = 2 for update;
4.commit;
5.1 row in set (…)

3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键值，是会出现冲突的。

insert into tab_with_index  values(1,'4');
session1session2
1.set autocommit=0set autocommit=0
2.select * from tab_with_index where id = 1 and name=‘1’ for update
3.select * from tab_with_index where id = 1and name=‘4’ for update
虽然session2访问的是和session1不同的记录，但是因为使用了相同的索引，所以需要等待锁

四、总结

对于MyISAM的表锁，主要讨论了以下几点：
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

  • 对于InnoDB表，本文主要讨论了以下几项内容：
    （1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
    （2）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

  • 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
  • 选择合理的事务大小，小事务发生锁冲突的几率也更小；
  • 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
  • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
  • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。
但丁jk
关注
专栏目录
mysql 锁机制与原理详解
congge
11-12 8395
mysql 锁原理详解
多线程-MyLock实现
yanghw117的博客
08-01 1210
1、计数器线程问题 import java.util.Random; public class Sequence { private int value; public int getNext() { return value++; } // 测试 public static void main(String[] args) { Sequence seq = new ...
redisson分布式锁
ZhouAn828的博客
09-01 366
基于redisson的分布式锁
基于嵌入式的密码访问的门锁系统
毕业作品网站
09-04 701
在本次实验中，我实现了通过键盘输入密码，若正确开启门锁（电机），若错误给出提示；还可以通过连续敲击特殊键，输入正确密码和新密码实现密码的更改。为了达到实验目标，在本次实验中我编写了两个程序：主程序 mylock 以及监控程序 lock_guard，接下来分开介绍两个程序的设计方案。使用了双线程，其中主线程负责接受输入以及进行 hash 校验等，led 线程负责控制 led 输出相应的提示语及结果。本次实验选题为基于密码访问的门锁系统，类似于现代智能家居中的智能门。一、 mylock 程序 1。
MySQL高级篇——锁的概述与案例应用
★★光亭★★
04-24 6224
MySQL高级篇——锁的概述与案例应用
MySQL锁机制
weixin_43573186的博客
04-05 1335
MySQL锁机制 mysql锁的分类： 按照锁粒度分类：行锁、表锁、页锁 按照锁使用方式：共享锁，排他锁 按照锁的思想：悲观锁、乐观锁 行锁类型：Record Lock、Gap Lock、Next-key Lock 锁粒度 1. 行锁 行锁是锁粒度最细的锁，表示只针对当前操作的行进行加锁。行锁有可能会出出现死锁的情况，行锁按照使用方式分为共享锁和排他锁；InnoDB默认使用行锁； 共享锁（S锁，读锁） 当事务A对数据加上了S锁，事务A可以读数据但不能修改数据，其他事务也只能对数据加S锁，不能加
『 MySQL篇 』：MySQL 锁机制介绍
m0_56361048的博客
06-15 8256
MySQL锁机制详解
MySQL锁机制详解
qq_43842093的博客
07-15 3733
会话192.168.11.101（1）”给表locktab加了锁，其他会话的操作：1、可以对其他表（表locktab以外的表）进行读、写操作。2、对表locktab：读操作可以，写操作需要等待释放锁。
MySQL 锁机制全面解析
一名后端程序猿
04-18 2444
mysql中常见的锁：全局锁、表锁、行锁、共享锁、排它锁、乐观锁、悲观锁、意向锁、间隙锁、临键锁
MYSQL锁机制
07-26
### MySQL锁机制详解 #### 一、MySQL锁机制概述 MySQL中的锁机制是数据库管理系统用于控制并发事务对数据资源访问的关键技术之一。合理地管理和利用锁机制能够有效地防止数据不一致性和提高系统的并发处理能力。...
MySQL锁机制与用法分析
01-19
本文实例讲述了MySQL锁机制与用法。分享给大家供大家参考，具体如下： MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁；BDB存储引擎采用的是...
Mysql 锁机制的详细说明
08-22
【MySQL 锁机制详解】 MySQL 的锁机制是数据库管理系统中用于协调多个并发事务对数据进行访问的一种机制，确保数据的一致性和完整性。在开发中，虽然我们可能并不总是主动使用锁，但它们在多用户环境下确保数据正确...
mysql 锁机制及mysql中的锁介绍.pdf
10-17
系统介绍mysql锁机制
MySQL中表的约束
pouop的博客
10-13 806
表中一定要有各种约束，通过约束，让我们来插入数据库中的数据是符合预期的。约束本质是通过技术手段，倒逼程序员插入正确的数据；反过来，站在MySQL的角度来单，内部已经插进来的数据，都i是符合数据约束的数据。约束的最终目标：保证数据完整性和可预期性为了完成以上的所有目标，就需要除了数据类型自带的范围约束以外的更多约束。
【MySQL】入门篇—数据库基础：数据库的定义与用途
thinking_chou的专栏
10-10 1456
数据库（Database）是一个有组织的数据集合，通常以电子形式存储在计算机系统中。数据库管理系统（DBMS）是用于创建、管理和操作数据库的软件。数据库可以支持多种数据类型，如文本、数字、图像等，并允许用户通过查询语言（如SQL）对数据进行操作。二、数据库的用途数据库在社会中扮演着至关重要的角色，几乎每个行业都依赖于数据库来管理和分析数据。通过高效的数据存储和管理，企业和组织能够更好地理解客户需求、优化运营流程、提高决策能力，并在竞争中保持优势。
MySQL(B站CodeWithMosh)——2024.10.10(13)
unicorn_lemon的博客
10-10 1383
运算符：LENGTH、UPPER、LOWER、LTRIM、RTRIM、SUBSTRING、LOCATE、REPLACE、CONCAT、NOW、CURDATE、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、DAYNAME、EXTRACT、DATE_FORM、DATE_ADD、INTERVAL、IFNULL、COALESCE、IF
【MySQL】入门篇—SQL基础：SQL语言概述
thinking_chou的专栏
10-12 950
数据定义语言（DDL）用于定义数据库结构，包括创建、修改和删除数据库对象（如表、索引等）。示例：创建用户表表示创建一个名为users的表。定义了一个自增的主键列。定义了一个不允许为空的用户名列。定义了一个不允许为空且唯一的邮箱列。
MySQL之Buffer Pool缓冲池详解
最新发布
XT4625的专栏
10-15 481
Innodb 存储引擎设计了一个缓冲池（，来提高数据库的读写性能。Buffer Pool 以页为单位缓冲数据，可以通过参数调整缓冲池的大小，默认是 128 M。Innodb 通过三种链表来管理缓页：Free List （空闲页链表），管理空闲页；Flush List （脏页链表），管理脏页；LRU List，管理脏页+干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。；
MySQL锁机制深度解析：从理论到实践
本资料详细介绍了MySQL锁机制的各个方面，包括其应用场景、工作原理、锁的类型和模式，以及如何通过参数配置和性能监控来优化数据库的并发性能和数据一致性。对于需要理解和使用MySQL锁机制的开发者来说，这是一份...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值