title: Greenplum - How To Handle Deadlock
body: <p>When try to run SQL transaction from Greenplum. getting this error.</p>
  
  <pre><code>Transaction (Process ID 52) was deadlocked on lock resources with 
  another process and has been chosen as the deadlock victim.
  Rerun the transaction.
  </code></pre>
  
  <p><strong>We Tried :</strong></p>
  
  <p>On  SQL server it is working But we wanted to write same transaction on greenplum</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>OUTPUT :</strong></p>
  
  <p>IT INCLUDES </p>
  
  <ol>
  <li><p>Conversion of mentioned sql transaction (retry and try/catch )block in greenplum syntax.</p></li>
  <li><p>As process ID keep changing , want to avoid hardcode value when passing into transaction.</p></li>
  </ol>
  
  <p>3.Also, I am trying to understand this error belongs to SQL SERVER OR transaction we written in greenplum.</p>
  
  <p><strong>Data flow in talend is:</strong></p>
  
  <pre><code>**Read from MS SQL SERVER &gt;&gt; write into hdfs &gt;&gt; load into greenplum** 
  </code></pre>
  
  <p>Any help on it would be much appreciated ?</p>