精华内容
下载资源
问答
  • MySQL 死锁产生原因及解决方法

    千次阅读 2021-02-01 09:09:52
    一、Mysql 锁类型加锁分析1、锁类型介绍:MySQL有三种锁的级别:页级、表级、行级。?表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。?行级锁:开销大,加锁慢;会出现死锁...

    一、Mysql 锁类型和加锁分析

    1、锁类型介绍:

    MySQL有三种锁的级别:页级、表级、行级。

    ?表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

    ?行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    ?页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

    算法:

    ?next KeyLocks锁,同时锁住记录(数据),并且锁住记录前面的Gap

    ?Gap锁,不锁记录,仅仅记录前面的Gap

    ?Recordlock锁(锁数据,不锁Gap)

    所以其实 Next-KeyLocks=Gap锁+ Recordlock锁

    二、死锁产生原因和示例

    1、产生原因:

    所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

    ?死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。

    ?那么对应的解决死锁问题的关键就是:让不同的session加锁有次序

    2、产生示例:

    案例一

    需求:将投资的钱拆成几份随机分配给借款人。

    起初业务程序思路是这样的:

    展开全文
  • mysql死锁问题的解决

    2021-02-05 15:10:47
    常常在使用Navicat或者JDBC插入数据到mysql时,会遇见中文乱码问题,这时怎么解决问题呢? 请看下面的总结: 1、建表时没有指定统一的编码格式导致 错误的建表方式: CREATE TABLE database_user( ID varchar(40) , ...

    常常在使用Navicat或者JDBC插入数据到mysql时,会遇见中文乱码问题,这时怎么解决问题呢? 请看下面的总结: 1、建表时没有指定统一的编码格式导致      错误的建表方式:     CREATE TABLE database_user(          ID varchar(40) ,          UserID varcha

    见官方文档:http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb-lock-modes

    可直接在mysql命令行执行:show engine innodb status\G;

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

    然后

    show processlist;

    kill processid;

    另外可以打开慢查询日志,linux下打开需在my.cnf的[mysqld]里面加上以下内容:

    long_query_time = 2

    log-slow-queries = /usr/local/mysql/mysql-slow.log

    今天安装mysql5.7结果发现安装进度始终停止在start service 这里,过了一会儿就弹出个弹出框说是时间太久了什么玩意儿的,于是乎开始各种百度、谷歌爬楼,整了半天依然不行,网上说的什么卸载、删除文件夹、清理注册表,表示通通试过了,均无效,防火

    最近在项目开发过程中,碰到了数据库的死锁问题,在解决问题的过程中,加深了对MySQLInnoDB引擎锁机制的理解。

    我们使用Show innodb status检查引擎状态时,发现了死锁问题:

    *** (1) TRANSACTION:

    TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS threadid 278546 starting index read

    mysql tables in use 1, locked 1

    LOCK WAIT 3 lock struct(s), heap size 320

    MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rowsfor update

    update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () whereSTATUS_ID=1061 and MON_TIME

    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

    RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY`of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locksrec but not gap waiting

    Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compactformat; info bits 0

    0: len 8; hex 800000000097629c; asc     b ;; 1: len 6; hex00002866eaee; asc  (f  ;; 2: len7; hex 00000d40040110; asc    @  ;; 3: len 8; hex 80000000000050b2;asc      P ;; 4:len 8; hex 800000000000502a; asc      P*;; 5: len 8; hex8000000000005426; asc      T&;; 6: len 8; hex800012412c66d29c; asc    A,f  ;; 7: len 23; hex75706c6f6164666972652e63 6f6d2f6 8616e642e706870; asc xxx.com/;; 8:len 8; hex 800000000000042b; asc        +;; 9: len4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24;asc      N$;;

    *** (2) TRANSACTION:

    TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS threadid 344086 updating or deleting, thread declared inside InnoDB499

    mysql tables in use 1, locked 1

    3 lock struct(s), heap size 320, undo log entries 1

    MySQL thread id 84, query id 162348739 dcnet03 dcnetUpdating

    update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in(9921180)

    *** (2) HOLDS THE LOCK(S):

    RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY`of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locksrec but not gap

    Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compactformat; info bits 0

    0: len 8; hex 800000000097629c; asc      b ;; 1: len 6; hex00002866eaee; asc  (f  ;; 2: len7; hex 00000d40040110; asc    @  ;; 3: len 8; hex 80000000000050b2;asc      P ;; 4:len 8; hex 800000000000502a; asc      P*;; 5: len 8; hex8000000000005426; asc      T&;; 6: len 8; hex800012412c66d29c; asc    A,f  ;; 7: len 23; hex75706c6f6164666972652e63 6f6d2f6 8616e642e706870; ascuploadfire.com/hand.php;; 8: len 8; hex 800000000000042b;asc        +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len8; hex 8000000000004e24; asc      N$;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

    RECORD LOCKS space id 0 page no 843102 n bits 600 index`KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0677833454 lock_mode X locks rec but not gap waiting

    Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compactformat; info bits 0

    0: len 8; hex 8000000000000425; asc        %;; 1: len8; hex 800012412c66d29c; asc    A,f  ;; 2: len 8; hex 800000000097629c;asc      b;;

    *** WE ROLL BACK TRANSACTION (1)

    该死锁问题涉及TSK_TASK表,该表用于保存系统监测任务,相关字段及索引如 下:

    ID:主键;

    MON_TIME:监测时间;

    STATUS_ID:任务状态;

    索 引:KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

    经分析,涉及的两条语句应该不会 涉及相同的TSK_TASK记录,那为什么会造成死锁呢?

    查询MySQL官网文档,发现这跟MySQL的索引机制有关。MySQL的InnoDB引擎是行级锁,我原来的理解是直接对记录进行锁定,实际上不是这样的,其要点如下:

    不是对记录进行锁定,而是对索引 进行锁定;

    在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking;

    如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID>10000会锁定所有主键大于等于1000的所有记录,在该语句完成之前,你就不能对主键等于10000的记录进行操作;

    当 非簇索引(non-cluster index)记录被锁定时,相关的簇索引(clusterindex)记录也需要被锁定才能完成相应的操作。

    再 分析一下发生问题的两条SQL语句,就不难找到问题所在了:

    当“update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () whereSTATUS_ID=1061 and MON_TIME

    假设“update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where IDin (9921180)”几乎同时执行时,本语句首先锁定簇索引(主键),由于需要更新STATUS_ID的值,所以还需要锁定KEY_TSKTASK_MONTIME2的某些索引记录。

    这样第一条语句锁定了KEY_TSKTASK_MONTIME2的记录,等待主键索引,而第二条语句则锁定了主键索引记录,而等待KEY_TSKTASK_MONTIME2的记录,这样死锁就产生了。

    我们通过拆分 第一条语句解决了死锁问题:即先查出符合条件的ID:select ID from TSK_TASK whereSTATUS_ID=1061 and MON_TIME < date_sub(now(),INTERVAL 30 minute);然后再更新状态:update TSK_TASK set STATUS_ID=1064where ID in (….)

    这样就不会产生索引的竞争问题,死锁问题就 解决了

    展开全文
  • Mysql 锁类型一、锁类型介绍:MySQL有三种锁的级别:页级、表级、行级。表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,...

    阅读本文大概需要 8.5 分钟。

    Mysql 锁类型

    一、锁类型介绍:

    MySQL有三种锁的级别:页级、表级、行级。表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

    行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

    算法:next KeyLocks锁,同时锁住记录(数据),并且锁住记录前面的Gap

    Gap锁,不锁记录,仅仅记录前面的Gap

    Recordlock锁(锁数据,不锁Gap)

    所以其实 Next-KeyLocks=Gap锁+ Recordlock锁

    二、死锁产生原因和示例

    1、产生原因:

    所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。

    表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。那么对应的解决死锁问题的关键就是:让不同的session加锁有次序

    2、产生示例:

    案例一

    需求:将投资的钱拆成几份随机分配给借款人。起初业务程序思路是这样的:投资人投资后,将金额随机分为几份,然后随机从借款人表里面选几个,然后通过一条条select for update 去更新借款人表里面的余额等。

    例如两个用户同时投资,A用户金额随机分为2份,分给借款人1,2B用户金额随机分为2份,分给借款人2,1由于加锁的顺序不一样,死锁当然很快就出现了。对于这个问题的改进很简单,直接把所有分配到的借款人直接一次锁住就行了。

    Select * from xxx where id in (xx,xx,xx) for update

    在in里面的列表值mysql是会自动从小到大排序,加锁也是一条条从小到大加的锁例如(以下会话id为主键):Session1:

    mysql> select * from t3 where id in (8,9) for update;

    +----+--------+------+---------------------+

    | id | course | name | ctime |

    +----+--------+------+---------------------+

    | 8 | WA | f | 2016-03-02 11:36:30 |

    | 9 | JX | f | 2016-03-01 11:36:30 |

    +----+--------+------+---------------------+

    rows in set (0.04 sec)

    Session2:

    select * from t3 where id in (10,8,5) for update;

    锁等待中……其实这个时候id=10这条记录没有被锁住的,但id=5的记录已经被锁住了,锁的等待在id=8的这里 不信请看Session3:

    mysql> select * from t3 where id=5 for update;

    锁等待中Session4:

    mysql> select * from t3 where id=10 for update;

    +----+--------+------+---------------------+

    | id | course | name | ctime |

    +----+--------+------+---------------------+

    | 10 | JB | g | 2016-03-10 11:45:05 |

    +----+--------+------+---------------------+

    row in set (0.00 sec)

    在其它session中id=5是加不了锁的,但是id=10是可以加上锁的。

    案例二

    在开发中,经常会做这类的判断需求:根据字段值查询(有索引),如果不存在,则插入;否则更新。以id为主键为例,目前还没有id=22的行Session1:

    select * from t3 where id=22 for update;

    Empty set (0.00 sec)

    session2:

    select * from t3 where id=23 for update;

    Empty set (0.00 sec)

    Session1:

    insert into t3 values(22,'ac','a',now());

    锁等待中……Session2:

    insert into t3 values(23,'bc','b',now());

    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction当对存在的行进行锁的时候(主键),mysql就只有行锁。

    当对未存在的行进行锁的时候(即使条件为主键),mysql是会锁住一段范围(有gap锁)锁住的范围为:(无穷小或小于表中锁住id的最大值,无穷大或大于表中锁住id的最小值)如:如果表中目前有已有的id为(11 , 12)那么就锁住(12,无穷大)如果表中目前已有的id为(11 , 30)那么就锁住(11,30)对于这种死锁的解决办法是:

    insert into t3(xx,xx) on duplicate key update `xx`='XX';

    用mysql特有的语法来解决此问题。因为insert语句对于主键来说,插入的行不管有没有存在,都会只有行锁

    案例三

    mysql> select * from t3 where id=9 for update;

    +----+--------+------+---------------------+

    | id | course | name | ctime |

    +----+--------+------+---------------------+

    | 9 | JX | f | 2016-03-01 11:36:30 |

    +----+--------+------+---------------------+

    row in set (0.00 sec)

    Session2:

    mysql> select * from t3 where id<20 for update;

    锁等待中Session1:

    mysql> insert into t3 values(7,'ae','a',now());

    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction这个跟案例一其它是差不多的情况,只是session1不按常理出牌了,Session2在等待Session1的id=9的锁,session2又持了1到8的锁(注意9到19的范围并没有被session2锁住),最后,session1在插入新行时又得等待session2,故死锁发生了。

    这种一般是在业务需求中基本不会出现,因为你锁住了id=9,却又想插入id=7的行,这就有点跳了,当然肯定也有解决的方法,那就是重理业务需求,避免这样的写法。

    案例四

    一般的情况,两个session分别通过一个sql持有一把锁,然后互相访问对方加锁的数据产生死锁。

    案例五

    两个单条的sql语句涉及到的加锁数据相同,但是加锁顺序不同,导致了死锁。

    案例六

    死锁场景如下:表结构:

    CREATE TABLE dltask (

    id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT ‘auto id’,

    a varchar(30) NOT NULL COMMENT ‘uniq.a’,

    b varchar(30) NOT NULL COMMENT ‘uniq.b’,

    c varchar(30) NOT NULL COMMENT ‘uniq.c’,

    x varchar(30) NOT NULL COMMENT ‘data’,

    PRIMARY KEY (id),

    UNIQUE KEY uniq_a_b_c (a, b, c)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’deadlock test’;

    a,b,c三列,组合成一个唯一索引,主键索引为id列。事务隔离级别:RR (Repeatable Read)每个事务只有一条SQL:

    delete from dltask where a=? and b=? and c=?;

    SQL的执行计划:

    死锁日志:

    众所周知,InnoDB上删除一条记录,并不是真正意义上的物理删除,而是将记录标识为删除状态。(注:这些标识为删除状态的记录,后续会由后台的Purge操作进行回收,物理删除。但是,删除状态的记录会在索引中存放一段时间。) 在RR隔离级别下,唯一索引上满足查询条件,但是却是删除记录,如何加锁?

    InnoDB在此处的处理策略与前两种策略均不相同,或者说是前两种策略的组合:对于满足条件的删除记录,InnoDB会在记录上加next key lock X(对记录本身加X锁,同时锁住记录前的GAP,防止新的满足条件的记录插入。) Unique查询,三种情况,对应三种加锁策略,总结如下:此处,我们看到了next key锁,是否很眼熟?对了,前面死锁中事务1,事务2处于等待状态的锁,均为next key锁。

    明白了这三个加锁策略,其实构造一定的并发场景,死锁的原因已经呼之欲出。

    但是,还有一个前提策略需要介绍,那就是InnoDB内部采用的死锁预防策略。找到满足条件的记录,并且记录有效,则对记录加X锁,No Gap锁(lock_mode X locks rec but not gap);

    找到满足条件的记录,但是记录无效(标识为删除的记录),则对记录加next key锁(同时锁住记录本身,以及记录之前的Gap:lock_mode X);

    未找到满足条件的记录,则对第一个不满足条件的记录加Gap锁,保证没有满足条件的记录插入(locks gap before rec);

    死锁预防策略

    InnoDB引擎内部(或者说是所有的数据库内部),有多种锁类型:事务锁(行锁、表锁),Mutex(保护内部的共享变量操作)、RWLock(又称之为Latch,保护内部的页面读取与修改)。InnoDB每个页面为16K,读取一个页面时,需要对页面加S锁,更新一个页面时,需要对页面加上X锁。

    任何情况下,操作一个页面,都会对页面加锁,页面锁加上之后,页面内存储的索引记录才不会被并发修改。因此,为了修改一条记录,InnoDB内部如何处理:根据给定的查询条件,找到对应的记录所在页面;

    对页面加上X锁(RWLock),然后在页面内寻找满足条件的记录;

    在持有页面锁的情况下,对满足条件的记录加事务锁(行锁:根据记录是否满足查询条件,记录是否已经被删除,分别对应于上面提到的3种加锁策略之一);

    相对于事务锁,页面锁是一个短期持有的锁,而事务锁(行锁、表锁)是长期持有的锁。因此,为了防止页面锁与事务锁之间产生死锁。

    InnoDB做了死锁预防的策略:持有事务锁(行锁、表锁),可以等待获取页面锁;但反之,持有页面锁,不能等待持有事务锁。根据死锁预防策略,在持有页面锁,加行锁的时候,如果行锁需要等待。

    则释放页面锁,然后等待行锁。此时,行锁获取没有任何锁保护,因此加上行锁之后,记录可能已经被并发修改。

    因此,此时要重新加回页面锁,重新判断记录的状态,重新在页面锁的保护下,对记录加锁。如果此时记录未被并发修改,那么第二次加锁能够很快完成,因为已经持有了相同模式的锁。

    但是,如果记录已经被并发修改,那么,就有可能导致本文前面提到的死锁问题。以上的InnoDB死锁预防处理逻辑,对应的函数,是row0sel.c::row_search_for_mysql()。感兴趣的朋友,可以跟踪调试下这个函数的处理流程,很复杂,但是集中了InnoDB的精髓。

    剖析死锁的成因

    做了这么多铺垫,有了Delete操作的3种加锁逻辑、InnoDB的死锁预防策略等准备知识之后,再回过头来分析本文最初提到的死锁问题,就会手到拈来,事半而功倍。首先,假设dltask中只有一条记录:(1, ‘a’, ‘b’, ‘c’, ‘data’)。三个并发事务,同时执行以下的这条SQL:

    delete from dltask where a=’a’ and b=’b’ and c=’c’;

    并且产生了以下的并发执行逻辑,就会产生死锁:

    上面分析的这个并发流程,完整展现了死锁日志中的死锁产生的原因。其实,根据事务1步骤6,与事务0步骤3/4之间的顺序不同,死锁日志中还有可能产生另外一种情况,那就是事务1等待的锁模式为记录上的X锁 + No Gap锁(lock_mode X locks rec but not gap waiting)。这第二种情况,也是”润洁”同学给出的死锁用例中,使用MySQL 5.6.15版本测试出来的死锁产生的原因。此类死锁,产生的几个前提:Delete操作,针对的是唯一索引上的等值查询的删除;(范围下的删除,也会产生死锁,但是死锁的场景,跟本文分析的场景,有所不同)

    至少有3个(或以上)的并发删除操作;

    并发删除操作,有可能删除到同一条记录,并且保证删除的记录一定存在;

    事务的隔离级别设置为Repeatable Read,同时未设置innodb_locks_unsafe_for_binlog参数(此参数默认为FALSE);(Read Committed隔离级别,由于不会加Gap锁,不会有next key,因此也不会产生死锁)

    使用的是InnoDB存储引擎;(废话!MyISAM引擎根本就没有行锁)

    展开全文
  • MySQL死锁解决方案

    2021-01-18 19:17:51
    一、MySQL锁类型1.MySQL常用存储引擎的锁机制MyISAMMEMORY采用表级锁(table-level locking)BDB采用页面锁(page-level locking)或表级锁,默认为页面锁InnoDB支持行级锁(row-level locking)表级锁,默认为行级锁2....

    一、MySQL锁类型

    1. MySQL常用存储引擎的锁机制

    MyISAM和MEMORY采用表级锁(table-level locking)

    BDB采用页面锁(page-level locking)或表级锁,默认为页面锁

    InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

    2.各种锁特点

    表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

    行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高

    页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

    3.各种锁的适用场景

    表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用

    行级锁则更适合于有大量按索引条件并发更新数据,同时又有并发查询的应用,如一些在线事务处理系统。

    二、 MySQL死锁产生原因

    所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

    死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。

    那么对应的解决死锁问题的关键就是:让不同的session加锁有次序。

    三、 MySQL死锁举例分析

    案例一:

    在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking。

    例如,一个表db.tab_test,结构如下:

    id:主键;state:状态;time:时间;索引:idx_1 (state, time)

    出现死锁日志如下:

    ***(1) TRANSACTION: TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index readmysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for updateupdate tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) (任务1的sql语句) ***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任务1等待的索引记录) RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;; *** (2) TRANSACTION: TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任务2的sql语句) *** (2) HOLDS THE LOCK(S): (任务2已获得的锁) RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任务2等待的锁) RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting  Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;; *** WE ROLL BACK TRANSACTION (1) (回滚了任务1,以解除死锁)

    原因分析:

    当“update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)”执行时,MySQL会使用idx_1索引,因此首先锁定相关的索引记录,因为idx_1是非主键索引,为执行该语句,MySQL还会锁定主键索引。

    假设“update tab_test set state=1067,time=now () where id in (9921180)”几乎同时执行时,本语句首先锁定主键索引,由于需要更新state的值,所以还需要锁定idx_1的某些索引记录。

    这样第一条语句锁定了idx_1的记录,等待主键索引,而第二条语句则锁定了主键索引记录,而等待idx_1的记录,这样死锁就产生了。

    解决办法

    拆分第一条sql,先查出符合条件的主键值,再按照主键更新记录:

    select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute); update tab_test state=1064,time=now() where id in(......);

    至此MySQL死锁问题得以解决!

    案例二:

    在开发中,经常会做这类的判断需求:根据字段值查询(有索引),如果不存在,则插入;否则更新。

    以id为主键为例,目前还没有id=22的行 Session1:select * from t3 where id=22 for update;Empty set (0.00 sec) session2:select * from t3 where id=23  for update;Empty set (0.00 sec) Session1:insert into t3 values(22,'ac','a',now());锁等待中…… Session2:insert into t3 values(23,'bc','b',now());ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

    当对存在的行进行锁的时候(主键),mysql就只有行锁。

    当对未存在的行进行锁的时候(即使条件为主键),mysql是会锁住一段范围(有gap锁)

    锁住的范围为:

    (无穷小或小于表中锁住id的最大值,无穷大或大于表中锁住id的最小值)

    如:如果表中目前有已有的id为(11 , 12)

    那么就锁住(12,无穷大)

    如果表中目前已有的id为(11 , 30)

    那么就锁住(11,30)

    对于这种死锁的解决办法是:

    insert into t3(xx,xx)

    on duplicate key update `xx`='XX';

    用mysql特有的语法来解决此问题。因为insert语句对于主键来说,插入的行不管有没有存在,都会只有行锁。

    案例三:

    c3aecf417d48264df5f6c72820b99d92.png

    一般的情况,两个session分别通过一个sql持有一把锁,然后互相访问对方加锁的数据产生死锁。

    案例四:

    e1daeb6f5cac5b4d26daf5dd6ab6fe62.png

    两个单条的sql语句涉及到的加锁数据相同,但是加锁顺序不同,导致了死锁。

    四、 MySQL死锁总结

    1.死锁预防策略

    InnoDB引擎内部(或者说是所有的数据库内部),有多种锁类型:事务锁(行锁、表锁),Mutex(保护内部的共享变量操作)、RWLock(又称之为Latch,保护内部的页面读取与修改)。

    InnoDB每个页面为16K,读取一个页面时,需要对页面加S锁,更新一个页面时,需要对页面加上X锁。任何情况下,操作一个页面,都会对页面加锁,页面锁加上之后,页面内存储的索引记录才不会被并发修改。

    因此,为了修改一条记录,InnoDB内部如何处理:根据给定的查询条件,找到对应的记录所在页面;对页面加上X锁(RWLock),然后在页面内寻找满足条件的记录;在持有页面锁的情况下,对满足条件的记录加事务锁(行锁:

    根据记录是否满足查询条件,记录是否已经被删除,分别对应于上面提到的3种加锁策略之一);

    死锁预防策略:相对于事务锁,页面锁是一个短期持有的锁,而事务锁(行锁、表锁)是长期持有的锁。因此,为了防止页面锁与事务锁之间产生死锁。

    InnoDB做了死锁预防的策略:持有事务锁(行锁、表锁),可以等待获取页面锁;但反之,持有页面锁,不能等待持有事务锁。根据死锁预防策略,在持有页面锁,加行锁的时候,如果行锁需要等待。则释放页面锁,然后等待行锁。此时,行锁获取没有任何锁保护,因此加上行锁之后,记录可能已经被并发修改。

    因此,此时要重新加回页面锁,重新判断记录的状态,重新在页面锁的保护下,对记录加锁。如果此时记录未被并发修改,那么第二次加锁能够很快完成,因为已经持有了相同模式的锁。

    但是,如果记录已经被并发修改,那么,就有可能导致本文前面提到的死锁问题。以上的InnoDB死锁预防处理逻辑,对应的函数,是row0sel.c::row_search_for_mysql()。

    感兴趣的朋友,可以跟踪调试下这个函数的处理流程,很复杂,但是集中了InnoDB的精髓。

    2.死锁产生的前提

    Delete操作,针对的是唯一索引上的等值查询的删除;(范围下的删除,也会产生死锁,但是死锁的场景,跟本文分析的场景,有所不同)。

    少有3个(或以上)的并发删除操作;

    并发删除操作,有可能删除到同一条记录,并且保证删除的记录一定存在;

    事务的隔离级别设置为Repeatable Read,同时未设置innodb_locks_unsafe_for_binlog参数(此参数默认为FALSE);(Read

    Committed隔离级别,由于不会加Gap锁,不会有next

    key,因此也不会产生死锁);

    使用的是InnoDB存储引擎;(废话!MyISAM引擎根本就没有行锁)。

    展开全文
  • MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁解决方法详解如下:1、MySQL常用存储引擎的锁机制MyISAMMEMORY采用表级锁(table-level locking)BDB采用页面锁(page-level locking)或表级锁,...
  • mysql出现死锁的原因及解决方案发布时间:2020-06-04 16:35:40来源:51CTO阅读:418作者:三月本文主要给大家介绍mysql出现死锁的原因及解决方案,文章内容都是笔者用心摘选编辑的,具有一定的针对性,对大家的...
  • 此时称系统处于死锁状态或系统产生死锁,这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力协助下,永远分配不到必需的资源而无法继续运行,这就产生...
  • 这种死锁异常一般要在特定时间特定数据特定业务操作才会复现,并且分析解决时还需要了解 MySQL 锁冲突相关知识,所以一般遇到这些偶尔出现的死锁异常,往往一时没有头绪,不好处理。本篇文章会讲解一下如果线上...
  • 当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。多个事务同时锁定同一个资源时,也会产生死锁。例如,设想下面两个事务同时处理StockPrice表: 事务1 START TRANSACTION; UPDATE StockPrice SET close = ...
  • Mysql死锁

    2021-01-28 05:00:53
    Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction 这样的日志 ,网上看了很多文章 发现这篇文章 ...
  • MySQL死锁问题的产生和解决 一、死锁产生 一个用户A 访问表A(锁住了表A),然后又访问表B;另一个用户B 访问表B(锁住了表B),然后企图访问表A;这时用户A由于用户B已经锁住表B,它必须等待用户B释放表B才能继续,...
  • MySQL死锁检测回滚

    2021-01-19 17:17:13
    重新温习下受益良多,其中死锁的判定规则,其实我们早在5年前解决秒杀场景的第一个版本就已经涉及,并且思路很相似,如果有时间的话,我会补充上一批文章说下如果关闭死锁检测对单行更新能提升多少性能。...
  • mysql死锁模拟案例、死锁的发生条件、怎么避免死锁、查看死锁日志以及解决方案
  • mysql死锁怎么造成的

    2021-01-18 19:38:39
    原标题:mysql死锁怎么造成的MySQL的三种锁:表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发...
  • 在多实例场景下 MySQL Server hang 住,无法测试下去,原生版本不存在这个问题,而新版本上出现了这个问题,不禁心头一颤,心中不禁感到奇怪,还好现场环境还在,为排查问题提供了一个好的环境,随即便投入到紧张的...
  • mysql死锁日志: 本地复现控制台日志: 死锁 死锁(Deadlock)所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统...
  • 在上一篇文章《锁的类型以及加锁原理》主要总结了 MySQL 锁的类型模式以及基本的加锁原理,今天我们就从原理走向实战,分析常见 SQL 语句的加锁场景。了解了这几种场景,相信小伙伴们也能举一反三,灵活地分析真实...
  • 解决MYSQL死锁之路

    2021-01-18 18:51:03
    我遇到死锁的处理方式无非就是判断返回是的状态是否为死锁或者失败或者掉线,失败则直接抛出异常触发事务,进行回滚。否则重新尝试业务逻辑提交,成功则进行业务流程失败则抛出异常进行回滚。当然我用乐观锁多一点,...
  • mysql死锁问题分析

    2021-01-20 02:34:26
    图4 聚簇索引二级索引下面分析下索引锁的关系。1)delete from msg where id=2;由于id是主键,因此直接锁住整行记录即可。 图52)delete from msg where token=’ cvs’;由于token...
  • 最近在做YMU(website monitoring)项目开发过程,碰到了数据库的死锁问题,在解决问题的过程中,加深了对MySQL InnoDB引擎锁机制的理解。我们使用Show innodb status检查引擎状态时,发现了死锁问题:*** (1) ...
  • 每个事务有多个update操作一个insert操作(都在同一张表)。DDL(删除了一些不必要的细节)默认隔离级别:Repeatable ReadCREATE TABLE `list_rate` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`hotel_id` bigint(20) ...
  • # 事务是什么特性:原子性、一致性、隔离性持久性...三种粒度锁的特性(逐步提高等级)表级锁 :限制其它用户修改行级锁 :多版本的并发控制死锁产生条件 MySQL 自动处理机制# 事物锁的实际应用01. 本章课程...
  • mysql 死锁清理

    2021-04-20 04:14:41
    MySQL死锁导致无法查询解决最近在维护数据库时,解析数据时候,数据一直不能入库。原因知道,是因为MySQL的事务产生了死锁,前几次我直接重启MySQL。www.2cto.com最近发现频繁的出现。所以找到一种方法。SELECT * ...
  • 二、 死锁产生的四个必要条件•互斥条件:指进程对所分配到的资源进行排它性使用,即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源,则请求者只能等待,直至占有资源的进程用毕释放•...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 31,434
精华内容 12,573
关键字:

mysql死锁的产生和解决

mysql 订阅