锐客网

  1. 首页 > 生活百科 > it技术 > >

Mysql锁冲突怎么解决 mysql上锁

IT技术事务

解决一次mysql死锁问题 多线程开启事务处理 。每个事务有多个update操作和一个insert操作（都在同一张表） 。
默认隔离级别：Repeatable Read
只有hotel_id=2和hotel_id=11111的数据
逻辑删除原有数据
插入新的数据
根据现有数据情况，update的时候没有数据被更新
报了非常多一样的错
发现居然有死锁 。
根据常识考虑，我每个线程（事务）更新的数据都不冲突 ， 为什么会产生死锁？
带着这个问题，打印mysql最近一次的死锁信息
show engine innodb status
显示如下
发现事务1在等待一个锁
事务2也在等待一个锁
而且事物2持有了事物1需要的锁
关于锁的描述，出现了 lock_mode，gap before rec ， insert intention 等字眼，看不懂说明了什么？说明我关于mysql的锁相关的知识储备还不够 。那就开始调查mysql的锁相关知识 。
通过搜索引擎 ，
锁的持有兼容程度如下表
那么再回到死锁日志，可以知道 :
事务1正在获取插入意向锁
事务2正在获取插入意向锁，持有排他gap锁
再看我们上面的锁兼容表格，可以知道， gap lock和insert intention lock是不兼容的
那么就可以推断出： 事务1持有gap lock，等待事务2的insert intention lock释放；事务2持有gap lock，等待事务1的insert intention lock释放，从而导致死锁 。
那么新的问题就来了，事务1的intention lock 为什么会和事务2的gap lock 有交集，或者说 ， 事务1要插入的数据的位置为什么会被事务2给锁?。?
让我回顾一下gap lock的定义：
间隙锁 ， 锁定一个范围，但不包括记录本身 。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
那为什么是gap lock，gap lock到底是基于什么逻辑锁的记录？发现自己相关的知识储备还不够 。那就开始调查 。
调查后发现，当当前索引是一个 普通索引 的时候，会加一个gap lock来防止幻读，此gap lock 会锁住一个左开右闭的区间 。假设索引为xx_idx（xx_id），数据分布为1,4,6,8,12，当更新xx_id=9的时候，这个时候gap lock的锁定记录区间就是(8，12]，也就是锁住了xxid in (9,10,11,12)的数据，当有其他事务要插入xxid in (9,10,11,12)的数据时，就会处于等待获取锁的状态 。
ps：当前索引不是普通索引 ， 而且是唯一索引等其他情况，请参考下面资料
MySQL 加锁处理分析
回到我自己的案例中，重新屡一下事务1的执行过程：
因为普通索引
KEY hotel_date_idx ( hotel_id , rate_date )
的关系 这段sql会获取一个gap lock，范围(2,11111]
这段sql会获取一个insert intention lock （waiting）
再看事务2的执行过程
因为普通索引
KEY hotel_date_idx ( hotel_id , rate_date )
的关系 这段sql也会获取一个gap lock，范围也是(2,11111]（根据前面的知识，gap lock之间会互相兼容，可以一起持有锁的）
这段sql也会获取一个insert intention lock （waiting）
看到这里，基本也就破案了 。因为普通索引的关系 ， 事务1和事务2的gap lock的覆盖范围太广，导致其他事务无法插入数据 。
重新梳理一下：
所以从结果来看，一堆事务被回滚 ， 只有10007数据被更新成功
gap lock 导致了并发处理的死锁
在mysql默认的事务隔离级别（repeatable read）下，无法避免这种情况 。只能把并发处理改成同步处理 。或者从业务层面做处理 。
共享锁、排他锁、意向共享、意向排他
record lock、gap lock、next key lock、insert intention lock
show engine innodb status
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锁表次数多,网站无法访问【Mysql锁冲突怎么解决 mysql上锁】网站无法访问解决方法如下Mysql锁冲突怎么解决：
1、点击电脑右下角网络标识Mysql锁冲突怎么解决 ， “网络与共享中心” 。
2、然后“internet选项”Mysql锁冲突怎么解决 ， “连接”Mysql锁冲突怎么解决，“局域网设置” ， 把“代理服务器”前面Mysql锁冲突怎么解决的勾选取消即可 。
关于Mysql锁冲突怎么解决和mysql上锁的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：关于农村家里怎么加网线路由器的信息

    下一篇：Python安装界面怎么选，python安装好的界面