Mysql死锁分析

事务1：

UPDATE vh_product_price

SET update_time=now(),update_person='zhaolingjun',sales_cvc_code='C6M02020AC16CGC1000003010',status='1',dealer_id=1255469042005170269,dealer_guide_price=148800,market_guide_price=108800,dealer_lowest_price=97800

WHERE (sales_cvc_code = 'C6M02020AC16CGC1000003010' and dealer_id = 1255469042005170269 and dr = 0)

事务2：

UPDATE vh_product_price

SET update_time=now(),update_person='zhaoyazhong',sales_cvc_code='C6M02020AC16CGC1000003010',status='1',dealer_id=1254231378003189822,dealer_guide_price=148800,market_guide_price=108800,dealer_lowest_price=89800

WHERE (sales_cvc_code = 'C6M02020AC16CGC1000003010' and dealer_id = 1254231378003189822 and dr = 0)

执行计划如下：

mysql> explain select count(*) from vh_product_price WHERE (sales_cvc_code = 'C6M02020AC16CGC1000003010' and dealer_id = 1254231378003189822 and dr = 0);

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

| id | select_type | table            | partitions | type        | possible_keys                                      | key                                                | key_len | ref  | rows | filtered | Extra                                                                            |

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

|  1 | SIMPLE      | vh_product_price | NULL       | index_merge | IDX_vh_prodice_sales_code,IDX_vh_prodice_dealer_id | IDX_vh_prodice_dealer_id,IDX_vh_prodice_sales_code | 8,802   | NULL | 5220 |    10.00 | Using intersect(IDX_vh_prodice_dealer_id,IDX_vh_prodice_sales_code); Using where |

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

表结构：

vh_product_price

CREATE TABLE `vh_product_price` (

  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',

  `dr` tinyint NOT NULL COMMENT '删除标记',

  `tenant_id` bigint NOT NULL COMMENT '租户ID',

  `instance_id` bigint NOT NULL COMMENT '实例ID',

  `create_person` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '创建人账户名称',

  `create_time` datetime NOT NULL COMMENT '创建时间',

  `update_person` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '修改人账户名称',

  `update_time` datetime NOT NULL COMMENT '更新时间',

  `extension` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '拓展字段内容',

  `sales_cvc_code` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '销售CVC代码',

  `status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '1' COMMENT '状态(1启用 2停用)',

  `dealer_id` bigint NOT NULL COMMENT '经销商id',

  `dealer_guide_price` decimal(16,4) NOT NULL DEFAULT '0.0000' COMMENT '经销商指导价',

  `market_guide_price` decimal(16,4) NOT NULL DEFAULT '0.0000' COMMENT '市场指导价',

  `dealer_lowest_price` decimal(16,4) NOT NULL DEFAULT '0.0000' COMMENT '经销商最低销售价',

  `org_brand` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '组织品牌 读取到当前经销商的组织结构中的品牌，用于以后多品牌扩展',

  `description` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '描述',

  PRIMARY KEY (`id`) USING BTREE,

  KEY `IDX_vh_prodice_sales_code` (`sales_cvc_code`) USING BTREE,

  KEY `IDX_vh_prodice_dealer_id` (`dealer_id`) USING BTREE,

  KEY `IDX_vh_prodice_create_ime` (`create_time`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1258548384653373474 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='车辆产品价格表';

场景1：

UPDATE vh_product_price

SET update_time=now(),update_person='zhaoyazhong',sales_cvc_code='C6M02020AC16CGC1000003010',status='1',dealer_id=1254231378003189822,dealer_guide_price=148800,market_guide_price=108800,dealer_lowest_price=89800

WHERE (sales_cvc_code = 'C6M02020AC16CGC1000003010' and dealer_id = 1254231378003189822 and dr = 0)

同样的sql语句不同的事物出现死锁。

场景2:

UPDATE vh_product_price

SET update_time=now(),update_person='zhaolingjun',sales_cvc_code='C6M02020AC16CGC1000003010',status='1',dealer_id=1255469042005170269,dealer_guide_price=148800,market_guide_price=108800,dealer_lowest_price=97800

WHERE (sales_cvc_code = 'C6M02020AC16CGC1000003010' and dealer_id = 1255469042005170269 and dr = 0)

事务2：

UPDATE vh_product_price

SET update_time=now(),update_person='zhaoyazhong',sales_cvc_code='C6M02020AC16CGC1000003010',status='1',dealer_id=1254231378003189822,dealer_guide_price=148800,market_guide_price=108800,dealer_lowest_price=89800

WHERE (sales_cvc_code = 'C6M02020AC16CGC1000003010' and dealer_id = 1254231378003189822 and dr = 0)

不同的sql出现死锁