Option to Control Wait Behavior for Uncommitted Inserts

The configuration option wait on uncommitted insert allows you to control the wait behavior of the update,insert, and delete commands for an uncommitted insert.

wait on uncommitted insert

Default value 0 (off)
Range of values 0 (off), 1 (on)
Status Dynamic
Display level Comprehensive
Required role System Administrator
Configuration group 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.