Can I run an update query against my SQL db while in READ UNCOMMITTED isolation level?

Currently I have with (nolock) specified after each table in all of the select statements within my stored procedure.

If I add the following code to the top of my stored procedure, can I still run an update/insert/delete query in my stored procedure?

set trans开发者_运维问答action isolation level READ UNCOMMITTED

---

Yes, but the update/insert/delete won't and can't be "dirty"

READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

It goes on to say that this "feature" is deprecated anyway

With NOLOCK on the SELECTs, this means locks are neither issued nor observed. From same link

No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data.

So:

• your own SELECTs do not issue shared locks
• you ignore other locks (eg dirty reads)

As to issuing NOLOCK everywhere, do you know why you do it? I or any other high rep user here would recommend against it...

• Is the NOLOCK (Sql Server hint) bad practice?
• Using NOLOCK Hint in EF4?
• Is there a way to get different results for the same SQL query if the data stays the same?
• Is NOLOCK the default for SELECT statements in SQL Server 2005? (note the "authority" site is wrong)

You should consider snapshot isolation (or fixing the indexes/queries) if you have too many proven blocking reads

---

Yes, you can. Isolation levels do not mean you can't update/insert/delete.

Isolation levels are more about ACID - about what gets locked and what is visible to other sessions.

From wikipedia, Isolation (database systems):

In database systems, isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations.