mysql锁表和解锁语句_db2查看是否锁表

2022-09-25 11:54:48 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

锁机制

共享锁与排他锁 共享锁（读锁）：其他事务可以读，但不能写。 排他锁（写锁） ：其他事务不能读取，也不能写。

粒度锁

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking） BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁 InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

不同粒度锁的比较：

表级锁： 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用

行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 最大程度的支持并发，同时也带来了最大的锁开销。

在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

页面锁： 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM 表锁

表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求； 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。 默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。 这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程**“饿死”** ，应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行）。

可以设置改变读锁和写锁的优先级：

  • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
  • 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
  • 给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

MyISAM加表锁方法：

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作 （UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

MyISAM存储引擎支持并发插入，以减少给定表的读和写操作之间的争用：

如果MyISAM表在数据文件中间没有空闲块，则行始终插入数据文件的末尾。 在这种情况下，你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁——你可以在其他线程进行读操作的时候，同时将行插入到MyISAM表中。 文件中间的空闲块可能是从表格中间删除或更新的行而产生的。 如果文件中间有空闲快，则并发插入会被禁用，但是当所有空闲块都填充有新数据时，它又会自动重新启用。 要控制此行为，可以使用MySQL的concurrent_insert系统变量。

  • 当concurrent_insert设置为0时，不允许并发插入。
  • 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这也是MySQL的默认设置。
  • 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

查询表级锁争用情况：

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺，如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况：

代码语言：javascript复制
mysql> SHOW STATUS LIKE 'Table%';
 ----------------------- --------- 
| Variable_name | Value |
 ----------------------- --------- 
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
 ----------------------- ---------

InnoDB加行级锁和表级锁(手动方式)

InnoDB锁模式： InnoDB 实现了以下两种类型的行锁：

共享锁（S）： 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 排他锁（X）： 允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

意向共享锁（IS）： 事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。 意向排他锁（IX）： 事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

意向锁: 假设你修改表结构了,那么如果表内还有行锁那么,这个操作就会处于等待状态,也就是说意向锁就是表的状态管理

锁模式的兼容情况：

（如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。）

InnoDB加锁方法：

  • 意向锁是 InnoDB 自动加的， 不需用户干预。
  • 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；
  • 对于普通 SELECT 语句，InnoDB 不会加任何锁；

事务可以通过以下语句显式给记录集加共享锁或排他锁：

  • 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

隐式锁定： InnoDB在事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

显式锁定 ：

代码语言：javascript复制
select ...from ...  lock in share mode //共享锁 
select ... from ... for update //排他锁

注意: 加锁只能在事物中加锁,而锁的释放只有在commit 或者rollback才后会释放

select for update： 在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。 select … for update 的使用场景：阻塞其他所有线程,为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

select lock in share mode ： in share mode 子句的作用就是将查找到的数据加上一个 共享锁，这个就是表示其他的事务只能对这些数据进行简单的读操作，并不能够进行增删改操作。select … lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。

性能影响：

  • select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。
  • select lock in share mode 语句是一个数据上一个共享锁的功能，它允许其他的事务也对该数据上共享锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。 原因是在读的过程中其他写的事物就会一直等着

for update 和 lock in share mode 的区别：

  • 前一个上的是排他锁，一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；
  • 后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。

InnoDB 行锁加锁原理：

  • InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
  • 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
  • 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。（更多阅读：MySQL索引总结）
  • 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。

InnoDB的间隙锁：

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB使用间隙锁的目的：

  1. 防止幻读，以满足相关隔离级别的要求；
  2. 满足恢复和复制的需要：

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：

一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。

二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

表锁的加锁和解锁(手动方式)

Mysql也支持lock tables和unlock tables，这都是在服务器层（MySQL Server层）实现的，和存储引擎无关，它们有自己的用途，并不能替代事务处理。 （除了禁用了autocommint后可以使用，其他情况不建议使用）：

LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。

UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时， 或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁 加锁语法

代码语言：javascript复制
LOCK TABLES t1 WRITE, t2 READ, ...;

解锁 注意: 这个释放锁只能释放我们手动使用LOCK 加的锁, 不能释放死锁等其他情况的事物锁等

代码语言：javascript复制
UNLOCK TABLES; --释放全部锁

使用LOCK TABLES的场景： 给表显示加表级锁（InnoDB表和MyISAM都可以），一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。（与MyISAM默认的表锁行为类似）

在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，并且 MySQL 不支持锁升级。也就是说，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

其实，在MyISAM自动加锁（表锁）的情况下也大致如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

例如，有一个订单表 orders，其中记录有各订单的总金额 total，同时还有一个订单明细 order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条 SQL：

代码语言：javascript复制
Select sum(total) from orders; 
Select sum(subtotal) from order_detail;

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail 表可能已经发生了改变。因此，正确的方法应该是：

代码语言：javascript复制
-- 给这个两个表加上读锁,local可以在表尾部进行插入
Lock tables orders read local, order_detail read local;
Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 
Unlock tables; --释放锁

（在 LOCK TABLES 时加了local选项，其作用就是允许当你持有表的读锁时，其他用户可以在满足 MyISAM 表并发插入条件的情况下，在表尾并发插入记录（MyISAM 存储引擎支持“并发插入”））

死锁（Deadlock Free）

死锁产生：

  • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
  • 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
  • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁： 数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。 死锁恢复： 死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。 外部锁的死锁检测： 发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决 死锁影响性能： 死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

MyISAM避免死锁：

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB手动加锁避免死锁：

  • 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
  • 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
  • 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
  • 通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。

死锁的解决办法和各种锁的情况查看

各种任务,锁,事务查看

查询任务列表

代码语言：javascript复制
SELECT * FROM information_schema.processlist

主要查看:

  1. Command 是Query ,Sleep
  2. Time 的时间(秒)
  3. state 里包含Waiting ...lock 关键字的状态
  4. info 是执行的sql语句

一般简单查询都应该0~5秒内完成，如果超时可能就存在异常等其他情况,另外，上面的状态大多数都是出现问题后，为排错提供的错误码；

查看线程操作表情况

代码语言：javascript复制
# 多少线程正在使用某张表In_use > 0 那么就表示有多个线程在操作这个表
show OPEN TABLES where In_use > 0;

表示有一个线程在使用t_user这个表

查看锁的事物情况

代码语言：javascript复制
--MySQL5.7锁机制和事务
- 记录了InnoDB中每一个正在执行的事务，包括该事务获得的锁信息，事务开始时间，事务是否在等待锁等信息
• Information_schema.innodb_trx 

-- 记录了InnoDB中事务在申请但目前还没有获取到的每个锁信息，以及当前事务的锁正在阻止其他事务获得锁
• Information_schema.innodb_locks 

-- 记录了InnoDB中事务之间相互等待锁的信息
• Information_schema.innodb_lock_waits 


 

--MySQL8.0锁机制和事务
-- 记录了InnoDB中每一个正在执行的事务，包括该事务获得的锁信息，事务开始时间，事务是否在等待锁等信息
• Information_schema.innodb_trx 

-- 记录了InnoDB中事务的每个锁信息，以及当前事务的锁正在阻止其他事务获得锁
• performance_schema.data_locks 

-- 记录了InnoDB中事务之间相互等待锁的信息
• performance_schema.data_lock_waits

-- 输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态帮助识别出有问题的查询语句等。
• processlist命令

基本上只要死锁那么 innodb_trx , [data_locks , innodb_lock_waits] OR [innodb_locks,innodb_lock_waits ] 这些表里一定有数据

innodb_trx表： trx_id：事务ID。 trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。 trx_started：事务开始时间。 trx_requested_lock_id：事务当前正在等待锁的标识， trx_wait_started：事务开始等待的时间。 trx_weight：事务的权重。 trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。 trx_query：事务正在执行的 SQL 语句。 trx_operation_state：事务当前操作状态。 trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。 trx_tables_locked：当前执行 SQL 的行锁数量。 trx_lock_structs：事务保留的锁数量。 trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。 trx_rows_locked：事务锁住的记录数。 trx_rows_modified：事务更改的行数。 trx_concurrency_tickets：事务并发票数。 trx_isolation_level：当前事务的隔离级别。 trx_unique_checks：是否打开唯一性检查的标识。 trx_foreign_key_checks：是否打开外键检查的标识。 trx_last_foreign_key_error：最后一次的外键错误信息。 trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。 trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识

innodb_locks表: lock_id：锁 ID。 lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。 lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。 lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。 lock_table：被锁定的或者包含锁定记录的表的名称。 lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。 lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。 lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。 lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。 lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

data_locks字段介绍

engine: 存储引擎（innodb） engine_lock_id 存储引擎内部的锁id，该值会发生动态变化，外部系统不应该依赖该值 engine_transaction_id: 2578 (information_schema.innodb_trx中的trx_id) thread_id: 持有锁的线程id event_id: 29 object_schema: 数据库名（lock_test） object_name: 表名（first_table） partition_name: 分区名 subpartition_name: 子分区名 index_name: 索引名 object_instance_begin: 锁的内存空间起始地址（140373282389696） lock_type: 锁类型（table/record） lock_mode: 锁模式（ix: 表意向排它锁， x： nextkey-lock, x, rec_not_gap: 行锁， x,gap: 间隙锁，lock_insert_intention插入意向锁） lock_status: granted、waiting lock_data： 锁的数据，当lock_type为record时才会有值（如果是聚族索引则直接显示主键，如果是非聚族索引则是，当前数据以及主键数据）

innodb_lock_waits表: requesting_trx_id：请求事务的 ID。 requested_lock_id：事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。 blocking_trx_id：阻塞事务的 ID。 blocking_lock_id：某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

data_lock_waits表： engine 请求锁定的存储引擎。 requesting_engine_lock_id 存储引擎请求的锁的id。要获取有关锁的详细信息，请将此列与表的engine_lock_id列连接在一起data_locks。 requesting_engine_transaction_id 请求锁定的事务的存储引擎内部id。 requesting_thread_id 请求锁定的会话的线程id。 requesting_event_id 在请求锁定的会话中导致锁定请求的performance schema事件。 requesting_object_instance_begin 请求的锁在内存中的地址。 blocking_engine_lock_id 阻止锁的id。 blocking_engine_transaction_id 拥有阻止锁的事务的存储引擎内部标识。 blocking_thread_id 拥有阻止锁的会话的线程id。 blocking_event_id 在持有该锁的会话中导致阻塞锁的performance schema事件。 blocking_object_instance_begin :阻塞锁在内存中的地址。

以上这些方法可以提供问题的源头,是因为啥锁的,和执行的sql都有显示

暂时死锁解决办法:

有些时候生产环境突然出现了死锁,那么我么第一件事就是赶紧把死锁给解决了,不要影响到用户的使用

注意: 在使用下命令解决前,需要先通过上面的方法来定位到问题或者通过系统日志来看看到底是那个表被锁了,这是必须的不然到时候解决问题都不知道从哪里下手

执行下面命令需要管理员数据库账户不然会导致查询不全: MySQL5.7

代码语言：javascript复制
SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE state LIKE '%lock%'
UNION
select  concat('KILL ',trx_mysql_thread_id,';') from information_schema.innodb_trx where trx_rows_locked>0 
UNION
SELECT concat('KILL ',REQUESTING_THREAD_ID,';') FROM information_schema.innodb_lock_waits
UNION  
SELECT concat('KILL ',THREAD_ID,';') FROM information_schema.innodb_locks

MySql8

代码语言：javascript复制
SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE state LIKE '%lock%'
UNION
select  concat('KILL ',trx_mysql_thread_id,';') from information_schema.innodb_trx where trx_rows_locked>0
UNION
SELECT concat('KILL ',REQUESTING_THREAD_ID,';') FROM performance_schema.data_lock_waits
UNION  
SELECT concat('KILL ',THREAD_ID,';') FROM performance_schema.data_locks

把查询出来的语句执行就行了(一般需要来回执行二次才行)

解决死锁的思路

一般上了生产环境的代码基本上业务上肯定是没啥问题,都是经过了大量的测试的了,那么从这一点我们就能把绝大部分可能性给排除,那么唯一可以发生死锁的情况基本上就3种情况:

  1. 定时器
  2. 多线程并发
  3. 手加动锁

那么我们就可以通过对应表找到,执行的定时器,多线程和加锁的sql ,进行排查和分析问题原因,按我经验来说基本上都是竞争太强烈导致的,比如定时器1秒执行一次,而定时器里的的代码逻辑比较复杂执行时间>1秒那么这样长久下去早晚出事

如果不上面情况那么你就需要按照下面这些情况慢慢的排查了

1）sql未使用索引，更新或删除单表中的数据

2）sql使用索引，但不是唯一索引，这个时候如果更新表中的大量数据或全量数据，此时系统如果有sql使用行锁语句执行，会造大量事务长时间等待、 锁冲突，因此mysql会将行锁升级为锁表,这个时候mysql为了防止死锁和事务回滚，会同时锁相关的表,然后就会发现sql执行报错

超过锁定等待超时,试着重新启动事务

3）使用类似select * from user for update的sql语句显式锁表

4）对线上大数据量并且活跃时间的表直接进行DDL(修改表结构…)操作或加索引操作，这也会造成锁表

重点: 第2条和第4条发生几率很高

死锁的预防措施

既然知道了锁表以后，我们有一些事后的补救措施，那我们是不是在刚开始设计的时候就可以尽可能规避这些坑呢，有没有一些比较好的实践？答案是有的，如下所示

1）对于大表的操作，查询条件一定要保证命中索引，如果能命中唯一索引就更好了

2）我们在程序开发的时候，尽可能将大事务拆分为小事务，减少锁表或回滚，比如：抽离部分业务逻辑异步发送消息队列处理

3）更新很频繁的业务，尽量转为批量处理，另外尽可能减少单次处理的数据量，大数据量可以分批次处理

4）尽量不要通过select * from user for update显示锁表，特别是高并发的时候，这种操作会很骚，严重影响tps，我们可以通过乐观锁加版本号实现或者先写到缓存然后异步写表等方案解决

5）业务高峰期，不要随便直接加字段或索引，尽量用户不活跃时执行，但是如果一定要在高峰期操作，可以复制旧表结构创建一个新表，然后在新表上加字段或索引，接着将原来旧表的数据copy到新表，再rename两个表，最后将rename期间旧表的增量数据迁移到新表

6）采用读写分离架构

点赞 -收藏-关注-便于以后复习和收到最新内容

有其他问题在评论区讨论-或者私信我-收到会在第一时间回复

如有侵权，请私信联系我

感谢,配合,希望我的努力对你有帮助^_^

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/172313.html原文链接：https://javaforall.cn

编程算法 压力测试 sql 云数据库SQLServer 数据库

0 人点赞