This Article is written to help to address TXE latch contention. It provides some of the reasons that a TXE latch is used under. It is by no means the all-encompassing list, but a good start. At the end is a an example of a worst case scenario.
The TXE latch is called during transaction end processing. It can also be called in conjunction with a MTX (microtransaction). The TXE latch is called at the beginning of MTX processing. The TXE latch is fetched every time a "logical operation" happens.
A logical operation constitutes:
- Schema change adding table or index.
- Splitting an index.
- Deleting an index entry.
- Deleting a key in a key field.
- Creating a record that spans 2 or more blocks.
- Updating a record that spans 2 or more blocks.
- Anything else that constitutes updating of 2 or more blocks.
Worst Case Scenario:Creating a record that is larger than the database block size will force the TXE latch. If the BI file needs to extend then we will hold the TXE latch while the BI file extends. To do this we may have to go to the OS to get the disk space. If we have to extend the database as well, then we will hold the same TXE while the DB extends, again going to the OS to get the disk space to extend.
In this scenario we have had to go to the OS to get the disk space twice before we have even started writing the record to the database.
If the client (or batch) session terminates abnormally at this stage, database corruption will ensue and necessarily result in corrective database actions, as the transaction can not be undone during crash recovery, because the BI note required for the undo indicates a record of one size and the record that needed to be undone reports a different size.
