Transaction – Data, Design, and SQL Server

A little known and perhaps underappreciated feature of SQL Server is the ability to assign a name and a description to a transaction. The name of the transaction is recorded in the transaction log, and the transaction is called a marked transaction. The syntax is BEGIN TRANSACTION TransactionName WITH MARK 'TransactionDescription' (the description is optional). The MSDN topic for BEGIN TRANSACTION has additional details. Marked transactions for all databases on the instance are also recorded in the dbo.logmarkhistory table in the MSDB database.

As the MSDN topic notes, one of the reasons to use marked transactions is to be able to restore a set of related databases, possibly residing on different SQL Server instances, to a logically consistent state. This could be achieved by using the STOPATMARK or STOPBEFOREMARK arguments of the RESTORE LOG statement. Another reason to use marked transactions, and perhaps a more common one, is to simplify recovery and minimize potential data loss when deploying database schema or data changes, i.e. during a release. The approach is to make all database changes in a marked transaction (the assumptions here are that all changes are transactional, and are sufficiently small to not cause concurrency or resource problems due to a large or long running transaction). If the change causes some unexpected results (also known as the “Oh no!” moment), it would be possible to RESTORE LOG … WITH STOPBEFOREMARK to restore the database to the point just before the erroneous transaction. This would be more accurate than restoring to a timestamp just before the change, which may not even be known.

None of this is really new or groundbreaking, and the main reason for this post is to describe some slightly unexpected results I noticed when testing a database change implemented with a marked transaction. The change script was completing successfully, however no marked transaction was recorded in the dbo.logmarkhistory table. This was puzzling, because I’ve used this approach before and did see the marks in that table. One difference in this case was that the target database was just created. The recovery model of the database was Full, but no full backup was taken yet, which means that the database was using the “pseudo-Simple” recovery model. Apparently, even though a marked transaction can be successfully committed against such a database (and the mark is actually recorded in the log, as seen in the output from fn_dblog()), it is not recorded in the dbo.logmarkhistory table. On reflection, this makes sense – if a database is still using the Simple recovery model, then it is not possible to restore the database to a point in time, or to a log mark, anyway, so recording a marked transaction in the dbo.logmarkhistory table would be misleading. As soon as I took a full backup of the database, marked transaction started appearing in the dbo.logmarkhistory table.

Recently I was troubleshooting a blocking problem where a number of sessions were blocked, waiting to acquire a page latch. While blocking was occurring, there were several rows in the output from sysprocesses that looked like this (only relevant columns are shown):

spid status blocked open_tran waitresource cmd lastwaittype

—— ———- ——- ——— ————- ——- ————-

1001 suspended 1294 2 8:6:792624 INSERT PAGELATCH_UP

In this case, session 1001, executing an INSERT statement, is waiting to acquire a latch on page 792624, which happens to be a PFS page (792624 / 8088 = 98, a whole number of PFS intervals). While this may provide a clue as to the cause of blocking, this is not the main topic of this post.

Note that the value in the open_tran column is 2. The open_tran column is described in documentation as the “number of open transactions for the process.” The intuitive conclusion from this is that session 1001 has two explicit transactions open, one nested in the other. However, this system uses stored procedures exclusively, and a review of all stored procedures that insert rows did not find any code that used explicit nested transactions.

After some research, I found that explicit nested transactions are not the only reason why the transaction count can be greater than 1 during execution of a DML statement. Consider the following code fragment:

CREATE TABLE T1

(

Col1 int

);

INSERT INTO T1

SELECT @@TRANCOUNT;

SELECT Col1 FROM T1;

UPDATE T1 SET

Col1 = @@TRANCOUNT

WHERE Col1 = 2;

SELECT Col1 FROM T1;

DELETE

FROM T1

WHERE Col1 = @@TRANCOUNT;

SELECT Col1 FROM T1;

Here’re the results, with comments added after the output from each statement:

(1 row(s) affected)

^^ INSERT statement ^^

Col1

———–

(1 row(s) affected)

^^ first SELECT statement ^^

(1 row(s) affected)

^^ UPDATE statement ^^

Col1

———–

(1 row(s) affected)

^^ second SELECT statement ^^

(1 row(s) affected)

^^ DELETE statement ^^

Col1

———–

(0 row(s) affected)

^^ third SELECT statement ^^

This shows that during execution of a DML statement that is not within any explicit transaction, there are actually two open transactions reported. The results are the same if instead of @@TRANCOUNT we use the open_tran column from sysprocesses, or the open_transaction_count column from the sys.dm_exec_requests DMV. Effectively, in addition to the one transaction always associated with any DML statement, there is another nested transaction opened internally by SQL Server, lasting for the duration of statement’s execution. This behavior occurs on all recent versions of SQL Server, starting with SQL Server 2000 (I did not test on older versions).

To be clear, the second transaction is open only while a DML statement is executing. The @@TRANCOUNT function (as well as sysprocesses and sys.dm_exec_requests) behaves as expected if used in a non-DML statement in procedural T-SQL code, which is the typical use case.

So as a practical matter, if you see the number of reported open transactions that is greater than expected, consider the context where that number was obtained, before concluding that it must be due to explicit nested transactions being used.

Tag: Transaction

Marked transactions

Transaction count during DML statements