- 首先按照表来区分锁---------->>>
- 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 表示返回的记录数，括号是累计。
不断学习共同进步！
540
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
