Greenplum - How To Handle Deadlock

Question

When try to run SQL transaction from Greenplum. getting this error.

Transaction (Process ID 52) was deadlocked on lock resources with 
another process and has been chosen as the deadlock victim.
Rerun the transaction.

We Tried :

On SQL server it is working But we wanted to write same transaction on greenplum

Transaction A


RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY

    truncate table tablename
    WAITFOR DELAY '00:00:05'  -- Wait for 5 ms
    Insert into tablename

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    PRINT 'Rollback Transaction'
    ROLLBACK TRANSACTION
    IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
    BEGIN
        WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
        GOTO RETRY -- Go to Label RETRY
    END
END CATCH

OUTPUT :

IT INCLUDES

1. Conversion of mentioned sql transaction (retry and try/catch )block in greenplum syntax.

2. As process ID keep changing , want to avoid hardcode value when passing into transaction.

3.Also, I am trying to understand this error belongs to SQL SERVER OR transaction we written in greenplum.

Data flow in talend is:

**Read from MS SQL SERVER >> write into hdfs >> load into greenplum** 

Any help on it would be much appreciated ?

Answer 1

You cannot begin or end the transaction within a transaction in Greenplum. It auto commit or auto roll back.

Multi-Version Concurrency Control, there is always a data consistency and also query always executes in sequential within parallel mechanisms.

RETRY: -- Label RETRY BEGIN

truncate table tablename
select pg_sleep(5) -- Wait for 5 ms
Insert into tablename

RAISE INFO 'Rollback Transaction'

IF ERROR_NUMBER = 1205 -- Deadlock Error Number
THEN 
select pg_sleep(5)  -- Wait for 5 ms
   -- Go to Label RETRY
END