January 15, 2009 at 9:09 am
When using a marked transaction across databases how can I guaranteed that the marker will be mark across all log backup at the point where no other transaction could jeopardize the state one of the databases once restored.
In other word, what I fear is on database A the marker is set before the end of part of transaction X on A while on database B the marker is set after the end of transaction X on B. (Transaction X ends between the log mark of database A and B and have an impact on both. Thus when restored, database A would not be in line with database B for transaction X)
I've read this: but I don't understand where that feature guaranteed consistency across databases.
January 15, 2009 at 9:59 am
Ok I've found something
Two-Phase Commit
Committing a distributed transaction occurs in two phases: prepare and commit. When a marked transaction is committed, the commit log record for each database in the marked transaction is placed in the log at a point where there are no in-doubt transactions in any of the logs. At this point, it is guaranteed that there are no transactions that appear as committed in one log, but not committed in another log.
The following steps accomplish this during the commit of a marked transaction:
1. Prepare phase of a marking transaction stalls all new prepares and commits.
2. Only commits of already prepared transactions are allowed to continue.
3. Marking transaction then waits for all prepared transactions to drain (with time-out).
4. Marked transaction is prepared and committed.
5. The stall of new prepares and commits is removed.
So I presume that anything that has not yet started before any mark point is delayed (the stall they are talking about)
Those running are granted a short period of time (the time-out they are talking about, how long is it?) to complete (otherwise what? are they dropped from the backup?)
I believe my fear would come from a poorly written batch that run multiple queries not in a transaction that overall impact consistency of multiple databases and a backup occurs meanwhile. A consistency issue could occurs but wouldn't if the whole batch would be in a transaction.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply