sql 执行select 可以使用的但是加了insert 会lock 怎么办？

Question

sql 执行select 可以使用的但是加了insert 会lock 怎么办？

INSERT INTO db_amazon_V10_statistics.tb_monthly_sales_statistics (seller_id, record_date, mean_sales, std_sales, mean_revenue, std_revenue)
SELECT
    seller_id,
    record_date,
    AVG(sales) AS mean_sales,
    STDDEV(sales) AS std_sales,
    AVG(revenue) AS mean_revenue,
    STDDEV(revenue) AS std_revenue
FROM
    db_amazon_V10.tb_product_month
where seller_id = "A00038802J7X43YTW44TD" and record_date = "2024-04-01"
ON DUPLICATE KEY UPDATE
    mean_sales = VALUES(mean_sales),
    std_sales = VALUES(std_sales),
    mean_revenue = VALUES(mean_revenue),
    std_revenue = VALUES(std_revenue);

Answer 1

让阿豪来帮你解答，本回答参考chatgpt3.5编写提供，如果还有疑问可以评论或留言

问题描述中提到，在执行SQL语句时，如果包含INSERT操作会导致锁定，那么可以考虑通过使用事务或者调整SQL语句的执行顺序来解决。下面分别给出两种解决方案：

1. 使用事务： 在执行SQL语句时，将其包裹在一个事务中，以确保整个操作的原子性。这样可以提高并发访问时的性能和数据一致性。 示例代码如下所示：

START TRANSACTION;
INSERT INTO db_amazon_V10_statistics.tb_monthly_sales_statistics (seller_id, record_date, mean_sales, std_sales, mean_revenue, std_revenue)
SELECT    seller_id,    record_date,    AVG(sales) AS mean_sales,    STDDEV(sales) AS std_sales,    AVG(revenue) AS mean_revenue,    STDDEV(revenue) AS std_revenue
FROM    db_amazon_V10.tb_product_month
where seller_id = 'A00038802J7X43YTW44TD' and record_date = '2024-04-01'
ON DUPLICATE KEY UPDATE    mean_sales = VALUES(mean_sales),    std_sales = VALUES(std_sales),    mean_revenue = VALUES(mean_revenue),    std_revenue = VALUES(std_revenue);
COMMIT;

1. 调整SQL语句的执行顺序： 将SELECT和INSERT分开执行，不要将它们放在同一条SQL语句中，可以避免对同一行数据进行读写操作时的锁定问题。 示例代码如下所示：

-- 执行SELECT语句
SELECT seller_id, record_date, AVG(sales) AS mean_sales, STDDEV(sales) AS std_sales, AVG(revenue) AS mean_revenue, STDDEV(revenue) AS std_revenue
FROM db_amazon_V10.tb_product_month
where seller_id = 'A00038802J7X43YTW44TD' and record_date = '2024-04-01';
-- 执行INSERT语句
INSERT INTO db_amazon_V10_statistics.tb_monthly_sales_statistics (seller_id, record_date, mean_sales, std_sales, mean_revenue, std_revenue)
VALUES ('A00038802J7X43YTW44TD', '2024-04-01', mean_sales, std_sales, mean_revenue, std_revenue)
ON DUPLICATE KEY UPDATE mean_sales = VALUES(mean_sales), std_sales = VALUES(std_sales), mean_revenue = VALUES(mean_revenue), std_revenue = VALUES(std_revenue);

通过以上两种方法，可以解决在执行SQL语句时加入INSERT操作导致锁定的问题。