title: is it a good idea to handle deadlock retry from stored procedure catch block
body: <p>From what i undertand it is impossible to completely prevent a transaction from deadlocking.</p>
  
  <p>I would like to have transaction that neverfail from the perpective of application code.
  So i have seen this pattern in use for Microsoft SQL and I wonder if this is a good idea?</p>
  
  <pre>
  
      DECLARE @retry  tinyint
      SET @retry  = 5
      WHILE @retry >0
      BEGIN
        BEGIN TRANSACTION
        BEGIN TRY
          // do transaction her
          COMMIT
          BREAK
        END TRY
        BEGIN CATCH
          ROLLBACK
  
          if (ERROR_NUMBER() = 1205 OR ERROR_NUMBER() = 1222)
          BEGIN
            SET @retry = @retry - 1
            IF @retry = 0
               RAISEERROR('Could not complete transaction',16,1);
            WAITFOR DELAY '00:00:00.05' -- Wait for 50 ms
            CONTINUE
          END
          ELSE
          BEGIN
            RAISEERROR('Non-deadlock condition encountered',16,1);
            BREAK;
          END
        END CATCH;
      END
  </pre>