Sql-server – Summary of locks for a query

You need exactly 3 lock hints

• READPAST
• UPDLOCK
• ROWLOCK

I answered this previously on SO: https://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition/940001#940001

As Remus says, using service broker is nicer but these hints do work

Your error about isolation level usually means replication or NOLOCK is involved.

The Profiler writes column is documented as

Number of physical disk writes performed by the server on behalf of the event.

Before the transaction is committed the log must be physically written to disc up to that point but those writes don't get included in the write stats reported Profiler.

For many inserts no physical writes may occur before the statement completes as the page is modified in the buffer cache and not written out to disc until later (e.g. by the checkpoint or lazy writer process).

For bulk inserts there is an eager writer so the pages may be written out to disc before . See Writing Pages in BOL for more.

However the above doesn't seem relevant as even when no pages are written to disc the physical writes can be reported as non zero.

In the test below Profiler reports 5 writes for the insert but monitoring the file writes with process monitor and sys.dm_io_virtual_file_stats shows that none actually occurred. From which I conclude that the physical writes column actually shows the number of pages made dirty (on the grounds that these will need to be written out to disc later)

So based on that assumption SQL Profiler shows 0 Writes in your test because you are inserting to a page that is already dirty.

TRUNCATE TABLE Foo

CHECKPOINT;

DBCC DROPCLEANBUFFERS;

/*Returns 0 rows*/
SELECT *
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = DB_ID()
       AND is_modified = 1

SELECT 'Before Insert', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

INSERT INTO Foo
            (ForeignID,
             FileExtension,
             MimeType)
VALUES      ( 1,
              'FOO',
              'Application/Foo')

/*Nothing Written. Figures same as previous query*/
SELECT 'After Insert', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

/*Returns 5 rows*/
SELECT *
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = DB_ID()
       AND is_modified = 1

CHECKPOINT;

/*Shows physical writes*/
SELECT 'After Checkpoint', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1) 

Profiler Trace

Process Monitor Writes after CHECKPOINT

So the CheckPoint actually physically wrote 8 pages (6 single pages and 1 double page) but shows up in Profiler as being responsible for only 4.

From the offsets in the Process Monitor screenshot it is possible to see exactly what pages were written to

+-----+---------------+-----------+
|  57 | syscolpars    | DATA_PAGE |
|  86 | sysrscols     | DATA_PAGE |
|  93 | sysrowsets    | DATA_PAGE |
| 143 | sysallocunits | DATA_PAGE |
| 168 | Foo           | DATA_PAGE |
| 169 | Foo           | IAM_PAGE  |
|   1 |               | PFS_PAGE  |
|   9 |               | BOOT_PAGE |
+-----+---------------+-----------+

The three additional pages not shown in the output from dm_os_buffer_descriptors are the database boot page and sysrscols, sysrowsets.

The write of the database boot page is correlated with the CHECKPOINT itself as the dbi_checkptLSN field gets updated.

It would be expected that the insert would cause the other two pages to be updated (The rcmodified and rcrows columns respectively) but this does not appear to happen immediately and I see no LOP_COUNT_DELTA entries in the log file either so I assume this must happen periodically rather than after every modification.

I'm not sure how to account for 9 writes being reported but only 8 occurring. Perhaps one page was double counted.