记录一次 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 (/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

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页