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