Dead ends - Does it really help?

Question

So, I have a request that is delaying me. People who know the system well cannot understand why the sproc is inhibited, but they tell me that I should just add this to it:

SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

Is this really an acceptable solution? What does it do?

Answer 1

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

This will cause the system to return inappropriate data, including duplicate records and missing records. Read more about Previously received lines can be skipped if the NOLOCK hint is used or here, in Timebomb there is a problem of consistency with NOLOCK / READ UNCOMMITTED .

Dead ends can be investigated and corrected, it does not really matter if you follow the correct procedure. Of course, throwing a dirty reading may seem easier, but along the way you will sit for a long time, looking at your general book and wondering why the hell is he doing not a balance of debits and credits. So read again until you really forget this: DIRTY READS INCONSISTENT READS .

If you want to get a distribution card, turn on snapshot isolation :

 ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON 

But keep in mind that isolation of snapshots does not fix deadlocks, it only hides them. A proper investigation of the cause and elimination of the impasse is always a suitable action.

Answer 2

NOCOUNT will save your request from returning the number of rows back to the calling application (i.e., 1,000,000 affected rows).

HAZARD IMAGE DEVELOPMENT PROCEDURE NEEDED TO DOWNLOAD, which allows you to read dirty data, as indicated here.

An isolation level may help, but do you want to allow dirty reads?

Answer 3

The best guide:

http://technet.microsoft.com/es-es/library/ms173763.aspx

Excerpt:

Indicates that statements can read rows that have been modified by other transactions but not yet committed.

Transactions performed on READ The UNCOMMITTED level does not issue a general lock to prevent other transactions from changing the data read by the current transaction. READ REQUIRED transactions also do not block exclusive locks, which are the current transaction from read lines that have been changed, but not committed by other transactions. when this option is set, you can read incomplete changes, which are called dirty reads. Values ​​in the data can be changed, and rows can appear or disappear in the data set until the transaction is completed. This parameter has the same effect as setting NOLOCK in all tables in all SELECT statements in a transaction. This is the least restrictive isolation level.

In SQL Server, you can also minimize contention blocking while protecting a transaction from dirty uncommitted data changes using either:

The isolation level is READ COMMITTED with the READ_COMMITTED_SNAPSHOT database parameter set to ON. SNAPSHOT isolation level

.

Answer 4

Accidentally adding SET parameters to a query is unlikely to help me be afraid

 SET NOCOUNT ON 

Will not affect the problem.

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

will prevent your request from removing common locks. Besides reading dirty data, it can also cause your query to read the same lines twice or not depend on what other parallel activity is happening.

Whether this solves your deadlock problem depends on the type of deadlock. This will have no effect if the problem is two authors' entries due to non-linear ordering of lock requests. (transaction 1 updates row a, transaction 2 updates row b, then tran 1 requests a lock on b and tran 2, asking for a lock on a)

Can you post an abusive request and deadlock schedule? (if you are on SQL 2005 or later)

Answer 5

On the other hand, there are two more aspects that can help.

1) Indexes and indexes used by SQL. The indexing strategy used in the tables affects the number of rows. If you make data changes using a unique index, you can reduce the likelihood of locks.

One algorithm - of course, it will not work in all cases. Using NOLOCK is intended, not globally.

 The "old" way: UPDATE dbo.change_table SET somecol = newval WHERE non_unique_value = 'something' The "new" way: INSERT INTO #temp_table SELECT uid FROM dbo.change_table WITH (NOLOCK) WHERE non_unique_value = 'something' UPDATE dbo.change_table SET somecol = newval FROM dbo.change_table c INNER JOIN #temp_table t ON (c.uid = t.uid) 

2) Transaction Duration The longer the transaction is open, the more likely it is to dispute. If there is a way to reduce the time that records remain locked, you can reduce the likelihood of a deadlock. For example, execute as many SELECT statements (for example, lookup) at the beginning of the code, and not perform INSERT or UPDATE, then find, then INSERT, and then another search. Here you can use the NOLOCK hint for SELECT on "static" tables that do not change, reducing the blocking of the "footprint" code.