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出现死锁
