Databases Reference
In-Depth Information
HEADER INFORMATION
m_slotCnt=2
m_freedata=126
HEADER
INFORMATION
(1,100)
(3,300)
FIGURE 7-14
This scenario rel ects one of the prime uses for latches — serializing writes to prevent lost
updates.
As mentioned before, you won't be able to repeat this demonstration. SQL Server wouldn't let you.
In order to present it here, the output had to be massaged. Now take a look at what actually
happens in a normal, i.e., latched, scenario. This you will be able to repeat.
With Latching
When these same steps are performed on a real SQL Server database, the behavior is affected by
latches.
When Transaction 1 (2,200) gets to the page of memory, it acquires a latch. This is an EX latch, which
you'll learn more about soon. A moment later, however, Transaction 2 (3,300) will also want an EX latch,
which it won't be able to get. It has to wait for Transaction 1 to i nish its business with that page
(though not the whole transaction), and you will begin to see waits in
sys.dm_os_wait_stats
showing this.
With latches, Transaction 1 holds the EX latch for as long as it is needed to both write the row and
update the page header and offset. Only then does it release the latch and allow another transaction
in. Because of this, the page is never seen in the state shown in Figure 7-10 earlier.
Note that the 2,200 transaction does not wait for the completion of its transaction before releasing
the latch. The latch isn't tied to the transaction in that sense. It's not a lock, designed to protect the
integrity of the transaction; it's a latch, designed to protect the integrity of the memory. Handling
the lock behavior, snapshot versions, and so on — that's all separate from this, but it may increase the
amount of work that needs to be done by the process that has taken out the latch.
Once the latch has been released, the 3,300 transaction can get in with its own EX latch and insert
its row of data, updating the header and offset accordingly, as seen in Figure 7-15 and
Figure 7-16.
Search WWH ::
Custom Search