精华内容
下载资源
问答
  • 主要给大家介绍了关于mysql出现报错:Deadlock found when trying to get lock; try restarting transaction的解决方法,文中通过示例代码介绍的非常详细,对大家具有一定的参考学习价值,需要的朋友们下面来一起看...
  • 在MySql插入数据的时候遇到 Deadlock found when trying to get lock 错误,日志如下 SequelizeDatabaseError: Deadlock found when trying to get lock; try restarting transaction at Query.formatError (/...

    在MySql插入数据的时候遇到 Deadlock found when trying to get lock 错误,日志如下

    SequelizeDatabaseError: Deadlock found when trying to get lock; try restarting transaction
        at Query.formatError (/www/server/node_modules/sequelize/lib/dialects/mysql/query.js:244:16)
        at Execute.handler [as onResult] (/www/server/node_modules/sequelize/lib/dialects/mysql/query.js:51:23)
        at Execute.execute (/www/server/node_modules/mysql2/lib/commands/command.js:30:14)
        at Connection.handlePacket (/www/server/node_modules/mysql2/lib/connection.js:417:32)
        at PacketParser.Connection.packetParser.p [as onPacket] (/www/server/node_modules/mysql2/lib/connection.js:75:12)
        at PacketParser.executeStart (/www/server/node_modules/mysql2/lib/packet_parser.js:75:16)
        at Socket.Connection.stream.on.data (/www/server/node_modules/mysql2/lib/connection.js:82:25)
        at Socket.emit (events.js:182:13)
        at addChunk (_stream_readable.js:283:12)
        at readableAddChunk (_stream_readable.js:264:11)
        at Socket.Readable.push (_stream_readable.js:219:10)
        at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
      name: 'SequelizeDatabaseError',
      parent:
       { Error: Deadlock found when trying to get lock; try restarting transaction
           at Packet.asError (/www/server/node_modules/mysql2/lib/packets/packet.js:712:17)
           at Execute.execute (/www/server/node_modules/mysql2/lib/commands/command.js:28:26)
           at Connection.handlePacket (/www/server/node_modules/mysql2/lib/connection.js:417:32)
           at PacketParser.Connection.packetParser.p [as onPacket] (/www/server/node_modules/mysql2/lib/connection.js:75:12)
           at PacketParser.executeStart (/www/server/node_modules/mysql2/lib/packet_parser.js:75:16)
           at Socket.Connection.stream.on.data (/www/server/node_modules/mysql2/lib/connection.js:82:25)
           at Socket.emit (events.js:182:13)
           at addChunk (_stream_readable.js:283:12)
           at readableAddChunk (_stream_readable.js:264:11)
           at Socket.Readable.push (_stream_readable.js:219:10)
           at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
         code: 'ER_LOCK_DEADLOCK',
         errno: 1213,
         sqlState: '40001',
         sqlMessage:
          'Deadlock found when trying to get lock; try restarting transaction',
         sql:
          'INSERT INTO `order` (`id`,`code`,`user_id`,`device_id`,`device_type`,`device_connect_type`,`project_id`,`product`,`product_name`,`product_detail`,`status`,`pay_status`,`pay_channel`,`amount`,`points`,`balance`,`client_ip`,`duration`,`pay_return`,`prepay_id`,`mch_id`,`appid`,`created_at`,`updated_at`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);',
         parameters:
          [ 'H00120200919560410573',
            4142,
            1059,
            1,
            1,
            11,
            1,
            '加强洗',
            '{}',
            1,
            1,
            3,
            400,
            0,
            0,
            '119.39.248.1',
            50,
            '{"return_code":"SUCCESS","return_msg":"OK","appid":"wx81dfeec7c40fa364","mch_id":"1462697002","nonce_str":"MwNSsAJbjogN8gwk","result_code":"SUCCESS","prepay_id":"wx191256043595822d1dc2764b9342080000","trade_type":"JSAPI"}',
            'wx191256043595822d1dc2764b9342080000',
            '1462697002',
            'wx81dfeec7c40fa364',
            '2020-09-19 12:56:04',
            '2020-09-19 12:56:04' ] },
      original:
       { Error: Deadlock found when trying to get lock; try restarting transaction
           at Packet.asError (/www/server/node_modules/mysql2/lib/packets/packet.js:712:17)
           at Execute.execute (/www/server/node_modules/mysql2/lib/commands/command.js:28:26)
           at Connection.handlePacket (/www/server/node_modules/mysql2/lib/connection.js:417:32)
           at PacketParser.Connection.packetParser.p [as onPacket] (/www/server/node_modules/mysql2/lib/connection.js:75:12)
           at PacketParser.executeStart (/www/server/node_modules/mysql2/lib/packet_parser.js:75:16)
           at Socket.Connection.stream.on.data (/www/server/node_modules/mysql2/lib/connection.js:82:25)
           at Socket.emit (events.js:182:13)
           at addChunk (_stream_readable.js:283:12)
           at readableAddChunk (_stream_readable.js:264:11)
           at Socket.Readable.push (_stream_readable.js:219:10)
           at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
         code: 'ER_LOCK_DEADLOCK',
         errno: 1213,
         sqlState: '40001',
         sqlMessage:
          'Deadlock found when trying to get lock; try restarting transaction',
         sql:
          'INSERT INTO `order` (`id`,`code`,`user_id`,`device_id`,`device_type`,`device_connect_type`,`project_id`,`product`,`product_name`,`product_detail`,`status`,`pay_status`,`pay_channel`,`amount`,`points`,`balance`,`client_ip`,`duration`,`pay_return`,`prepay_id`,`mch_id`,`appid`,`created_at`,`updated_at`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);',
         parameters:
          [ 'H00120200919560410573',
            4142,
            1059,
            1,
            1,
            11,
            1,
            '加强洗',
            '{}',
            1,
            1,
            3,
            400,
            0,
            0,
            '119.39.248.1',
            50,
            '{"return_code":"SUCCESS","return_msg":"OK","appid":"wx81dfeec7c40fa364","mch_id":"1462697002","nonce_str":"MwNSsAJbjogN8gwk","result_code":"SUCCESS","prepay_id":"wx191256043595822d1dc2764b9342080000","trade_type":"JSAPI"}',
            'wx191256043595822d1dc2764b9342080000',
            '1462697002',
            'wx81dfeec7c40fa364',
            '2020-09-19 12:56:04',
            '2020-09-19 12:56:04' ] },
      sql:
       'INSERT INTO `order` (`id`,`code`,`user_id`,`device_id`,`device_type`,`device_connect_type`,`project_id`,`product`,`product_name`,`product_detail`,`status`,`pay_status`,`pay_channel`,`amount`,`points`,`balance`,`client_ip`,`duration`,`pay_return`,`prepay_id`,`mch_id`,`appid`,`created_at`,`updated_at`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);',
      parameters:
       [ 'H00120200919560410573',
         4142,
         1059,
         1,
         1,
         11,
         1,
         '加强洗',
         '{}',
         1,
         1,
         3,
         400,
         0,
         0,
         '119.39.248.1',
         50,
         '{"return_code":"SUCCESS","return_msg":"OK","appid":"wx81dfeec7c40fa364","mch_id":"1462697002","nonce_str":"MwNSsAJbjogN8gwk","result_code":"SUCCESS","prepay_id":"wx191256043595822d1dc2764b9342080000","trade_type":"JSAPI"}',
         'wx191256043595822d1dc2764b9342080000',
         '1462697002',
         'wx81dfeec7c40fa364',
         '2020-09-19 12:56:04',
         '2020-09-19 12:56:04' ] }

    Mysql语句死锁了,为什么会出现这种情况呢?

    出现Mysql死锁通常是两个Mysql客户端都请求更新数据,Update和Delete的时候,为啥Insert的时候也出现死锁呢?

    使用 show engine innodb status; 查一下发生死锁的语句,如下

    UPDATE语句是在实时更新iot设备状态和订单状态时用到的,属于高频调用,所以很容易遇到两条语句同时请求Mysql

    同时,这个UPDATE语句是个关联表更新,效率低,耗时较长

    于是对UPDATE语句对于逻辑进行修改,去掉关联查询,并移到频率较低的业务模块

    总结一下:

    1.会导致Mysql锁定的查询语句应当尽可能短小,缩短锁定时长;

    2.跨表关联更新效率低,具体原因待研究,应当避免使用;

    3.INSERT语句触发死锁的原因待查;(难道关联更新的UPDATE语句锁定了整个表?)

    一下是网上找到的相关资料

    记一个引起MYSQL死锁Deadlock found when trying to get lock; try restarting transaction的例子

    http://www.04007.cn/article/347.html

    MYSQL遇到Deadlock found when trying to get lock,解决方案

    https://blog.csdn.net/loophome/article/details/79867174

    展开全文
  • Deadlock found when trying to get lock; try restarting transaction update f_order set delivery_status = ?, version = ? + 1 where order_id = ? and version = ? and data_state = 0 查看表结构发现order_...

    最近线上项目里的一条update语句出现了报错


    Deadlock found when trying to get lock; try restarting transaction


    update f_order set delivery_status = ?, version = ? + 1 where order_id = ? and version = ? and data_state = 0
    

    查看表结构发现order_id 和 delivery_status 都是非主键索引
    在这里插入图片描述
    翻阅 资料后发现是更新非主键索引引起的死锁

    参考资料 https://blog.csdn.net/aesop_wubo/article/details/8286215

    原因:

    因为是读写库使用的INNODB引擎

    行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。

    这个update语句会执行以下步骤:

    1、由于用到了非主键索引,首先需要获取idx_1上的行级锁

    2、紧接着根据主键进行更新,所以需要获取主键上的行级锁;

    3、更新完毕后,提交,并释放所有锁。

    如果在步骤1和2之间突然插入一条语句:update user_item …where id=? and user_id=?,这条语句会先锁住主键索引,然后锁住idx_1。

    蛋疼的情况出现了,一条语句获取了idx_1上的锁,等待主键索引上的锁;另一条语句获取了主键上的锁,等待idx_1上的锁,这样就出现了死锁。

    总结 : 一条sql操作非主键索引 mysql会先锁住非主键索引再锁定主键索引

    解决方式:

    避免 update 索引字段的时候 , 用了另一个索引字段 where 去查询的情况发生
    换句话说 , 也就是set条件中和where条件中只能有一个索引 , 不然就会出现锁表的情况
    也可以根据不同的业务场景也可以修改需要更新字段的索引

    展开全文
  • 这是由于多个事务进行提交操作的时候容易发生死锁的情况,所以需要一个一个的进行操作 也就是说,对于一个事务,最好就commit一次,不要统一到一次提交;,虽然说这样会浪费一些时间,但是安全;...

    这是由于多个事务进行提交操作的时候容易发生死锁的情况,所以需要一个一个的进行操作

    也就是说,对于一个事务,最好就commit一次,不要统一到一次提交;,虽然说这样会浪费一些时间,但是安全;

    展开全文
  • 1213 - Deadlock found when trying to get lock; try restarting transaction [b]0.查看MySQL当前连接线程:[/b] mysql> show processlist; +---------+-----------------+-------------------+------+---------+---...
    MySQL 事务的学习整理:[url]http://blog.csdn.net/mchdba/article/details/12242685[/url]
    
    mysql事务处理用法与实例详解:[url]http://www.cnblogs.com/ymy124/p/3718439.html[/url]
    常见的表死锁情况及解决方法:[url]http://www.cnblogs.com/jeffry/p/6014881.html[/url]
    MySQL事务autocommit自动提交:[url]http://www.qttc.net/201208175.html[/url]
    MySql 死锁时的一种解决办法:[url]http://www.cnblogs.com/farb/p/MySqlDeadLockOneOfSolutions.html[/url]
    Mysql并发时经典常见的死锁原因及解决方法:[url]http://www.cnblogs.com/zejin2008/p/5262751.html[/url]
    mysql死锁几种情况的测试:[url]http://www.2cto.com/database/201605/507289.html[/url],
    [url]http://blog.csdn.net/aoerqileng/article/details/51354357[/url]
    Mysql中那些锁机制之InnoDB:[url]http://www.2cto.com/database/201508/429967.html[/url]
    InnoDB Record, Gap, and Next-Key Locks:[url]http://www.cnblogs.com/zemliu/p/3503496.html[/url]

    [b]准备工作:[/b]
    建表,初始化数据,

    SET FOREIGN_KEY_CHECKS=0;

    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
    `id` int(11) NOT NULL auto_increment COMMENT 'id',
    `name` varchar(10) default NULL,
    `age` int(11) default NULL,
    `registerTime` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES ('1', 'jack', '23', null);
    INSERT INTO `user` VALUES ('2', 'mark', '67', null);
    INSERT INTO `user` VALUES ('3', 'donald', null, '2017-06-13 16:23:23');

    [b]模拟死锁:[/b]
    开启会话A,开启一个事务
    Session A:
    mysql> begin;
    Query OK, 0 rows affected

    mysql> select * from user where id=1 lock in share mode;;
    +----+------+-----+--------------+
    | id | name | age | registerTime |
    +----+------+-----+--------------+
    | 1 | jack | 23 | NULL |
    +----+------+-----+--------------+
    1 row in set


    开启会话B,删除用户id为1的用户
    Session B:
    mysql> delete from user where id = 1;


    回到会话A,删除用户id为1的用户
    Session A:
    mysql> mysql> delete from user where id = 1;
    1213 - Deadlock found when trying to get lock; try restarting transaction



    [b]0.查看MySQL当前连接线程:[/b]

    mysql> show processlist;
    +---------+-----------------+-------------------+------+---------+---------+------------------------+------------------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +---------+-----------------+-------------------+------+---------+---------+------------------------+------------------------------+
    | 1 | event_scheduler | localhost | NULL | Daemon | 3031152 | Waiting on empty queue | NULL |
    | 1019932 | donald | 192.168.31.153:15217 | NULL | Sleep | 1872 | | NULL |
    | 1019933 | donald | 192.168.31.153:15218 | test | Sleep | 188 | | NULL |
    | 1020542 | donald | 192.168.31.153:16735 | test | Sleep | 179 | | NULL |
    | 1020543 | donald | 192.168.31.153:16748 | test | Sleep | 14 | | NULL |
    | 1020544 | donald | 192.168.31.153:16751 | test | Query | 72 | updating | delete from user where id =1 |
    | 1020545 | donald | 192.168.31.153:16753 | test | Query | 0 | init | show processlist |
    +---------+-----------------+-------------------+------+---------+---------+------------------------+------------------------------+
    7 rows in set



    mysql>
    [b]1.查看引擎日志分析死锁的原因:[/b]
    show engine innodb status\G;

    查看死锁信息

    | InnoDB |      |
    =====================================
    2017-07-18 18:25:02 650ceb70 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 6 seconds
    -----------------
    BACKGROUND THREAD 后台线程
    -----------------
    srv_master_thread loops: 530 srv_active, 0 srv_shutdown, 3056084 srv_idle
    srv_master_thread log flush and writes: 3027072
    ----------
    SEMAPHORES 信号量
    ----------
    OS WAIT ARRAY INFO: reservation count 1059
    OS WAIT ARRAY INFO: signal count 1047
    Mutex spin waits 1035, rounds 8167, OS waits 165
    RW-shared spins 870, rounds 26073, OS waits 867
    RW-excl spins 33, rounds 1021, OS waits 25
    Spin rounds per wait: 7.89 mutex, 29.97 RW-shared, 30.94 RW-excl
    ------------------------
    LATEST DETECTED DEADLOCK 上次探测到死锁的状态
    ------------------------
    2017-07-18 18:16:22 698d9b70
    *** (1) TRANSACTION:事务1035410,MySQL事务线程1020087
    TRANSACTION 1035410, ACTIVE 73 sec starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 312, 1 row lock(s)
    MySQL thread id 1020087, OS thread handle 0x69879b70, query id 195420 192.168.31.153 donald updating
    delete from user where id = 1
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:等待锁行级锁RECORD LOCKS,X锁,
    RECORD LOCKS space id 7405 page no 3 n bits 72 index `PRIMARY` of table `test`.`user`
    trx id 1035410 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
    0: len 4; hex 80000001; asc ;;
    1: len 6; hex 0000000fcc91; asc ;;
    2: len 7; hex 68000080210a23; asc h ! #;;
    3: len 4; hex 6a61636b; asc jack;;
    4: len 4; hex 80000017; asc ;;
    5: SQL NULL;

    *** (2) TRANSACTION:事务1035409,MySQL事务线程1020088
    TRANSACTION 1035409, ACTIVE 107 sec starting index read
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 312, 2 row lock(s), undo log entries 1
    MySQL thread id 1020088, OS thread handle 0x698d9b70, query id 195422 192.168.31.153 donald Sending data
    select * from user where id =1 lock in share mode
    *** (2) HOLDS THE LOCK(S):当前事务持有锁,S锁,
    RECORD LOCKS space id 7405 page no 3 n bits 72 index `PRIMARY` of table `test`.`user`
    trx id 1035409 lock_mode X locks rec but not gapRecord lock, 非gap锁
    heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
    0: len 4; hex 80000001; asc ;;
    1: len 6; hex 0000000fcc91; asc ;;
    2: len 7; hex 68000080210a23; asc h ! #;;
    3: len 4; hex 6a61636b; asc jack;;
    4: len 4; hex 80000017; asc ;;
    5: SQL NULL;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 7405 page no 3 n bits 72 index `PRIMARY` of table `test`.`user`
    trx id 1035409 lock mode S waiting Record lock, heap no 2 PHYSICAL RECORD:
    n_fields 6; compact format; info bits 32
    0: len 4; hex 80000001; asc ;;
    1: len 6; hex 0000000fcc91; asc ;;
    2: len 7; hex 68000080210a23; asc h ! #;;
    3: len 4; hex 6a61636b; asc jack;;
    4: len 4; hex 80000017; asc ;;
    5: SQL NULL;

    *** WE ROLL BACK TRANSACTION (1)
    ------------
    TRANSACTIONS 回滚事务1035425
    ------------
    Trx id counter 1035425
    Purge done for trx's n:o < 1035408 undo n:o < 0 state: running but idle
    History list length 1046
    LIST OF TRANSACTIONS FOR EACH SESSION:每个会话的事务
    ---TRANSACTION 0, not started,
    MySQL thread id 1020076, OS thread handle 0x650ceb70, query id 195466 192.168.31.153 donald init
    show engine innodb status
    ---TRANSACTION 1035423, not started
    MySQL thread id 1020089, OS thread handle 0x6503bb70, query id 195454 192.168.31.153 donald cleaning up
    ---TRANSACTION 1035424, not started
    MySQL thread id 1020087, OS thread handle 0x69879b70, query id 195455 192.168.31.153 donald cleaning up
    ---TRANSACTION 0, not started
    MySQL thread id 1019933, OS thread handle 0x6506cb70, query id 195428 192.168.31.153 donald cleaning up
    ---TRANSACTION 1035409, ACTIVE 627 sec 事务1035409,激活状态,MySQL线程id,1020088
    3 lock struct(s), heap size 312, 5 row lock(s), undo log entries 1
    MySQL thread id 1020088, OS thread handle 0x698d9b70, query id 195456 192.168.31.153 donald cleaning up
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 state: waiting for completed aio requests (log thread)
    I/O thread 2 state: waiting for completed aio requests (read thread)
    I/O thread 3 state: waiting for completed aio requests (read thread)
    I/O thread 4 state: waiting for completed aio requests (read thread)
    I/O thread 5 state: waiting for completed aio requests (read thread)
    I/O thread 6 state: waiting for completed aio requests (write thread)
    I/O thread 7 state: waiting for completed aio requests (write thread)
    I/O thread 8 state: waiting for completed aio requests (write thread)
    I/O thread 9 state: waiting for completed aio requests (write thread)
    Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
    ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    3034 OS file reads, 46735 OS file writes, 12238 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 26781, seg size 26783, 0 merges
    merged operations:
    insert 0, delete mark 0, delete 0
    discarded operations:
    insert 0, delete mark 0, delete 0
    Hash table size 9239933, node heap has 24 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 709253455618
    Log flushed up to 709253455618
    Pages flushed up to 709253455618
    Last checkpoint at 709253455618
    0 pending log writes, 0 pending chkp writes
    3279 log i/o's done, 0.00 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 2136604672; in additional pool allocated 0
    Dictionary memory allocated 745414
    Buffer pool size 128000
    Free buffers 93253
    Database pages 34723
    Old database pages 12657
    Modified db pages 0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 1, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 2779, created 31944, written 37451
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 34723, unzip_LRU len: 0
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ----------------------
    INDIVIDUAL BUFFER POOL INFO
    ----------------------
    ---BUFFER POOL 0
    Buffer pool size 16000
    Free buffers 11538
    Database pages 4459
    Old database pages 1625
    Modified db pages 0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 0, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 387, created 4072, written 5592
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 4459, unzip_LRU len: 0
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ---BUFFER POOL 1
    Buffer pool size 16000
    Free buffers 11712
    Database pages 4285
    Old database pages 1563
    Modified db pages 0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 0, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 373, created 3912, written 4091
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 4285, unzip_LRU len: 0
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ---BUFFER POOL 2
    Buffer pool size 16000
    Free buffers 11728
    Database pages 4269
    Old database pages 1557
    Modified db pages 0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 0, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 283, created 3986, written 4206
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 4269, unzip_LRU len: 0
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ---BUFFER POOL 3
    ...
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ---BUFFER POOL 4
    ....
    ---BUFFER POOL 5
    ...
    ---BUFFER POOL 6
    ....
    ---BUFFER POOL 7
    ....
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    0 read views open inside InnoDB
    Main thread process no. 4472, id 1741499248, state: sleeping
    Number of rows inserted 1222447, updated 48, deleted 1745, read 61951636
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================


    [b]2.查看Mysql事务:[/b]
    2.a 查看当前事务:

    mysql> select * from information_schema.INNODB_TRX;
    +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
    | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_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 | trx_is_read_only | trx_autocommit_non_locking |
    +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
    | 1035424 | LOCK WAIT | 2017-07-18 18:17:43 | 1035424:7405:3:2 | 2017-07-18 18:17:43 | 2 | 1020087 | delete from user where id = 1 | starting index read | 1 | 1 | 2 | 312 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 | 0 | 0 |
    | 1035409 | RUNNING | 2017-07-18 18:14:35 | NULL | NULL | 4 | 1020088 | NULL | NULL | 0 | 0 | 3 | 312 | 5 | 1 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 | 0 | 0 |
    +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
    2 rows in set

    mysql>

    2.b 查看当前锁定的事务

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
    +------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
    | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
    +------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
    | 1035424:7405:3:2 | 1035424 | X | RECORD | `test`.`user` | PRIMARY | 7405 | 3 | 2 | 1 |
    | 1035409:7405:3:2 | 1035409 | S | RECORD | `test`.`user` | PRIMARY | 7405 | 3 | 2 | 1 |
    +------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
    2 rows in set


    2.c 查看当前等锁的事务
    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
    +-------------------+-------------------+-----------------+------------------+
    | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
    +-------------------+-------------------+-----------------+------------------+
    | 1035424 | 1035424:7405:3:2 | 1035409 | 1035409:7405:3:2 |
    | 1035424 | 1035424:7405:3:2 | 1035409 | 1035409:7405:3:2 |
    +-------------------+-------------------+-----------------+------------------+
    2 rows in set



    找出死锁关联的事务线程id(trx_mysql_thread_id),从上面的分析得出,持有锁的MySQL事务线程id为1020088


    [b]3.Kill 关联事务线程[/b]

    mysql> kill 1020088;
    Query OK, 0 rows affected


    [b]4.再次查看当前等待锁的事务及当前事务:[/b]

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    Empty set

    mysql>
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
    Empty set

    mysql> select * from information_schema.INNODB_TRX;
    Empty set



    在分析之前先来看一下MySQL的锁机制,MySQLl锁机制有行级锁和表级锁,
    InnoDB实现了两种类型的行锁:
    共享锁(S):允许一个事务去读一行,阻止其他事务获得相同的数据集的排他锁。
    排他锁(X):允许获得排他锁的事务更新数据,但是组织其他事务获得相同数据集的共享锁和排他锁。

    共享锁就是我读的时候,你可以读,但是不能写。排他锁就是我写的时候,你不能读也不能写。其实就是MyISAM的读锁和写锁,但是针对的对象不同了而已。

    除此之外InnoDB还有两个表锁:
    意向共享锁(IS):表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁
    意向排他锁(IX):类似上面,表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

    InnoDB行锁模式兼容列表,见下这篇文章
    InnoDB的行锁模式及加锁方法:[url]http://lib.csdn.net/article/mysql/8747[/url]

    [color=red][b]注意:[/b][/color]
    当一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务;反之如果请求不兼容,则该事务就等待锁释放。意向锁是InnoDB自动加的,不需要用户干预。

    对于insert、update、delete,InnoDB会自动给涉及的数据加排他锁(X);
    对于一般的Select语句,InnoDB不会加任何锁,事务可以通过以下语句给显示加共享锁或排他锁。

    共享锁:select * from table_name where .....lock in share mode
    排他锁:select * from table_name where .....for update
    [b]分析死锁原因:[/b]
    出现死锁的原因,是因为会话A开启一个事务,以共享锁S锁,获取user表id为1的一行记录;
    会话B,删除user表id为1记录,请求排他锁X锁,由于SX锁互斥,会话B等待会话A释放共享锁S锁,进入请求队列等待;这时,会话A删除user表id为1记录,请求排他锁X锁,但会话B在请求队列中,还轮不到会话A,会话A就等待,这种循环等待出现,死锁就出现了。

    附:
    这部分与上面无关,只作为记录
    检查数据库表状态:
    mysql> show table status from test_db;
    +---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------+
    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
    +---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------+
    | user | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2017-07-17 19:39:33 | NULL | NULL | utf8_general_ci | NULL | | |
    | tb_message | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 14 | 2017-07-17 19:14:02 | NULL | NULL | utf8_general_ci | NULL | | |
    | tb_log | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 0 | 0 | 2 | 2017-07-18 02:00:31 | NULL | NULL | utf8_general_ci | NULL | |
    +---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------+
    3 rows in set


    mysql>

    检查指定表状态:
    mysql> check table user;
    +----------------------------+-------+----------+----------+
    | Table | Op | Msg_type | Msg_text |
    +----------------------------+-------+----------+----------+
    | test_db.user | check | status | OK |
    +----------------------------+-------+----------+----------+
    1 row in set


    如果表状态不OK,则修复:
    mysql>  repair table user;



    查看数据库当前事务提交状态:
    mysql> show variables like 'autocommit';  
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit | ON |
    +---------------+-------+
    1 row in set


    查看数据库事务隔离级别:
    mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set

    mysql>

    MySQL的线程状态快照:
    快照1:
    mysql> show processlist
    -> ;
    +------+---------+-------------------+----------------+---------+------+---------------------------------+----------------------------------------------------------------------------------------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +------+---------+-------------------+----------------+---------+------+---------------------------------+----------------------------------------------------------------------------------------------------+
    | 2721 | donald | 192.168.31.153:50725 | test_db | Query | 1070 | Waiting for table metadata lock | ALTER TABLE `user`
    CHANGE COLUMN `REGISTRER_NUM` `REGISTER_NUM` varchar(13) CHARACTER SET u |
    | 2724 | donald | 192.168.31.153:57376 | NULL | Sleep | 2432 | | NULL |
    | 2725 | donald | 192.168.31.153:57381 | test_db | Sleep | 2423 | | NULL |
    | 2727 | donald | 192.168.31.153:51135 | test_db | Sleep | 0 | | NULL |
    | 2728 | donald | 192.168.31.153:51156 | test_db | Sleep | 2017 | | NULL |
    | 2731 | donald | 192.168.31.153:51789 | test_db | Query | 647 | Waiting for table metadata lock | CREATE TABLE `user` (
    `COM_ID` bigint(20) NOT NULL,
    `BUSI_NO` varchar(32) DEFAULT NULL |
    | 2735 | donald | 192.168.31.153:51813 | test_db | Query | 546 | Waiting for table metadata lock | CREATE TABLE `user` (
    `COM_ID` bigint(20) NOT NULL,
    `BUSI_NO` varchar(32) DEFAULT NULL |
    | 2736 | donald | 192.168.31.153:58929 | test_db | Query | 0 | init | show processlist |
    +------+---------+-------------------+----------------+---------+------+---------------------------------+----------------------------------------------------------------------------------------------------+
    8 rows in set

    快照2:
    mysql> show processlist;
    +---------+-----------------+-------------------+------+---------+---------+------------------------+---------------------------------------------------------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +---------+-----------------+-------------------+------+---------+---------+------------------------+---------------------------------------------------------------------+
    | 1 | event_scheduler | localhost | NULL | Daemon | 3030806 | Waiting on empty queue | NULL |
    | 1019932 | donald | 192.168.31.153:15217 | NULL | Sleep | 1526 | | NULL |
    | 1019933 | donald | 192.168.31.153:15218 | test | Sleep | 1174 | | NULL |
    | 1020076 | donald | 192.168.31.153:15254 | test | Query | 0 | init | show processlist |
    | 1020087 | donald | 192.168.31.153:15262 | test | Sleep | 14 | | NULL |
    | 1020089 | donald | 192.168.31.153:15612 | test | Query | 41 | update | INSERT INTO `user` (`id`, `name`, `age`) VALUES ('1', 'jack', '23') |
    | 1020540 | donald | 192.168.31.153:16592 | test | Sleep | 64 | | NULL |
    +---------+-----------------+-------------------+------+---------+---------+------------------------+---------------------------------------------------------------------+
    7 rows in set
    展开全文
  • 项目中遇到一个mysql死锁的问题,报的异常如下 :com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction 我把问题简单化一下: ...
  • 在更改了删除方式之后,Deadlock found when trying to get lock; try restarting transaction,这个错误有效的减少了。 这个错误是说,mysql发现了一个死锁,那么就重启这个事务,这样就解决了数据死锁的问题。 ...
  • Mysql死锁问题

    2018-01-22 10:37:49
    com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ... Deadlock found when trying to get lock; try restarting
  • 【OGG】OGG故障错误处理总结

    千次阅读 2019-09-30 01:21:58
    run GGSCI command but the Alert window report "Application failded to initialize(0xc000026e)" 。 GoldenGate 在 Windows 平台上需要安装 Microsoft Visual C ++ 2005 SP1 Redistributable Package ...
  • --- Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction; nested exception is ...
  • OGG故障错误处理总结

    千次阅读 2020-08-19 17:13:39
    示例9-10: - SQL error 1403 mapping 2010-02-25 13:20:08 GGS WARNING 218 Oracle GoldenGate Delivery for Oracle, rep_stnd.prm: SQL error 1403 mapping HR.MY_EMPLOYEE to HR.MY_EMPLOYEE. 可能原因包括以下...
  • MySQL更新丢失

    2021-01-27 17:23:12
    try restarting transaction 文章导航-readme MySql 更新死锁问题 Deadlock found when trying to get lock; try restarting transaction 1.场景 //t ... 随机推荐 Android性能优化之App应用启动分析与优化 前言: ...
  • ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 当对存在的行进行锁的时候(主键),mysql就只有行锁。 当对不存在的行进行锁的时候(即使条件为主键),mysql是会锁住一段...
  • 案例:银行的数据库里面存储着用户的账户信息表,当用户 A 想用户 B 转账的时候,正常情况下,A 账户的余额减少,B 账户的余额增加;但是由于某种原因(例如突然断电),当 A 账户的余额减少之后... ... ...事务Tran...
  • Oracle Database Server Messages(一)

    千次阅读 2014-02-24 17:36:30
    Skip Headers ...Oracle9i Database Error Messages Release 2 (9.2) Part Number A96525-01 Home Book List Contents Index Master Index Feedback
  • mysql锁问题

    2017-02-22 17:55:44
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction  分析:客户端一锁定age范围为[6,12),客户端二锁定age范围(6,12],所以在(6,12)范围内,两个客户端都不能进行操作。 ...
  • mysql批量update死锁

    千次阅读 2019-05-07 10:16:32
    when trying to get lock; try restarting transaction; 2、发生原因 T1:begin tran select * from table lock in share mode update table set column1='hello' T2:begin tran select * from table lock ...
  • C++ Object Persistence with ODB

    千次阅读 2013-05-30 21:10:23
    source: http://www.codesynthesis.com/products/odb/doc/manual.xhtml#16 C++ Object Persistence with ODB Copyright © 2009-2013 Code Synthesis Tools CC ...Permission is granted to copy, distribute

空空如也

空空如也

1
收藏数 20
精华内容 8
关键字:

sequelizeerror:deadlockfoundwhentryingtogetlock;tryrestartingtran