数据库 mysql 锁详细介绍&数据表锁分析技巧

谁践踏了优雅 2023-02-10 03:46 18阅读 0赞

# mysql的数据库引擎中的InnoDb下的行锁、表锁、意向锁等 #

##    行锁 ##

mysql的行锁是通过索引加载的，即是行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描

## 行锁类型 ##

共享锁 和 排它锁

##    共享锁（S锁） ##

也叫读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

SELECT column FROM table ... LOCK IN SHARE MODE;

##    排它锁（X锁） ##

也叫写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。  
如果在一条select语句后加上for update，则查询到的数据会被加上一条排它锁，其它事务可以读取，但不能进行更新和插入操作

SELECT column FROM table ... FOR UPDATE;

##    间隙锁 ##

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内并不存在的记录，叫做间隙

InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁

## 表锁 ##

就是没有对没有索引字段上进行事务操作，会导致表锁，其实也可以叫意向锁

##    意向锁 ##

意向锁产生的主要目的是为了处理行锁和表锁之间的冲突，事务在请求S锁和X锁前，需要先获得对应的IS、IX锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。  
如果没有意向锁的话，则需要遍历所有整个表判断是否有行锁的存在，以免发生冲突  
如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果  
举例mysql中添加一个排他锁流程是这样的，首先去获取意向排他锁这时会去检测是否有其他已经占用的锁并且是否存在冲突，如果不冲突则获取到意向排他锁，是否存在当前行有排他锁或范围内的间隙锁如果没有则加上排他锁

##    意向锁类型 ##

意向共享锁 和意向排它锁  
意向锁之前是相互兼容的  
排他锁持有后其他不管什么锁都是冲突的

![20200519211054479.png][]

## 查看innodb引擎的运行时信息  ##

show engine innodb status

## 查看锁表情况 ##

show status like 'Table%';

查询表级锁争用情况 Table\_locks\_immediate  指的是能够立即获得表级锁的次数  Table\_locks\_waited  指的是不能立即获取表级锁而需要等待的次数

获取锁定次数、锁定造成其他线程等待次数，以及锁定等待时间信息

查看造成死锁的sql语句，分析索引情况，然后优化sql语句；

show status like '%lock%';

## 查看超时时间： ##

show variables like '%timeout%';

\+—————————-+——–+  
| Variable\_name | Value |  
\+—————————-+——–+  
| Table\_locks\_immediate | 795505 |  
| Table\_locks\_waited | 0 |  
| Table\_open\_cache\_hits | 0 |  
| Table\_open\_cache\_misses | 0 |  
| Table\_open\_cache\_overflows | 0 |  
\+—————————-+——–+  
5 rows in set (0.00 sec)

Table\_locks\_immediate 指的是能够立即获得表级锁的次数  
Table\_locks\_waited 指的是不能立即获取表级锁而需要等待的次数，如果数量大，说明锁等待多，有锁争用情况

## 查看正在被锁定的的表 ##

show OPEN TABLES where In_use > 0;

mysql> show OPEN TABLES where In\_use > 0;  
\+————–+—————+——–+————-+  
| Database | Table | In\_use | Name\_locked |  
\+————–+—————+——–+————-+  
| music | class\_record | 1 | 0 |  
| vipswoole | chat\_message | 3 | 0 |  
| music | user\_account | 1 | 0 |  
| music | sales\_channel | 1 | 0 |  
| music | class\_room | 5 | 0 |  
| music | user | 1 | 0 |  
| music\_school | user | 1 | 0 |  
\+————–+—————+——–+————-+  
7 rows in set (0.00 sec)

## 查看正在锁的事务 ##

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

## 等待锁定 ##

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

## 如果查看到锁争用情况严重，可以再查看当前执行的SQL ： ##

查看当前线程处理情况，如果不使用full关键字，信息字段中只会显示每个语句的前100个字符。

show processlist; 
    show full processlist;
    
    SELECT * FROM information_schema.PROCESSLIST;

## 查询正在执行的进程 ##

SELECT * FROM information_schema.PROCESSLIST where length(info) >0 ;

(mysqladmin -uroot -p -P 3306 processlist)

mysqladmin命令有一个debug参数，可以分析当前MySQL服务的状态信息，同时也可以用来帮助我们定位当前锁的详细情况，这里我们通过该命令分析一下当前MySQL服务的详细状态，执行mysqladmin命令如下：

\[root@phpmysql02 data\]\# mysqladmin -ujss -p -S /data/3306/mysql.sock debug

Enter password:  
debug会将状态信息生成到mysql的错误文件，一般锁的信息都会保存在最后几行，这里我们在操作系统层error log最后几行：

\[root@phpmysql02 data\]\# tail -10 phpmysql02.err

Thread database.table\_name Locked/Waiting Lock\_type  
2 hdpic.t\_wiki\_zutu Waiting - write Highest priority write lock  
123890 hdpic.t\_wiki\_zutu\_category Locked - read Low priority read lock  
123890 hdpic.t\_wiki\_zutu\_photo Locked - read Low priority read lock  
123890 hdpic.t\_wiki\_zutu Locked - read Low priority read lock  
124906 hdpic.t\_wiki\_zutu Waiting - read Low priority read lock  
从上述信息可以看出，123890持有的读锁阻塞了2的写入和124906的读操作，这个状态符合我们的推论，接下来处理就比较单纯了，如果现状不可接受，不能继续等待，将123890杀掉，释放资源即可：

# 查询到相对应的进程===然后 kill id
    kill $pid
    
    kill 123890;

Query OK, 0 rows affected (0.00 sec)  
再次执行show processlist查看：

## 使用系统表进行锁查询： ##

SELECT
    	r.trx_isolation_level,
    	r.trx_id waiting_trx_id,
    	r.trx_mysql_thread_id waiting_trx_thread,
    	r.trx_state waiting_trx_state,
    	lr.lock_mode waiting_trx_lock_mode,
    	lr.lock_type waiting_trx_lock_type,
    	lr.lock_table waiting_trx_lock_table,
    	lr.lock_index waiting_trx_lock_index,
    	r.trx_query waiting_trx_query,
    	b.trx_id blocking_trx_id,
    	b.trx_mysql_thread_id blocking_trx_thread,
    	b.trx_state blocking_trx_state,
    	lb.lock_mode blocking_trx_lock_mode,
    	lb.lock_type blocking_trx_lock_type,
    	lb.lock_table blocking_trx_lock_table,
    	lb.lock_index blocking_trx_lock_index,
    	b.trx_query blocking_query 
    FROM
    	information_schema.innodb_lock_waits w
    	INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    	INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
    	INNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id = w.blocking_trx_id
    	INNER JOIN information_schema.innodb_locks lr ON lr.lock_trx_id = w.requesting_trx_id

涉及的3张表说明：

information\_shcema下的三张表（通过这三张表可以更新监控当前事物并且分析存在的锁问题）  
—— innodb\_trx （ 打印innodb内核中的当前活跃（ACTIVE）事务）  
—— innodb\_locks （ 打印当前状态产生的innodb锁 仅在有锁等待时打印）  
—— innodb\_lock\_waits （打印当前状态产生的innodb锁等待 仅在有锁等待时打印）

1) innodb\_trx表结构说明 （摘取最能说明问题的8个字段）  
字段名 说明  
trx\_id innodb存储引擎内部唯一的事物ID  
trx\_state  
当前事物状态（running和lock wait两种状态）  
trx\_started  
事物的开始时间  
trx\_requested\_lock\_id 等待事物的锁ID，如trx\_state的状态为Lock wait，那么该值带表当前事物等待之前事物占用资源的ID，若trx\_state不是Lock wait 则该值为NULL  
trx\_wait\_started 事物等待的开始时间  
trx\_weight 事物的权重，在innodb存储引擎中，当发生死锁需要回滚的时，innodb存储引擎会选择该值最小的进行回滚  
trx\_mysql\_thread\_id mysql中的线程id, 即show processlist显示的结果  
trx\_query 事物运行的SQL语句  
2）innodb\_locks表结构说明

字段名 说明  
lock\_id 锁的ID  
lock\_trx\_id 事物的ID  
lock\_mode 锁的模式（S锁与X锁两种模式）  
lock\_type 锁的类型 表锁还是行锁（RECORD）  
lock\_table 要加锁的表  
lock\_index 锁住的索引  
lock\_space 锁住对象的space id  
lock\_page 事物锁定页的数量，若是表锁则该值为NULL  
lock\_rec 事物锁定行的数量，若是表锁则该值为NULL  
lock\_data 事物锁定记录主键值，若是表锁则该值为NULL（此选项不可信）  
3）innodb\_lock\_waits表结构说明  
字段名 说明  
requesting\_trx\_id申请锁资源的事物ID  
requested\_lock\_id申请的锁的ID  
blocking\_trx\_id阻塞其他事物的事物ID  
blocking\_lock\_id阻塞其他锁的锁ID

可以根据这三张表进行联合查询，得到更直观更清晰的结果。

## 查看表索引信息 ##

SHOW INDEX FROM account;

[20200519211054479.png]: https://img-blog.csdnimg.cn/20200519211054479.png