mysql存储过程出现锁表锁行的情况怎么解决

行锁的等待

高唐网站建设公司成都创新互联公司,高唐网站设计制作，有大型网站制作公司丰富经验。已为高唐上千家提供企业网站建设服务。企业网站搭建\成都外贸网站建设要多少钱，请找那个售后服务好的高唐做网站的公司定做！

在介绍如何解决行锁等待问题前，先简单介绍下这类问题产生的原因。产生原因简述：当多个事务同时去操作（增删改）某一行数据的时候，MySQL 为了维护 ACID 特性，就会用锁的形式来防止多个事务同时操作某一行数据，避免数据不一致。只有分配到行锁的事务才有权力操作该数据行，直到该事务结束，才释放行锁，而其他没有分配到行锁的事务就会产生行锁等待。如果等待时间超过了配置值（也就是 innodb_lock_wait_timeout 参数的值，个人习惯配置成 5s，MySQL 官方默认为 50s），则会抛出行锁等待超时错误。

如上图所示，事务 A 与事务 B 同时会去 Insert 一条主键值为 1 的数据，由于事务 A 首先获取了主键值为 1 的行锁，导致事务 B 因无法获取行锁而产生等待，等到事务 A 提交后，事务 B 才获取该行锁，完成提交。这里强调的是行锁的概念，虽然事务 B 重复插入了主键，但是在获取行锁之前，事务一直是处于行锁等待的状态，只有获取行锁后，才会报主键冲突的错误。当然这种 Insert 行锁冲突的问题比较少见，只有在大量并发插入场景下才会出现，项目上真正常见的是 updatedelete 之间行锁等待，这里只是用于示例，原理都是相同的。

三、产生的原因根据我之前接触到的此类问题，大致可以分为以下几种原因

MySQL从入门到精通(九) MySQL锁，各种锁

锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）争用外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言是尤其重要，也更加复杂。MySQL中的锁，按照锁的粒度分为:1、全局锁，就锁定数据库中的所有表。2、表级锁，每次操作锁住整张表。3、行级锁，每次操作锁住对应的行数据。

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将阻塞。其典型的使用场景就是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。但是对数据库加全局锁是有弊端的，如在主库上备份，那么在备份期间都不能执行更新，业务会受影响，第二如果是在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

解决办法是在innodb引擎中，备份时加上--single-transaction参数来完成不加锁的一致性数据备份。

添加全局锁: flush tables with read lock; 解锁 unlock tables。

表级锁,每次操作会锁住整张表.锁定粒度大,发送锁冲突的概率最高,并发读最低,应用在myisam、innodb、BOB等存储引擎中。表级锁分为: 表锁、元数据锁(meta data lock, MDL)和意向锁。

表锁又分为: 表共享读锁 read lock、表独占写锁write lock

语法: 1、加锁 lock tables 表名 ... read/write

2、释放锁 unlock tables 或者关闭客户端连接

注意: 读锁不会阻塞其它客户端的读，但是会阻塞其它客户端的写，写锁既会阻塞其它客户端的读，又会阻塞其它客户端的写。大家可以拿一张表来测试看看。

元数据锁,在加锁过程中是系统自动控制的，无需显示使用，在访问一张表的时候会自动加上，MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML和DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作时，加MDL写锁(排他).

查看元数据锁:

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;

意向锁，为了避免DML在执行时，加的行锁与表锁的冲突，在innodb中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。意向锁分为,意向共享锁is由语句select ... lock in share mode添加。意向排他锁ix,由insert，update，delete，select。。。for update 添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;

行级锁，每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最高，并发读最高，应用在innodb存储引擎中。

innodb的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，对于行级锁，主要分为以下三类:

1、行锁或者叫record lock记录锁，锁定单个行记录的锁，防止其他事物对次行进行update和delete操作，在RC,RR隔离级别下都支持。

2、间隙锁Gap lock,锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事物在这个间隙进行insert操作，产生幻读，在RR隔离级别下都支持。

3、临键锁Next-key-lock,行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap,在RR隔离级别下支持。

innodb实现了以下两种类型的行锁

1、共享锁 S: 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

2、排他锁 X: 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

insert 语句 排他锁 自动添加的

update语句 排他锁 自动添加

delete 语句 排他锁 自动添加

select 正常查询语句 不加锁 。。。

select 。。。lock in share mode 共享锁 需要手动在select 之后加lock in share mode

select 。。。for update 排他锁 需要手动在select之后添加for update

默认情况下，innodb在repeatable read事务隔离级别运行，innodb使用next-key锁进行搜索和索引扫描，以防止幻读。

间隙锁唯一目的是防止其它事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用的间隙锁。

MySQL锁表和解锁操作

1、确定mysql有锁表的情况则使用以下命令查看锁表进程

2、杀掉查询结果中已经锁表的trx_mysql_thread_id

扩展：

1、查看锁的事务

2、查看等待锁的事务

3、查询是否锁表：

4、查询进程

mysql行锁解锁问题

是这样的 你 select * from xx where id=xx for update

数据库会给你改成

begin;

select * from xx where id=xx for update

commit;

这个是叫做 隐式提交。--mysql有很多隐式提交的语句的

所以如果你要显式提交事务的话

你得手动 在select * from xx where id=xx for update 语句之前，输入begin;

等你想结束的时候commit/rollback就行了

mysql8删除行锁

mysql8删除行锁？

回答如下：mysql8删除行锁需要插件，该问题易解决，举一反三，活跃在良好的网络氛围，行为习惯良好。认可度极高！

MySQL - for update 行锁 表锁

for update 的作用是在查询的时候为行加上排它锁，当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。

它的典型使用场景是 高并发并且对于数据的准确性有很高要求 ，比如金钱、库存等，一般这种操作都是很长一串并且开启事务的，假如现在要对库存进行操作，在刚开始读的时候是1，然后马上另外一个进程将库存更新为0了，但事务还没结束，会一直用1进行后续的逻辑，就会有问题，所以需要用for upate 加锁防止出错。

行锁的具体实现算法有三种：record lock、gap lock以及next-key lock。

只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取 gap lock 或 next-key lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用 gap lock 或 next-key lock

for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

select 语句默认不获取任何锁，所以是可以读被其它事务持有排它锁的数据的！

InnoDB 既实现了行锁，也实现了表锁。

当有明确指定的主键/索引时候，是行级锁，否则是表级锁

假设表 user，存在有id跟name字段，id是主键，有5条数据。

明确指定主键，并且有此记录，行级锁

无主键/索引，表级锁

主键/索引不明确，表级锁

明确指定主键/索引，若查无此记录，无锁

参考博文：

新闻标题：mysql行锁怎么解 mysql行锁死锁
文章网址：http://azwzsj.com/article/dooidsg.html