MySQL高级学习之表锁

MySQL高级学习之表锁

参考文章:
MySQL高级知识(十三)——表锁

1.概述

锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。

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

2.分类

从对数据操作的粒度分 :

1) 表锁:操作时,会锁定整个表。

2) 行锁:操作时,会锁定当前操作行。

从对数据操作的类型分:

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

2) 写锁(排它锁):当前操作没有完成之前,它会阻断其他写锁和读锁。

3.表锁

MyISAM 存储引擎只支持表锁,这也是MySQL开始几个版本中唯一支持的锁类型。

3.1如何加表锁

MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT 等)前,会

自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显式加锁语法:

加读锁 : lock table table_name read;

加写锁 : lock table table_name write;

3.2查看表是否被加锁以及释放锁

# 查看表是否被加锁
show open tables;
# 释放锁
unlock tables;

MySQL高级学习之表锁

3.2读锁案例

CREATE TABLE `tb_book` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  `publish_time` DATE DEFAULT NULL,
  `status` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,‘java编程思想‘,‘2088-08-01‘,‘1‘);
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,‘solr编程思想‘,‘2088-08-08‘,‘0‘);



CREATE TABLE `tb_user` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,‘令狐冲‘);
INSERT INTO tb_user (id, name) VALUES(NULL,‘田伯光‘);

会话A:

1)给tb_book加读锁

lock table tb_book read;

MySQL高级学习之表锁

2)执行查询操作

MySQL高级学习之表锁

可以正常查询,获取数据

会话B:

3)执行查询操作

select * from tb_book;

MySQL高级学习之表锁

会话A:

4)查询未锁定的表tb_user

select * from tb_user;

MySQL高级学习之表锁

无法查询。。。

会话B:

5)查询未锁定的表tb_user

select * from tb_user;

MySQL高级学习之表锁

可以正常查询

会话A:

6)执行插入操作

insert into tb_book values(null,‘Mysql高级‘,‘2088-01-01‘,‘1‘);

MySQL高级学习之表锁

执行插入报错,因为当前tb_book获得的是读锁,不能执行更新操作

会话B:

7)执行插入操作

insert into tb_book values(null,‘Mysql高级‘,‘2088-01-01‘,‘1‘);

MySQL高级学习之表锁

阻塞,当在会话A中释放锁后,会话B中的insert语句立即执行。

3.3.写锁案例

会话A:

1)获得tb_book的写锁

lock table tb_book write ;

2)执行查询操作

select * from tb_book;

MySQL高级学习之表锁

查询操作执行成功

3)执行更新操作

update tb_book set name = ‘java编程思想(第二版)‘ where id = 1;

MySQL高级学习之表锁

更新成功

会话B:

4)执行查询操作

select * from tb_book;

MySQL高级学习之表锁

? 阻塞,当在会话A中释放写锁后,会话B中的select语句立即执行。

MySQL高级学习之表锁

3.4结论

对MyISAM 表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;

对MyISAM 表的写操作,则会阻塞其他用户对同一表的读和写操作;

简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁,则既会阻塞读,又会阻塞写。

以上结论都是在MyISAM引擎下得出的。

3.5 表锁定分析

3.5.1 使用如下命令查看是否有表被锁定

show open tables where In_use>0;

3.5.2 使用如下命令分析表锁

show status like ‘table%‘;

MySQL高级学习之表锁

注意两个变量的值:

  • Table_locks_immediate:产生表级锁定的次数,表示可立即获取锁的查询次数,每立即获取锁一次该值加1。

  • Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁该值加1),此值高则说明存在较

    严重的表级锁争用情况。

MySQL高级学习之表锁

上一篇:SQL常用三大语言言


下一篇:Sqoop导入/导出Hive到Mysql中包含自增ID字段/自动写入时间字段