INF: Optimizer Hint NOLOCK or Isolation Level READ UNCOMMITTED Generates Error 605

• Microsoft SQL Server versions 7.0, 6.0, 6.5

SUMMARY

Either running a query with the Optimizer hint NOLOCK or setting the transaction isolation level to READ UNCOMMITTED, may generate transient 605 error messages.

MORE INFORMATION

Normally access to data that is being altered by either another user or process is denied because of locks placed on the data. However, the NOLOCK and READ UNCOMMITTED commands allow a query to read data that is locked by another user. This is referred to as a dirty read because it is possible to read values that have not yet been committed and are subject to change.

When a query using NOLOCK or READ UNCOMMITTED attempts to read data that is in the process of being moved or altered by another user, a 605 error occurs. The severity level of 605 errors occurring during a dirty read operation is 12 versus a severity level of 21 when reading committed data. If a 605 error with a severity level of 12 occurs, it is most likely a transient 605 error and does not indicate a database consistency problem. To verify that it is a transient 605 error, rerun the query at a later time.

If the error persists, remove the NOLOCK hint or set the transaction isolation level to READ COMMITTED and verify that a 605 severity level 21 does not occur. A 605 level 21 error indicates possible database corruption. If a 605 level 21 error occurs, refer to SQL Server Books Online for more information and contact your primary support provider for further assistance.

Additional query words:

Keywords : kbSQLServ600 kbSQLServ650 kbSQLServ700
Version : winnt:6.0,6.5,7.0
Platform : winnt
Issue type : kbinfo