sql - Redshift - 并发写入 - 如果动态创建查询,则插入不起作用
问题描述
我有一个存储过程:
CREATE OR REPLACE PROCEDURE public.lock_users(j_id "varchar",order_id "varchar",order_detail_id "varchar",insert_qry varchar(65535),rec_per_order "varchar")
LANGUAGE plpgsql
AS $$
declare
lc_stmt varchar(65535);
BEGIN
lc_stmt = 'INSERT INTO test.USER_LOCK
SELECT '''||$1||''', '''||$2||''', '''||$3||''', user_id,cast(TIMEOFDAY() as timestamp) FROM ('||insert_qry|| 'AND USER_ID NOT IN (SELECT USER_ID FROM test.USER_LOCK)) WHERE ORDER_CNT <='||rec_per_order||'))';
EXECUTE ''||lc_stmt||'';
END
$$
;
从上述过程生成的一个示例查询是:
INSERT INTO test.USER_LOCK
SELECT '657d7563-6de4-4dc9-ac74-3c23adf7a4e9', 'DSS-12345', 'DSS-74523-4-7569',
USER_ID,cast(TIMEOFDAY() as timestamp)
FROM (
SELECT USER_ID FROM (
SELECT * FROM (
SELECT XA.USER_ID, XA.EMAIL_ID,YA.COMPANY_NAME
rank() OVER (PARTITION BY XA.account_id ORDER BY XA.account_id) ORDER_CNT
FROM test.contacts_20 XA
LEFT JOIN test.accounts_20 YA
ON XA.ACCOUNT_ID = YA.ACCOUNT_ID
AND XA.COUNTRY = YA.COUNTRY
WHERE XA.IS_CONTACT_SUPPRESSED = 0
AND UPPER(XA.TELE_SUPPRESSION_LOB) != UPPER('DSS')
AND XA.TELE_SUPPRESSION_LOB != 'BOTH'
AND XA.IS_TELE_VERIFIED = 1
AND XA.IS_TELE_SUPPRESSED = 0
AND UPPER(PHONE_LINE) = 'DIRECT'
AND XA.COUNTRY IN (
SELECT INCLUSION_VALUE FROM user_inc_list
WHERE JOB_ID = '657d7563-6de4-4dc9-ac74-3c23adf7a4e9'
AND UPPER(INCLUSION_TYPE) = 'COUNTRY')
AND XA.COUNTRY NOT IN (
SELECT EXCLUSION_VALUE FROM user_exc_list
WHERE JOB_ID = '657d7563-6de4-4dc9-ac74-3c23adf7a4e9'
AND UPPER(EXCLUSION_TYPE) = 'COUNTRY')
AND XA.USER_ID NOT IN (
SELECT USER_ID FROM test.user_lead_20
WHERE (CURRENT_DATE - creation_date::date) <= 60 AND UPPER(LOB) != 'DSS' AND AGENCY_ID != '1456')
AND XA.USER_ID NOT IN (
SELECT USER_ID FROM test.user_lead_20
WHERE (CURRENT_DATE - creation_date::date) <= 60 AND UPPER(LOB) != 'DSS' AND SPONSOR_ID != '8659')
AND USER_ID NOT IN (
select USER_ID
from user_e_history
where sf_campaign_id = 'DSS-12345' AND (CURRENT_DATE - creation_date::date) >= 7 AND channel = 'TELE')
AND USER_ID NOT IN (
select USER_ID from user_e_history
where creation_date::date = CURRENT_DATE AND channel = 'TELE' )
AND USER_ID NOT IN (
select USER_ID from test.user_lead_20
where sf_campaign_id = 'DSS-12345' GROUP BY USER_ID,"DOMAIN" HAVING COUNT(*) >= 3 )
AND USER_ID NOT IN (
select USER_ID from test.user_lead_20
where AGENCY_ID = 1456 and (CURRENT_DATE - creation_date::date) <= 180 )
AND XA.E_domain NOT LIKE '%.gov'
AND USER_ID NOT IN (
SELECT USER_ID FROM test.USER_LOCK)) WHERE ORDER_CNT <=20));
当我并行执行这个存储过程时,它给了我这个错误:
SQL Error [500310] [XX000]: [Amazon](500310) Invalid operation: 1023
Details:
Serializable isolation violation on table - 132075, transactions forming the cycle are: 2040186, 2040187 (pid:14687);
当我更改存储过程而不是传递参数并在查询中创建固定插入时,它可以工作。
这是有效的存储过程:
CREATE OR REPLACE PROCEDURE public.new_procedure(type_value "varchar")
LANGUAGE plpgsql
AS $$
declare
lc_stmt varchar;
BEGIN
lc_stmt = 'INSERT into temp_table
select ct_id,email_id,first_name,last_name from users where active_type = '''||$1||''' ';
EXECUTE ''||lc_stmt||'';
END
$$
;
我无法理解此问题的原因和解决方案。请帮忙。
解决方案
动态过程中的插入查询会查找不在 user_lock 表中的 user_id 并插入结果。两个动态版本之间的结果集中似乎有共同的 user_id。
所以假设当你的第一个版本被执行时,它可能会在 user_lock 表中添加一个 user_id,同样的 user_id 也可能在第二个版本插入到 user_lock 表的结果集中。
因此,取决于哪个版本首先运行,这两个版本的结果集与串行执行的情况相比会有所不同,即它们不是“可串行隔离的”。
而且您在测试示例中没有收到错误,因为它是一种独立的插入(用户和 temp_table)。
尝试LOCK可能会解决此问题。
推荐阅读
- python - 在 Linux 上以无头模式打开新选项卡时无法下载文件
- laravel - 在一个查询中获取和更新数据 Laravel
- sql-server-2012 - 如何选择不同的列一次并空白到其相似的 id 列
- html - 为什么图表没有出现在我的网站 html 上
- android - 是否有强制使用“FrameLayout”来加载片段?
- multithreading - 如何将结构传递给QT线程
- jmeter - Jmeter - 如何仅在关闭所有从属中的主线程后执行拆除线程组?
- javascript - 为什么html画布线动画可以工作,但在已经画线之后却不行
- git - 有没有办法不跟踪对 Github 上特定存储库的贡献?
- kubernetes - 如果健康检查不满足successThreshold和failureThreshold,pod的状态应该是什么,也不