Distributed
Transactions
A transaction within a single instance of
the Database Engine that spans two or more databases is actually a distributed
transaction. The instance manages the distributed transaction internally; to
the user, it operates as a local transaction.
A distributed commit must be managed
differently by the transaction manager to minimize the risk that a network
failure may result in some resource managers successfully committing while
others roll back the transaction. This is achieved by managing the commit
process in two phases (the prepare phase and the commit phase), which is known
as a two-phase commit (2PC).
Prepare phase
When
the transaction manager receives a commit request, it sends a prepare command
to all of the resource managers involved in the transaction. Each resource
manager then does everything required to make the transaction durable, and all
buffers holding log images for the transaction are flushed to disk. As each
resource manager completes the prepare phase, it returns success or failure of
the prepare to the transaction manager.
Commit phase
If
the transaction manager receives successful prepares from all of the resource
managers, it sends commit commands to each resource manager. The resource
managers can then complete the commit. If all of the resource managers report a
successful commit, the transaction manager then sends a success notification to
the application. If any resource manager reported a failure to prepare, the
transaction manager sends a rollback command to each resource manager and
indicates the failure of the commit to the application.
Database Engine
applications can manage distributed transactions either through Transact-SQL or
the database API.
Transact-SQL
Process
The distributed
transactions started in Transact-SQL have a relatively simple structure:
- A Transact-SQL script or application connection
executes a Transact-SQL statement that starts a distributed transaction.
- The instance of the SQL Server Database Engine
executing the statement becomes the controlling server in the transaction.
- The script or application then executes either
distributed queries against linked servers or remote stored procedures
against remote servers.
- As distributed queries and remote procedure calls are
made, the controlling server automatically calls Microsoft Distributed
Transaction Coordinator (MS DTC) to enlist the linked and remote servers
in the distributed transaction.
- When the script or application issues either a COMMIT
or ROLLBACK statement, the controlling instance of SQL Server calls MS DTC
to manage the two-phase commit process or to notify the linked and remote
servers to roll back their transactions.
·
Start a distributed transaction.
·
You need to enable rpc and rpcOut options if you are using link
server.
·
Perform distributed queries against linked servers or execute
remote procedure calls against remote servers.
·
Call the standard Transact-SQL COMMIT TRANSACTION, COMMIT WORK,
ROLLBACK TRANSACTION, or ROLLBACK WORK statements to complete the transaction.
Step1: Configure your remote Procedure transaction. You
need to enable it on both databases
SP_CONFIGURE
'REMOTE PROC TRANS', 1
RECONFIGURE WITH OVERRIDEStep2: Change following options from “False” to “True” in link server settings
EXEC
master.dbo.sp_serveroption @server=N'192.168.25.26', @optname=N'rpc',
@optvalue=N'true'
GO
EXEC
master.dbo.sp_serveroption @server=N'192.168.25.26', @optname=N'rpc out',
@optvalue=N'true'
GOTo understand detail about link server please have a look at this msdn link
Step2: Change following options from “False” to “True” in link server settings
http://msdn.microsoft.com/en-us/library/ff772782.aspx
Step3:
a). Create your procedure on first database as per your requirement. You can call query directly by using AT clause to other database like this
Declare @vstr nvarchar ( 4000);
set @vstr = 'delete from System.dbo.Lookup
where id in (1,2,3)’
exec (@vstr ) AT [LinkServer]b). Create your procedure on first and second database as per your requirement. You can call procedure from one database to other as follows
Declare @vstr nvarchar ( 4000);
set @vstr = 'System.dbo..update_addr '
exec @vstr
p1,p2 -- ParameterValues
Step4: You must apply transaction. Distributed transaction
can be applied specifically with Distributed word if not mention. Engine will
automatically check weather this transaction is distributed or not and it
applies distributed transaction at its own. Complete sample code is as follows
===========================================================================
Create PROCEDURE [dbo].[DeleteUsers]
@UserID int
AS
BEGIN
SET NOCOUNT
ON;
Declare @vstr nvarchar ( 4000);
-- Start
distributed Transaction
BEGIN DISTRIBUTED TRANSACTION;
-- 2. Change
local author information
delete from
[DeleteUsers] where userid in (@UserID)
-- 3. Delete
data from remote server using link server
set @vstr = 'delete from
System.dbo.Lookup where id in ' + cast(@UserID as nvarchar(15))
exec (@vstr ) AT [LinkServer]
-- 4. Commit the
DTC transaction
COMMIT TRANSACTION;
END
===========================================================================
If you want to
look at the example to call procedure look at the code below or go to this link
============================================================================
/*******************************************************/
/* Using the
server configuration parameter for */
/* implicit
server-initiated transactions. */
/*******************************************************/
CREATE PROCEDURE change_addr(@au_id
varchar(11), -- author ID
@addr varchar(40), -- new address
@toserver varchar(12) ) -- server name
AS -- procedure follows
declare @execstr varchar(200)
-- 1. Start a
Transaction. This transaction will become
-- a DTC distributed transaction when the remote
-- stored procedure is called because the server
-- configuration parameter is set.
BEGIN TRANSACTION
-- 2. Change
local author information
update authors set address = @addr where au_id = @au_id
-- 3. Make a
string with the server name and procedure
-- to execute
select @execstr = @toserver '.pubs..update_addr
'
-- 4. Update
remote server.
-- ( Note that these servers must be added to
each other
-- via sp_addserver and sp_addremotelogin )
exec @execstr
@au_id, @addr
-- 5. Commit the
DTC transaction
COMMIT TRANSACTION
/*******************************************************/
/* Stored
procedure to update an author's address on
*/
/* each remote
server. */
/*******************************************************/
CREATE PROCEDURE update_addr(@au_id
varchar(11), -- author ID
@addr varchar(40) ) -- new address
AS -- procedure follows
update authors set address
= @addr where
au_id = @au_id
===========================================================================
2 comments:
I was suggested this website by my cousin. I am not sure whether this post
is written by him as nobody else know such detailed about my trouble.
You're incredible! Thanks!
Also see my webpage :: Recovery for exchange server
To remember i have received this error message every time i need to set RPC options
"Transaction context in use by another session"
Post a Comment