July 15, 2009 at 5:20 pm
Hello,
I have a question regarding distributed transactions. I have 2 SQL servers, each linked to the other. I've created a distributed transaction that updates a table on each database and would like to know if I am on the right path.
On Server A, I have the following stored proc:
CREATE PROCEDURE spX
@StrValue VARCHAR(5)
@StrKey VARCHAR(5)
AS
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
UPDATE myTable SET colValue=@StrValue where colKey=@StrKey
IF (@@ERROR <> 0) GOTO ERROR_HANDLER
EXEC [SERVER-B].[DB].[OWNER].[spY] @StrValue, @StrKey
IF (@@ERROR <> 0) GOTO ERROR_HANDLER
IF (@@TRANCOUNT > 0)
BEGIN
COMMIT TRANSACTION
END
SET XACT_ABORT OFF
SET NOCOUNT OFF
RETURN 0
ERROR_HANDLER:
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION
END
SET XACT_ABORT OFF
SET NOCOUNT OFF
RETURN 1
And on Server B, I have the following stored proc:
CREATE PROCEDURE spY
@StrValue VARCHAR(5)
@StrKey VARCHAR(5)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
UPDATE myTable SET colValue=@StrValue where colKey=@StrKey
IF (@@ERROR <> 0) GOTO ERROR_HANDLER
COMMIT TRANSACTION
SET NOCOUNT OFF
SET XACT_ABORT OFF
RETURN 0
ERR_HANDLER:
ROLLBACK TRANSACTION
SET NOCOUNT OFF
SET XACT_ABORT OFF
RETURN 1
Now, my question is on stored proc spY in Server B, is it necessary that i use TRANSACTION (that is, begin, commit and rollback)? Or can I simply have the UPDATE statement and be done with it?
Also, are any locks imposed on myTable in Server B during this transaction? This is my main concern as I want to make sure applications accessing Server B won't be affected (deadlocks for instance).
July 16, 2009 at 6:50 am
Yeah, you can just leave that as is. You don't need to add another transaction to it.
It's an update statement and it's within a distributed transaction, so yeah, you're going to see locking, which will lead to blocking, but not necessarily deadlocks. Locking is pretty unavoidable when you're changing data. But assuming that nothing strange or odd is going on, that doesn't look like a procedure that will, in and of itself, lead to deadlocks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply