wait on uncommitted insert

Allows you to control the wait behavior of the update,insert, and delete commands for an uncommitted insert.

Summary Information
Default value	0
Range of values	0 (off), 1 (on)
Status	Dynamic
Display level	Comprehensive
Required role	System administrator
Configuration groups	SQL Server Administration

wait on uncommitted insert can only be used for DOL tables on transaction isolation 0, 1, and 2, and does not affect the behavior of transaction isolation 3.

When wait on uncommitted insert is set to 1:

at isolation level 0, delete and update queries block on uncommitted inserted rows with the key value of interest, and select queries read dirty and do not block on uncommitted inserts
at isolation levels 1 and 2, select, delete, and update queries, all block on uncommitted inserted rows

When wait on uncommitted insert is set to 0, the behavior of select, update, delete, and insert is the same as in previous releases.

Note: When wait on uncommitted insert is set to 1 (to wait for uncommitted inserted rows) concurrency might descend and deadlocks can be encountered where they were not encountered before.