problems with replication while rolling back nested transactions

Replication appears to be reading rolled back transactions. We are seeing
rows on our subscriber that do not exist on our publisher. The data on the
publisher is what is expected, the rolled back rows appearing on the
subscriber obviously are not.
Here's the basic structure of my two stored procs. They are done this way
because, depending on business rules, I can call the inner proc by itself or
from the outer.
sp_outer
{ basics of the outer
SET IMPLICIT_TRANSACTIONS OFF
Begin Transaction
Save Transaction Group
While more deals
Call sp_inner
{ basics of the inner
SET IMPLICIT_TRANSACTIONS OFF
Begin Transaction
Save Transaction Individual
work...
If Fail ROLLBACK Individual
If Pass COMMIT
}
End
If Any Fail in inner ROLLBACK Group outer
Else COMMIT outer
}
Problem is occuring when outer transaction rolls back. From on the
sqlserver docs "Committing inner transactions is ignored by Microsoft? SQL
Server. The transaction is either committed or rolled back based on the
action taken at the end of the outermost transaction. If the outer
transaction is committed, the inner nested transactions are also committed.
If the outer transaction is rolled back, then all inner transactions are also
rolled back, regardless of whether or not the inner transactions were
individually committed."
Again, what we see on our publisher is as expected. Subscribers having
different rows are the issue.
The chart at the bottom shows different ordering of the rollbacks and
commits and establishes a pattern of the problem occurs with commited
transactions prior to or between two rolled back transactions.
This sure looks like a replication bug as the publisher behaves as expected.
What do you think?
Exists on PublisherExists on Subscriber
Begin Outer
Rollback InnerNoNo
Commit InnerNoYes
Commit InnerNoYes
Rollback InnerNoNo
Rollback Outer
Begin Outer
Commit InnerNoYes
Rollback InnerNoNo
Commit InnerNoYes
Rollback InnerNoNo
Rollback Outer
Begin Outer
Rollback InnerNoNo
Commit InnerNoYes
Rollback InnerNoNo
Commit InnerNoNo
Rollback Outer
Begin Outer
Rollback InnerNoNo
Rollback InnerNoNo
Commit InnerNoNo
Commit InnerNoNo
Rollback Outer
Begin Outer
Commit InnerNoNo
Commit InnerNoNo
Commit InnerNoNo
Rollback InnerNoNo
Rollback Outer
Begin Outer
Rollback InnerNoNo
Commit InnerNoNo
Commit InnerNoNo
Commit InnerNoNo
Rollback Outer
TPerry,
I admire your methodicalness (if this is a proper word) and agree that this
looks confusing
If we compare

> Begin Outer
> Rollback Inner No No
> Commit Inner No Yes
> Commit Inner No Yes
> Rollback Inner No No
> Rollback Outer
and

> Begin Outer
> Rollback Inner No No
> Commit Inner No No
> Commit Inner No No
> Commit Inner No No
> Rollback Outer
Why should the first and second commits should be different? Interesting!
I can't test all this right now, but one thing I did test in the past was
that setting the transaction isolation level as serializable and setting
xact_abort on before running the transactions altered the behaviour; if code
in a stored procedure was rolled back on the publisher, it wasn't sent to
the subscriber. Please can you test your scenario under these conditions to
see if this fixes your issue.
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||If a transaction is logged, it will be replicated.
If the transaction is not committed the log reader can't read it, and hence
it will not be replicated.
If you can isolate a sequence of transactions that cause this phenomena you
could use sp_browsereplcmds to see if they make it to the distribution
database. If so they will be replicated.
It sounds like to me there is a logical error somewhere in your proc.
"tperry" <tperry@.discussions.microsoft.com> wrote in message
news:D2DF5BE8-66E0-45E6-891D-DD61BD23EBD3@.microsoft.com...
> Replication appears to be reading rolled back transactions. We are seeing
> rows on our subscriber that do not exist on our publisher. The data on
> the
> publisher is what is expected, the rolled back rows appearing on the
> subscriber obviously are not.
> Here's the basic structure of my two stored procs. They are done this way
> because, depending on business rules, I can call the inner proc by itself
> or
> from the outer.
> sp_outer
> { basics of the outer
> SET IMPLICIT_TRANSACTIONS OFF
> Begin Transaction
> Save Transaction Group
> While more deals
> Call sp_inner
> { basics of the inner
> SET IMPLICIT_TRANSACTIONS OFF
> Begin Transaction
> Save Transaction Individual
> work...
> If Fail ROLLBACK Individual
> If Pass COMMIT
> }
> End
> If Any Fail in inner ROLLBACK Group outer
> Else COMMIT outer
> }
> Problem is occuring when outer transaction rolls back. From on the
> sqlserver docs "Committing inner transactions is ignored by Microsoft?
> SQL
> Server. The transaction is either committed or rolled back based on the
> action taken at the end of the outermost transaction. If the outer
> transaction is committed, the inner nested transactions are also
> committed.
> If the outer transaction is rolled back, then all inner transactions are
> also
> rolled back, regardless of whether or not the inner transactions were
> individually committed."
> Again, what we see on our publisher is as expected. Subscribers having
> different rows are the issue.
> The chart at the bottom shows different ordering of the rollbacks and
> commits and establishes a pattern of the problem occurs with commited
> transactions prior to or between two rolled back transactions.
> This sure looks like a replication bug as the publisher behaves as
> expected.
> What do you think?
>
> Exists on Publisher Exists on Subscriber
> Begin Outer
> Rollback Inner No No
> Commit Inner No Yes
> Commit Inner No Yes
> Rollback Inner No No
> Rollback Outer
> Begin Outer
> Commit Inner No Yes
> Rollback Inner No No
> Commit Inner No Yes
> Rollback Inner No No
> Rollback Outer
> Begin Outer
> Rollback Inner No No
> Commit Inner No Yes
> Rollback Inner No No
> Commit Inner No No
> Rollback Outer
> Begin Outer
> Rollback Inner No No
> Rollback Inner No No
> Commit Inner No No
> Commit Inner No No
> Rollback Outer
> Begin Outer
> Commit Inner No No
> Commit Inner No No
> Commit Inner No No
> Rollback Inner No No
> Rollback Outer
> Begin Outer
> Rollback Inner No No
> Commit Inner No No
> Commit Inner No No
> Commit Inner No No
> Rollback Outer
>