SQL优化教程09-锁机制

EumenCoder

已于 2022-08-31 18:03:22 修改

阅读量404

点赞数

分类专栏： SQL优化文章标签： sql 数据库 oracle

于 2022-08-31 16:55:52 首次发布

本文链接：https://blog.csdn.net/weixin_42339278/article/details/126618310

版权

SQL优化专栏收录该内容

9 篇文章 1 订阅

订阅专栏

锁机制详解

锁：解决因资源共享而造成的并发问题。

锁的分类

（1）操作类型：

读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰。
写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他的读操作，写操作。

（2）操作范围：

表锁：

一次性对一张表加锁，如MyISAM存储引擎使用表锁。
优点：开销小，加锁快，无死锁
缺点：锁的范围大，容易发生锁冲突，容易发生高并发问题。

行锁

一次性对一条数据加锁，如InnoDB存储引擎使用行锁。
优点：锁的范围小，不易发生锁冲突，不易发生高并发问题。
缺点：开销大，加锁慢，容易出现死锁情况。

页锁（不常用）

表锁教程

建表语句：——MySQL和SLQSERVER支持自增操作，oracle需要借助序列来实现自增。

create table tablelock
(
id int primary key auto_increment,
name varchar(20)
)engine myisam;

插入数据：

insert into tablelock(name) values('a1');
insert into tablelock(name) values('a2');
insert into tablelock(name) values('a3');
insert into tablelock(name) values('a4');
insert into tablelock(name) values('a5');
commit;

加读锁操作：

lock table 表1 read/write, 表2 read/write ...
lock table tablelock read;

查看加锁的表：

show open tables;

测试加读锁之后的读写操作：
会话0：

对tablelock表操作：
select * from tablelock;              ——读（查），可以
delete from tablelock where id = 1;   ——写（增删改），不可以
对emp表操作：
select * from emp;                    ——读，不可以
delete from emp where eid=1;          ——写，不可以

会话1（其他会话操作tablelock表）：

select * from tablelock;               ——读，可以
delete from tablelock where id = 1;    ——写，会等待会话0将锁释放

会话2（其他会话操作emp表（未加锁））：

select * from emp;                    ——读，可以
delete from emp where eno = 1;        ——写，可以

释放锁：

unlock tables；

总结：
（1）如果会话0（访问数据库的dos命令行或者数据库客户端连接）对表A加了read锁，则该会话可以对A进行读操作，不能进行写操作。不能对其他表进行任何读写操作。

（2）如果会话0对表A加了write锁，则该会话可以对加锁的表进行任何操作，但是不能操作别的表。
其他会话可以对加锁的表进行操作的前提是：等待会话0将写锁释放。

MySQL表级锁的锁模式：

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁。
在执行更新操作前，会自动给涉及的表加写锁。
所以，对MyISAM表进行操作，会有以下情况：
（1）MyISAM的读操作（加读锁），不会阻塞其他进程会同一表的读请求，但是会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。
（2）MyISAM的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

表加锁分析

查看表加锁情况

show open tables;                   ——Name_locked为1表示加锁，为0表示没有加锁

在这里插入图片描述

分析表锁定的严重程度

show status like 'table%'
Table_locks_immediate:马上可以获取到的锁数
Table_locks_waited:需要等待的表锁数（该值越大，说明该表的锁竞争越厉害，有更多的需求需要修改或查询表）

一般建议：
Table_locks_immediate/Table_locks_waited > 5000 ，采用InnoDB引擎（行锁），否则用MyISAM(表锁）
原因：比重越大，说明可以获取到的锁较多，所以用行锁，提高并发效率。
在这里插入图片描述

行锁教程

建表语句：

create table linelock
(
id int primary key auto_increment,
name varchar(20)
)engine innoDB;

插入数据：

insert into linelock(name) values('a1');
insert into linelock(name) values('a2');
insert into linelock(name) values('a3');
insert into linelock(name) values('a4');
insert into linelock(name) values('a5');
commit;

由于MySQL默认自动commit，因此暂时关闭自动commit。（以下三条语句都可以）

set autocommit=0;
start transaction;
begin;

会话0：写操作

insert into linelock values('a6');

会话1：写操作同样的数据

update linelock set name='ax' where id =6;      ——执行后发现处于等待中，需要等会话0 commit/rollback后才可以执行

行锁注意事项

（1）如果没有索引或者索引失效，行锁将自动转为表锁
首先查看索引：

show index from linelock;

添加索引：

alter table linelock add index idx_linelock_name(name);

会话0：写操作：

update linelock set name='a1' where name ='3';

会话1：写操作（对不同数据）

update linelock set name='a2' where name='4';

上面两个操作因为操作的是不同数据，因此都可以操作成功。

会话0：写操作

update linelock set name='a1' where name =3;

会话1：写操作（对不同数据）

update linelock set name='a2' where name=4;

在上述操作中，由于name发生了类型转换，因此索引失效，行锁变为表锁，因此会话1操作需要等待会话0commit或者rollback。

（2）对select语句进行加锁

通过for update 语句对select语句进行加锁。

select * from linelock where id=2 for update;

行锁分析

show status like '%innodb_row_lock%';

参数说明：
Innodb_row_lock_current_waits：当前正在等待锁的数量。
Innodb_row_lock_time：等待总时长。从系统启动开始。
Innodb_row_lock_time_avg：平均等待时长。
Innodb_row_lock_time_max：最大等待时长。
Innodb_row_lock_waits：等待次数。