Single database transactions

Baseline 1 was executed against a single database. The entries from the transaction log were then read. Criteria:

1. The stored procedure was run from DB1 (DBID 7).
2. This transaction is on a single database, NOT a CDT.
3. This is a successfully committed transaction.

Image:Baseline 1

This gives us the entries in the transaction log on a standard non-CDT transaction. Using this, it is possible to compare the entries for a CDT and a non-CDT transaction.

From reading these, we can see that single database transactions are straightforward; the log will start the transaction, indicated by “LOP_BEGIN_XACT”.

NOTE: LOP_BEGIN_XACT isn’t actually entered in the log until the code reaches its first IUD (Insert / Update / Delete).

Once the IUD statement has been executed, it will enter the relevant row in the transaction log to indicate the action carried out; in this case LOP_INSERT_ROWS.

Finally, when it reaches the commit statement in code, it will carry out the commit and enter LOP_COMMIT_XACT in to the transaction log. Those are the complete set of rows entered in the log for this type of transaction.

NOTE: [SPID] holds the identity of the connection used for this transaction. As SPIDs are reusable, this is not unique to this transaction.

The [Master DBID] and [Master XDESID] are currently NULL as these columns are not used in non-CDT transactions. They will become important later when looking at CDT.

Baseline 2 deals with a failed transaction and the entries made in the transaction log. Criteria:

1. The stored procedure was run from DB1 (DBID 7).
2. This transaction is on a single database, NOT a CDT.
3. The transaction was paused before commit and the SPID killed.
4. This is a failed, rolled back transaction.

Image:Baseline 2

Baseline 2 starts off with the same entries as Baseline 1; LOP_BEGIN_XACT and LOP_INSERT_ROWS. At this point the transaction fails and the entries in the transaction reflect this. First any statements that had run (insert rows in our case) are rolled back and then the transaction is aborted LOP_ABORT_XACT. The single database transaction rollback is important later when considering how CDT handles the same issue.