mysql深入理解全局锁和表锁解决mdl锁和死锁问题(代码片段)

关键词：

说明

不太深入细节，mysql的数据库可以粗略划分为global、table、row三种锁

锁的种类

全局锁global lock

如何加锁

全局锁又称为库锁，即锁住整个db instance.

当我想让一个表read-only的时候，可以通过下面命令添加全局锁

• Flush tables with read lock (FTWRL) 命令
• set global readonly = true 全局参数
  Update,Create,ALter,Commit,事务等操作都会被锁住。

什么时候使用全局锁

使用mysqldump备份整库

• 为了避免备份过程中新数据写入，加全局锁让库read-only
• gobal locks会杜绝任何更新操作，业务可能因此中断。

myisam的备份只能通过家全居锁来避免数据写入。 对于innodb数据库来说可以添加 --single-transaction参数执行备份，而无需添加全局锁

主从同步时让slave只读

表锁 tablelock

T1,T2代表两个窗口，两个线程，用来模拟多线程操作下表锁的效果

lock table $tablename read

• insert、drop、truncate、rename、update 都不能执行

操作	T1(lock user)	T2	说明
select	可以	可以	可读
insert\\update\\delete	报错	卡死	其他线程等待T1释放锁
crop\\rename\\truncate	报错	卡死	其他线程等待T1释放锁
unlock tables	-	继续执行

mysql> lock table user read;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user(username) values('zhangsan1');
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated

mysql> drop table user;
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated

mysql> truncate table user;
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
mysql>

新的事物可以解除表锁，如下

mysql> lock table user read;
Query OK, 0 rows affected (0.00 sec)

# 添加读锁后不能插入
mysql> insert into user(username) values('zhangsan3');
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated

# 当前线程下启动新的事物
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user(username) values('zhangsan3');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

# 事物结束后表锁已经解除了
mysql> insert into user(username) values('zhangsan4');
Query OK, 1 row affected (0.00 sec)

lock table $tablename write

和read相比

• 其他线程只读
• 当前线程可读可写（在读锁模式下，所有线程都是只读的）

表锁总结

• unlock tables;只能释放当前用户session线程下的锁。即T2不能释放T1的锁，必须由T1自己执行unlock tables;
• lock table xxx read,加锁的对象是表，当前线程和其他线程都只能读取表内容，不能执行任何其他修改变更操作。在lock read模式下，其他线程卡死，直到T1释放了表锁，才能执行更新操作。
• lock table xxx write,当前线程可以读，可以写。其他线程只读
• T1开启事务后，锁自动释放

释放表锁的三种方法

• 在加锁的线程上执行 unlock tables;
• 在加锁线程上执行 begin开启新事物
• 退出exit或者断开线程会自动释放锁

show processlit ，kill 线程,不能释放锁，只能用来杀死等待的线程。

元数据锁 meta data lodk (MDL)

MDL是表锁的一种，我们不需要显式的使用，系统会自动添加。主要是为了避免表结构修改的时候能够正常读写数据。

• 增删改查数据的时候，MDL read lock。不互斥，其他线程还是可以同时增删改表数据。
• 改变表结构时候，MLD write lock或read-write lock 。互斥，防止其他线程修改表结构。等到write锁释放的时候才能继续。

1. t1中开启一个事务，查询数据库结构。（此时user会加上MDL read lock)
2. t2执行alter语句，发现卡死了。发现processlist中T2的alter在等待MDL锁。
3. t1执行commit提交事务后。t2的alter语句正常执行

结果分析

1. 当t1开启事务后，执行表操作（增删改查）都可以。系统会给user添加MDL read lock。 注意：单纯beign开启事务不加锁）
2. 当t1事务提交前，t2可以执行增删改查，但是不能修改表结构。
3. t1事务提交以后，user上的MDL释放。
4. MDL锁不会超时，只要不提交事务，就会一直所这边表，业务可能因此崩溃。

如何排查死锁

如何解决MDL锁问题

通过上面分析，直到只要提交或者回滚事务就可以了，问题在于show processlist只能看到被阻塞的sql。并不能找到锁住表的事务元凶。

# 查询一下事务开启超过60s的 事务id
 select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\\G;

找到事务idtrx_mysql_thread_id，这里是12.然后show processlist找到这个id。

mysql>  select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\\G;
*************************** 1. row ***************************
                    trx_id: 421546431100768
                 trx_state: RUNNING
               trx_started: 2022-04-23 12:42:44
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 12
                 trx_query: select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                      |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------+
|  8 | root | localhost | test | Query   |  110 | Waiting for table metadata lock | alter table user add locaton3 varchar(20) |
| 12 | root | localhost | test | Query   |    0 | starting                        | show processlist                          |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------+
2 rows in set (0.00 sec)

kill 12 杀死这个进程即可。

实际场景中，可能存在多个进程。但最终目的是完结这些进程中的事务，通常只有一个元凶，干掉元凶即可。

如何解决死锁（待详细补充）

show processlist 干掉其中一个

查看锁的方法

show engine innodb status
------------
TRANSACTIONS
------------
Trx id counter 165918
Purge done for trx's n:o < 165917 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421546431100768, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)

-- 查看当前所有事务
select * from information_schema.innodb_trx;
-- 查看正在锁的事务
select * from information_schema.innodb_locks;
-- 查看等待锁的事务
select * from information_schema.innodb_lock_waits;
-- 查看表锁
show open tables where In_use>0;
--查看死锁
show engine innodb status\\G
--查看元数据锁
SELECT * FROM performance_schema.setup_instruments;
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
SELECT * FROM performance_schema.metadata_locks;

mysql全局锁和表锁(代码片段)

MySQL全局锁和表锁前言全局锁思考表级锁1表锁2元数据锁(metadatalock,MDL)思考如何安全的给小表加字段要变更的表是热点表，数据不大，但请求频繁，现在不得不加字段，该怎么办？前言数据库锁设计的初衷是为... 查看详情

mysql全局锁和表锁(代码片段)

MySQL全局锁和表锁前言全局锁思考表级锁1表锁2元数据锁(metadatalock,MDL)思考如何安全的给小表加字段要变更的表是热点表，数据不大，但请求频繁，现在不得不加字段，该怎么办？前言数据库锁设计的初衷是为... 查看详情

day870.全局锁和表锁-mysql实战(代码片段)

全局锁和表锁Hi，我是阿昌，今天学习记录的是关于全局锁和表锁的内容。数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。锁就是... 查看详情

mysql基础篇之全局锁和表锁--06(代码片段)

Mysql基础篇之全局锁和表锁--06前言全局锁表级锁小结前言今天我要跟你聊聊MySQL的锁。数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁... 查看详情

关于mysql中的表锁和行锁

什么情况下会触发表锁和行锁mysql行锁和表锁锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访... 查看详情

day870.全局锁和表锁-mysql实战(代码片段)

全局锁和表锁Hi，我是阿昌，今天学习记录的是关于全局锁和表锁的内容。数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。锁就是... 查看详情

06|全局锁和表锁：给表加个字段怎么有这么多阻碍？(代码片段)

...时候要刷脏页的数据到磁盘,因为要保持数据的一致性，理解的执行FTWRL时候是所有事务都提交完毕的时候mysqldump+-single-transaction也是保证事务的一致性,但他只针对有支持事 查看详情

锁机制

...然想到了锁现在我们就来看看锁mysql举例：InnoDB引擎的行锁和表锁 表锁：不会出现死锁，发生锁冲突几率高，并发低。行锁：会出现死锁，发生锁冲突几率低，并发高。锁冲突：例如说事务A将某几行上锁后，事务B又对其上... 查看详情

重新整理mysql基础篇—————表锁和全局锁[六](代码片段)

前言锁从大的方面可以分为：1.全局锁2.表锁3.行锁正文全局锁全局锁就是对整个数据加上读锁。在mysql中，加入全局锁的命令就是:Flushtableswithreadlock(FTWRL)这个时候会让整个数据库处于只读状态，之后其他线程的数据更新、数据定... 查看详情

数据库中的行锁和表锁

一、事务并发调度的问题脏读：A事务读取B事务尚未提交的更改数据，并在这个数据基础上操作。如果B事务回滚，那么A事务读到的数据根本不是合法的，称为脏读。在oracle中，由于有version控制，不会出现脏读。不可重复读：A事... 查看详情

用sql语句，怎么解决mysql数据库死锁

...最低，并发度也最高　　页面锁：开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般　　3、各种锁的适用场景　　表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如... 查看详情

mysql的全局锁和表级锁(代码片段)

...篇我就来聊聊MySQL的锁，不涉及MySQL的事务隔离级别。全局锁MySQL的全局锁会关闭所有打开的表，并使全部的表处于只读状态，它们的命令为：#全局锁，简称FTWRLFLUSHTABLESWITHREADLOCK;#解锁命令UNLOCKTABLES;对FTWRL进行... 查看详情

mysql发生死锁问题请求帮助

...最低，并发度也最高　　页面锁：开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般　　3、各种锁的适用场景　　表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如... 查看详情

mysql锁机制表锁和行锁详解

...锁）：当前写操作没有完成前，它会阻断其他锁和读锁。从对数据操作的粒度分：表锁行锁表锁（ 查看详情

mysql锁机制(代码片段)

...响数据库并发访问性能的一个重要因素。在MySQL中，锁和存储引擎是强相关的，不同的存储引擎有不同的锁机制。1锁的粒度  从锁的粒度来说，mysql的锁分为行锁、页面锁和表锁。不同的存储引擎支持不同粒度的锁... 查看详情

大牛总结的mysql锁优化

...L数据引擎的一部分，今天我们就一起来学习MySQL的数据库锁和它的优化。MySQL锁分类当多个事务或者进程访问同一个资源的时候，为了保证数据的一致性，就需要用到锁机制。从锁定资源的角度来看，MySQL中的锁分为：表级锁行... 查看详情

mysql基础篇之行锁--07(代码片段)

...检测小结前言在上一篇文章中，我跟你介绍了MySQL的全局锁和表级锁，今天我们就来讲讲MySQL的行锁。MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支... 查看详情

mysql原理篇之锁--14(代码片段)

...（ConsistentReads）锁定读（LockingReads）共享锁和独占锁锁定读的语句写操作多粒度锁MySQL中的行锁和表锁其他存储引擎中的锁InnoDB存储引擎中的锁InnoDB中的表级锁InnoDB中的行级锁InnoDB锁的内存结构解决并发事务带来... 查看详情