【Mysql】数据库锁

淩亂°似流年 2023-03-03 04:58 16阅读 0赞

# 一、数据库锁 #

## 1.1 锁的概念 ##

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

## 1.2 锁的分类 ##

**● 操作类型区分**

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

**● 操作粒度区分**

*  表锁：偏向 MyISAM 存储引擎，开销小，加锁快；无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低
 *  行锁：偏向 InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
 *  页锁

## 1.3 读锁 ##

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

\# 查看表的加锁情况

show open tables;

\# 手动增加表锁

lock table \[表名\] read(write), \[表名2\] read(write)，其他..

示例：

lock tble mylock read, book write;

\# 解锁

unlock tables;

![20200730103011453.png][]

![20200730103050172.png][]

## 1.4 写锁 ##

![20200730104009947.png][]

![20200730104645867.png][]

## 1.5 表锁定分析 ##

![20200730105017876.png][]

![20200730105239217.png][]

## 1.6 行锁 ##

![20200730105638980.png][]

![20200730105759869.png][]

![20200730105840605.png][]

![20200730105910450.png][]

![20200730105938714.png][]

![20200730105949464.png][]

![2020073011002893.png][]

**注意： **

索引失效可能导致行锁变为表锁，特别是 varchar 字段忘记加引号

**\# 加行锁**

select * from test_innode_lock where a=8 for update;

## 1.7 间隙锁 ##

![20200730135223226.png][]

## 1.8 总结 ##

![20200730140011681.png][]

![20200730140059690.png][]

![20200730140240546.png][]

![20200730140312346.png][]

![20200730140336532.png][]

# 二、 MySQL 主从复制 #

[20200730103011453.png]: /images/20230208/c8fa27fda1754c36a4e65af75ef6f042.png
[20200730103050172.png]: /images/20230208/e5f8948249424bf79d669a33ce1e6099.png
[20200730104009947.png]: /images/20230208/34cd1d70108840959277e97699893921.png
[20200730104645867.png]: /images/20230208/3c71d39f737f4e03898f54615705cad4.png
[20200730105017876.png]: /images/20230208/e1769f74859c46e7ad755bd55159a6ff.png
[20200730105239217.png]: /images/20230208/687cec7a362b496ea2721b4da1ac5611.png
[20200730105638980.png]: /images/20230208/3f8f90f1bacb4bf68187907fe4838e14.png
[20200730105759869.png]: /images/20230208/b228fa9b1e924031a8d69ccff5036451.png
[20200730105840605.png]: /images/20230208/47eb04aaa5ef41b1bd140c463c564056.png
[20200730105910450.png]: /images/20230208/247f7c7b64d44809b664fbd2b29fa3b6.png
[20200730105938714.png]: /images/20230208/640a8b732b274a6180cf67d60ec35aed.png
[20200730105949464.png]: /images/20230208/4bccaae1f1de41ada24dcb0aa3ddcfed.png
[2020073011002893.png]: /images/20230208/49ff239fdfd84863b4502009e4c8a039.png
[20200730135223226.png]: /images/20230208/38c509c671fa480eb0d517c09f73d2d4.png
[20200730140011681.png]: /images/20230208/9d6f9ef6ce15494084387c5c26cc79d5.png
[20200730140059690.png]: /images/20230208/2c14fd4399bf43799e3ee7def57db469.png
[20200730140240546.png]: /images/20230208/e799bddf8b5947f294f5c57e1b6c2485.png
[20200730140312346.png]: /images/20230208/ff9aec7e263d4acab78d14bd82550990.png
[20200730140336532.png]: /images/20230208/d4cdb06feefe4448885688573656118c.png