CREATE TABLE Accounts
(
id int primary key,
value money
)

--DROP TABLE Postings

CREATE TABLE Postings
(
id int identity primary key,
account int,
value money
)


CREATE 
--ALTER
PROC UpdateAccount(@id int)
as
BEGIN TRAN
DECLARE @amount money
select @amount = sum(value) from postings where account = @id
WAITFOR DELAY '00:00:20'
UPDATE ACCOUNTS Set value = value + @amount where id = @id
DELETE postings where account = @id
COMMIT TRAN


CREATE 
--ALTER
PROC MovePendingPostings(@from int, @to int)
as
BEGIN TRAN
DECLARE @amount money
select @amount = value from accounts where id = @from
WAITFOR DELAY '00:00:20'
INSERT INTO POSTINGS VALUES (@from, @amount)
UPDATE accounts set value = value - @amount where id = @from
COMMIT TRAN



CREATE 
--ALTER
PROC UpdateAccountRetry(@id int)
as
DECLARE @tryCount int
set @tryCount = 0;
WHILE @tryCount < 3
BEGIN
SET @tryCount = @tryCount + 1
BEGIN TRAN
BEGIN TRY
DECLARE @amount money
select @amount = ISNULL(sum(value), 0) from postings where account = @id
WAITFOR DELAY '00:00:20'
UPDATE ACCOUNTS Set value = value + @amount where id = @id
DELETE postings where account = @id
COMMIT TRAN
SET @tryCount = 3
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205
BEGIN
ROLLBACK TRAN
PRINT 'retry'
END
ELSE
BEGIN
SET @tryCount = 3
END
END CATCH
END

CREATE 
--ALTER
PROC MovePendingPostingsRetry(@from int, @to int)
as
DECLARE @tryCount int
set @tryCount = 0;
WHILE @tryCount < 3
BEGIN
SET @tryCount = @tryCount + 1
BEGIN TRAN
BEGIN TRY
DECLARE @amount money
select @amount = value from accounts where id = @from
WAITFOR DELAY '00:00:20'
INSERT INTO POSTINGS VALUES (@from, @amount)
UPDATE accounts set value = value - @amount where id = @from
COMMIT TRAN
SET @tryCount = 3
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205
BEGIN
ROLLBACK TRAN
PRINT 'retry'
END
ELSE
SET @tryCount = 3
ROLLBACK TRAN
END CATCH
END




select * from postings

truncate table accounts


DECLARE @id int
set @id = 100
while @id > 0
BEGIN
INSERT INTO Accounts VALUES (@id, 0)
SET @id = @id - 1
END

INSERT INTO Postings VALUES (20, 100)
INSERT INTO Postings VALUES (20, 120)
INSERT INTO Postings VALUES (20, 10)

exec UpdateAccount 20
select * from accounts

select * from postings

--DROP TABLE TableA

CREATE TABLE TableA
(
id int primary key,
value int
)



--DROP TABLE TableB
CREATE TABLE TableB
(
id int  primary key,
value int
)

INSERT INTO TableA VALUES (1, 1)
INSERT INTO TableA VALUES (2, 2)
INSERT INTO TableA VALUES (3, 3)

INSERT INTO TableB VALUES (1, 1)
INSERT INTO TableB VALUES (2, 2)
INSERT INTO TableB VALUES (3, 3)


CREATE 
--ALTER
Proc MoveAToB(@id int)
AS
BEGIN TRAN
DECLARE @amount int
SELECT @amount = value from TableA WHERE id = @id
WAITFOR DELAY '00:00:20'
UPDATE TableB SET value = @amount where id = @id
COMMIT TRAN


CREATE Proc MoveBToA(@id int)
AS
BEGIN TRAN
DECLARE @amount int
SELECT @amount = value from TableB WHERE id = @id
WAITFOR DELAY '00:00:20'
UPDATE TableA SET value = @amount where id = @id
COMMIT TRAN


select * from tablea

CREATE 
--ALTER
Proc MoveAToBRetry(@id int)
AS
DECLARE @tryCount int
SET @tryCount = 3
WHILE @tryCount > 0
BEGIN
SET @tryCount = @tryCount - 1
BEGIN TRAN
BEGIN TRY
DECLARE @amount int
SELECT @amount = value from TableA WHERE id = @id
WAITFOR DELAY '00:00:20'
UPDATE TableB SET value = @amount where id = @id
COMMIT TRAN
SET @tryCount = 0
END TRY
BEGIN CATCH
ROLLBACK TRAN
IF ERROR_NUMBER() = 1205
BEGIN
IF XACT_STATE() <> 0 ROLLBACK TRAN
print 'retry' + CAST(@tryCount as varchar(max))
print XACT_STATE()
WAITFOR DELAY '00:00:01'
END
ELSE
SET @tryCount = 0
END CATCH
END

SET DEADLOCK_PRIORITY NORMAL

CREATE 
--ALTER
Proc MoveBToARetry(@id int)
AS
DECLARE @tryCount int
SET @tryCount = 3
WHILE @tryCount > 0
BEGIN
SET @tryCount = @tryCount - 1
BEGIN TRAN
BEGIN TRY
DECLARE @amount int
SELECT @amount = value from TableB WHERE id = @id
WAITFOR DELAY '00:00:20'
UPDATE TableA SET value = @amount where id = @id
COMMIT TRAN
SET @tryCount = 0
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRAN
IF ERROR_NUMBER() = 1205
BEGIN
print 'retry' + CAST(@tryCount as varchar(max))
print XACT_STATE()
END
ELSE
SET @tryCount = 0
END CATCH
END


CREATE 
--ALTER
Procedure MoveAToBRetryX(@id int)
AS
DECLARE @tryCount int
SET @tryCount = 3
Retry:
SET @tryCount = @tryCount - 1
BEGIN TRAN
BEGIN TRY
DECLARE @amount int
SELECT @amount = value from TableA WHERE id = @id
WAITFOR DELAY '00:00:20'
UPDATE TableB SET value = @amount where id = @id
COMMIT TRAN
END TRY

BEGIN CATCH
DECLARE @error_message NVARCHAR(MAX)
SET @error_message = ERROR_MESSAGE()
+ N' from procedure ' + ERROR_PROCEDURE()
+ ' line ' + CAST(ERROR_LINE() as NVARCHAR(5)) 
DECLARE @error_severity INT
SET @error_severity = ERROR_SEVERITY();
DECLARE @error_state INT
SET @error_state = ERROR_STATE();
IF @error_state = 0 SET @error_state = 1
IF ERROR_NUMBER() = 1205
BEGIN
IF XACT_STATE() <> 0 ROLLBACK TRAN
IF @tryCount > 0 GOTO Retry
END
ELSE
IF XACT_STATE() <> 0 ROLLBACK TRAN
SET @tryCount = 0
RAISERROR (@error_message, @error_severity, @error_state)
END CATCH



BEGIN CATCH
DECLARE @error_message NVARCHAR(MAX)
SET @error_message = ERROR_MESSAGE()
+ N' from procedure ' + ERROR_PROCEDURE()
+ ' line ' + CAST(ERROR_LINE() as NVARCHAR(5)) 
DECLARE @error_severity INT
SET @error_severity = ERROR_SEVERITY();
DECLARE @error_state INT
SET @error_state = ERROR_STATE();
IF @error_state = 0 SET @error_state = 1
IF XACT_STATE() <> 0 ROLLBACK TRAN
-- do error handling here

-- pass error back to client
-- if it cannot be handled
RAISERROR (@error_message, @error_severity, @error_state)
END CATCH




