NESTING_TRANSACTION_READONLY

(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

(Back to main page…)

Description:

This latch class, along with NESTING_TRANSACTION_FULL, is used to control access to the transaction description structure (called an XDES) for the parent transaction of a parallel nested transaction. A query that involves a parallel operator must start a sub-transaction for each parallel thread that is used, so these transactions are sub-transactions (called parallel nested transactions) of the main transaction. The _READONLY is for a transaction that’s read-only, i.e. it hasn’t changed the database.

This latch class replaced the NESTED_TRANSACTION_READONOY latch class from SQL Server 2008 onward.

(Books Online description: “Internal use only.”)

Questions/comments on this latch class? Click here to send Paul an email, especially if you have any information to add to this topic.

Added in SQL Server version:

2008

Removed in SQL Server version:

N/A

Extended Events latch_class value:

Maps to the TRAN_NESTING_RO map_value in sys.dm_xe_map_values.

The map_key value in sys.dm_xe_map_values is 112 in 2008 and 2008 R2, and 109 in 2012 and 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.

Other information:

These latches indicate that parallelism is happening, so for contention issues, follow the general steps for troubleshooting parallelism problems (see the CXPACKET wait type). I’ve heard anecdotal evidence that these latches can be problematic under certain circumstances when RCSI (read-committed snapshot isolation) is enabled, and there is a known bug (fixed) in SQL Server 2012 and 2014 that is described in KB 2806979.

See the LATCH_EX wait type for more information about latches in general and how to troubleshoot them.

Known occurrences in SQL Server (list number matches call stack list):

1. Creating a rowset (a way of transferring rows from the Storage Engine to the Query Processor) (in this case, for a parallel table scan)
2. Creating a rowset (in this case, as part of processing a sort for a top-N select)

And many, many more similar stacks.

Abbreviated call stacks (list number matches known occurrences list):

1. XeSqlPkg::latch_suspend_end::Publish+138
   LatchBase::Suspend+16b8
   LatchBase::AcquireInternal+415
   ParNestedXact::GetXdesInternal+f8
   GetTableCursor+e1
   GetQETableCursor+21a
   CQScanRowsetNew::ParallelGetRowset+6cf
   CQScanTableScanNew::CQScanTableScanNew+12b
   CXteTableScan::QScanGet+af
   CBpXte::PqsInitializeBatchProcessingTree+43
   CQScanXProducerNew::CQScanXProducerNew+251
   FnProducerThread+78f
   SubprocEntrypoint+a7f
   SOS_Task::Param::Execute+21e
   SOS_Scheduler::RunTask+a8
2. XeSqlPkg::latch_suspend_end::Publish+138
   LatchBase::Suspend+16b8
   LatchBase::AcquireInternal+415
   ParNestedXact::GetXdesInternal+f8
   OpenRowsetSS::OpenWorkRowset+e6
   GetTableCursor+81
   CQPWorkTable::GetIRowsetAndWakeup+2c1
   CHashWorktablePartitionGroup::CreateWorktable+36f
   CHashWorktablePartitionGroup::CHashWorktablePartitionGroup+fa
   CHashWorktablePartitionGroupCompile::PhpgCreatePartitionGroup+67
   CQScanHash::CQScanHash+3ed
   CXteHash::QScanGet+82
   CQScanSortNew::CQScanSortNew+cf
   CQScanTopSortNew::CQScanTopSortNew+28
   CXteTopSort::QScanGet+a5
   CBpXte::PqsInitializeBatchProcessingTree+43
   CQScanXProducerNew::CQScanXProducerNew+251
   FnProducerThread+78f
   SubprocEntrypoint+a7f
   SOS_Task::Param::Execute+21e
   SOS_Scheduler::RunTask+a8