- 首先按照表来区分锁---------->>>
- innodb支持表锁,行锁;
- myisam支持表锁;
- dbd支持页面锁和表锁;
表锁加锁快,开销小,不会出现死锁;锁定范围大,发生锁冲突的概率最高,并发度也是最低;
行锁加锁慢,开销大,会出现死锁;锁定范围小,发生锁冲突概率小,并发度高;
页面锁是介于表锁和行锁之间,范围介于两者之间,并发度一般;
由于mysql8.0默认引擎innodb,默认为行锁,所以先整理一下行锁----
行锁
行锁–
行锁分为共享锁和排他锁
共享锁---->又叫读锁,当一个线程获取读锁后,会阻塞其他用户对该行数据的写操作(即阻止其他事务的排他锁),但不会阻止其他用户对改行数据的读操作(共享锁);
排他锁---->又叫写锁,当一个线程获取写锁之后,会阻塞其他用户对改行数据的读写(update,delete,操作,即其他事务不能够获得该行数据的共享锁与排他锁;
行锁的实现方式—
InnoDB行锁是给索引项加锁来实现的。所以只有通过索引项来操作数据才会有行锁,如果没有操作索引项 用的则是表锁;
默认情况下innodb用的是隐式加锁–
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加写锁–排他锁(X)因为要操作数据;
对于普通SELECT语句,InnoDB不会加任何锁,因为不需要操作数据,只是读取数据;
显示加锁的操作格式如下–
共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 排他锁(X) :SELECT * FROM table_name WHERE ... FOR UPDATE
**释放锁的形式—>>**提交事务与事务结束,锁就会自动释放,(这里包括 commit 与Rollback);
对于行锁用在事务的操作上,单纯为某一行数据加行锁没有太大意义(行锁是加在索引上的而不是数据上的)下面做一下演示:
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> desc huixin ;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| user_id | int | NO | PRI | NULL | |
| user_name | varchar(64) | YES | | NULL | |
| basic_salary | int | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select user_id ,user_name from huixin where user_id=1002 for update ;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 1002 | 赵四 |
+---------+-----------+
1 row in set (0.00 sec)
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> select user_id ,user_name from huixin where user_id=1002 lock in share mode ;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 1002 | 赵四 |
+---------+-----------+
1 row in set (0.00 sec)
mysql> insert into huixin values(1008,'李二狗',4500);
Query OK, 1 row affected (0.00 sec)
mysql> update huixin set user_name='zhaosi' where user_id=1002 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
用select 。。。。。where [condition] lock in share mode 来获得共享锁,在查询时候确保该记录没有被其他用户用update或者delete所操作,不然查询的数据可能跟原表不能保持一致。
以上是两个用户登陆数据库,由于不能同时触发命令所以也就无法触发错误提示,仅供了解,如果是不同的设备登陆,如果我在查询的时候加了行级锁,另一个设备2同时在修改我要查询的数据,则会阻塞设备2上对该数据的修改;
用select 。。。。。where [condition] lock in share mode 来获得排他锁,在修改数据的时候不允许其他用户读和写该数据;
以上是两个用户登陆数据库,由于不能同时触发命令所以也就无法触发错误提示,仅供了解,如果是不同的设备登陆,如果我在修改数据的时候加了行级锁,另一个设备同时在查看或者修改我要修改的数据,则会阻塞在设备2上对该数据的操作;
以上就是行锁的原理;
锁的基本模式
意向锁
意向锁是数据库自己维护的,不需要人为干预,即当我们为一行数据加共享/排他锁时会自动在这张表上加一个意向共享/排他锁;
这样反推一下,如果一张表上至少有一个意向锁,那么说名这张表的某些数据被某些事务加上了锁;
锁的细化
记录锁---->>
新建一个表----test
create table test (id int ,name varchar(8), primary key (id ) );
mysql> insert into test values (1,'张三'),(2,'张三2'),(3,'张三3'),(4,'张三4');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
当我们对于唯一性的索引(包括唯一索引和主键索引)使用等值查询,精准匹配到一
条记录的时候,这个时候使用的就是记录锁。
间隙锁---->>
当我们查询的记录不存在,无论是用等值查询还是范围查询的时候,它使用的都是间隙锁。
临键锁---->>
当我们使用了范围查询,不仅仅命中了 Record 记录,还包含了 Gap 间隙,在这种情况下我们使用的就是临键锁,它是 MySQL 里面默认的行锁算法,相当于记录锁加上间隙锁。
我们一番操作后,表的数据如下:
mysql> select * from test ;
+----+----------+
| id | name |
+----+----------+
| 2 | 张三2 |
| 4 | 张三4 |
| 6 | 李四 |
| 8 | 王五 |
| 10 | 李四10 |
| 13 | 赵13 |
| 18 | 王五18 |
+----+----------+
7 rows in set (0.00 sec)
开启两个事务—>
表锁
下面是表锁—
innodb和myisam以及dbd都支持表锁,所以还是以innodb的表锁为例演示–
表上锁的形式----
lock table tablename read ----读锁 ,即只允许读,不允许修改;
解锁----lock table tablename write;
mysql> use company
Database changed
mysql> #这里是不上锁的表huixin;
mysql> desc huixin ;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| user_id | int | NO | PRI | NULL | |
| user_name | varchar(64) | YES | | NULL | |
| basic_salary | int | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> insert into huixin values(1011,'王二小',3600);
Query OK, 1 row affected (0.01 sec)
mysql> #以下是上表锁的huixin;
mysql> lock table huixin read ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from huixin ;
+---------+-----------+--------------+
| user_id | user_name | basic_salary |
+---------+-----------+--------------+
| 1001 | 爱德华 | 2500 |
| 1002 | 赵四 | 2500 |
| 1003 | 王伟 | 2200 |
| 1004 | 宫本 | 2100 |
| 1005 | 黄淮 | 3000 |
| 1006 | 胡歌 | 5000 |
| 1007 | 张浩 | 6500 |
| 1008 | 李二狗 | 4500 |
| 1011 | 王二小 | 3600 |
+---------+-----------+--------------+
9 rows in set (0.00 sec)
mysql> insert into huixin values(1009,'郑中基',5620);
ERROR 1099 (HY000): Table 'huixin' was locked with a READ lock and can't be updated
mysql> #上读锁之后,表就处于只读状态,不能修改表中数据;
#解锁
mysql> lock table huixin write ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into huixin values(1009,'郑中基',5620);
Query OK, 1 row affected (0.00 sec)
mysql> select * from huixin ;
+---------+-----------+--------------+
| user_id | user_name | basic_salary |
+---------+-----------+--------------+
| 1001 | 爱德华 | 2500 |
| 1002 | 赵四 | 2500 |
| 1003 | 王伟 | 2200 |
| 1004 | 宫本 | 2100 |
| 1005 | 黄淮 | 3000 |
| 1006 | 胡歌 | 5000 |
| 1007 | 张浩 | 6500 |
| 1008 | 李二狗 | 4500 |
| 1009 | 郑中基 | 5620 |
| 1011 | 王二小 | 3600 |
+---------+-----------+--------------+
10 rows in set (0.00 sec)
mysql>
重点练习一下间隙锁
话不多说上代码—
#session1中,首先在huixin表中插入一列num,并设置为普通索引,(已经设置好了)这里不演示操作可自行操作
mysql> select * from huixin where user_id>1003 for update ;
+---------+-----------+--------------+-----+
| user_id | user_name | basic_salary | num |
+---------+-----------+--------------+-----+
| 1005 | ceshi | 4556 | 5 |
| 1006 | test | 4455 | 5 |
+---------+-----------+--------------+-----+
2 rows in set (0.00 sec)
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from huixin where num = 3 for update ;
+---------+-----------+--------------+-----+
| user_id | user_name | basic_salary | num |
+---------+-----------+--------------+-----+
| 1003 | sdsad | 4242 | 3 |
+---------+-----------+--------------+-----+
1 row in set (0.00 sec)
mysql>
#在session2进行操作
mysql> use company
Database changed
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into huixin values (1234 ,'test',7878,2);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into huixin values (1234 ,'test',7878,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into huixin values (1234 ,'test',7878,5);
Query OK, 1 row affected (0.00 sec)
我们可以发现在(1,3]和[3,5)之间上了间隙锁这也是为啥插不进去数据的原因。
总结–设计表的时候尽量避免间隙锁的产生,间隙锁属于行锁的范畴,如果num不属于索引,那么此锁就属于表所的范畴,在huixin表中插入任何数据都会出现阻塞。
死锁
死锁的产生是多个session去操作同一行数据,同时该行数据又被加锁了,演示代码如下–
#session1中
mysql> set@@autocommit =0; #关闭自动提交
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%isolation%'; #查看隔离级别
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from huixin where user_id =1111 for update ;
Empty set (0.00 sec)
mysql> insert into huixin values(1111,'ceshi',5200,100);
Query OK, 1 row affected (32.79 sec)
#session2中
mysql> set @@autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from huixin where user_id=1111 for update ;
Empty set (0.00 sec)
mysql> insert into huixin values (1111,'ceshi',5200,100);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>
可以发现多个session同时操作一行数据(必须是索引才是行锁的范畴),会产生死锁。
死锁的解决方案
首先要解决死锁问题,在程序的设计上,当发现程序有高并发的访问某一个表时,尽量对该表的执行操作串行化,或者锁升级,一次性获取所有的锁资源。
然后也可以设置参数innodb_lock_wait_timeout,超时时间,并且将参数innodb_deadlock_detect 打开,当发现死锁的时候,自动回滚其中的某一个事务。
***总结– ***
innodb存储引擎实现了行级锁,在锁定范围上更小,更精细,能实现更为复杂的操作但是所带来的性能损耗也会比表级锁很大,但是行锁在并发性能上的优势很明显。
当并发量很大的时候,innodb的整体性能要比myisam高,所以在使用何种引擎的时候要考虑锁的问题,在选择锁的时候应该考虑并发量是不是很大,合理使用锁,以下是使用行锁的建议—
- 尽量控制事务的大小,减少锁定资源量和锁定时长;
- 数据检索最好是通过索引,因为时间快性能较好(当然数据量小也会进行全表扫描),同时也可以避免升级成表锁–合理使用索引可以更好的提高性能;
- 尽可能减少在数据范围内的检索条件比如 检索条件 <50 要比 <100要好(这要求我们大致推算出数据的位置),避免间隙锁带来的负面影响而不能操作一些数据;
- 在合适的情况下尽量使隔离级别较小,同时最好是一次锁定所有的所需操作的资源;
死锁
产生死锁的四个必要条件:
(1) 互斥条件:一个资源每次只能被一个进程使用。
(2) 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。
(3) 不剥夺条件:进程已获得的资源,在末使用完之前,不能强行剥夺。
(4) 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
降低死锁发生概率:
(1)按同一顺序访问对象。
(2)避免事务中的用户交互。
(3)保持事务简短并在一个批处理中。
(4)使用低隔离级别。
(5)使用绑定连接。
分库分表
**垂直分库------->>>**减少并发压力。
垂直分库的做法,把一个数据库按照业务拆分成不同的数据库:
抽象概念表示:
实际应用:
**水平分表------->>>**解决存储瓶颈。
水平分库分表的做法,把单张表的数据按照一定的规则分布到多个数据库。
就是我们索索地集群,每一个数据库服务器上都有该表;
抽象概念表示:
之后会学习到mysql 主从,分库分表的一些知识;
慢查询
听名字就知道 —查询的慢了,会产生慢查询日志;
开启慢查询日志是小号性能的,所以默认是关闭状态;(默认查询时间超过10秒会被记录到慢查询日志);
查看慢查询日志状态—>>
mysql> show variables like 'slow_query%' ;
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/Gavin-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)
下面我们开启这个开关----->>
仅本次有效,下次登陆时会失效(如果要永久有效,则需要修改配置文件my.cnf)
mysql> set @@global.slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set @@global.long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/Gavin-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)
模拟慢查询---->>
没有条件也要创造条件整出慢查询---->>
mysql> select sleep(10) ;
+-----------+
| sleep(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.00 sec)
mysql> select * from test ;
+----+----------+
| id | name |
+----+----------+
| 2 | 张三2 |
| 4 | 张三4 |
| 6 | 李四 |
| 7 | 小七 |
| 8 | 王五 |
| 10 | 李四10 |
| 13 | 赵13 |
| 18 | 王五18 |
+----+----------+
8 rows in set (0.00 sec)
mysql> show global status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set (0.00 sec)
查看慢日志文件---->>
首先找到慢日志文件的位置
慢性查询日志主要包含了
#端口号 socket 套接
#时间 用户 查询时间 锁的时间 查询语句等信息
[root@Gavin /]# cat /usr/local/mysql/data/Gavin-slow.log
/usr/local/mysql/bin/mysqld, Version: 8.0.27 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2021-05-13T03:49:35.241012Z
# User@Host: gavin[gavin] @ localhost [] Id: 12
# Query_time: 10.000687 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
use gavin;
SET timestamp=1653536965;
select sleep(10);
有了慢查询日志,怎么去分析这个日志呢?
人工?如果记录数过多…
还好mysql为我们提供了一种工具----mysqldumpslow
在mysql之外,通过 mysqldumpslow -s t -t 10 -g 'select' /usr/local/mysql/data/Gavin-slow.log
分析出 慢查询的数量,慢查询的语句
[root@Gavin /]# mysqldumpslow -s t -t 10 -g 'select' /usr/local/mysql/data/Gavin-slow.log
Reading mysql slow query log from /usr/local/mysql/data/Gavin-slow.log
Count: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows=1.0 (1), gavin[gavin]@localhost
select sleep(N)
Died at /usr/local/mysql/bin/mysqldumpslow line 162, <> chunk 1.
#Count 代表这个 SQL 执行了多少次;
#Time 代表执行的时间,括号里面是累计时间;
#Lock 表示锁定的时间,括号是累计;
Rows 表示返回的记录数,括号是累计。
不断学习共同进步!