Redshift - Concurrent Write - Insert does not work if query is dynamicly created

Question

I have a stored procedure:

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
  $$
;

One example query which gets generated from the above procedure is:

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));

When I execute this stored procedure parallely, it gives me this error:

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);

When I change my stored procedure and instead of passing parameters and creating fixed insert into query, it works.

This is the stored procedure which works:

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
 $$
;

I am not able to understand the cause and solution for this. Please help.

Answer 1

The Insert query in your dynamic procedure look out for the user_id's which are not in the user_lock table and insert the result. It seems there are common user_id in the result set between the two dynamic versions.

So suppose when your 1st version is executed, it might add a user_id to the user_lock table, that same user_id might also be in the result set to be inserted into the user_lock table by the 2nd version.

So depending on which version runs first, the result set of both the version will differ compared to if they were executed serially i.e. they are not "Serializable Isolated".

And you are not getting the error in your test example, because it a kind of independent insert (users and temp_table).

Trying a LOCK might resolve this.