首页 > 数据库 >mysql的锁机制-MyISAM表锁

mysql的锁机制-MyISAM表锁

时间:2022-11-17 10:02:14浏览次数:47  
标签:mylock 查询 session MyISAM mysql Table 表锁 id

1、MySQL锁的基本介绍

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

相对其他数据库而言,MySQL的锁机制比较简单,其最 显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。 ​ 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度 来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有 并发查询的应用,如一些在线事务处理(OLTP)系统。

2、MyISAM表锁

MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)表独占写锁(Table Write Lock)

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

建表语句:

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

MyISAM写锁阻塞读的案例:

当一个线程获得对一个表的写锁之后,只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待,直到锁释放为止。

session1session2
获取表的write锁定 lock table mylock write;  
当前session对表的查询,插入,更新操作都可以执行 select * from mylock; insert into mylock values(5,'e'); 当前session对表的查询会被阻塞 select * from mylock;
释放锁: unlock tables; 当前session能够立刻执行,并返回对应结果

 

MyISAM读阻塞写的案例:

一个session使用lock table给表加读锁,这个session可以锁定表中的记录,但更新和访问其他表都会提示错误,同时,另一个session可以查询表中的记录,但更新就会出现锁等待。

session1session2
获得表的read锁定 lock table mylock read;  
当前session可以查询该表记录: select * from mylock; 当前session可以查询该表记录: select * from mylock;
当前session不能查询没有锁定的表 select * from person Table 'person' was not locked with LOCK TABLES 当前session可以查询或者更新未锁定的表 select * from mylock insert into person values(1,'zhangsan');
当前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');
释放锁 unlock tables; 获得锁,更新成功

注意:

MyISAM在执行查询语句之前,会自动给涉及的所有表加读锁,在执行更新操作前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此用户一般不需要使用命令来显式加锁,上例中的加锁时为了演示效果。

MyISAM的并发插入问题

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

session1session2
获取表的read local锁定 lock table mylock read local  
当前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
当前session不能查询没有锁定的表 select * from person Table 'person' was not locked with LOCK TABLES 其他session可以进行插入操作,但是更新会阻塞 update mylock set name = 'aa' where id = 1;
当前session不能访问其他session插入的记录;  
释放锁资源:unlock tables 当前session获取锁,更新操作完成
当前session可以查看其他session插入的记录  

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺:

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 352   |
| Table_locks_waited    | 2     |
+-----------------------+-------+
--如果Table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况。

 

 

 

 

标签:mylock,查询,session,MyISAM,mysql,Table,表锁,id
From: https://www.cnblogs.com/qxh-beijing2016/p/16898428.html

相关文章

  • MySQL8.0.26-Linux版安装
    MySQL8.0.26-Linux版安装1.准备一台Linux服务器云服务器或者虚拟机都可以;Linux的版本为CentOS7;2.下载Linux版MySQL安装包https://downloads.mysql.com/archives/......
  • MySQL 创建存储过程,使用 while 批量插入数据
    1、创建带字段的表student(id,name,age)#id整型主建不能为空自动增长#name字符型长度50不能为空#age整型不能为空默认值18mysql>createtablestudent(->i......
  • MySQL Binlog 的配置
    binlog简介binlog是一个二进制格式的文件,用于记录用户对数据库增量操作的SQL语句信息,例如更改数据库表和更改内容的SQL语句都会记录到binlog里,但是对库表等内容的查询不会记......
  • MySQL 关闭slave主重
    1、停止从服务 stopslave;2、检查slave_open_temp_tables变量的值 showstatuslike'%slave%';mysql>showstatuslike'%slave%';+------------------------+-------+......
  • MySQL高级篇-第2章MySQL的数据目录
    第02章MySQL的数据目录1.MySQL8的主要目录结构find/-namemysql安装好MySQL8之后,我们查看如下的目录结构:这里是执行命令后的结果1.1数据库文件的存放路径MySQ......
  • Docker-compose 安装 Nginx PHP MySQL
    1、创建mkdir-p/docker/wwwmkdir-p/docker/nginx/conf.d/2、配置vimnginx/conf.d/default.confserver{listen80;server_namelocalhost;location/{......
  • MySQL动态SQL
    if单独使用的时候需要注意where需要带一条永真的语句如“1=1”,或者有其他不被if包含的语句,否则就可能出现“whereand titlelike#{title}”,只有一条语句,然后还被and连......
  • MySQL 索引最左前缀原则失效?
    测试索引最左前缀原则,发现缺失带头索引后,索引还是生效的。一、测试创建测试表CREATETABLE`user`(`id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'主键',......
  • MySQL索引和SQL调优手册
    MySQL索引MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。为了避免混乱,本文将只关注于BTre......
  • mysql学习(9):联合索引和多个单列索引的使用区别详解
    前言为了提高数据库的查询效率,建索引是最常用的手段。那么问题来了,如果查询条件为2个及以上,我们是创建多个单列索引好呢,还是建一个联合索引比较好?正文我们首先来建一张表,......