From what i undertand it is impossible to completely prevent a transaction from deadlocking.
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?
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
Aucun commentaire:
Enregistrer un commentaire